Question: How do you set up PostgreSQL replication on Windows?

Answer

Setting up PostgreSQL replication on Windows involves configuring one server as the primary (master) and another as the standby (slave). Replication can be synchronous or asynchronous, but the most common setup is asynchronous. The primary server processes transactions and asynchronously replicates them to the standby server. This guide will walk you through setting up basic asynchronous streaming replication.

Prerequisites

• Two PostgreSQL servers installed on Windows.
• Network connectivity between both servers.
• Identical PostgreSQL versions on each server.

1. Configure the Primary Server

On the primary server, locate your postgresql.conf file (typically found in the data directory) and make the following changes:

listen_addresses = '*' # listen on all interfaces
wal_level = replica    # required for replication
max_wal_senders = 5    # max number of walsender processes
archive_mode = on      # enables archiving; off by default
archive_command = 'copy "%p" "C:\\path\\to\\archive\\%f"'  # command to use to archive a logfile segment

Also, edit the pg_hba.conf file to allow the standby server to connect:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Allow replication connections from standby IP address
host    replication     all             standby_ip/32           trust

Replace standby_ip with the actual IP address of your standby server. After making these changes, restart your PostgreSQL service.

2. Prepare the Standby Server

On the standby server, you'll need to create a base backup of the primary server. First, stop the PostgreSQL service on the standby server. Then, run the following command from the standby server:

pg_basebackup -h primary_ip -D C:\\path\\to\\standby\\data\\directory -U replicator -P -v -R

Replace primary_ip with the IP address of your primary server and adjust the path to where you want the data directory on the standby server. This command uses a user named replicator, which you should have created on the primary server with replication privileges.

3. Configure the Standby Server

After taking the base backup, a recovery.conf file will be created in the data directory on the standby server. Verify that this file contains the correct connection information for the primary server:

standby_mode = 'on'
primary_conninfo = 'host=primary_ip port=5432 user=replicator password=yourpassword'
trigger_file = 'C:\\path\\to\\trigger\\file'

Make sure to replace primary_ip, replicator, and yourpassword with your actual primary server's IP, replication user, and password, respectively.

Finally, start the PostgreSQL service on the standby server. It will now start in standby mode and begin replicating from the primary server.

Conclusion

You've now set up basic asynchronous streaming replication for PostgreSQL on Windows. This setup provides a read-only copy of your database on the standby server, which can be promoted to a primary server if needed. For production environments, further tuning and additional configuration such as WAL archiving and monitoring are recommended.