Обсуждение: Best way to stop Streaming Replication?
PG 14.10 and 15.5, if it matters.
Running pg_terminate_backend() on the walsender pid (and then dropping the replication slot) seems the canonical solution, based on StackExchange, but that leaves primary_conninfo in postgresql.auto.conf and standby.signal in $PGDATA.
Promoting the secondary (and then dropping the replication slot) handles all those issues, but leaves it in R/W mode, which might cause split brain issues with cluster managers.
Is there a better way than the two I've described
Use a some tools like patroni. I'm patroni user since 2016 and happy with it.
br
Kaido
On Fri, 22 Dec 2023, 21:49 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
PG 14.10 and 15.5, if it matters.Running pg_terminate_backend() on the walsender pid (and then dropping the replication slot) seems the canonical solution, based on StackExchange, but that leaves primary_conninfo in postgresql.auto.conf and standby.signal in $PGDATA.Promoting the secondary (and then dropping the replication slot) handles all those issues, but leaves it in R/W mode, which might cause split brain issues with cluster managers.Is there a better way than the two I've described
Hi Ron,
What is your main goal?
What is the purpose of stopping replication?
What will you do with replication when you have stopped it?
Are you trying to stop replaying transaction logs from the master and keep the replica running?
Will you resume log replay at some point?
Do you want to break replication completely while keeping standby receiving read-only queries?
Have you checked the pg_wal_replay_pause() function
?
Best regards.
Samed YILDIRIM
On Fri, 22 Dec 2023 at 23:12, kaido vaikla <kaido.vaikla@gmail.com> wrote:
Use a some tools like patroni. I'm patroni user since 2016 and happy with it.brKaidoOn Fri, 22 Dec 2023, 21:49 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:PG 14.10 and 15.5, if it matters.Running pg_terminate_backend() on the walsender pid (and then dropping the replication slot) seems the canonical solution, based on StackExchange, but that leaves primary_conninfo in postgresql.auto.conf and standby.signal in $PGDATA.Promoting the secondary (and then dropping the replication slot) handles all those issues, but leaves it in R/W mode, which might cause split brain issues with cluster managers.Is there a better way than the two I've described
On Fri, Dec 22, 2023 at 4:38 PM Samed YILDIRIM <samed@reddoc.net> wrote:
Hi Ron,What is your main goal?
The main goal is to stop replication, not pause it. (I chose the word carefully, but your questions are understandable.)
What is the purpose of stopping replication?What will you do with replication when you have stopped it?
Are you trying to stop replaying transaction logs from the master and keep the replica running?
Want the replica still up, but does not matter if it's in replica mode.
Will you resume log replay at some point?
No.
Do you want to break replication completely while keeping standby receiving read-only queries?
How do you do that?
Have you checked the pg_wal_replay_pause() function?
Yes, that's for pausing, not stopping.
Best regards.Samed YILDIRIMOn Fri, 22 Dec 2023 at 23:12, kaido vaikla <kaido.vaikla@gmail.com> wrote:Use a some tools like patroni. I'm patroni user since 2016 and happy with it.brKaidoOn Fri, 22 Dec 2023, 21:49 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:PG 14.10 and 15.5, if it matters.Running pg_terminate_backend() on the walsender pid (and then dropping the replication slot) seems the canonical solution, based on StackExchange, but that leaves primary_conninfo in postgresql.auto.conf and standby.signal in $PGDATA.Promoting the secondary (and then dropping the replication slot) handles all those issues, but leaves it in R/W mode, which might cause split brain issues with cluster managers.Is there a better way than the two I've described
Hi Ron,
You probably create your replica by using pg_basebackup along with its -R (write recovery conf), --create-slot, and --slot=SLOTNAME options.
If your purpose is to create a read-only copy of a database without active replication, I recommend you to skip all 3 parameters, and then, create a standby.signal file, which is an empty file, in the fresh base backup directory. Finally, you can start your postgresql instance against the data directory. The new cluster will be a read-only replica instance without replicating anything from anywhere. And, it will not leave any leftover on the master database, such as a replication slot to be dropped manually. However, you should still check restore_command and primary_conninfo in the configuration of the new replica. You should delete their values if there is any. Otherwise, it can try to fetch transaction logs from the archive depending on your setup. Or, it can try to connect a primary server for replication.
If you already have a replication created with a replication slot, then, you can clean restore_command and primary_conninfo settings on replica, reload configuration of the replica, and then remove the leftover replication slot from the master. For example:
- on replica
- alter system set primary_conninfo to '';
- alter system set restore_command to '';
- select pg_reload_conf();
- on master
- select pg_drop_replication_slot('ron_replication_slot1');
Best regards.
Samed YILDIRIM
On Sat, 23 Dec 2023 at 19:34, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Fri, Dec 22, 2023 at 4:38 PM Samed YILDIRIM <samed@reddoc.net> wrote:Hi Ron,What is your main goal?The main goal is to stop replication, not pause it. (I chose the word carefully, but your questions are understandable.)What is the purpose of stopping replication?What will you do with replication when you have stopped it?Are you trying to stop replaying transaction logs from the master and keep the replica running?Want the replica still up, but does not matter if it's in replica mode.Will you resume log replay at some point?No.Do you want to break replication completely while keeping standby receiving read-only queries?How do you do that?Have you checked the pg_wal_replay_pause() function?Yes, that's for pausing, not stopping.Best regards.Samed YILDIRIMOn Fri, 22 Dec 2023 at 23:12, kaido vaikla <kaido.vaikla@gmail.com> wrote:Use a some tools like patroni. I'm patroni user since 2016 and happy with it.brKaidoOn Fri, 22 Dec 2023, 21:49 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:PG 14.10 and 15.5, if it matters.Running pg_terminate_backend() on the walsender pid (and then dropping the replication slot) seems the canonical solution, based on StackExchange, but that leaves primary_conninfo in postgresql.auto.conf and standby.signal in $PGDATA.Promoting the secondary (and then dropping the replication slot) handles all those issues, but leaves it in R/W mode, which might cause split brain issues with cluster managers.Is there a better way than the two I've described
On Sat, Dec 23, 2023
If you already have a replication created with a replication slot, then, you can clean restore_command and primary_conninfo settings on replica, reload configuration of the replica, and then remove the leftover replication slot from the master. For example:
- on replica
- alter system set primary_conninfo to '';
- alter system set restore_command to '';
- select pg_reload_conf();
- on master
- select pg_drop_replication_slot('ron_replication_slot1');
Excellent. Thank you
Hi, is there any good reason to cut of logical and/or physical replication for the time pg_repack (please no discussion about pg_repack at all, customer request, his idea, his strict wish) is running? I am not so deep into how pg_repack is running things but as the docs are saying it could affect hard at least the publications for logrep. Haven't tested it yet and just hopeing for a quick answer here :-) Thanks and best regards Dirk
Στις 29/1/24 16:56, ο/η Dirk Krautschick έγραψε: > Hi, > > > is there any good reason to cut of logical and/or physical replication > for the time pg_repack (please no discussion about pg_repack at all, > customer request, his idea, his strict wish) is running? I am not so > deep into how pg_repack is running things but as the docs are saying > it could affect hard at least the publications for logrep. Haven't > tested it yet and just hopeing for a quick answer here :-) Why should logical replication break? Why would you want to fill your repl slots (if any) by suspending physical replication while pg_repack runs? I would advise reading about pg_repack and/or testing yourself to see how it works. > > > Thanks and best regards > > > Dirk > > > -- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only)
On Mon, Jan 29, 2024 at 2:27 PM Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> wrote:
Στις 29/1/24 16:56, ο/η Dirk Krautschick έγραψε:
> Hi,
>
>
> is there any good reason to cut of logical and/or physical replication
> for the time pg_repack (please no discussion about pg_repack at all,
> customer request, his idea, his strict wish) is running? I am not so
> deep into how pg_repack is running things but as the docs are saying
> it could affect hard at least the publications for logrep. Haven't
> tested it yet and just hopeing for a quick answer here :-)
Why should logical replication break?
LR gets fiddly, and needs manual attention when DDL changes are applied, no?
For example, we're not using LR to migrate from 9.6 to 14 because the application regularly makes DDL modifications (inheritance partitioning: dropping old tables, creating new ones, and dropping/recreating the triggers; 71 of them). I don't trust my ability to catch all those changes, and apply them to the new server.
Why would you want to fill your repl slots (if any) by suspending
physical replication while pg_repack runs?
Fear of corrupting the destination.
On Mon, 2024-01-29 at 20:00 -0500, Ron Johnson wrote: > On Mon, Jan 29, 2024 at 2:27 PM Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> wrote: > > Στις 29/1/24 16:56, ο/η Dirk Krautschick έγραψε: > > > is there any good reason to cut of logical and/or physical replication > > > for the time pg_repack (please no discussion about pg_repack at all, > > > customer request, his idea, his strict wish) is running? I am not so > > > deep into how pg_repack is running things but as the docs are saying > > > it could affect hard at least the publications for logrep. Haven't > > > tested it yet and just hopeing for a quick answer here :-) > > > > Why should logical replication break? > > LR gets fiddly, and needs manual attention when DDL changes are applied, no? Yes, but pg_repack doesn't change the table definition or contents, so it should be able to work with logical replication. I guess the question is if pg_repack WAL logs everything correctly. If yes, I'd assume that it should work as well with logical replication as VACUUM (FULL). I guess you'd have to try it out, perhaps with a concurrent pgbench run. Yours, Laurenz Albe