Re: Best way to stop Streaming Replication?

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?


Ref: https://www.postgresql.org/docs/15/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL-TABLE

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