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 Server – All 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
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:
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:
Recommended books:
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.
See more: