Mastering Physical Standby: Enabling "Read Only With Apply" Mode for Optimal Database Replication
Problem: You're working with a PostgreSQL database and want to ensure high availability by implementing a physical standby server. However, you need a balance between replication speed and the ability to query data on the standby server.
Rephrased: Imagine your primary database is like a bustling restaurant, constantly taking orders (transactions). You want a backup kitchen (standby) that replicates those orders but can also prepare some dishes for customers (read-only queries). This is where the "Read Only with Apply" mode comes in handy.
Scenario:
Let's say you have a primary server (primary) with the following configuration:
# primary postgresql.conf
wal_level = logical
max_wal_senders = 3
And a standby server (standby) with the following configuration:
# standby postgresql.conf
hot_standby = on
wal_receiver_status_interval = 1000
This setup allows the standby server to receive WAL (Write-Ahead Log) files from the primary, but it's in "hot standby" mode, meaning it's not ready for queries.
Enabling "Read Only With Apply":
To enable "Read Only with Apply" mode on the standby server, you need to make the following changes:
-
Set
hot_standbytoon: This is essential for the standby server to receive WAL files. -
Set
hot_standby_feedbacktoon: This allows the standby server to send feedback to the primary regarding its progress in applying the WAL files. -
Set
hot_standby_modetoread only with apply: This is the core setting that enables read-only queries on the standby server while still actively applying WAL files.
Here's how the postgresql.conf on the standby server should look:
# standby postgresql.conf
hot_standby = on
hot_standby_feedback = on
hot_standby_mode = 'read only with apply'
wal_receiver_status_interval = 1000
Why "Read Only With Apply" is Beneficial:
- Reduced Downtime: With read-only queries on the standby, you can perform backups, analyze data, and test queries without affecting the primary server's performance.
- Improved Consistency: By continuously applying WAL files, the standby server remains relatively up-to-date with the primary, minimizing the impact of a potential failover.
- Increased Resilience: Even in the event of a primary server failure, the standby server is ready to take over quickly, ensuring minimal downtime and service disruption.
Important Considerations:
- Performance Trade-off: While read-only queries are possible, keep in mind that applying WAL files adds overhead to the standby server. This might affect query performance slightly.
- WAL Lag: The standby server will experience a lag behind the primary, depending on the frequency of transactions and the network bandwidth between the servers.
Example:
Imagine you're running a website with a high-volume online store. With "Read Only with Apply" mode enabled, you can:
- Run reports on sales data on the standby server without impacting customer orders on the primary server.
- Test new features in a live-like environment on the standby, ensuring seamless transition upon deployment.
- Back up the database regularly to ensure data integrity and recovery capabilities.
Conclusion:
By enabling "Read Only With Apply" mode on your PostgreSQL physical standby server, you can strike a balance between replication speed and the ability to perform read-only queries. This setting empowers you to optimize your database replication strategy, enhance availability, and build a more robust and resilient database infrastructure.
References:
