SearchPostgreSQL 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.
We recommend to always use the latest stable version of PostgreSQL. Officially tested and approved are the versions 13 to 17.
In the PostgreSQL database, sensitive data such as user credentials or access tokens will be stored.
If you are using ACME or database types of TLS certificates, the cryptographic keys for all Varnish managed HTTP traffic will be stored in the database as well.
Make sure your database setup, including WAL files and backups, conform to all requirements regarding the storage of cryptographic and sensitive data.
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.
Please consult the Documentation on postgresql.org for up-to-date instructions.
The following has been tested for Alma Linux 9 / Rocky Linux 9:
$ sudo dnf -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
$ sudo dnf -y update
$ sudo dnf -y install postgresql17-server postgresql17-contrib
# Set up database
$ sudo PGSETUP_INITDB_OPTIONS="-k -EUTF8 --locale=en_US.utf8" /usr/pgsql-17/bin/postgresql-17-setup initdb
Initializing database ... OK
# Set timezone parameter to UTC
$ sudo sed -i -e "s/#\?timezone .*/timezone = 'UTC'/g" /etc/postgresql/17/main/postgresql.conf
$ sudo systemctl enable postgresql-17
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-17.service, pointing to /usr/lib/systemd/system/postgresql-17.service.
$ sudo systemctl start postgresql-17
The following installation instructions are taken from the PostgreSQL website.
We strongly recommend installing specific stable version instead of the generic postgresql package
to avoid unintended downtimes due to unscheduled upgrades.
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
# Install PostgreSQL:
sudo apt -y install postgresql-17
Then set the used timezone parameter to UTC:
sudo sed -i -e "s/#\?timezone .*/timezone = 'UTC'/g" /etc/postgresql/17/main/postgresql.conf
sudo systemctl restart postgresql
The location of the PostgreSQL configuration file and it’s default values may vary. Verify that the time zone setting is active before continuing.
For more details, please see postgresql.org.
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/17/data/server.crt \
-keyout /var/lib/pgsql/17/data/server.key \
-subj "/CN=pgserver.domain.com"'
To set up a few basic configurations, add the following to the end of
/var/lib/pgsql/17/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!
sudo systemctl enable postgresql-17
sudo systemctl start postgresql-17
Make sure you pick a strong password, and store it somewhere safe!
$ sudo su - postgres -c "/usr/pgsql-17/bin/createuser -P SYSTEMNAME"
Enter password for new role:
Enter it again:
$ sudo su - postgres -c "/usr/pgsql-17/bin/createdb -EUTF8 -l en_US.utf8 -O SYSTEMNAME SYSTEMNAME"
In the file /var/lib/pgsql/17/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-17
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-17/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
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 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/17/backups/basebackup
/usr/pgsql-17/bin/pg_basebackup -D /var/lib/pgsql/17/backups/basebackup -Ft -z
Make sure the return code from this command is properly monitored and alerts should it fail!
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-17/bin/pg_dumpall -g > /var/lib/pgsql/17/backups/global.sql
/usr/pgsql-17/bin/pg_dump -Fc -d SYSTEMNAME -f /var/lib/pgsql/17/backups/SYSTEMNAME.dump
Make sure the return code from this command is properly monitored and alerts should it fail!!
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-17/bin/pg_basebackup -D /var/lib/pgsql/17/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-17
sudo systemctl start postgresql-17