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

["Westwood, Giles" <g.westwood@xxxxxxxxx>]

On Fri, Sep 24, 2021 at 5:02 PM Tim <timfosho@xxxxxxxxx> 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