On 5/12/20 5:27 AM, Kouber Saparev wrote:
I am trying to upgrade PostgreSQL from 9.5 to 12 using pg_upgrade.
/usr/lib/postgresql/12/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/9.5/main \
--new-datadir=/var/lib/postgresql/12/main \
--old-bindir=/usr/lib/postgresql/9.5/bin \
--new-bindir=/usr/lib/postgresql/12/bin \
--old-options '-c config_file=/etc/postgresql/9.5/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' \
--link \
--jobs=16
It takes 47 minutes for the upgrade to finish (for a 28 GB database). It
hangs on two particular steps:
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Are you sure?
From here:
~/src/bin/pg_upgrade/pg_upgrade.c
prepare_new_cluster(void)
{
/*
* It would make more sense to freeze after loading the schema,
but that
* would cause us to lose the frozenids restored by the load.
We use
* --analyze so autovacuum doesn't update statistics later
*/
prep_status("Analyzing all rows in the new cluster");
exec_prog(UTILITY_LOG_FILE, NULL, true, true,
"\"%s/vacuumdb\" %s --all --analyze %s",
new_cluster.bindir,
cluster_conn_opts(&new_cluster),
log_opts.verbose ? "--verbose" : "");
check_ok();
/* -- NEW -- */
start_postmaster(&new_cluster, true);
check_new_cluster();
report_clusters_compatible();
pg_log(PG_REPORT,
"\n"
"Performing Upgrade\n"
"------------------\n");
prepare_new_cluster();
stop_postmaster(false);
/*
* Destructive Changes to New Cluster
*/
copy_xact_xlog_xid();
/* New now using xids of the old system */
/* -- NEW -- */
start_postmaster(&new_cluster, true);
prepare_new_globals();
create_new_objects();
stop_postmaster(false);
So the analyze(and freeze) are done before the new cluster are fully
populated. Is the time being taken maybe for the loading schema/data
portion?
Which basically runs:
vacuumdb --all --analyze
vacuumdb --all --freeze
This is where all these 47 minutes are spent, yet I do not understand
neither why, nor how I can improve this part. Can I skip it somehow and
launch the vacuum manually afterwards?
Per postgresql.conf, I gave:
work_mem = 128MB
maintenance_work_mem = 8GB
max_parallel_maintenance_workers = 16
max_parallel_workers = 16
The server has 44 GB available memory, and 24 cores.
Do you have any ideas how to speed-up the entire process?
--
Kouber Saparev
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
