PostgreSQL has made some significant strides in making it a less painful database to manage in a high-availability environment. The trouble is, these advances could be documented in a more straightforward (e.g. "hacker-friendly" way). To fill in the gaps here are my scribbles on the essential additions to the classical postgreSQL formula.
2ndquadrant offers several high-quality software solutions that are publicly available. The following software are currently available in their repositories: * barman, barman-cli, python-barman - barman is a hot backup and recovery tool * repmgr - repmgr - toolkit for easier replication management and failover * pgbouncer - pgbouncer - connection pooler for high-availability architectures * postgresql11-pglogical - pglogical next-generation logical replication for postgreSQL
An interesting addition is also postgresql BDR, which is a multi-master solution.
Configuring the repositories
The repositories offered by 2ndquadrant don't contain the postgreSQL packages. Instead, the upstream package repositories need to be installed first. Repositories for CentOS/RHEL/Fedora are available here For CentOS 7 on x86_64 one can install the packages directly using the commands below:
wget https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm sudo yum install pgdg-redhat-repo-latest.noarch.rpm
Once the above repositories the 2ndquadrant repositories can be added either by their instructions or by following the instructions below. Add a file titled 2ndquadrant-dl-default-release-pg11 in /etc/yum.repos.d with the following content:
[2ndquadrant-dl-default-release-pg11] name=2ndQuadrant packages (PG11) for $releasever - $basearch baseurl=https://dl.2ndquadrant.com/default/release/rpm/packages/centos/$releasever/$basearch/11/ enabled=1 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-2NDQ-DL-DEFAULT [2ndquadrant-dl-default-release-pg11-debug] name=2ndQuadrant packages (PG11) for $releasever - $basearch - Debug baseurl=https://dl.2ndquadrant.com/default/release/rpm/dbg_packages/centos/$releasever/$basearch/11/ enabled=1 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-2NDQ-DL-DEFAULT [2ndquadrant-dl-default-release-pg11-source] name=2ndQuadrant packages (PG11) for $releasever - $basearch - Source baseurl=https://dl.2ndquadrant.com/default/release/rpm/src_packages/centos/$releasever/$basearch/11/ enabled=0 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-2NDQ-DL-DEFAULT
Add the 2ndquadrant public key to the right location:
curl https://dl.2ndquadrant.com/gpg-key.asc -o /etc/pki/rpm-gpg/RPM-GPG-KEY-2NDQ-DL-DEFAULT chmod 644 /etc/pki/rpm-gpg/RPM-GPG-KEY-2NDQ-DL-DEFAULT
The above steps are equivalent to just doing curl | bash, however doing curl | bash is a practice that needs to die. It's insecure, makes it harder to create instructions that can be used with configuration management (like ansible or puppet), and just makes people dumber (since they won't know what the script does). But, to each their own.
Once the repositories have been configured we can install the postgresql packages. We'll install postgresql with pglogical simply with:
yum install postgresql11-pglogical
pglogical will automatically pull in postgresql11-server. We'll inspect the package contents to figure out where the package manager put the important files:
repoquery -l postgresql11-server
The most important thing to note from the above command are: * postgresql data directory is /var/lib/pgsql/11/data . This is good because the filepath matches the SELinux context defined in /etc/selinux/targeted/active/file_contexts * the authentication file is at /var/lib/pgsql/11/data/pg_hba.conf and the main configuration is at /var/lib/pgsql/11/data/postgresql.conf * user commands are in /usr/pgsql-11/bin (which is outside of the system path, so for autocompletion you might need to modify your shell config) * the defaults file is in /etc/sysconfig/pgsql * the systemd service file is /usr/lib/systemd/system/postgresql-11 so service actions should use postgresql-11 instead of just postgresql
Initializing the PostgreSQL database
Before we can start the database it needs to be initialized. This can be done with the following command:
/usr/pgsql-11/bin/postgresql-11-setup initdb postgresql-11 Initializing database ... OK
Start the database:
systemctl start postgresql-11
systemctl status postgresql-11 and
netstat -tlpn | grep 5432 or
ss -tlpn | grep 5432 to verify postgresql is active. The postmaster process should be listening on port 5432.
To login to the database, switch to the postgresql user and run psql:
[root@db01 ~]# su - postgres -bash-4.2$ psql psql (11.5) Type "help" for help. postgres=#
Enable the postgresql to run on startup:
systemctl enable postgresql-11
See also https://fedoraproject.org/wiki/PostgreSQL for more information about the initial setup. Most commands should work with the upstream packages, though there are differences. Caveat emptor. A good guide for the vanilla packages is also https://tecadmin.net/install-postgresql-11-on-centos/