Table of Contents

PostgreSQL

PostgreSQL is an open-source relational database written in C, it originally was designed as a successor to the Ingress database, but later gained SQL support and was renamed to PostgreSQL in 1996.

Installation

Install the postgresql package using Bulge. bulge i postgresql

If you're intending to run the PostgreSQL server, be sure to either reboot or run systemd-sysusers & systemd-tmpfiles –create before doing so.

Initial Configuration

Before you can start using the PostgreSQL server, you'll need to generate the database cluster as the postgres user: sudo -u postgres initdb -D /srv/pgsql/data

If successful, the output should look similar to below: 

The files belonging to this database system will be owned by user "postgres". 
This user must also own the server process. 
The database cluster will be initialized with locale "C". 
The default database encoding has accordingly been set to "SQL_ASCII". 
The default text search configuration will be set to "english". 
Data page checksums are disabled. 
fixing permissions on existing directory /srv/pgsql/data ... 
ok creating subdirectories ... 
ok selecting dynamic shared memory implementation ... 
posix selecting default max_connections ... 100 
selecting default shared_buffers ... 128MB 
selecting default time zone ... UTC 
creating configuration files ... ok 
running bootstrap script ... ok 
performing post-bootstrap initialization ... ok 
syncing data to disk ... ok 
initdb: warning: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. 
Success. You can now start the database server using: /usr/bin/pg_ctl -D /srv/pgsql/data -l logfile start

You'll now be able to start the server with the systemd service: systemctl start postgresql.service

Useful Configuration

Require password for login

Modify pg_hba.conf to set the authentication methods for each user you'd like to use password authentication (or modify “all” to affect all users) to scram-sha-256 or md5 (LESS SECURE, only for legacy applications). 

/srv/pgsql/data/pg_hba.conf

...
local all user scram-sha-256
...

You'll also need to modify postgresql.conf to set the password encryption method, it must be same as pg_hba.conf. 

/srv/pgsql/data/postgresql.conf

...
password_encryption = scram-sha-256
...

Restart the server using systemd systemctl restart postgresql.service and then set user passwords by running ALTER USER user WITH ENCRYPTED PASSWORD 'password'; in the PostgreSQL console.

Allow remote users to connect

In situations where you want database users to be able to connect over the network, and don't want to do SSH tunneling, you can configure PostgreSQL to accept remote connections.

It is highly recommended you also follow the Require password for login configuration steps.

Modify postgresql.conf to your needs: 

/srv/pgsql/data/postgresql.conf

...
listen_addresses='localhost,local_ip_address'
...

You can use * to listen on all addresses.

Then in pg_hba.conf add a line like the following: 

/srv/pgsql/data/pg_hba.conf

...
host all all ip_address scram-sha-256
...

You can accept all connections by using 0.0.0.0/0 as the IP address.

Reboot the server via systemd systemctl restart postgresql.service for the changes to take effect.

File Locations

Listed below are the PostgreSQL configuration files that are used by the server, if you need an sample configuration file for any reason, you can find them at /usr/share/postgresql/<file>.sample.