postgresqlpostgresql-9.3postgresql-10pg-upgrade

pg_upgradecluster taking too much time(around 8 hours for 165GB database) any workarounds?


I am trying upgrade postgres-9.3 to postgres-10 with database size around 165GB. I am using "sudo pg_upgradecluster 9.3 main" to do so but it's taking around 8hours which is way too much downtime for my live webapp. Any suggestions to make it better with lesser downtime and faster.


Solution

  • You can tell pg_upgradecluster to use Postgres' pg_upgrade tool with the --link option which should then finish in minutes rather than hours:

    pg_upgradecluster --method=upgrade --link ......
    

    Note that --link will not copy your data, so the only way to revert the upgrade is to restore your last backup to a 9.3 installation.

    Quote from the Postgres manual

    If you use link mode, the upgrade will be much faster (no file copying) and use less disk space, but you will not be able to access your old cluster once you start the new cluster after the upgrade.

    ...

    If you want to use link mode and you do not want your old cluster to be modified when the new cluster is started, make a copy of the old cluster and upgrade that in link mode. To make a valid copy of the old cluster, use rsync to create a dirty copy of the old cluster while the server is running, then shut down the old server and run rsync --checksum again to update the copy with any changes to make it consistent.