Question: How do you configure replication in PostgreSQL?

Answer

PostgreSQL offers several methods for database replication, allowing a primary server to replicate its data to one or more secondary servers. Among the most common replication methods are Streaming Replication and Logical Replication. This guide focuses on setting up Streaming Replication.

Prerequisites

• Two or more PostgreSQL servers (a primary and at least one standby).
• Same version of PostgreSQL installed on all servers.
• Network connectivity between all participating servers.
• A dedicated replication user on the primary server.

1. Configure the Primary Server

On the primary server, you need to make changes to two main configuration files: postgresql.conf and pg_hba.conf.

Edit postgresql.conf

listen_addresses = '*' # listen on all interfaces
wal_level = replica # minimum for streaming replication
max_wal_senders = 5 # max number of walsender processes
archive_mode = on # enables archiving; off if not needed
archive_command = 'cp %p /path/to/archive/%f' # local archive command; adjust as necessary

Edit pg_hba.conf Add the following line to allow the standby server to connect:

host replication replicator <standby_IP_address>/32 md5

Replace <standby_IP_address> with the actual IP address of your standby server and replicator with the name of your replication user.

After making these changes, restart the PostgreSQL service to apply them.

2. Prepare the Standby Server

Before setting up the standby server, ensure that the primary server is correctly configured and running.

Create a Base Backup You can use the pg_basebackup tool to create a base backup of the primary server.

pg_basebackup -h <primary_IP_address> -D /var/lib/postgresql/12/main -U replicator -v -P --wal-method=stream

Replace <primary_IP_address> with the IP address of your primary server. Adjust the path (/var/lib/postgresql/12/main) according to your PostgreSQL version and installation.

Create standby.signal On the standby server, create an empty file named standby.signal in the PostgreSQL data directory to indicate that this server should start in standby mode.

Edit postgresql.conf on the Standby Ensure the following settings are adjusted in your postgresql.conf on the standby server:

hot_standby = on # allows connections and queries during recovery

3. Start the Standby Server

With the standby.signal file in place and configurations adjusted, start the PostgreSQL service on the standby server. It should connect to the primary and begin replicating.

4. Monitoring Replication Status

To monitor the replication status, you can use the pg_stat_replication view on the primary server:

SELECT * FROM pg_stat_replication;

This basic setup gets you started with PostgreSQL streaming replication. There are many other options and configurations available depending on your specific needs, including synchronous replication, multiple standbys, and using replication slots.