3

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.

Tarandeep Singh
  • 33
  • 5

1 Answers1

5

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.

Tarandeep Singh
  • 33
  • 5
  • Thanks for reply, but won't that just replace my old 9.3 cluster, and there is no turning back once upgrade if somehow postgres-10 isn't working smoothly or upgrade have some issues. – Tarandeep Singh Jun 28 '19 at 06:42
  • Yes, that's the downside but you can always restore your backup if things go wrong –  Jun 28 '19 at 06:46
  • When i used ```sudo pg_upgradecluster --method=upgrade --link 9.3 main ``` getting error about Your installation references loadable libraries that are missing from the new installation and when i checked the ''loadable_libraries.txt'' it has this ```could not load library "$libdir/plpython2": ERROR: could not access file "$libdir/plpython2": No such file or directory ``` – Tarandeep Singh Jul 01 '19 at 07:38