Home / server / Questions / 554209
In Process
Peter Westlake
Asked: 2013-11-12 10:15:57 +0800 CST

How to replicate a PostgreSQL database through a firewall?

  • 772

I have the following requirements for replication:

  1. There is a primary server, which does all data modification.
  2. There is a secondary server, usable for read-only queries, up-to-date within a minute or so.
  3. The primary can access the secondary over the network, but the secondary cannot reach the primary.

The one-way access requirement is the one that causes the difficulty for the replication methods described in the documentation.

Ideally the solution should work using packages available in Debian Stable.

postgresql

1 Answers

  1. You may use log shipping only (not streaming replication) with archive_timeout set to 60 seconds.

    This delay is referred to in the doc as an acceptable minimum. Quotes:

    To limit how old unarchived data can be, you can set archive_timeout to force the server to switch to a new WAL segment file periodically. When this parameter is greater than zero, the server will switch to a new segment file whenever this many seconds have elapsed since the last segment file switch, and there has been any database activity, including a single checkpoint [...]

    Note that archived files that are closed early due to a forced switch are still the same length as completely full files. Therefore, it is unwise to use a very short archive_timeout — it will bloat your archive storage. archive_timeout settings of a minute or so are usually reasonable

    Running read-only queries on such a slave is a different problem. The doc says to set wal_level to hot_standby:

    To enable read-only queries on a standby server, wal_level must be set to hot_standby on the primary, and hot_standby must be enabled in the standby

    Once hot_standby is on, the communication with the primary is supposed to work too. The various settings that control it like wal_receiver_status_interval, wal_receiver_timeout, max_standby_streaming_delay can be disabled one by one, but it's not mentioned in the doc that we may just disable the mechanism as a whole.

    As an experiment, it might be interesting to see what happens when the connection to a primary is perpetually down (as opposed to temporarily down, which the standby should deal with for the sake of robustness). But as the normal operating mode for a production server that seems weird.

    • 1