Step-by-step guide to setup PostgreSQL Replication

In PostgreSQL, replicating data from one database server to another is referred to as PostgreSQL Replication. The server that holds the original data is commonly referred to as the Master server, and the server that receives the copied data is known as the Replica server. Learn how to set up PostgreSQL replication with this step-by-step guide.

Models of PostgreSQL Database Replication

Single-Master Replication in PostgreSQL refers to a replication setup where there is only one Master server and multiple Replica servers. In this configuration, all write operations are performed on the Master server, and these changes are then propagated to the Replica servers. This allows for multiple Replica servers to be used for read-only operations, improving performance and providing redundancy.

Multi-Master Replication in PostgreSQL refers to a replication setup where there are multiple Master servers and multiple Replica servers. In this configuration, all write operations can be performed on any of the Master servers, and these changes are then propagated to the other Master servers and to the Replica servers. This allows for multiple servers to be used for both read and write operations, improving performance and providing redundancy.

In this article we will explain step-by-step guide to setup single-master replication in PostgreSQL.

Step-by-step guide to set up PostgreSQL replication

1. Install and configure PostgreSQL on both the master and the slave servers.
2. On the master server, open the postgresql.conf file and set the following parameters:

wal_level = replica
max_wal_senders = [number of slaves]
archive_mode = on
archive_command = 'test ! -f [archive_directory]/%f && cp %p [archive_directory]/%f'

3. On the master server, open the pg_hba.conf file and add a line to allow replication connections from the slave server.
4. On the slave server, open the postgresql.conf file and set the following parameters:

hot_standby = on

5. On the slave server, open the recovery.conf file and add the following lines:

standby_mode = 'on'
primary_conninfo = 'host=[master_server_ip] port=[master_server_port] user=[replication_user] password=[replication_password]'
trigger_file = '[path_to_trigger_file]'

6. Restart both the master and slave servers to apply the changes.
7. On the master server, execute the following command:

pg_basebackup -D [slave_data_directory] -Fp -P -R -h [master_server_ip] -U [replication_user]

8. On the slave server, execute the following command:

pg_ctl start -D [slave_data_directory] -w

That’s it, you have successfully set up PostgreSQL replication.

Note: It’s recommended to test the replication in a safe environment before applying it in production.

Learn more about PostgreSQL Replication and Automatic Failover.