4

I am looking to upgrade our Postgres 9.6 server to Postgres 13.

I have looked into the following options:

  • pg_upgrade, if I was doing this without the --link option, this would take considerable time and also a lot of unreclaimable disk space, the databases totals 1.1TB, so to do such a task I would need double the amount of disk.

  • PG Dump and Restore - I could utilize pg_dumpall however to do 1.1TB of databases would take considerable time to backup and restore to a new server.

What is the most efficient way to ensure minimal downtime when doing upgrade from pg 9.6 to 13. I can get a new server and install PG 13 and just dump and restore as mentioned above but that would take time.

I have looked into pglogical also but not entirely sure if this will give me what I need.

Any help is much appreciated.

Improve this question
2
  • Well, then use --link.
    – Laurenz Albe
    Jun 9, 2021 at 8:24
  • @LaurenzAlbe is there many drawbacks when using --link, I know its much quicker, is there any drawbacks from a maintenance perspective (VACUUM/ANALYZE etc). We use streaming replication as well will this interfere at all? Thanks again by the way!
    – rdbmsNoob
    Jun 9, 2021 at 8:45

1 Answer 1

Reset to default
4

Use pg_upgrade --link, which will be fast regardless of the size of the database.

There is no need to VACUUM after that, you only have to ANALYZE the tables, for which you can run the script that pg_upgrade provides. The documentation for pg_upgrade shows how to use rsync to quickly upgrade a standby server along with the primary.

You can of course use logical replication as well (no need for pglogical). Apart from being more complicated, you should refrain from DDL statements while you replicate. But it can reduce the downtime still more.

Improve this answer
2
  • Thanks @LaurenzAlbe I have tried this without the link option took around 45minutes to perform the upgrade and about 25mins to do the ./analyze_new_cluster.sh. What is the negative of using the --link part of pg_upgrade. I will look at the rsync part of pg_upgrade as we have a master/slave setup so it would need to be done on the slave machine. With regards to the logical replication how would this be possible if both master/slave running 9.6 and I'd need to get them both to PG 13. Also the VACUUM/ANALYZE, meant just in general after the upgrading is done and system is back to normal.
    – rdbmsNoob
    Jun 9, 2021 at 8:58
  • 2
    You can start using the database before ./analyze_new_cluster.sh is done. The only down side of --link is that you won't have the original cluster any more, so if you want to revert to the old version, you have to restore from backup.
    – Laurenz Albe
    Jun 9, 2021 at 9:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.