Search
Varnish Controller

PostgreSQL

PostgreSQL is the database used by Varnish Controller. Brainz is the only process that interacts with the database. Hence, all Brainz instances need access to the database.

Installation

For PostgreSQL packages, we use the official distribution from postgresql.org, as this allows for the best ability to get supported patches for the correct versions. To do this, we integrate with the upstream repository. If a product like RedHat Satellite is used, that response can instead be integrated through Satellite, the installation of which is not within the scope of these instructions.

CentOS Installation

$ sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
$ sudo yum -y update
$ sudo yum -y install postgresql12-server postgresql12-contrib

A similar process applies to CentOS 8, but requires a different RPM and also requires disabling the built-in module. See https://www.postgresql.org/download/linux/redhat/ for details of this process.

Debian/Ubuntu Installation

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

sudo apt-get -y install postgresql-12

For more details, please see postgresql.org.

Initializing a default PostgreSQL cluster

The next step is to initialize a cluster and to set the database for automatic start (which is not done by default on Redhat/CentOS platforms).

$ sudo PGSETUP_INITDB_OPTIONS="-k -EUTF8 --locale=en_US.utf8" /usr/pgsql-12/bin/postgresql-12-setup initdb
Initializing database ... OK
$ sudo systemctl enable postgresql-12
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-12.service, pointing to /usr/lib/systemd/system/postgresql-12.service.
$ sudo systemctl start postgresql-12

Setting up for SSL

Proper SSL setup requires setting up at least a dedicated CA (which may only include the PostgreSQL server if necessary), but is outside the scope of this document. To enable basic encryption (but none of the other protections from SSL), a self-signed certificate can be used. Create this certificate by doing the following:

$ su - postgres -c 'openssl req -new -x509 -days 365 -nodes -text \
     -out /var/lib/pgsql/12/data/server.crt \
     -keyout /var/lib/pgsql/12/data/server.key \
     -subj "/CN=pgserver.domain.com"'

Basic configuration

To set up a few basic configurations, add the following to the end of /var/lib/pgsql/12/data/postgresql.conf:

listen_addresses='*'
ssl=on
ssl_min_protocol_version='TLSv1.2'
password_encryption='scram-sha-256'
shared_buffers='512MB'
work_mem='8MB'
maintenance_work_mem='256MB'
autovacuum_work_mem='128MB'
log_min_error_statement='warning'
log_line_prefix='%m [%p] %q%u@%d(%h) '
log_statement='ddl'

Do not make any other changes unless you are familiar with tuning PostgreSQL!

Start and enable service for autostart

$ sudo systemctl enable postgresql-12
$ sudo systemctl start postgresql-12

Create an application user and database

Make sure you pick a strong password, and store it somewhere safe!

$ sudo su - postgres -c "/usr/pgsql-12/bin/createuser -P SYSTEMNAME"
Enter password for new role:
Enter it again:
$ sudo su - postgres -c "/usr/pgsql-12/bin/createdb -EUTF8 -l en_US.utf8 -O SYSTEMNAME SYSTEMNAME"

Configure IP-based access

In the file /var/lib/pgsql/12/data/pg_hba.conf, make one entry for each subnet or host that will be running application servers. It is also possible to add a row for 0.0.0.0/0, but this is not recommended. Note! If ipv6 is in use, it might be necessary to add both the ipv4 and ipv6 addresses/networks, to make sure connectivity will always work. In a dynamic environment it is also possible to specify hostnames instead of IP addresses. Note that in this case a DNS lookup will be made for each connection made to the database, and that reverse and forward entries must match for the connection to be accepted. For each host or subnet, add a row like:

hostssl SYSTEMNAME SYSTEMNAME  192.168.7.0/24 scram-sha-256

Once this is done, reload the PostgreSQL configuration:

$ sudo systemctl reload postgresql-12

Preparing the cluster for replication (optional)

When setting up replication for the first time, a separate user should always be used. This is only done once per PostgreSQL cluster. Create one with a strong password by doing:

$ sudo su - postgres -c "/usr/pgsql-12/bin/createuser --replication -P replica"
Enter password for new role:
Enter it again:

With that done, the IP-based access lists also have to be opened. This step needs to be repeated for each new replica that’s added. The recommendation is to always list all the available PostgreSQL in the access files across all servers, so that a replica can be promoted and the others re-pointed without having to reconfigure the servers completely. Add one or more of the following lines to pg_hba.conf:

hostssl replication replica 192.168.6.2/32 scram-sha-256

Setting up backups

Backups can either be run from the local machine, in which case the output of the backups needs to be backed up again using some form of central backup management, or from a separate machine. If run from a separate machine, this machine needs the PostgreSQL backup tools (on CentOS that will be in the package postgresql12 per above) of the same version as the server is running. Note that it is NOT possible to safely back up the database by just backing up the filesystem or the virtual machine it is running on, unless PostgreSQL is stopped before the backup is taken! To set up a full backup job to run locally, create a cronjob that runs the following command as the user postgres (not as root!) on a regular basis. Adapt as necessary to include for example a timestamp in the backup (in which case backup aging has to be included as well).

rm -rf /var/lib/pgsql/12/backups/basebackup
/usr/pgsql-12/bin/pg_basebackup -D /var/lib/pgsql/12/backups/basebackup -Ft -z

Make sure the return code from this command is properly monitored and alerts should it fail!

Setting up dumps as alternative backups

The backups outlined above are the most efficient way to do backups of PostgreSQL, but come with the drawback of only being useful for restoring the entire database cluster (not individual tables), and requiring the restoration to be done to exactly the same version of PostgreSQL. A second option for backups is to use SQL level dumps, which provides this flexibility, but instead are more complicated and slower to restore. To make this type of backup, create a similar cronjob (also as the user postgres), and include the following:

/usr/pgsql-12/bin/pg_dumpall -g > /var/lib/pgsql/12/backups/global.sql
/usr/pgsql-12/bin/pg_dump -Fc -d SYSTEMNAME -f /var/lib/pgsql/12/backups/SYSTEMNAME.dump

Make sure the return code from this command is properly monitored and alerts should it fail!!

Setting up a replica

To set up a replica, follow the above steps for Installing PostgreSQL packages. After that, instead of initializing a cluster, run the following command as user postgres:

$ /opt/pgsql-12/bin/pg_basebackup -D /var/lib/pgsql/12/data -Fp -R \
    -C -S nameofreplica -h ipofmaster -U replica

And enter the password for the replica user when prompted. When this is done, as root, run :

$ sudo systemctl enable postgresql-12
$ sudo systemctl start postgresl-12

References

https://www.postgresql.org/docs/