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.
Join this channel to get access to perks: www.youtube.com/@lazysysad/join
PLEASE SUBSCRIBE :)
PLEASE HIT LIKE IF IT HELPED :)
GIVE SUPPORT - / lazysysad
BUY ME A COFFEE - www.buymeacoffee.com/lazysysad
PAYPAL - paypal.me/lazysysad
add the PostgreSQL repository to your system
dnf install -y download.postgresql.org/pub/r...
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.
vi /var/lib/pgsql/15/data/postgresql.conf
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
vi /var/lib/pgsql/15/data/pg_hba.conf
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.
To enable WAL log hints, you can set the parameter in the postgresql.conf file.
vi /var/lib/pgsql/15/data/postgresql.conf
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!!!

[@oinvest1677]

Very straight forward Thanks!

[@lazysysad]

You're welcome 😊

[@andikamitrakaruna6495]

This is very helpful. Thank you

[@lazysysad]

Glad it was helpful!

[@utkarshshukla2448]

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

[@lazysysad]

I'll create another video for this 👍

[@sreenivasanatla8192]

excellent steps

[@lazysysad]

Glad you liked it 😊

[@SirWolf2018]

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.

[@wilfredomartel7781]

Maybe a video of logical replication?

[@KLB-rc3fi]

thanx

[@lazysysad]

You're welcome 😊

[@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?

[@lazysysad]

I will create a separate video for that. So please subscribe :)

[@sreenivasanatla8192]

@@lazysysad Please do

[@novaturient3257]

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

[@lazysysad]

can you give more context?

[@rens071]

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

[@lazysysad]

The -Xs option in the pg_basebackup specifies that the backup should include the required WAL (Write-Ahead Logging) files necessary for point-in-time recovery. This is important for setting up replication, as it allows the standby server to catch up with changes made on the primary server.