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 |
CodePudding user response:
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.