inchirags@gmail.com Chirag's PostgreSQL DBA Tutorial https://www.chirags.in
*****************************************************************************************
*PostgreSQL 16 - Promote Slave to Master and Master to Slave in Ubuntu 24.04 LTS step by step process*
*****************************************************************************************
Steaming Replication YouTube Link:
(हिंदी) - https://youtu.be/32asObleinQ
(English) - https://youtu.be/63QDP15K2jM
Two PostgreSQL servers:
Master Server1: 192.168.224.134
Slave Server2: 192.168.224.135
Step 1: Preparation
Before proceeding with the switchover, ensure that:
Both the master and the slave are running fine and are in sync.
You have superuser privileges on both servers.
You have a backup strategy in place.
1. Verify the Replication Status
On the master, check the status of the replication:
sudo -u postgres psql -c "SELECT state, sent_lsn, write_lsn, flush_lsn, replay_lsn FROM pg_stat_replication;"
On the slave, verify if it is in sync with the master:
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
It should return true on the slave.
This should show replication status with a connected slave.
2. Stop Write Activity on Master
To avoid data inconsistencies, stop any write activity on the master. This ensures there are no writes in progress during the failover.
Important:
Test the Master - Slave:
In Master:
[root@localhost ~]# sudo -u postgres psql
Create the database:
postgres=# create database testrepli;
Connect the database:
postgres=# \c testrepli
Create the table:
testrepli=# CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
position VARCHAR(100),
salary NUMERIC(10, 2)
);
Insert data:
testrepli=# INSERT INTO employees (name, position, salary)
VALUES
('Chirag Mahto', 'Database Administrator', 85000.00),
('Arun Kumar', 'Software Developer', 65000.00);
Query the table in master:
testrepli=# SELECT * FROM employees;
In Slave:
[root@localhost ~]# sudo -u postgres psql
Connect the database:
postgres=# \c testrepli
Query the table in slave:
testrepli=# SELECT * FROM employees;
Step 2: Promote Slave to Master
1. Promote the Slave
On the slave (new master), execute the following command to promote it to the master:
su - postgres
/usr/lib/postgresql/16/bin/pg_ctl promote -D /var/lib/postgresql/16/main
Output:
waiting for server to promote.... done
server promoted
Alternatively, you can run:
sudo -u postgres psql -c "SELECT pg_promote();"
This will make the slave a read-write master.
In Slave Server:
sudo vi /etc/postgresql/16/main/pg_hba.conf
Add replication access for the slave:
host replication replicator 192.168.224.134/24 md5
sudo systemctl restart postgresql
On the new master, create a replication slot and a replication user:
sudo -u postgres psql
CREATE ROLE replication WITH REPLICATION PASSWORD 'admin@123' LOGIN;
2. Check if the Promotion Succeeded
Once the promotion is done, verify the role of the new master:
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
It should return false for the new master.
Step 3: Demote the Old Master to Slave
Step-by-Step Process to Demote Old Master to Slave
1. Ensure the Old Master is Stopped
The first step is to stop the PostgreSQL service on the old master to prevent any further writes while switching roles.
sudo systemctl stop postgresql
2. Remove Old WAL Files
Clean up the old WAL files, as these can interfere with replication.
su - postgres
cp -R /var/lib/postgresql/16/main/ /var/lib/postgresql/16/main_old/
rm -rf /var/lib/postgresql/16/main/
pg_basebackup -h 192.168.224.135 -D /var/lib/postgresql/16/main/ -U replicator -P -v -R -X stream -C -S slaveslot1
Notice that standby.signal is created and the connection settings are appended to postgresql.auto.conf.
ls -ltrh /var/lib/postgresql/16/main/
3. Update the postgresql.conf on the Old Master
Modify the configuration of the old master to allow it to run as a standby.
Edit postgresql.conf to configure replication settings:
sudo vi /etc/postgresql/16/main/postgresql.conf
Update the following parameters to reflect the standby role:
wal_level = replica
hot_standby = on
max_wal_senders = 10
primary_conninfo = 'host=192.168.224.135 port=5432 user=replication password=admin@123'
primary_conninfo: This tells the old master (now standby) where the new master is and how to connect to it. Adjust the host, user, and password to match your replication setup.
4. Set Up Replication Slots and User on the New Master (Former Slave)
If not already done, set up a replication slot on the new master to manage the standby nodes.
This ensures that WALs will be shipped from the new master to the standby (old master).
5. Start the PostgreSQL Service on the Old Master
Now, you can start the PostgreSQL service on the old master, which is configured as a slave.
sudo systemctl start postgresql
6. Check the Replication Status
On the new master (former slave), check if the old master has connected and is now replicating as a standby:
sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;"
You should see the old master listed as a replica. Also, on the old master (now the standby), you can confirm it’s running in recovery mode by checking:
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
It should return true, confirming that it is now running as a standby.
7. Test the Setup
To ensure that everything is working correctly, try writing some data on the new master and verify that it is replicated to the old master.
In Master:
sudo -u postgres psql
Create the database:
postgres=# create database testrepli2;
Connect the database:
postgres=# \c testrepli2
Create the table:
testrepli2=# CREATE TABLE employees2 (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
position VARCHAR(100),
salary NUMERIC(10, 2)
);
Insert data:
testrepli2=# INSERT INTO employees2 (name, position, salary)
VALUES
('Chirag Mahto', 'Database Administrator', 85000.00),
('Arun Kumar', 'Software Developer', 65000.00);
Query the table:
testrepli=# SELECT * FROM employees2;
In Slave:
sudo -u postgres psql
Connect the database:
postgres=# \c testrepli2
Query the table:
testrepli2=# SELECT * FROM employees2;
Then, check if the data is replicated to the old master (now the standby) by querying the same table there.
If you want to enable synchronous, the run the below command on master database server and reload postgresql service.
sudo -u postgres psql
ALTER SYSTEM SET synchronous_standby_names TO '*';
\q
sudo systemctl restart postgresql
For any doubts and query, please write on YouTube video comments section.
Note : Flow the Process shown in video.
Please, Subscribe and like for more videos:
https://youtube.com/@chiragstutorial
Don't forget to, Follow, Like, Share &, Comment
Tutorial Link :
https://www.chirags.in/tutorials/PostgreSQL_Database
Thanks & Regards,
Chitt Ranjan Mahto "Chirag"
_________________________________________________________________________________________
Note: All scripts used in this demo will be available in our website.
Link will be available in description.
