Document index disabling method and initial copy order

[gilesw]

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]

@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]

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]

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.