PostgreSQL Tutorial - 16 - How to setup streaming replication in PostgreSQL step by step on Ubuntu

In this Video, we will see how to setup streaming replication in PostgreSQL step by step on Ubuntu 22.04.

PostgreSQL Master name and IP address:

PGMaster and 192.168.72.128

PostgreSQL Slave/Replica name and IP address:

PGSlave and 192.168.72.129

On Master and Slave servers, PostgreSQL 14 must have installed.

Step1: Configurations on master server

listen_addresses = '*'

2. Now, connect to PostgreSQL on master server and create replica login.



host replication replicator 192.168.72.129/24 md5

How to setup streaming replication in PostgreSQL step by step on Ubuntu

4. Now, restart the PostgreSQL on Master server by using below command.

sudo systemctl restart postgresql

Step3: Configurations on slave(standby) server
1. We have to stop PostgreSQL on Slave server by using following command.

sudo systemctl stop postgresql

2. Now, switch to postgres user and take backup of main(data) directory.

su - postgres
cp -R /var/lib/postgresql/14/main/ /var/lib/postgresql/14/main_old/


3. Now, remove the contents of main(data) directory on slave server.

rm -rf /var/lib/postgresql/14/main/

4. Now, use basebackup to take the base backup with the right ownership with postgres(or any user with right permissions).

pg_basebackup -h 192.168.72.128 -D /var/lib/postgresql/14/main/ -U replicator -P -v -R -X stream -C -S slaveslot1

Then provide the password for user replicator created in master server.

pg_basebackup: initiating base backup, waiting for checkpoint to complete
....................................
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed

ls -ltrh /var/lib/postgresql/14/main/

7. Now connect the master server, you should be able to see the replication slot called slotslave1 when you open the pg_replication_slots view as follows.

SELECT * FROM pg_replication_slots;

Step3. Test replication setup
1. Now start PostgreSQL on slave(standby) server.

systemctl start postgresql

2. Now, try to create object or database in slave(standby) server. It throws error, because slave(standby) is read-only server.

create database slave1;

3. WE can check the status on standby using below command.

SELECT * FROM pg_stat_wal_receiver;

4. Now, verify the replication type synchronous or aynchronous using below command on master database server.

SELECT * FROM pg_stat_replication;

5. Lets create a database in master server and verify its going to replicate to slave or not.

create database stream;

6. Now, connect to slave and verify the database copied or not.

select datname from pg_database;

7. If you want to enable synchronous, the run the below command on master database server and reload postgresql service.

ALTER SYSTEM SET synchronous_standby_names TO '*';
systemctl reload postgresql

Thats all. We have successfully setup streaming replication in PostgreSQL step by step on Ubuntu.

For any doubts and query, please write on youtube video comments section.

Note : Flow the Process shown in video.

😉Subscribe and like for more videos:
💛Don't forget to, 💘Follow, 💝Like, 💖Share 💙&, Comment

Tutorial Link :

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.

#ChiragsTutorial
#DBATutorial
#DBATutorialLearning
#DBAEducation
#DBATutorialFree
#DatabaseTutorial
#PostgreSQLDatabaseTutorial
#PostgreSQLDBATutorial
#PostgreSQLDatabaseTutorial
#ChiragsDBATutorial
#ChiragsDatabaseTutorial
#ChiragsPostgreSQLDatabaseTutorial
#ChiragsPostgreSQLDBATutorial
#ChiragsPostgreSQLDatabaseTutorial
#CreateDatabaseinPostgreSQL

[foroughiarash]

Very good and easy, I was stuck for some days to configure Patroni but I couldn't and got many errors, but this video made my day!
Thanks

[onobrerodrigo]

Esse vídeo salva vidas. Eu tenho ele salvo, pois sempre tem algo que eu esqueço. Mas agora mapeei e automatizei o processo.

Usei o vídeo como base para usar o Principal e o Replica no mesmo servidor.

[nyaupaneshital]

Thanks for the tutorial ! Gets work done !

[neiliwael]

i'm getting this error on the replica, any idea to help plz ? " could not send replication command "START_REPLICATION": ERROR: replication slot "slaveslot1" does not exist"

[golthikaseechandrasekhar]

nice info & presentation. could you let me know step by step process for failover & fail back as well.

[durgaprasadpj.badri]

im not able to get the streaming data ive configured all but what would be the issue?

[SayaliChitrakoti]

hello Chirag
I m getting error when i m frring SELECT * FROM pg_replication_slots; command
error is psql: error: connection to server on socket failed: No such file or directory
Is the server running locally and accepting connections on that socket?
can you plz help Asap

[SayaliChitrakoti]

hello Chirag
I m getting error when i m frring SELECT * FROM pg_replication_slots; command
error is psql: error: connection to server on socket failed: No such file or directory
Is the server running locally and accepting connections on that socket?

[siphondlalane]

Hi Chirags, on step 3 under "Test replication setup" I get an error when I try to log to postgres I get this "psql: error: connection to server on socket failed: No such file or directory
Is the server running locally and accepting connections on that socket?" Kindly help on how to go on with this.

[utkarshshukla]

Awesome video sir, sir can you also make video on fallback and load balancing in database replication, it will be very helpfull sir

[InaparthyNiharika]

can you explain the way you have created servers?