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.

  • How is the one-way access requirement a problem? What specifically prevents you from implementing streaming replication by opening appropriate firewall ports? – voretaq7 Nov 11 '13 at 18:34
  • The one-way requirement is a problem because the docs for streaming replication say, "The standby connects to the primary...", and it isn't allowed to do that. This is an absolute requirement of the system, not a technical limitation. The sole reason for having the secondary server is to allow clients outside the wall to perform queries. – Peter Westlake Nov 11 '13 at 19:00
  • 1
    Then you're asking for the impossible - if the standby server cannot have communication with the primary server your only option is log shipping, which violates your near-real-time queryable slave requirement in (2). – voretaq7 Nov 11 '13 at 19:51
  • How much delay would be involved? The events being recorded in the database happen on a timescale of hours, so a few minutes would be acceptable. Otherwise I'll have the application write to the second database directly. It can avoid problems caused by now() and the like by reading what is actually written, not just unthinkingly repeating statements. – Peter Westlake Nov 12 '13 at 11:42
  • Log shipping operates per log segment, and it's a nondeterministic value between segments (either "when the server is configured to switch segments", "when some event triggers a segment switch", or "when the current segment is full") -- practically I've never seen/operated a server with segment rotation faster than once per hour - "minutes" would be unreasonable. You are pursuing bad solutions -- the correct thing to do is rethink your architecture. – voretaq7 Nov 12 '13 at 16:31

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.

  • This is helpful, thanks, but it does sound as though this is a very unsupported configuration! – Peter Westlake Nov 15 '13 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.