I upgraded from Postgres 10 to Postgres 14 using pg_upgrade --link option. The total databases size is around 10TB. The pg_upgrade was successful and quick and like the tool suggested -
Optimizer statistics are not transferred by pg_upgrade. Once you start the new server, consider running: /usr/pgsql-14/bin/vacuumdb --all --analyze-in-stages
I ran the above command but the process is stuck. As a side effect of this (or not, not sure) when I create a publication the prompt never comes back and the publication is not created even after many hours.
postgres=# select * from pg_stat_progress_vacuum;
| c1 | c2 |
|---|---|
| pid | 9520 |
| datid | 16402 |
| datname | xyz |
| relid | 22423 |
| phase | vacuuming indexes |
| heap_blks_total | 232816470 |
| heap_blks_scanned | 36766348 |
| heap_blks_vacuumed | 0 |
| index_vacuum_count | 0 |
| max_dead_tuples | 11184809 |
| num_dead_tuples | 11184521 |
This is the same output from yesterday. What can I do to speed up this and the "create publication" command? On a side note: The VM on which Postgres is running is quite powerful (64GB RAM, 16 cores). Thanks!
edit 1: the output of pg_stat_activity for the same pid,
| c1 | c2 |
|---|---|
| pid | 9520 |
| backend_start | 2021-12-06 15:13:23.479071-08 |
| xact_start | 2021-12-06 15:13:23.512581-08 |
| query_start | 2021-12-06 15:13:23.512581-08 |
| state_change | 2021-12-06 15:13:23.512581-08 |
| wait_event_type | Timeout |
| wait_event | VacuumDelay |
| state | active |
| backend_xmin | 3140627534 |
| query | autovacuum: VACUUM xyz (to prevent wraparound) |
| backend_type | autovacuum worker |
vacuumdb --all --analyze-in-stages will not run VACUUM, but ANALYZE, so you have to look into pg_stat_progress_analyze to see how it is doing.
The VACUUM process you see running is unrelated to that. It is an anti-wraparound vacuum that is currently sleeping, but otherwise processing. Let it finish; this process is important for the health of your database. If you want further autovacuum runs on that table to complete faster, reduce autovacuum_vacuum_cost_delay for that table.