Install and Setup PostgreSQL 15 Streaming Replication with Failover Procedure on AlmaLinux 9

In this video, you will learn how to install PostgreSQL 15 on AlmaLinux 9 and setup asynchronous replication. You will also learn how to perform failover.

PLEASE SUBSCRIBE :)
PLEASE HIT LIKE IF IT HELPED :)

add the PostgreSQL repository to your system

disable the built-in PostgreSQL module
sudo dnf -qy module disable postgresql

install PostgreSQL 15 on both Primary and secondary servers.
dnf install -y postgresql15-server postgresql15

initialize the database
/usr/pgsql-15/bin/postgresql-15-setup initdb

start the PostgreSQL service and enable it to start automatically on boot.
systemctl enable --now postgresql-15

Edit the PostgreSQL configuration file on the primary server to allow replication.

listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 8
hot_standby = on

Save and exit the configuration file.

create a replication user on the primary server.
sudo su - postgres
psql
CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'replicator_password';
\q

add a rule in the host-based authentication file
host replication replicator 192.168.100.75/32 trust

Now save and exit.

restart the PostgreSQL service
systemctl restart postgresql-15

Firewall
add a new rule to the firewall of both primary and secondary servers
firewall-cmd --zone=public --add-port=5432/tcp --permanent
firewall-cmd --reload

Now that you have created the replication user and configured the primary server, you can move on to configuring the secondary server.

Change as postgres user and run the pg_basebackup command.
sudo su - postgres
pg_basebackup -h 192.168.100.72 -U replicator -Fp -Xs -P -R -D /var/lib/pgsql/15/data/

execute systemctl command to start and enable on boot the PostgreSQL service on the secondary server.
systemctl enable --now postgresql-15

At this point, you have configured the secondary server and enabled asynchronous replication.

Verification
To test the replication setup, insert some data into the primary database and verifying that it appears on the secondary server.

su - postgres
psql -c "CREATE TABLE test1 (id serial PRIMARY KEY, data text);"
psql -c "INSERT INTO test (data) VALUES ('test data');"
psql -c "SELECT * FROM test;"

If the replication is working correctly, the SELECT statement should return the inserted row on both the primary and secondary servers.

You can do additional test by performing a 'WRITE' action from the 'SLAVE' server.

psql -c "CREATE TABLE test1 (id serial PRIMARY KEY, data text);"

The result should be 'Cannot execute CREATE TABLE' as shown here.

To verify the streaming status of a secondary server,
psql -x -c "SELECT * FROM pg_stat_replication;"

To view the status of the WAL receiver process on a secondary server in PostgreSQL, you can use this command

psql -x -c "select * from pg_stat_wal_receiver;"

Failover
To perform a failover from a primary PostgreSQL server to a secondary server, you need to promote the secondary server by running the pg_ctl promote command as postgres user.

The pg_ctl promote command is used to initiate the failover process. It signals the standby server to take over as the new primary server and start accepting read-write connections.

wal_log_hints = on

SECONDARY
On your secondary server, verify that it's still in a read-only state by running the following command.
psql -c "SELECT pg_is_in_recovery();"

/usr/pgsql-15/bin/pg_ctl promote

Drop me your feedback and comments below.

That's all for now.

If this video helped you in any way, please like share and subscribe!

Thank you!!!

[utkarshshukla]

Thanks sir for this wonderfull video, have 2 doubts ->
1- I have promoted secondary server to primary one. How do I demote it back to be secondary server and accept read-only connections?
2- How can we do loadbalancing in this case ?

please explain sir and if you have made a video on it please share or make one it will be very helpfull

[SirWolf]

I think it would be useful to add chapters in this video so I can skip the installation part. I came here for the replication configuration. Other than that, it helped, thanks.

[igorisn]

Thanks for the video, it helped a lot. I have promoted secondary server to primary one. How do I demote it back to be secondary server and accept read-only connections?

[novaturient]

could you please help me with the failover pg_ctl command for ubuntu server

[rens]

is it correct that wal files are not copied to standby server?