The Build

13:04

Why you want WAL archiving as well as streaming replication

4 October 2012

A client of ours recently had me log into their server to set up a tablespace scheme for them. While I was in, I noticed that the secondary of the streaming replication pair wasn’t connecting to the primary. A quick check showed that the primary had been moved from one internal IP address to another, and in doing so everything had been updated except the pg_hba.conf file… so the secondary wasn’t able to connect.

This had happened several weeks prior.

The good news is that in addition to streaming replication, we had set up WAL archiving from the primary to the secondary, so the secondary was staying up to date using the WAL segments. We didn’t have to reimage the secondary; just fixing the pghba.conf and reloading the primary fixed the problem. Thanks to pgarchivecleanup, neither side was building up WAL segments.

There are several good reasons for including WAL archiving in your streaming replication setup. This kind of accidental problem is one of them.

RobJ at 07:46, 5 October 2012:

Did the client monitor their secondary server to ensure that it was never too far behind the primary? If they did, and WAL archiving kept the secondary server relatively current, then it’s understandable that they didn’t know about the pg_hba.conf problem.

Comment