PostgreSQL 9.2 requires archiving for streaming replication?

Is it allowed and / or reasonable to configure the PostgreSQL 9.2 core server for NOT archiving, but for streaming replication. This is configured as shown below:

wal_level = hot_standby archive_mode = off 

Can I configure a β€œslave” server (hot standby) to archive WAL segments?

 wal_level = hot_standby hot_standby = on archive_mode = on 

This will reduce network traffic on the main server in half (replication, but not archiving). This seems reasonable, and the documentation seems to support this configuration, but I would prefer a little certainty that we have a good configuration.

+6
source share
1 answer

From the documentation (added by me separately):

If you use streaming replication without continuous file-based archiving, you need to set wal_keep_segments in the master file to a value high enough to ensure that the old WAL segments are not processed too early, while in standby mode you may still need to catch them . If the backup is too far behind, it must be reinitialized from the new backup database. If you set up a WAL archive that is accessible from standby, wal_keep_segments is not required, since in standby you can always use the archive to catch up.

So, from my understanding, when you have too many transactions, a slave may have a hard time staying in sync. Especially if the wizard deletes the WAL files before the subordinate really gets what is inside. Without archive_mode on the wizard, WAL files can be deleted, leaving no way to return them.

If you keep WAL archiving in place and add streaming to the hot spare-with-archives work structure, this cannot happen, because the subordinate can always access the archived WAL and will return unsynchronized transactions as soon as the lower activity on the stream allows this is. Without access to the archive, risk can obviously lose the integrity of your slave after some really hard things.

+7
source

Source: https://habr.com/ru/post/956097/


All Articles