Set up master-slave replication in PostgreSQL, including all the necessary configuration files and settings.

postgresql scaling - master-slave replication.md

Set up master-slave replication in PostgreSQL, including all the necessary configuration files and settings.

Assuming that you already have two servers with PostgreSQL installed, one of which will be designated as the master and the other as the slave, here are the steps to set up master-slave replication:

On the master server:

1. Open the postgresql.conf file (usually located in /etc/postgresql/<version>/main/) and modify the following settings:

listen_addresses = '*'
max_wal_senders = 10
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

The listen_addresses setting allows the server to accept connections from remote clients, while the max_wal_senders, wal_level, archive_mode, and archive_command settings enable and configure the WAL (Write-Ahead Log) archiving and streaming that is necessary for replication.

2. Open the pg_hba.conf file (usually located in /etc/postgresql/<version>/main/) and add the following line at the end:

host    replication     <replication_user>     <slave_ip_address>/32     md5

Replace <replication_user> with the name of the replication user that you want to create, and <slave_ip_address> with the IP address of the slave server. This line allows the slave server to connect to the master server for replication.

3. Create a new replication user and grant it the necessary permissions by running the following SQL commands:

CREATE USER <replication_user> REPLICATION LOGIN CONNECTION LIMIT 10 ENCRYPTED PASSWORD '<password>';
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO <replication_user>;

Replace <replication_user> with the name of the replication user that you want to create, and <password> with a strong password for the user.

4. Restart the PostgreSQL service to apply the changes.

5. Take a base backup of the master database by running the following command:

pg_basebackup -h <master_ip_address> -D /mnt/server/backup -U <replication_user> -P -X stream

Replace <master_ip_address> with the IP address of the master server, and <replication_user> with the name of the replication user that you created in step 3. This command creates a backup of the master database and stores it in the /mnt/server/backup directory on the slave server.

On the slave server:

1. Open the postgresql.conf file and modify the following settings:

listen_addresses = '*'
hot_standby = on
max_standby_streaming_delay = -1

The listen_addresses setting allows the server to accept connections from remote clients, while the hot_standby and max_standby_streaming_delay settings enable and configure the hot standby mode that is necessary for replication.

2. Open the pg_hba.conf file and add the following line at the end:

host    replication     <replication_user>     <master_ip_address>/32     md5

Replace <replication_user> with the name of the replication user that you created on the master server, and <master_ip_address> with the IP address of the master server. This line allows the slave server to connect to the master server for replication.

3. Create a new directory for storing the WAL archives by running the following command:

mkdir /mnt/server/archivedir

4. Create a new recovery.conf file in the PostgreSQL data directory (usually located in /var/lib/postgresql/<version>/main/) and add the following settings:

standby_mode = 'on'
primary_conninfo = 'host=<master_ip_address> port=5432 user=<replication_user> password=<password> application_name=<slave_name>'
restore_command = 'cp /mnt/server/archivedir/%f "%p"'

Replace <master_ip_address> with the IP address of the master server, <replication_user> with the name of the replication user that you created on the master server, <password> with the password for the replication user, and <slave_name> with a name for the slave server. These settings enable the slave server to connect to the master server for replication and configure the restore command that is used to retrieve archived WAL segments from the master server.

5. Start the PostgreSQL service to apply the changes.

6. Restore the base backup that was taken from the master server by running the following command:

pg_ctl -D /var/lib/postgresql/<version>/main/ stop
rm -rf /var/lib/postgresql/<version>/main/*
pg_basebackup -h <master_ip_address> -D /var/lib/postgresql/<version>/main/ -U <replication_user> -P -X stream

Replace <version> with the version number of PostgreSQL that you are using, <master_ip_address> with the IP address of the master server, and <replication_user> with the name of the replication user that you created on the master server. These commands stop the PostgreSQL service, delete the existing data directory, and restore the base backup from the master server.

7. Start the PostgreSQL service again to start the replication process.

That's it! With these steps, you should have successfully set up master-slave replication in PostgreSQL.