0

I wanted to check if there is a way to stop and resume PostgreSQL replication using pglogical? For some reason, if either the publisher or subscriber needs to restart and go offline for sometime (or connectivity issues because of some n/w issues), is there a way to stop the replication and resume it again? I know it is not a relevant example but AWS DMS (which used Postgres native logical replication) gives you an option to stop/resume the replication. Wanted to check if there is a similar option available in pglogical.

Thanks

P_Ar
  • 377
  • 2
  • 9
  • 25
  • If the network is down then it is stopped already. If you don't like the log pollution from failed reconnect attempts, you can ALTER SUBSCRIPTION ... DISABLE. – jjanes Feb 25 '22 at 17:52
  • @jjanes even after disable subscription, publisher server accumulating storage. any solution? – Naresh Tank Apr 02 '22 at 07:15
  • @NareshTank It is unclear what you want a solution *to*. If you want to eventually resume the replica, you need to keep the WAL needed to do that. If you don't, then drop its slot on the master. – jjanes Apr 02 '22 at 16:00
  • @jjanes thanks for reply, let me clear. I will resume the replication and I don't want to lose data so can't drop slot. even in ideal (zero connection at night time) sate WAL is accumulating storage. I want to stop or reduce storage accumulation. – Naresh Tank Apr 04 '22 at 06:20

1 Answers1

0

Sure there is. I presume You have pglogical extension installed and there is standard logical replication:

select pglogical.alter_subscription_disable('subscription_name');

You can use a WHERE clause to aim it specifically:

select pglogical.alter_subscription_disable(subscription_name) from pglogical.subscriptions where writer = 'name_of_writer';
ďobo
  • 326
  • 2
  • 4