I have a production server running Postgres 9.4. The database is > 10 GB. Is it possible to upgrade to Postgres 9.5 without downtime and without loosing data?
Upgrade tutorials recommend to stop Postgres while executing sudo pg_upgradecluster 9.4 main
, but this may take a long time. Updating a 10 GB cluster can take several hours!
I also tried pg_dump mydb > db.sql
. Dropping the database and inserting the dump again in PG 9.4 (psql -d mydb -f db.sql
) took about 50 minutes.
But inserting the dump in PG 9.5 finished only after mor than 7 hours. Especially creating the index was really slow…
2016-07-18 00:13:55 CEST [60358-5] ERROR: canceling autovacuum task
2016-07-18 00:13:55 CEST [60358-6] CONTEXT: automatic analyze of table ...
2016-07-18 00:36:20 CEST [60366-1] ERROR: canceling autovacuum task
2016-07-18 00:36:20 CEST [60366-2] CONTEXT: automatic analyze of table ...
2016-07-18 04:21:40 CEST [60361-1] ERROR: canceling autovacuum task
2016-07-18 04:21:40 CEST [60361-2] CONTEXT: automatic analyze of table ...
2016-07-18 07:55:19 CEST [61316-1] ERROR: canceling autovacuum task
2016-07-18 07:55:19 CEST [61316-2] CONTEXT: automatic analyze of table ...
So neither pg_upgradecluster
nor pg_dump
is an acceptable solution. Even with PG 4 you would have a downtime of at least 50 minutes. Therefore: How can databases be upgraded on production servers or big master-slave-clusters without downtime and dataloss?
Best Answer
No downtime at all is not possible without some clustering magic.
Some other possibilities:
pg_upgrade
with the--link
option. With this option, the original DB files are not copied, rather they are hard-linked to the new directory, greatly speeding up the process. Please pay attention that this will permanently alter the source DB files.pg_dump
and restore on the new database. You can greatly shorten the time needed by temporarily disabling synchronous writes in the new database (fsync = false
in the new PG instance's config file)pg_dump
to load, via network, the dump to the new instance. When done, swap the ports and use the new instance.