search

2ndQuadrant / pglogical

Logical Replication extension for PostgreSQL 17, 16, 15, 14, 13, 12, 11, 10, 9.6, 9.5, 9.4 (Postgres), providing much faster replication than Slony, Bucardo or Londiste, as well as cross-version upgrades.
http://2ndquadrant.com/en/resources/pglogical/
Other
1.01k stars 153 forks source link

Document index disabling method and initial copy order #350

Open gilesw opened 2 years ago

gilesw commented 2 years ago

We're using this technique of disabling the indexes of our pglogical slave then triggering the pglogical subscription which performs the copy, then when complete it starts writing the delta. The problem is we have a window after the copy has run and we haven't rebuilt and re-enabled the indexes when conflicts happen:-


2021-10-27 18:25:46 UTC [450025]: [852275-1] user=[unknown],db=xxxx,app=pglogical apply 16393:1821676733,client= LOG:  CONFLICT: remote UPDATE on relation public.notification_item replica identity index notification_activity_pkey (tuple not found). Resolution: skip.

So in order to catch it as fast as possible I'm looking to find when the initial copy has finished by repeating this until it succeeds:-

select status FROM pglogical.show_subscription_status();\" | grep replicating'

but it would be nice to know what order the copies happened so I could potentially enable some indexes earlier. Generally some documentation on this method would help people out.

I've dug about in the pg_stat_activity table and I can see the copy commands happening but I can't work out the order.

joshuabaird commented 2 years ago

@gilesw Not sure if this will help, but you can also use pglogical.wait_for_subscription_sync_complete(sub_name) which will block until the initial sync is complete.

I don't think this helps your ordering challenge, though.

eulerto commented 2 years ago

You can use the function pglogical.pglogical.wait_for_table_sync_complete that waits until an specific table is synchronized. However, it won't return until the sync is done.

The other option is to repeatedly query the table pglogical.local_sync_status and check the sync_status column until you get the desired status.

gilesw commented 2 years ago

Thanks chaps I'll add that to my function library. Turns out I hadn't understood the index disabling method only works for non primary key indexes. We went for the basebackup initialize convert to pglogical method (pglogical_create_subscriber.) Again it would be good if the different methods with different pro's and cons were spelt out in the docs.