Index ¦ Archives ¦ Atom

Initial PostgreSQL 11 setup on CentOS 7 with 2ndquadrant goodies

Introduction

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.

Available software

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.

Installation

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

Run 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/

© Bruno Henc. Built using Pelican. Theme by Giulio Fidente on github.