Re: Performance for initial copy when using pg_logical to upgrade Postgres

On Fri, Sep 24, 2021 at 5:02 PM Tim <timfosho(at)gmail(dot)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