Re: Best way to stop Streaming Replication?

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(at)gmail(dot)com> wrote:

> On Fri, Dec 22, 2023 at 4:38 PM Samed YILDIRIM <samed(at)reddoc(dot)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?
>> Ref:
>> https://www.postgresql.org/docs/15/functions-admin.html#FUNCTIONS-RECOVERY-CONTROL-TABLE
>>
>
> Yes, that's for pausing, not stopping.
>
>
>> Best regards.
>> Samed YILDIRIM
>>
>>
>> On Fri, 22 Dec 2023 at 23:12, kaido vaikla <kaido(dot)vaikla(at)gmail(dot)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(at)gmail(dot)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
>>>>
>>>