Learn how to – Configure PostgreSQL Replication on Rocky 8|AlmaLinux 8.
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-contribFor 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.rpmTo avoid conflicts disable the default repository,
sudo dnf -qy module disable postgresqlInstall PostgreSQL 14 on Rocky Linux 8|AlmaLinux 8 with the command:
sudo dnf install -y postgresql14-server postgresql14-contribOnce 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 initdbStart and enable the service with the command:
sudo systemctl start postgresql-14
sudo systemctl enable postgresql-14Verify 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 --reloadStep 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.confMake 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.confMake 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 md5Remember to replace the IP Addresses for the primary and replica hosts. Save the file and restart the service:
sudo systemctl restart postgresql-14Now save the changes and create a replication user.
sudo su - postgres
createuser --replication -P rep_userProvide a desired password and exit.
exitStep 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-14Remove 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/ -PProvide the password for the replication user created on the primary host to obtain the backup
Password:
27205/27205 kB (100%), 1/1 tablespaceOnce complete as shown above, exit.
exitEdit the PostgreSQL configuration.
sudo vim /var/lib/pgsql/14/data/postgresql.confMake the below changes.
# line 60 : uncomment and change
listen_addresses = '*'
# line 325 : uncomment
hot_standby = onAlso, edit the hba.conf file.
sudo vim /var/lib/pgsql/14/data/pg_hba.confEdit 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 md5The 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-14Step 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
psqlCreate a test database.
# CREATE DATABASE testdb;
CREATE DATABASENow on the replica hosts, check if it exists:
$ sudo -u postgres psql
psql (14.3)
Type "help" for help.
postgres=# lSample 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:
- How To Configure MongoDB Replication On Ubuntu
- Install PostgreSQL 14 on Ubuntu 22.04 (Jammy Jellyfish)
- How to deploy Redis StatefulSet Cluster in Kubernetes
Leave a Reply
You must be logged in to post a comment.