Re: Best way to stop Streaming Replication? - Mailing list pgsql-admin
| From | Samed YILDIRIM |
|---|---|
| Subject | Re: Best way to stop Streaming Replication? |
| Date | |
| Msg-id | CAAo1mbmer8vP2y=0qFuTeseng7n+z5J+MNnPsOPHjz5PgithXw@mail.gmail.com Whole thread Raw |
| In response to | Re: Best way to stop Streaming Replication? (Ron Johnson <ronljohnsonjr@gmail.com>) |
| Responses |
Re: Best way to stop Streaming Replication?
|
| List | pgsql-admin |
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
pgsql-admin by date: