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?
Other Common PostgreSQL Questions (and Answers)
- How do you manage Postgres replication lag?
- How can I limit the number of rows updated in a PostgreSQL query?
- How does sharding work in PostgreSQL?
- How do you limit the number of rows deleted in PostgreSQL?
- How do you use the PARTITION OVER clause in PostgreSQL?
- What are PostgreSQL replication slots and how do they work?
- How can you partition an existing table in PostgreSQL?
- How do you partition a table by multiple columns in PostgreSQL?
- How do you check the replication status in PostgreSQL?
- What are the scaling limits of PostgreSQL?
- How do you scale Azure PostgreSQL?
- How do you use the limit clause in PostgreSQL to get the top N rows of a query result?
Free System Design on AWS E-Book
Download this early release of O'Reilly's latest cloud infrastructure e-book: System Design on AWS.
Switch & save up to 80%
Dragonfly is fully compatible with the Redis ecosystem and requires no code changes to implement. Instantly experience up to a 25X boost in performance and 80% reduction in cost