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.

Was this content helpful?

White Paper

Free System Design on AWS E-Book

Download this early release of O'Reilly's latest cloud infrastructure e-book: System Design on AWS.

Free System Design on AWS E-Book
Start building today

Dragonfly is fully compatible with the Redis ecosystem and requires no code changes to implement.