PostgreSQL is an open-source relational database that offers dependability and resilience. It extends the SQL language by providing many features that store and scale the most complicated workloads. This database system dates back to 1986, introduced as part of the Postgres project at the University of California at Berkeley. Ever since, there has been 30 years of active development on the core platform to gain the current reputation of reliability, data integrity, robust feature set, extensibility e.t.c

One of the amazing features offered by PostgreSQL is data replication. Streaming replication, a feature added to PostgreSQL 9.0 offers the capability to ship and apply the WAL XLOG records. It offers several functions that include:

  • Log-shipping where XLOG records generated in the primary are periodically shipped to the standby via the network.
  • Continuous recovery– XLOG records shipped are replayed as soon as possible without waiting until the XLOG file has been filled
  • Connection settings and authentication – it allows users to configure similar settings as a normal connection to a connection for SR for example keepalive, pg_hba.conf
  • Progress report – The primary and standby report the progress of log-shipping in the PS display.
  • Multiple standbys – Multiple standbys can establish a connection to the primary for SR.
  • Graceful shutdown – when shutdowns are executed, the primary waits until the XLOG records up to the shutdown checkpoint record have been sent to standby.
  • Activation – standby can keep waiting for activation as long as required by the user.

This guide provides an in-depth illustration of how to configure the PostgreSQL Replication on Rocky Linux 8|AlmaLinux 8.

Step 1 – Install PostgreSQL on All Rocky Linux 8|AlmaLinux 8 Nodes

For streaming replication to occur, you need to have PostgreSQL installed on all the nodes. This can be done using the steps below:

List the available versions available in the default Rocky Linux 8|AlmaLinux 8 repositories.

$ dnf module list postgresql
....
Name                        Stream                  Profiles                            Summary                                             
postgresql                  9.6                     client, server [d]                  PostgreSQL server and client module                 
postgresql                  10 [d]                  client, server [d]                  PostgreSQL server and client module                 
postgresql                  12                      client, server [d]                  PostgreSQL server and client module                 
postgresql                  13                      client, server [d]                  PostgreSQL server and client module                 

Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled
  • For PostgreSQL 13

From the provided lists, you can install a preferred version say PostgreSQL 13. 

sudo dnf -qy module enable postgresql:13
sudo dnf install postgresql-server postgresql13-contrib

For PostgreSQL 14

For this guide, we will use PostgreSQL 14, installed by adding an extra repository.

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

To avoid conflicts disable the default repository,

sudo dnf -qy module disable postgresql

Install PostgreSQL 14 on Rocky Linux 8|AlmaLinux 8 with the command:

sudo dnf install -y postgresql14-server postgresql14-contrib

Once enabled, initialize your PostgreSQL database with the command:

sudo /usr/pgsql-13/bin/postgresql-14-setup initdb
##OR
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb

Start and enable the service with the command:

sudo systemctl start postgresql-14
sudo systemctl enable postgresql-14

Verify that the service is running:

$ systemctl status postgresql-14
● postgresql-14.service - PostgreSQL 14 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2022-06-16 07:35:42 EDT; 15s ago
     Docs: https://www.postgresql.org/docs/14/static/
 Main PID: 6914 (postmaster)
    Tasks: 8 (limit: 23544)
   Memory: 16.7M
   CGroup: /system.slice/postgresql-14.service
           ├─6914 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
           ├─6916 postgres: logger 
           ├─6918 postgres: checkpointer 
           ├─6919 postgres: background writer 
           ├─6920 postgres: walwriter 
           ├─6921 postgres: autovacuum launcher 
           ├─6922 postgres: stats collector 
           └─6923 postgres: logical replication launcher

Allow the service through the firewall.