2018年12月15日(土) 7:59 Ghiurea, Isabella <Isabella.Ghiurea@xxxxxxxxxxxxxx>:
Thank you Scott, this will only pause the WAL's .
if you have a lot of activity happening in master while slave db is been backup this can fail behind a lot and eventually can fill up /wal directory. I also use the option you mentioned , but I was looking for an option to actually disable the replication at master host completely since I must insert 200GB of content in db and next I can re-enable replication using resync slave with master ( pg_restore). I am trying to have the replication disabled while I am loading that amount of data to not affect the performance of insert .
200GB is not a trivial amount of data, but certainly nothing exceptional these days.
You can "stop" replication simply by stopping the attached standby(s).
Whether that will cause the wal directory to fill up depends on how the primary (master) is configured.
If the standby is using a replication slot, and is offline, the primary will continue to accumulate WAL files until the standby comes back on line. If that's the case, you'd need to drop the replication slot.
If you have an "archive_command" defined, WAL files will be kept until they are successfully archived, which can be slow if e.g. they're being uploaded to cloud storage.
If replication slots/WAL archiving is not an issue, WAL will generally grow to around the value specified by "max_wal_size" , but is unlikely to exceed it by a substantial amount; see the documentation for details [1]; also check the value of "
[1] https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-MAX-WAL-SIZE
wal_keep_segments".[1] https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-MAX-WAL-SIZE
You mention pg_restore - that's for restoring dumps made with pg_dump, and won't work for re-syncing the standby. If you don't have the WAL files available anyway, you'll need to re-clone the standby from scratch, using e.g. pg_basebackup or repmgr
A more efficient, less risky approach, which will keep the standby online without causing problems for the primary, would be to use some kind of intermediate server to store the generated WAL [2]; the standby can then retrieve WAL from there, meaning the WAL doesn't need to be stored on the primary. It's still possible - depending on factors such as load, hardware, configuration - that the standby will fall behind, but that will likely be preferable to a full re-clone, and if managed properly you'll have a backup available at all times.
[2] e.g. Barman; see https://www.pgbarman.org/
Regards
Ian Barwick
--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
