Обсуждение: Best way to stop Streaming Replication?

Поиск
Список
Период
Сортировка

Best way to stop Streaming Replication?

От
Ron Johnson
Дата:
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

Re: Best way to stop Streaming Replication?

От
kaido vaikla
Дата:
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

Re: Best way to stop Streaming Replication?

От
Samed YILDIRIM
Дата:
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.
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

Re: Best way to stop Streaming Replication?

От
Ron Johnson
Дата:
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?
 

Yes, that's for pausing, not stopping.
 
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.
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

Re: Best way to stop Streaming Replication?

От
Samed YILDIRIM
Дата:
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?
 

Yes, that's for pausing, not stopping.
 
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.
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

Re: Best way to stop Streaming Replication?

От
Ron Johnson
Дата:
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 

pg_repack vs. running logical/physical replication

От
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 :-)


Thanks and best regards


Dirk




Re: pg_repack vs. running logical/physical replication

От
Achilleas Mantzios
Дата:
Στις 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)




Re: pg_repack vs. running logical/physical replication

От
Ron Johnson
Дата:
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.
 

Re: pg_repack vs. running logical/physical replication

От
Laurenz Albe
Дата:
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