How to setup master-slave replication in postgres 13

Setting up master-slave replication in PostgreSQL 13 involves configuring the master server to stream changes to one or more slave servers. The process requires careful planning and configuration to ensure data consistency and reliability. Below is a step-by-step guide to set up master-slave replication:

Assumptions:

1. You have already installed PostgreSQL 13 on both the master and slave servers.
2. Both servers are reachable and can communicate with each other.

Step 1: Configure the Master Server

1. Open the PostgreSQL configuration file on the master server. The configuration file is usually named postgresql.conf and can be found in the PostgreSQL data directory (e.g., /var/lib/pgsql/13/data/postgresql.conf).
2. Set the following parameters in the postgresql.conf file:

# Enable replication
wal_level = replica

# Define the location where the write-ahead log (WAL) files will be stored. Ensure it's accessible to the PostgreSQL server.
wal_level = replica
archive_mode = on
archive_command = 'cp %p /path/to/wal_archive/%f'

# Set a unique identifier for the master server (replace 'my_cluster' with a name of your choice)
wal_sender_name = 'my_cluster'

# Allow connections from slave servers
max_wal_senders = 10
wal_keep_segments = 32

3. Save the changes and restart PostgreSQL on the master server to apply the configuration.

Step 2: Create Replication User on the Master Server

1. Access the PostgreSQL server on the master server using the psql command or a PostgreSQL client tool.
2. Create a new replication user that will be used by the slave to connect and replicate data:

CREATE USER replication_user REPLICATION LOGIN CONNECTION LIMIT 10 ENCRYPTED PASSWORD 'your_password';

3. Replace 'your_password' with a strong password for the replication user.

Step 3: Configure the Slave Server

1. Open the PostgreSQL configuration file on the slave server (postgresql.conf).
2. Set the following parameters:

# Enable replication
wal_level = replica

# Set a unique identifier for the slave server (replace 'my_slave' with a name of your choice)
wal_receiver_create = on
wal_receiver_name = 'my_slave'

# Define the location where the write-ahead log (WAL) files will be stored. Ensure it's accessible to the PostgreSQL server.
archive_mode = on
archive_command = 'cp %p /path/to/wal_archive/%f'

# Allow connections to the master server
hot_standby = on

3. Save the changes and restart PostgreSQL on the slave server.

Step 4: Take a Base Backup and Start Replication

1. On the slave server, take a base backup from the master using the pg_basebackup utility. Run this command on the slave server:

pg_basebackup -h master_ip_address -D /path/to/data_directory -U replication_user -P -X stream

2. Replace master_ip_address with the IP address or hostname of the master server and /path/to/data_directory with the PostgreSQL data directory on the slave.
3. Provide the password for the replication user when prompted.

Step 5: Create a Recovery Configuration File on the Slave Server

1. Create a file named recovery.conf in the PostgreSQL data directory on the slave server.
2. Add the following configuration to the recovery.conf file:

standby_mode = on
primary_conninfo = 'host=master_ip_address port=5432 user=replication_user password=your_password'
trigger_file = '/path/to/data_directory/trigger_file'

3. Replace master_ip_address, replication_user, and your_password with the appropriate values.
4. Make sure to replace /path/to/data_directory with the correct path to the PostgreSQL data directory on the slave.
5. Save the recovery.conf file.

Step 6: Start PostgreSQL on the Slave Server Start PostgreSQL on the slave server. It will connect to the master server and begin replication based on the WAL files.

Step 7: Monitor Replication You can monitor the replication status using the PostgreSQL logs, replication views, and tools like pg_stat_replication or pg_stat_wal_receiver on the master and slave servers.

That’s it! Now, you have a master-slave replication setup in PostgreSQL 13. Please note that setting up replication involves careful planning and consideration of your specific use case and requirements. Always test the setup thoroughly before using it in a production environment.