0

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.

voretaq7
  • 79,879
  • 17
  • 130
  • 214
Peter Westlake
  • 806
  • 2
  • 6
  • 17
  • How is the one-way access requirement a problem? What *specifically* prevents you from implementing [streaming replication](http://www.postgresql.org/docs/current/interactive/warm-standby.html#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 can create vpn, standby server as vpn server. Then connect using vpn ip address – Superbiji Apr 24 '19 at 05:54

1 Answers1

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.

Daniel Vérité
  • 3,045
  • 16
  • 19