Table of Contents
Data integrity is non-negotiable in high-availability PostgreSQL environments. What happens if a primary database crashes before its changes are propagated to the standby? This is where PostgreSQL Synchronous Replication ensures your transactions are safe before committing them.
In this guide, I’ll walk you through configuring synchronous replication in PostgreSQL, step by step, ensuring minimal downtime and data consistency across nodes.
PostgreSQL Synchronous Replication Explained
Synchronous replication ensures that a transaction is not considered committed on the primary database until it has been written to at least one standby. This guarantees data durability but introduces potential latency.
For a deeper understanding of how PostgreSQL manages replication, refer to the official PostgreSQL documentation on warm standby and replication.
Key Differences from Asynchronous Replication
- Asynchronous: Primary sends changes to standby, but commits regardless of confirmation.
- Synchronous: Primary waits for standby confirmation before committing.
When to Use Synchronous Replication
Use synchronous replication when:
- Data consistency across nodes is critical (e.g., banking, financial systems).
- Read replicas must have up-to-date data at all times.
- Failover needs to happen without data loss.
Configuring Synchronous Replication in PostgreSQL
Enable WAL Archiving
Edit postgresql.conf on the primary server to enable write-ahead logging (WAL):
wal_level = replica
synchronous_commit = on
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archivedir/%f'Define Synchronous Standby Nodes
Set synchronous replication parameters in postgresql.conf:
synchronous_standby_names = 'ANY 1 (standby1, standby2)'This ensures at least one of the specified standby nodes confirms transaction receipt before the primary commits it.
Configure Primary for Replication
Modify pg_hba.conf (typically located at /etc/postgresql/14/main/pg_hba.conf) to allow standby servers to connect:
host replication replica_user 192.168.1.100/24 md5Restart PostgreSQL:
systemctl restart postgresqlSet Up the Standby Server(s)
On the standby server, stop PostgreSQL and create a base backup:
pg_basebackup -h primary_host -U replica_user -D /var/lib/postgresql/data --wal-method=stream -PUse the standby.signal file instead of recovery.conf since PostgreSQL 12:
standby_mode = 'on'
primary_conninfo = 'host=primary_host port=5432 user=replica_user password=yourpassword'
trigger_file = '/tmp/failover.trigger'Start the standby server:
systemctl start postgresqlChecking and Resolving Replication Lag
Check for delays using:
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;An acceptable replication lag is typically under a few seconds. If the lag exceeds 10-15 seconds, investigate network bottlenecks, adjust wal_sender_timeout, or increase wal_keep_size to prevent standby desynchronization. You can also monitor replication health using:
SELECT * FROM pg_stat_replication;This view provides insights into WAL sender status, replication lag, and standby activity, allowing you to fine-tune performance based on real-time data. Check for delays using:
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;An acceptable replication lag is typically under a few seconds. If the lag exceeds 10-15 seconds, investigate network bottlenecks, adjust wal_sender_timeout, or increase wal_keep_size to prevent standby desynchronization.
Performance Considerations
Reduce Latency with synchronous_commit = remote_apply
This setting ensures data durability while reducing commit delays. Transactions are applied on the standby before acknowledgment, minimizing primary server wait time. This improves throughput, especially in high-transaction environments, while maintaining data consistency.
Use Quorum Commit for Multiple Standbys
Instead of waiting for all standbys, configure quorum commit:
synchronous_standby_names = 'ANY 2 (standby1, standby2, standby3)'This speeds up performance while maintaining redundancy. For high-traffic environments, also consider tuning:
wal_keep_size = 512MB
wal_sender_timeout = 60s
max_wal_senders = 10These settings help ensure smooth WAL streaming and prevent unnecessary replication delays. Instead of waiting for all standbys, configure quorum commit:
synchronous_standby_names = 'ANY 2 (standby1, standby2, standby3)'This speeds up performance while maintaining redundancy.
Wrapping Up PostgreSQL Synchronous Replication
Synchronous replication is the key to ensuring zero data loss in mission-critical PostgreSQL deployments. While it introduces additional latency, the trade-off is worth it for industries where data accuracy outweighs speed.
Need help optimizing your PostgreSQL high-availability setup? Check out our detailed PostgreSQL High Availability and Failover Strategies guide for in-depth insights. Ashnik provides expert consulting, hands-on setup, and training to ensure seamless replication and failover strategies for your business-critical applications.
Subscribe to The Ashnik Times for more insights!
