SearchVarnish Controller
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.
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.
$ 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.
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.
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).
# Set timezone to UTC
$ sudo sed -i -e "s/#timezone .*/timezone = 'UTC'/g" /var/lib/pgsql/12/data/pg_hba.conf
$ 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
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"'
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!
$ sudo systemctl enable postgresql-12
$ sudo systemctl start postgresql-12
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"
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
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
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!
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!!
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