The Hidden Trap of Legacy PostgreSQL Replication: Intro
In the modern business world, High Availability and Disaster Recovery are not optional for database systems. PostgreSQL gives you strong replication mechanisms to address those needs. The catch, especially when you are working with older PostgreSQL versions, is that anyone used to the comfort and automation of modern systems can run into some hidden traps.
In this post I want to dig into the subtleties of legacy PostgreSQL replication, the dangers it carries, and how to dodge them. The goal is to give you a heads-up about the kinds of issues that can come up while you manage existing legacy systems or set up something new, and to help you take some proactive measures. To get the full benefit of PostgreSQL, you have to understand what this old-school inheritance demands of you.
A Quick Look at Legacy PostgreSQL Replication
PostgreSQL has been building out replication options for many years to keep databases continuously available. Modern streaming replication and logical replication are quite mature and easy to use, but in older versions things were a bit different. Pre-9.0 in particular leaned more on manual procedures and file-based approaches.
Understanding these older mechanisms is the starting point for diagnosing and fixing replication issues on legacy systems. Many things that just work on modern versions had to be done by hand in older ones. That is what trips people up: a misconfiguration or a missed detail can put system stability at risk.
File-Based Log Shipping: Flexibility and Risks
One of the oldest and most fundamental replication methods in PostgreSQL is file-based log shipping. The idea is to copy completed Write-Ahead Log (WAL) segments from the primary server to the standby and replay them there. The two main parameters that drive this are archive_mode and archive_command.
The primary archives finished WAL segments into a specific directory, and archive_command ships those segments to the standby or to some shared storage like NFS or S3. The standby then uses restore_command to pull those archived segments back and apply them to its own database. This is fully asynchronous and does not need a live link between primary and standby, which gives it a certain operational flexibility.
That flexibility comes with serious risks, though. The lag between transferring and replaying WAL segments increases the data loss window. If the primary crashes and the latest WAL segment has not reached the standby yet, the data in that segment is lost. On top of that, if archive_command fails or there is a network problem and WAL files cannot be transferred, the standby falls behind, and at some point replication breaks completely. Disk usage is another big downside: archived WALs need to be regularly managed and cleaned up.
-- primary postgresql.conf settings
archive_mode = on
archive_command = 'cp %p /mnt/server_archive/%f' -- Example: copies WAL files to another directory
log_destination = 'stderr' -- Optional: where logs go-- standby recovery.conf (or postgresql.conf in 12+) settings
standby_mode = on
primary_conninfo = 'host=primary_ip port=5432 user=replication_user password=your_password'
restore_command = 'cp /mnt/server_archive/%f %p' -- Example: pulls WAL files from the archive directory
recovery_target_timeline = 'latest'Early Streaming Replication: Wins and Limits
Streaming replication, introduced in PostgreSQL 9.0, was a real step up from file-based log shipping. It sends WAL records directly from the primary to the standby as a stream, skipping the archive-and-copy dance. That cuts replication lag significantly and shrinks the data-loss window.
Streaming replication holds a continuous TCP/IP connection between primary and standby through the primary_conninfo parameter. The wal_sender process on the primary ships WAL records, and the wal_receiver process on the standby applies them. The standby ends up much closer to the primary in time.
Early versions of streaming replication, however, were not as flexible or robust as what we have today. The replication slots feature, for example, only landed in PostgreSQL 9.4. Without slots, if the standby goes offline for a while or the primary cleans up WAL files based on wal_keep_segments, the standby can fall too far behind and replication breaks. Recovering from that meant taking a fresh base backup from the primary, which is time-consuming and resource-heavy.
-- primary postgresql.conf settings (example for PostgreSQL 9.x)
wal_level = hot_standby
max_wal_senders = 5 -- At least the number of standbys, ideally more
wal_keep_segments = 32 -- Each segment is 16MB. This setting can blow up your disk.
listen_addresses = '*'-- standby recovery.conf (example for PostgreSQL 9.x)
standby_mode = on
primary_conninfo = 'host=primary_ip port=5432 user=replication_user password=your_password'
recovery_target_timeline = 'latest'The Hidden Trap: WAL Segment Management and Disk Use
One of the sneakiest and most common traps in legacy PostgreSQL replication is mismanaging Write-Ahead Log (WAL) segments and ending up out of disk space. Modern PostgreSQL has reduced this risk a lot through features like replication slots, min_wal_size, and max_wal_size, but on legacy systems these controls are either missing or much more primitive.
WAL segments are small files (typically 16MB) that record every change made to the database. For replication to work properly, WAL segments produced on the primary have to make it to the standby and get applied. In older versions, the controls around how long the primary keeps WAL segments and when it cleans them up were more limited. That can lead to serious issues, especially in high-throughput systems or when replication breaks down.
wal_keep_segments and min_wal_size: When Settings Bite Back
In older PostgreSQL versions, the wal_keep_segments parameter controlled the minimum number of WAL segments that the primary held in pg_wal (formerly pg_xlog). It was used to limit how far behind the standby could fall. Get this setting wrong, and the consequences can be ugly.
If wal_keep_segments is set too low and the standby goes offline for a while because of a network blip, performance issue, or maintenance, the primary can clean up the segments the standby still needs. When the standby comes back, it cannot find them anymore, replication breaks, and you are stuck taking a full base backup to restart it.
If, on the other hand, wal_keep_segments is set too high (or left unset, or stays at the default) and replication does not run for a long time, the pg_wal directory grows out of control and can fill up the disk completely. When that happens, the primary cannot write any new WAL, all database operations stop, and you are facing a real outage that needs immediate intervention.
replication slots (PostgreSQL 9.4) and min_wal_size/max_wal_size (PostgreSQL 9.5) largely solved this. Slots stop the primary from cleaning up WAL segments the standby still needs, and the new size parameters let you manage the WAL directory more dynamically. On older versions, where none of this exists, WAL management becomes a manual chore.
-- An example of a dangerous config for PostgreSQL 9.x or earlier
-- postgresql.conf
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 16 -- If the standby is offline long enough, 16 segments will not be enough.In this scenario, with each WAL segment at 16MB, wal_keep_segments = 16 only gives you a 256MB buffer. On a write-heavy system, that buffer evaporates very quickly.
Replication Lag and WAL Buildup
Replication lag tells you how far the standby is behind the primary. On legacy systems, monitoring lag was harder, and the impact of that lag on WAL buildup on the primary was even more critical.
If the standby cannot keep up with changes from the primary fast enough, because of network issues, slow disk I/O, weak CPU, or heavy queries running on the standby, the lag grows. While the lag exists, the primary keeps producing new WAL segments. Even with wal_keep_segments set, if the lag is longer than what that setting permits, the primary will keep holding on to segments it would otherwise clean up.
That makes the pg_wal directory grow uncontrolled and eventually fills up the disk. When the primary cannot find disk space for critical operations, the database stops cold. On systems with heavy write workloads or standbys with prolonged issues, this can quickly turn into a real disaster.
Network Issues and Disk I/O: Performance Bottlenecks
Smooth replication does not just depend on a good config, it also depends on a solid network and high-performance disks. On legacy PostgreSQL replication setups, these infrastructure factors had a more visible impact and were harder to manage. They still matter on modern systems, but the less flexible WAL management on legacy systems makes their effects more destructive.
Bandwidth and Latency
As the name implies, streaming replication needs WAL records to flow continuously from the primary to the standby. For that flow to be healthy, you need enough network bandwidth and low enough latency.
- Low Bandwidth: When the primary is producing a lot of WAL data because of write-heavy workloads, and the network cannot move that data fast enough, WAL sender processes get backed up. WAL files pile up on the primary and you start risking running out of disk. This is especially common when replicating across geographically distant servers.
- High Network Latency: Network latency increases the time it takes for WAL records to get from the primary to the standby. That increases replication lag, and in synchronous replication setups (if you are using one), it directly impacts transaction performance on the primary. On older versions, monitoring and tuning around latency required more involved tooling.
Network issues are usually transient (short outages, packet loss), but persistent or recurring ones can keep replication permanently behind. That makes wal_keep_segments ineffective and leads to WAL buildup.
Why Disk I/O Matters
Disk I/O is just as important as network throughput, both on the primary and the standby.
- Disk I/O on the Primary: The primary writes WAL files constantly while processing transactions. If the disk subsystem is slow or under heavy I/O load, WAL writes slow down. That delays transactions and degrades overall performance. The rate at which WAL files are produced also affects how fast they can be shipped to the standby.
- Disk I/O on the Standby: The standby applies the WAL records it receives from the primary against its own database. That apply step is heavy on disk write I/O. If the standby’s disks are slow or busy with other I/O, it cannot apply WAL records fast enough. Replication lag goes up, and the lag itself raises the risk of WAL buildup back on the primary.
Old hardware or badly configured storage can create exactly these I/O bottlenecks. HDDs instead of SSDs, or wrongly chosen RAID levels, make things worse. For replication to be healthy, both sides need disk systems with enough I/O headroom.
Upgrades and Version Compatibility: Replication Is Brittle
The PostgreSQL ecosystem keeps moving forward, with significant improvements and new features in every major release. That progress, however, brings its own challenges, especially when you are upgrading from older versions or trying to set up replication between different versions. Legacy replication mechanisms were more brittle around version compatibility, and bad planning could lead to serious outages.
Major Version Differences and Replication
Major upgrades in PostgreSQL (for example, going from 9.x to 10.x or 11.x) can change the on-disk format. Physical replication (both file-based log shipping and streaming replication) requires the primary and standby to be on exactly the same major version. Physical replication across different major versions simply does not work.
That means upgrading an older PostgreSQL server forces you to upgrade your replication setup as well. The typical procedure looks like this:
- Stop replication on the standbys.
- Upgrade the primary (using
pg_upgrade, for example). - Take a fresh base backup from the upgraded primary.
- Rebuild the standbys from that base backup and restart replication.
For large databases, this process can be time-consuming and may need a planned downtime window. On legacy versions, the complexity of this process and the number of manual steps made it more error-prone. Modern versions, particularly with logical replication (PostgreSQL 10+) which works across major versions, made these upgrades a lot easier. That option, however, is not available on the older systems.
Minor Version Updates and Best Practices
Unlike major upgrades, minor version updates (like 9.6.1 to 9.6.2) usually do not change the on-disk format and do not break physical replication compatibility. So they can generally be applied more safely with less downtime.
You still want to be careful, though:
- Standbys First, Primary Last: Apply minor updates to the standbys first, then to the primary. That way, if something goes wrong on the primary, you can fail over to a standby quickly.
- Test Environment: Always test updates in a non-production environment first to catch any surprises.
- Documentation: Read the PostgreSQL release notes carefully before upgrading. Once in a while, even a minor update can require special handling.
On older PostgreSQL versions, finding good information about updates and getting community support could be harder. So even minor updates needed careful planning and execution. A safe upgrade strategy reduces the brittleness of replication and helps your system last longer.
Monitoring and Alerts: An Early Warning System
On legacy PostgreSQL replication setups, actively monitoring replication state and setting up early warning alerts was crucial. Modern systems have features like pg_replication_slots that automatically prevent replication from breaking, but legacy systems do not. That meant DBAs and sysadmins had to be much more vigilant. Monitoring is the first step to staying ahead of a potential disaster.
pg_stat_replication and pg_replication_slots (or the Lack of Them)
The pg_stat_replication view, introduced in PostgreSQL 9.1, is hugely valuable for monitoring streaming replication. It shows how far the standbys are behind the primary (replay_lag) and the state of WAL sending and applying.
-- To check replication status on the primary
SELECT
client_addr,
state,
sync_state,
sync_priority,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn,
(pg_current_wal_lsn() - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;This query shows, for each standby, how much WAL has been sent (sent_lsn), written to disk (write_lsn), flushed from OS cache to disk (flush_lsn), and applied to the database (replay_lsn). The replication_lag_bytes value tells you how far behind the standby is. If it keeps growing, that is a clear sign of a replication problem.
However, the replication slots feature did not exist before PostgreSQL 9.4. So if a standby went offline, the primary kept cleaning up WAL files based on wal_keep_segments. By the time the standby came back, the segments it needed could already be gone, and replication would break. With pre-9.4, pg_stat_replication could only show you the current state, not prevent a future problem.
Custom Scripting and Monitoring Tools
Because legacy systems lacked auto-protection mechanisms like replication slots, DBAs commonly wrote their own monitoring and alerting scripts. These scripts would track replication lag, the size of the WAL directory, and whether archive_command was succeeding.
Some script ideas:
- WAL Directory Size Check: A script that runs
du -sh /var/lib/postgresql/data/pg_walregularly and alerts you when the directory crosses a threshold. - Replication Lag Check: A script that queries
pg_stat_replicationat a chosen interval to trackreplay_lagand notifies you when it exceeds a limit. - Archive Command Success: A script that checks the age of the most recent file in the archive directory to confirm
archive_commandis still working (for file-based log shipping).
These kinds of scripts could be wired into enterprise monitoring like Nagios, Zabbix, or Prometheus to set up centralised observability and alerting. Early warnings give DBAs the chance to step in before issues escalate into a real outage.
Suggestions for Working With Legacy Systems
Working with legacy PostgreSQL replication takes more attention and more manual effort than modern setups. That does not mean it is hopeless, though. With the right strategies and proactive measures, you can run a reliable replication environment on legacy systems too. Here are some things I suggest:
Regular WAL Segment Cleanup and Archiving
If you are using file-based log shipping, or older streaming replication without replication slots, WAL segment management is on you.
- Archive Directory Management: If you archive WAL files via
archive_commandto a separate directory or remote storage, audit that directory regularly. Set up a cron job or script to automatically delete WAL files older than a certain age (say 7 days). When you do that cleanup, however, make sure you account for the needs ofbasebackupandPITR(Point-in-Time Recovery)…