Hi,
upgrading a database containing > 90000 tables and about the same number of
views and indexes with pg_upgrade takes several hours. I've started the
upgrade more than five hours ago and the "Creating dump of database schemas"
step is still not finished.
ps xaf output:
60709 pts/10 S+ 0:00 | \_ sudo -u postgres pg_upgrade -d
/var/lib/pgsql/data.intern -D /var/lib/pgsql/data -b /usr/lib/postgresql92/bin
-B /usr/lib/postgresql94/bin -j12 -k
60710 pts/10 S+ 0:00 | \_ pg_upgrade -d
/var/lib/pgsql/data.intern -D /var/lib/pgsql/data -b /usr/lib/postgresql92/bin
-B /usr/lib/postgresql94/bin -j12 -k
60806 pts/10 S+ 0:00 | \_ pg_upgrade -d
/var/lib/pgsql/data.intern -D /var/lib/pgsql/data -b /usr/lib/postgresql92/bin
-B /usr/lib/postgresql94/bin -j12 -k
60809 pts/10 S+ 0:00 | \_ sh -c
"/usr/lib/postgresql94/bin/pg_dump" --host "/data/pgsql" --port 50432 --
username "postgres" --schema-only --quote-all-identifiers --binary-upgrade --
format=custom --file="pg_upgrade_dump_16428.custom" "zms" >>
"pg_upgrade_dump_16428.log" 2>&1
60815 pts/10 S+ 3:52 | \_
/usr/lib/postgresql94/bin/pg_dump --host /data/pgsql --port 50432 --username
postgres --schema-only --quote-all-identifiers --binary-upgrade --
format=custom --file=pg_upgrade_dump_16428.custom zms
60719 pts/10 S+ 0:00 /usr/lib/postgresql92/bin/postgres -D
/var/lib/pgsql/data.intern -p 50432 -b -c listen_addresses= -c
unix_socket_permissions=0700 -c unix_socket_directory=/data/pgsql
60720 ? Ss 0:00 \_ postgres: logger process
60722 ? Ss 0:00 \_ postgres: checkpointer process
60723 ? Ss 0:08 \_ postgres: writer process
60724 ? Ss 0:00 \_ postgres: wal writer process
60725 ? Ss 0:04 \_ postgres: stats collector process
60817 ? Rs 334:12 \_ postgres: postgres zms [local] SELECT
The database is version 9.2, pg_upgrade is version 9.4.1. In a discussion at
lwn.net I've been told, that 9.4.1 already contains fixes for inefficiencies
and that it shouldn't be so slow anymore: http://lwn.net/Articles/645600/
What can I do to help improve pg_dump/pg_upgrade for my use case?
Regards,
Stefan