Re: PG how to stop streaming replication( master host)

2018年12月15日(土) 7:59 Ghiurea, Isabella <Isabella(dot)Ghiurea(at)nrc-cnrc(dot)gc(dot)ca>:

> 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 "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