Date: Sat, 15 Dec 2018 08:56:25 +0100
From: "Jehan-Guillaume (ioguix) de Rorthais" <ioguix@free.fr>
To: Isabella.Ghiurea@nrc-cnrc.gc.ca
Cc: Ian Lawrence Barwick <barwick@gmail.com>, pgsql-admin
 <pgsql-admin@postgresql.org>
Subject: Re: PG how to stop streaming replication( master host)
Message-ID: <20181215085625.14d8c172@firost>
In-Reply-To: 
 <CAB8KJ=hNLcMLPd5cKgmFMPNyAHmWcU2FWGUBjf3kY=90oSRK6g@mail.gmail.com>
References: <1544827614785.42348@nrc-cnrc.gc.ca>
	<SN6PR11MB2976E78D06B066CCAC7C5F7DA7A10@SN6PR11MB2976.namprd11.prod.outlook.com>
	<1544828326249.49231@nrc-cnrc.gc.ca>
	<CAB8KJ=hNLcMLPd5cKgmFMPNyAHmWcU2FWGUBjf3kY=90oSRK6g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

Hi,

If you don't want to pay the performance penalty of synchronous replication
during your batch, "set synchronous_commit to local" in your
session/transaction. Your replication will still be active, but asynchronou=
sly
only fot this session or transaction. You could even create a dedicated bat=
ch
role with this parameter (alter role set ...) if you can not edit your batc=
h to
set it there.

If the replication is network bound, the standby will likely disconnect as =
soon
as it is lagging too much anyway. So make sure your wal archiving / shippin=
g is
working fine and fast enough.

If you have slots, drop them first if you are afraid of disk saturation.

If you decide to stop your standbies (you have at least two of them right?)=
 and
rebuild them, I would advice to restore a PITR backup: this is faster and
lighter for your prod.

And my church would be to use pgbackrest for backup, archiving and restore =
:)

++
On Sat, 15 Dec 2018 11:46:26 +0900
Ian Lawrence Barwick <barwick@gmail.com> wrote:

> 2018=E5=B9=B412=E6=9C=8815=E6=97=A5(=E5=9C=9F) 7:59 Ghiurea, Isabella <Is=
abella.Ghiurea@nrc-cnrc.gc.ca>:
>=20
> > Thank you Scott, this will only pause the WAL's  .
> >
> > if you have a lot of activity happening in master while slave  db is be=
en
> > 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 exceptiona=
l =20
> these days.
>=20
> You can "stop" replication simply by stopping the attached standby(s).
>=20
> Whether that will cause the wal directory to fill up depends on how the
> primary (master) is configured.
>=20
> 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 lin=
e.
> If that's the case, you'd need to drop the replication slot.
>=20
> If you have an "archive_command" defined, WAL files will be kept until th=
ey
> are successfully archived, which can be slow if e.g. they're being upload=
ed
> to cloud storage.
>=20
> If replication slots/WAL archiving is not an issue, WAL will generally gr=
ow
> to around the value specified by "max_wal_size" , but is unlikely to exce=
ed
> it by a substantial amount; see the documentation for details [1]; also
> check the value of "wal_keep_segments".
>=20
> [1]
> https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-MAX-W=
AL-SIZE
>=20
> 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
>=20
> 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, hardwar=
e,
> 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 back=
up
> available at all times.
>=20
> [2] e.g. Barman; see https://www.pgbarman.org/
>=20
> Regards
>=20
> Ian Barwick
>=20


--=20
Jehan-Guillaume de Rorthais
Dalibo