On Fri, Sep 24, 2021 at 5:02 PM Tim <timfo...@gmail.com> wrote: > I'm currently doing this with a 2.2 TB database. > > Best way IMO is to (UPDATE pg_index SET indisready = false ... ) for non > PK indexes for the largest tables. Then just set it back to indisready = > true after its done and run a REINDEX CONCURRENTLY on the indexes that were > disabled. > > Got about a transfer speed of 100GB per 50 minutes with this method with > consistent results. >
Thanks Tim, that has worked great. I'm trying to automate the whole process but I can't see a way of seeing when the initial pglogical copy is complete short of checking the disk space. All I've found is:- select * from pglogical.local_sync_status; sync_kind | sync_subid | sync_nspname | sync_relname | sync_status | sync_statuslsn -----------+------------+--------------+--------------+-------------+---------------- d | 1821676733 | | | d | 0/0 (1 row) or xxx=# select * from pg_stat_replication ; -[ RECORD 1 ]----+-------------------------------- pid | 3469521 usesysid | 77668435 usename | xxx application_name | xxxx_snap client_addr | 10.44.16.83 client_hostname | client_port | 52594 backend_start | 2021-10-27 12:51:17.618734+00 backend_xmin | 221892481 state | startup sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async
