pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPG how to stop streaming replication( master host)
6+ messages / 4 participants
[nested] [flat]
* PG how to stop streaming replication( master host)
@ 2018-12-14 22:46 Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
0 siblings, 1 reply; 6+ messages in thread
From: Ghiurea, Isabella @ 2018-12-14 22:46 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PG how to stop streaming replication( master host)
@ 2018-12-14 22:50 Scott Whitney <scott@journyx.com>
parent: Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
0 siblings, 1 reply; 6+ messages in thread
From: Scott Whitney @ 2018-12-14 22:50 UTC (permalink / raw)
To: pgsql-admin; Ghiurea, Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
I use this during my backups to have a consistent state on my multiple slaves.
https://coderwall.com/p/nirwxa/pause-and-resume-hot_standby-replication-on-postgres-database
________________________________
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
[http://journyx.com/emailImages/ScottWhitney.png]
[http://journyx.com/emailImages/linkedin.png]<https://www.linkedin.com/company/journyx-inc/; [http://journyx.com/emailImages/twitter.png] <https://www.twitter.com/journyxinc;
[http://journyx.com/emailImages/bluebar.jpg]
Scott Whitney
Director of IT
[http://journyx.com/emailImages/journyx.jpg]
scott@journyx.com<mailto:scott@journyx.com> | +1 (800) 755-9878
7600 Burnet Road, Ste. 300
Austin, TX 78757
Visit us at www.journyx.com<https://www.journyx.com/;
To unsubscribe from Journyx promotional emails, click here <http://journyx.com/communication-preferences; or visit http://journyx.com/communication-preferences.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PG how to stop streaming replication( master host)
@ 2018-12-14 22:58 Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
parent: Scott Whitney <scott@journyx.com>
0 siblings, 2 replies; 6+ messages in thread
From: Ghiurea, Isabella @ 2018-12-14 22:58 UTC (permalink / raw)
To: pgsql-admin
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.
https://coderwall.com/p/nirwxa/pause-and-resume-hot_standby-replication-on-postgres-database
________________________________
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
[http://journyx.com/emailImages/ScottWhitney.png]
[http://journyx.com/emailImages/linkedin.png]<https://www.linkedin.com/company/journyx-inc/; [http://journyx.com/emailImages/twitter.png] <https://www.twitter.com/journyxinc;
[http://journyx.com/emailImages/bluebar.jpg]
Scott Whitney
Director of IT
[http://journyx.com/emailImages/journyx.jpg]
scott@journyx.com<mailto:scott@journyx.com> | +1 (800) 755-9878
7600 Burnet Road, Ste. 300
Austin, TX 78757
Visit us at www.journyx.com<https://www.journyx.com/;
To unsubscribe from Journyx promotional emails, click here <http://journyx.com/communication-preferences; or visit http://journyx.com/communication-preferences.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PG how to stop streaming replication( master host)
@ 2018-12-14 23:20 Scott Whitney <scott@journyx.com>
parent: Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
1 sibling, 0 replies; 6+ messages in thread
From: Scott Whitney @ 2018-12-14 23:20 UTC (permalink / raw)
To: pgsql-admin; Ghiurea, Isabella <isabella.ghiurea@nrc-cnrc.gc.ca>
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.
https://coderwall.com/p/nirwxa/pause-and-resume-hot_standby-replication-on-postgres-database
________________________________
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
[http://journyx.com/emailImages/ScottWhitney.png]
[http://journyx.com/emailImages/linkedin.png]<https://www.linkedin.com/company/journyx-inc/; [http://journyx.com/emailImages/twitter.png] <https://www.twitter.com/journyxinc;
[http://journyx.com/emailImages/bluebar.jpg]
Scott Whitney
Director of IT
[http://journyx.com/emailImages/journyx.jpg]
scott@journyx.com<mailto:scott@journyx.com> | +1 (800) 755-9878
7600 Burnet Road, Ste. 300
Austin, TX 78757
Visit us at www.journyx.com<https://www.journyx.com/;
To unsubscribe from Journyx promotional emails, click here <http://journyx.com/communication-preferences; or visit http://journyx.com/communication-preferences.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PG how to stop streaming replication( master host)
@ 2018-12-15 02:46 Ian Lawrence Barwick <barwick@gmail.com>
parent: Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
1 sibling, 1 reply; 6+ messages in thread
From: Ian Lawrence Barwick @ 2018-12-15 02:46 UTC (permalink / raw)
To: Isabella.Ghiurea@nrc-cnrc.gc.ca; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PG how to stop streaming replication( master host)
@ 2018-12-15 07:56 Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
parent: Ian Lawrence Barwick <barwick@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Jehan-Guillaume (ioguix) de Rorthais @ 2018-12-15 07:56 UTC (permalink / raw)
To: Isabella.Ghiurea@nrc-cnrc.gc.ca; +Cc: Ian Lawrence Barwick <barwick@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2018-12-15 07:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-12-14 22:46 PG how to stop streaming replication( master host) Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
2018-12-14 22:50 ` Scott Whitney <scott@journyx.com>
2018-12-14 22:58 ` Ghiurea, Isabella <Isabella.Ghiurea@nrc-cnrc.gc.ca>
2018-12-14 23:20 ` Scott Whitney <scott@journyx.com>
2018-12-15 02:46 ` Re: PG how to stop streaming replication( master host) Ian Lawrence Barwick <barwick@gmail.com>
2018-12-15 07:56 ` Re: PG how to stop streaming replication( master host) Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox