Configure PostgreSQL Replication on Rocky Linux 8|AlmaLinux 8

Posted on 144 views

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.

sudo firewall-cmd --add-port=5432/tcp --permanent
sudo firewall-cmd --reload

Step 2 – Configure the PostgreSQL Primary Host

Now proceed and make the below configuration to the primary host.

sudo vim /var/lib/pgsql/14/data/postgresql.conf

Make the below adjustments.

# line 60 : uncomment and change
listen_addresses = '*'

# line 205 : uncomment
wal_level = replica

# line 210 : uncomment
synchronous_commit = on

# line 298 : uncomment (max number of concurrent connections from streaming clients)
max_wal_senders = 10

# line 302 : uncomment and change (minimum number of past log file segments)
wal_keep_segments = 10

# line 312 : uncomment and change
synchronous_standby_names = '*'

Also, open the below file for editing.

sudo vim /var/lib/pgsql/14/data/pg_hba.conf

Make the below changes.

# end line : comment out existing lines and all new lines
# host replication [replication user] [allowed network] [authentication method]
#local   replication     all                                     peer
#host    replication     all             127.0.0.1/32            scram-sha-256
#host    replication     all             ::1/128                 scram-sha-256
host    replication     rep_user        192.168.205.2/32            md5
host    replication     rep_user        192.168.205.3/32            md5

Remember to replace the IP Addresses for the primary and replica hosts. Save the file and restart the service:

sudo systemctl restart postgresql-14

Now save the changes and create a replication user.

sudo su - postgres
createuser --replication -P rep_user

Provide a desired password and exit.

exit

Step 3 – Configure the PostgreSQL Replica Host

Now proceed and make configurations to the replica host. Begin by stopping the PostgreSQL service:

sudo systemctl stop postgresql-14

Remove the existing data.

sudo rm -rf /var/lib/pgsql/14/data/*

Now obtain backup from the primary host(192.168.205.2 for this case)

sudo su - postgres
pg_basebackup -R -h 192.168.205.2 -U rep_user -D /var/lib/pgsql/14/data/ -P

Provide the password for the replication user created on the primary host to obtain the backup

Password: 
27205/27205 kB (100%), 1/1 tablespace

Once complete as shown above, exit.

exit

Edit the PostgreSQL configuration.

sudo vim /var/lib/pgsql/14/data/postgresql.conf

Make the below changes.

# line 60 : uncomment and change
listen_addresses = '*'

# line 325 : uncomment
hot_standby = on

Also, edit the hba.conf file.

sudo vim /var/lib/pgsql/14/data/pg_hba.conf

Edit the file as shown.

# end line : comment out existing lines and all new lines
# host replication [replication user] [allowed network] [authentication method]
#local   replication     all                                     peer
#host    replication     all             127.0.0.1/32            scram-sha-256
#host    replication     all             ::1/128                 scram-sha-256
host    replication     rep_user        192.168.205.2/32            md5
host    replication     rep_user        192.168.205.3/32            md5

The lines above already exist since the files have been copied from the primary host. Save the file and start the PostgreSQL service.

sudo systemctl start postgresql-14

Step 4 – Test Streaming Replication on Rocky Linux 8|AlmaLinux 8

Once the above configurations have been made, we now proceed and validate if the replication is happening.

sudo su - postgres
psql -c "select usename, application_name, client_addr, state, sync_priority, sync_state from pg_stat_replication;"

Sample Output:

How-To-Configure-PostgreSQL-Replication-on-Rocky-LinuxAlmaLinux-1024x109

To do this, we will create a database on the primary host and check if it appears on the replica host.

Access the PostgreSQL shell on the primary host

psql

Create a test database.

# CREATE DATABASE testdb;
CREATE DATABASE

Now on the replica hosts, check if it exists:

$ sudo -u postgres psql
psql (14.3)
Type "help" for help.

postgres=# \l

Sample Output:

How-To-Configure-PostgreSQL-Replication-on-Rocky-LinuxAlmaLinux-1

Final Thoughts

That is it! We have PostgreSQL Replication on Rocky Linux 8|AlmaLinux 8 configured and running as expected. I hope this added value to you.

coffee

Gravatar Image
A systems engineer with excellent skills in systems administration, cloud computing, systems deployment, virtualization, containers, and a certified ethical hacker.