2

I'm trying to upgrade my postgres database from 9.6 to 10 without any downtime. Can this be done?

user3837299
  • 329
  • 2
  • 12
  • 1
    https://severalnines.com/blog/how-upgrade-postgresql10-postgresql11-zero-downtime or https://blog.2ndquadrant.com/near-zero-downtime-automated-upgrades-postgresql-clusters-cloud/ –  Jan 23 '19 at 12:16
  • You'd use a replication solution like Slony, then switch over. It is not a simple thing to do, though. – Laurenz Albe Jan 23 '19 at 12:26
  • How big is your database, how much data you insert/update/delete per day? – JosMac Jan 23 '19 at 16:35
  • 500G ... approx 10k ins/upd/del – user3837299 Jan 24 '19 at 08:11
  • 1
    `pg_upgrade` with the `--link` option should allow for a very short downtime though. –  Jan 24 '19 at 09:39

1 Answers1

3

As "a_horse_with_no_name" mentioned logical replication is a very good choice in your situation.

Only problem is 9.6 does not have internal implementation yet so you would have to use extension "pglogical" on both DB - here I found some description - https://rosenfeld.herokuapp.com/en/articles/infrastructure/2017-11-10-upgrading-postgresql-from-9-6-to-10-with-minimal-downtime-using-pglogical - skip parts about Docker and see how pglogical works.

The only slight problem is pglogical must be added into "shared_preload_libraries" parameter and postgresql service must be restarted which can be sometimes difficult on production...

I did quite a lot of tests with pglogical (here are some notes - http://postgresql.freeideas.cz/pglogical-postgresql-9-6-small-hints-debian/) although at the end I never used pglogical on production. So I do not have experiences from long usage.

But I presume some problems can be similar to the internal implementation of logical replication in PG 10 and 11. So here are my notes from my current usage of internal logical replication on PG 11 - http://postgresql.freeideas.cz/setting-logical-replication-is-not-entirely-straight-forward/ - maybe something from it would help you.

My recommendation for you would be:

  • make a hot backup copy of your PG 9.6 database on some other machine on cloud VM with exactly the same OS and if possible disks types and configuration using pg_basebackup - you can find some inspiration here:
  • or if you already use pg_basebackup for tar backups of your db restore latest backup on other machine or VM (http://postgresql.freeideas.cz/pg_basebackup-pgbarman-restore-tar-backup/)
  • start is as normal server (not as hot standby) and test pglogical on this copy of your DB against some testing installation of PG 10 - test it as close to the production environment as possible including at least simulated DML operations of similar intensity - this will show you differences in load on machine / VM.
  • I highly recommend to set monitoring for example using telegraf + influxdb + Grafana (easiest implementation by my opinion) to be able to analyze CPU and memory usage later - this can be very crucial part for usage on production !
  • after hopefully short and successful tests implement it and celebrate your success :-) and please write about you experiences. Because I believe a lot of people would welcome it.
JosMac
  • 2,164
  • 1
  • 17
  • 23