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

PG how to stop streaming replication( master host)

From
"Ghiurea, Isabella"
Date:

Hi List

I am running PGSLQ 10.4 with repmgr in synchronous streaming replication  2 servers: master+slave, I would like to learn if there are any  options to stop replication ( no just  pause /resume  the WAL's) master to slave without  rebooting the master server  or the only option  is to take  the  Pg db slave host down and eventually the  the /wal directory will get 100% full .


Isabella

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

From
Scott Whitney
Date:
I use this during my backups to have a consistent state on my multiple slaves.


From: Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
Sent: Friday, December 14, 2018 4:46:55 PM
To: pgsql-admin@postgresql.org
Subject: PG how to stop streaming replication( master host)
 

Hi List

I am running PGSLQ 10.4 with repmgr in synchronous streaming replication  2 servers: master+slave, I would like to learn if there are any  options to stop replication ( no just  pause /resume  the WAL's) master to slave without  rebooting the master server  or the only option  is to take  the  Pg db slave host down and eventually the  the /wal directory will get 100% full .


Isabella



 

Scott Whitney
Director of IT

scott@journyx.com | +1 (800) 755-9878

7600 Burnet Road, Ste. 300
Austin, TX 78757

Visit us at www.journyx.com



To unsubscribe from Journyx promotional emails, click here or visit http://journyx.com/communication-preferences.

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

From
"Ghiurea, Isabella"
Date:

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   .




From: Scott Whitney <scott@journyx.com>
Sent: Friday, December 14, 2018 2:50 PM
To: pgsql-admin@postgresql.org; Ghiurea, Isabella
Subject: Re: PG how to stop streaming replication( master host)
 
I use this during my backups to have a consistent state on my multiple slaves.


From: Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
Sent: Friday, December 14, 2018 4:46:55 PM
To: pgsql-admin@postgresql.org
Subject: PG how to stop streaming replication( master host)
 

Hi List

I am running PGSLQ 10.4 with repmgr in synchronous streaming replication  2 servers: master+slave, I would like to learn if there are any  options to stop replication ( no just  pause /resume  the WAL's) master to slave without  rebooting the master server  or the only option  is to take  the  Pg db slave host down and eventually the  the /wal directory will get 100% full .


Isabella



 

Scott Whitney
Director of IT

scott@journyx.com | +1 (800) 755-9878

7600 Burnet Road, Ste. 300
Austin, TX 78757

Visit us at www.journyx.com



To unsubscribe from Journyx promotional emails, click here or visit http://journyx.com/communication-preferences.

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

From
Scott Whitney
Date:
Wow. That's a huge challenge.

What do you expect the masters and slaves to do at that time?

What is your goal here?

If you're inserting that much data over time of the backup, my only answer there is actual Oracle in a distributed cluster...

PG certainly can handle it, but we're now dealing with transaction situations.

Wow. If you're inserting that much during the backup, that's certainly something which can be designed on PG for sure. Is it a single DB on a single cluster?



From: Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
Sent: Friday, December 14, 2018 4:58:46 PM
To: pgsql-admin@postgresql.org
Subject: Re: PG how to stop streaming replication( master host)
 

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   .




From: Scott Whitney <scott@journyx.com>
Sent: Friday, December 14, 2018 2:50 PM
To: pgsql-admin@postgresql.org; Ghiurea, Isabella
Subject: Re: PG how to stop streaming replication( master host)
 
I use this during my backups to have a consistent state on my multiple slaves.


From: Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
Sent: Friday, December 14, 2018 4:46:55 PM
To: pgsql-admin@postgresql.org
Subject: PG how to stop streaming replication( master host)
 

Hi List

I am running PGSLQ 10.4 with repmgr in synchronous streaming replication  2 servers: master+slave, I would like to learn if there are any  options to stop replication ( no just  pause /resume  the WAL's) master to slave without  rebooting the master server  or the only option  is to take  the  Pg db slave host down and eventually the  the /wal directory will get 100% full .


Isabella



 

Scott Whitney
Director of IT

scott@journyx.com | +1 (800) 755-9878

7600 Burnet Road, Ste. 300
Austin, TX 78757

Visit us at www.journyx.com



To unsubscribe from Journyx promotional emails, click here or visit http://journyx.com/communication-preferences.

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

From
Ian Lawrence Barwick
Date:


2018年12月15日(土) 7:59 Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.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

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

From
"Jehan-Guillaume (ioguix) de Rorthais"
Date:
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 asynchronously
only fot this session or transaction. You could even create a dedicated batch
role with this parameter (alter role set ...) if you can not edit your batch 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 / shipping 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年12月15日(土) 7:59 Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.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
>



--
Jehan-Guillaume de Rorthais
Dalibo