postgresql - user level install

Want to set-up a relational database and have browser access through localhost on our local machine. If successful, we can next learn cloud install or connect clusters. DBeaver is installed as our GUI admin tool. PgAdmin is good when running but often spawns lib errors. Regardless, the install portions of administration are CLI.

installation steps (2 hrs)

1. server (1 hour)

An entirely initalized PostgreSQL cluster has two parts, a database and a server. The server acts like Apache and manages connections. We start with that portion first, the databases need to be created from within the server. The one exception is when we run "initdb", a default database "postgres is created, so it can communicate with an admin tools.

Pacman installs the PostgreSQL server into a "bin" directory. This is fine. But, we want all configuration and data writing into our home directory, and we don't want to have to become another user or admin to run PostgreSQL. The idea is simple backup, portability, and user-level permissions; nothing root or weird PAM errors.

1a. install server

# pacman -S postgresql dbeaver

Postgresql install is about 51MB, not too bad. Service runs on port 5432 (change this in postgresql.conf ), and is not a systemctl service. At the user level we can start it, and stop it without becoming root.

1b. intitialize default cluster

Like any other application, I want to access all PostgreSQL files and tasks at the user level: never want to become root or change to another user. Yet when pacman installs Postgresql, it creates a "postgres" group in /etc/group and does not append $USER (eg. 'foo') to this group.

When initdb initializes PostgreSQL, it creates a default cluster and DB named "postgres" and puts them in /var/lib/postgres/data. No! I want the files in the home "foo" directory, like any other app.

Solution: 1) Append ourselves ("foo") to the "postgres" group in /etc/group. 2) run $ initdb and specify our home directory as the data repository.

# nano /etc/group [add self to postgrep group]
$ mkdir postgres
$ initdb -D /home/foo/postgres/data

1c. lockfile hack

There's a third permissions issue. When PostgreSQL runs at user-level, it is thwarted attempting to write a session lockfile into a root controlled /run/postgresql folder. To properly solve this error probably requires manipulating groups, but I simply changed the permissions on /run/postgresql to $USER. The problem disappeared.

# mkdir /run/postgresql/
# chown -R foo:foo /run/postgresql

1d. start server

With all the above accomplished, we can start the server as a user.

$ postgres -D '/home/foo/postgres/data'

1e. make permanent

We'd prefer not to have to delineate the data directory each time we start the posgres server. So we want set the path correctly. This initdb wiki notes the "PGDATA" variable added to the /home/foo/.bashrc file will make this permanent.

$ nano .bashrc
export PGDATA="/home/foo/postgres/data"

Logout and back in and then verify using "$ listenv". Now we can start our cluster (server) with $ postgres

1e. logs, tables, configuration

Unless modified in postgresql.conf, logs will appear in our home directory. Tables are in /home/foo/postgres/data.We can also configure the server, apart from the database and cluster, in /home/foo/postrgres/data/postgresql.conf. This would arrange which port we listen on, and other features. The security related conf is pg_hba.conf. There are others. All of these control the server, not the databases themselves

2. connect to defaults (10 minutes)

With a lot of work, our server environment has been completed. default "postgres" cluster and database were created by the initdb process. Let's leave them so we don't disrupt any administrative dependencies, but let's also move on to creating our own cluster and databases.

From the CLI, there is plenty we can arrange without dbeaver. DBeaver kicks butt once our databases are filled with data. For now though, to check our connection and create databases, we want to use the psql command. Our default username is "foo" when we connect because that's the user we ran initdb. The default database "postgres" however also must be called. So here is our connection command.

$ psql -U foo postgres

Again, there are three entitites: the cluster and the default DB are named "postgres", and the username is our own home directory name.

3. create clusters and databases (whatever no. of hours)

Our server environment is complete and our plans for data begin. We might have created a UML schema or it might be trial and error against a CSV or something else.

We're connected to the database through psql, so now we want to make a cluster and a database

BTW, there are many pages about dropping clusters and databases, and we can delete them from the data folder as well, so it's not that difficult to drop. Ok, but let's add.

4. dbeaver

Now that we're at step 3, we can add dbeaver to visualize our setup. Dbeaver is java and so needs JDBC compliant databases. Postgres is Type 4 java-compliant, inherently java-compatible. Dbeaver connects to a user at startup, 'postgres' by default, so change it to your $USER, eg "foo".

concepts

Roles and users

Somewhat confusing -- an imperfect analogy is that roles are governments and users are those operating within those governments. None of these will be created until we create a database. Just turning on the server ("postgres" command) does not create a database nor its associated roles.

postgres - users and roles  (22:52) E-Multi-Skills Database Tutorials, 2020. users can login, roles cannot. Role is a function, user is a client. Dbeaver seeks to connect to a role. It will operate as an agent so it nees a role with significant privileges.
postgres - roles, schema, security  (32:31) databasetorque, 2021. role can cally.
PBX - true overhead costs  (11:49) Rich Technology Center, 2020. Average vid, but tells hard facts. Asteriks server ($180) discussed.