5 ms·

Suppose I had a "friend" with a PostgreSQL 9.6 instance (a large single node)... what's the best way to upgrade to PostgreSQL 14?

by buro9 5y ago

sudhirj 5y ago
With downtime, I guess the pg_upgrade tool works fine. Without downtime / near-zero downtime is more interesting though. Since this is an old version, something like Bucardo maybe? It can keep another pg14 instance in sync with your old instance by copying the data over and keeping it in sync. Then you switch your app over to the new DB and kill the old one. Newer versions make this even easier with logical replication - just add the new DB as a logical replica of the old one, and kill the old one and switch in under 5 seconds when you're ready.
- yarcob 5y ago
  I think I remember a talk where someone manually set up logical replication with triggers and postgres_fdw to upgrade from an old server with zero downtime.
- Diggsey 5y ago
  pg_upgrade has a `link` option that when used reduces the time take to ~15 seconds. IMO, that counts as "near-zero downtime".
  - sudhirj 5y ago
    Sort of, yeah. I usually consider zero-downtime to be ‘imperceptible to users’. This option is interesting, though, first I’ve heard of it. Thanks!
nicoburns 5y ago
Looks like the best approach might be to use in-place upgrade tool that ships with Postgres to upgrade to v10 (use Postgres v10 to perform this upgrade). From there you'd be able to create a fresh v14 instance and use logical replication to sync over the v10 database. Before briefly stopping writes while you swap over to the v14 database. EDIT: Looks like pg_upgrade supports directly upgrading multiple major versions. So maybe just use that if you can afford the downtime.
- yarcob 5y ago
  Why would you first upgrade to PG 10?
  - nicoburns 5y ago
    Because version below 10 don't support logical replication. The alternative would be to use a 3rd party extension for replication, which may well be a good option but I don't have any experience with that so I can't really comment.
  - zozbot234 5y ago
    Native logical replication (making it possible to upgrade without downtime) was introduced in pgSQL 10. But if you're going to have downtime anyway, there's no reason not to do the upgrade in a single step. pg_upgrade should support that quite easily.
    - pilif 5y ago
      also, logical replication has some issues that make it not necessarily perfect for cross version migration: schema changes and sequence usages are not synced. While schema changes are probably not much of a problem because that's something you can easily prevent during migration, sequence usage is because once you fail over to the logically replicated secondary, you will have to reset all sequences to their current values or all further inserts into tables with sequences will fail. the other option, of course is to not use sequences but rather use UUIDs for your primary keys, but those have their other collection of issues
yarcob 5y ago
Depends on whether you can shut down the server for some time or not. If you can live with some downtime, just use pg_upgrade if you install the new server on the same machine: https://www.postgresql.org/docs/current/pgupgrade.html https://www.postgresql.org/docs/current/pgupgrade.html
briffle 5y ago
We are currently using pg_upgrade to go from 9.6 to 13 in our systems. its supported and works well. We are using hte 'hardlinks' feature that makes it extremely fast as well.
- zozbot234 5y ago
  Do be aware that using the "hardlinks" feature will mean that restarting the old database version will not work properly and may not be safe. You should make sure to copy the old directory beforehand if you might need that, or simply restore from backup.
  - Tostino 5y ago
    I'd argue you shouldn't be performing an upgrade at all without a proper backup. But yes, absolutely do not use a hardlinks upgrade unless you have a backup laying around.
olavgg 5y ago
This is one of the major pain points with PostgreSQL. Unless you absolutely need any new features and uptime is important, you can just continue using PostgreSQL 9.6 even thought it is EOL. https://www.postgresql.org/support/versioning/ https://www.postgresql.org/support/versioning/ It will most likely work great for many more years. I wish future versions of PostgreSQL will have some backwards compatibility for old system/data tables/datastructures and be able to do live migration when running a newer release. pg_upgrade will not work if the internal data structure for your data changes. It only recreates the system tables. https://www.postgresql.org/docs/14/pgupgrade.html https://www.postgresql.org/docs/14/pgupgrade.html
- Tostino 5y ago
  This is not really true for a number of reasons. pg_upgrade is absolutely the preferred method if you are able to spare small downtime. It will absolutely work to upgrade your existing data structures to be compatible with the new Postgres version, so not sure what you even mean. It doesn't automatically re-create indexes (to take advantage of new features) for example, but that is likely something you don't want to do right away (incurring extra downtime) when you are doing an upgrade anyways. You can easily just REINDEX after the upgrade is complete.
  - olavgg 5y ago
    If you read the link about pg_upgrade, you're not guaranteed that pg_upgrade will work if the underlying data storage format changes. pg_upgrade may not work in a future version of PostgreSQL. It works for version 14 though.
jeremy_sikich 5y ago
Pglogical, replicate to a new database server. There will be minimal downtime and it allows you to test the process thoroughly.
- bananaoomarang 5y ago
  Yeah this is how we do it... Even with terrabytes of data it seems to be pretty efficient and robust, and easy to just keep up the replicating version until you're satisfied it all looks good.
jeltz 5y ago
If you can afford a short downtime I would recommend just running pg_upgrade and upgrade directly to 13. Preferably test it out first before doing it in production.
dylanz 5y ago
If you're on a single node, probably something like this. This is what my "friend" is doing later this week. He's migrating from 9.5 to 13: sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo apt-get update sudo apt install postgresql-13 sudo service postgresql stop sudo mkdir -p /secure/pgsql13/data sudo chown -R postgres:postgres /secure/pgsql13/data sudo -u postgres /usr/lib/postgresql/13/bin/initdb -D /secure/pgsql13/data sudo rm -rf /var/lib/postgresql/13/main sudo ln -s /secure/pgsql13/data /var/lib/postgresql/13/main sudo chown -hR postgres:postgres /var/lib/postgresql/13/main sudo ln -s /etc/postgresql/9.5/main/postgresql.conf /var/lib/postgresql/9.5/main/postgresql.conf sudo chown -hR postgres:postgres /etc/postgresql/9.5/main/postgresql.conf cd /tmp sudo -u postgres time /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir /usr/lib/postgresql/9.5/bin --new-bindir /usr/lib/postgresql/13/bin --old-datadir /var/lib/postgresql/9.5/main --new-datadir /var/lib/postgresql/13/main --link --check sudo -u postgres time /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir /usr/lib/postgresql/9.5/bin --new-bindir /usr/lib/postgresql/13/bin --old-datadir /var/lib/postgresql/9.5/main --new-datadir /var/lib/postgresql/13/main --link sudo service postgresql start 13 sudo -u postgres "/usr/lib/postgresql/13/bin/vacuumdb" --all -j 32 --analyze-only sudo -u postgres ./delete_old_cluster.sh
- sharadov 5y ago
  I second this, upgraded 100 odd instances using pg_upgrade. And it's lightning fast, takes less than a min whether the db cluster is a couple GB or a few TB. Just make sure that you always run the check mode to catch incompatibilities between versions.
- throw0101a 5y ago
  > wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - Is there a reason to do this rather than putting the key in /etc/apt/trusted.gpg.d/?
  - idoubtit 5y ago

Suppose I had a "friend" with a PostgreSQL 9.6 instance (a large single node)... what's the best way to upgrade to PostgreSQL 14?

by buro9 5y ago

Suppose I had a "friend" with a PostgreSQL 9.6 instance (a large single node)... what's the best way to upgrade to PostgreSQL 14?

sudhirj 5y ago

With downtime, I guess the pg_upgrade tool works fine. Without downtime / near-zero downtime is more interesting though. Since this is an old version, something like Bucardo maybe? It can keep another pg14 instance in sync with your old instance by copying the data over and keeping it in sync. Then you switch your app over to the new DB and kill the old one. Newer versions make this even easier with logical replication - just add the new DB as a logical replica of the old one, and kill the old one and switch in under 5 seconds when you're ready.

yarcob 5y ago
I think I remember a talk where someone manually set up logical replication with triggers and postgres_fdw to upgrade from an old server with zero downtime.
Diggsey 5y ago
pg_upgrade has a `link` option that when used reduces the time take to ~15 seconds. IMO, that counts as "near-zero downtime".
- sudhirj 5y ago
  Sort of, yeah. I usually consider zero-downtime to be ‘imperceptible to users’. This option is interesting, though, first I’ve heard of it. Thanks!

nicoburns 5y ago

Looks like the best approach might be to use in-place upgrade tool that ships with Postgres to upgrade to v10 (use Postgres v10 to perform this upgrade). From there you'd be able to create a fresh v14 instance and use logical replication to sync over the v10 database. Before briefly stopping writes while you swap over to the v14 database. EDIT: Looks like pg_upgrade supports directly upgrading multiple major versions. So maybe just use that if you can afford the downtime.

yarcob 5y ago
Why would you first upgrade to PG 10?
- nicoburns 5y ago
  Because version below 10 don't support logical replication. The alternative would be to use a 3rd party extension for replication, which may well be a good option but I don't have any experience with that so I can't really comment.
- zozbot234 5y ago
  Native logical replication (making it possible to upgrade without downtime) was introduced in pgSQL 10. But if you're going to have downtime anyway, there's no reason not to do the upgrade in a single step. pg_upgrade should support that quite easily.
  - pilif 5y ago
    also, logical replication has some issues that make it not necessarily perfect for cross version migration: schema changes and sequence usages are not synced. While schema changes are probably not much of a problem because that's something you can easily prevent during migration, sequence usage is because once you fail over to the logically replicated secondary, you will have to reset all sequences to their current values or all further inserts into tables with sequences will fail. the other option, of course is to not use sequences but rather use UUIDs for your primary keys, but those have their other collection of issues

yarcob 5y ago

Depends on whether you can shut down the server for some time or not. If you can live with some downtime, just use pg_upgrade if you install the new server on the same machine: https://www.postgresql.org/docs/current/pgupgrade.html https://www.postgresql.org/docs/current/pgupgrade.html

briffle 5y ago

We are currently using pg_upgrade to go from 9.6 to 13 in our systems. its supported and works well. We are using hte 'hardlinks' feature that makes it extremely fast as well.

zozbot234 5y ago
Do be aware that using the "hardlinks" feature will mean that restarting the old database version will not work properly and may not be safe. You should make sure to copy the old directory beforehand if you might need that, or simply restore from backup.
- Tostino 5y ago
  I'd argue you shouldn't be performing an upgrade at all without a proper backup. But yes, absolutely do not use a hardlinks upgrade unless you have a backup laying around.

olavgg 5y ago

This is one of the major pain points with PostgreSQL. Unless you absolutely need any new features and uptime is important, you can just continue using PostgreSQL 9.6 even thought it is EOL. https://www.postgresql.org/support/versioning/ https://www.postgresql.org/support/versioning/ It will most likely work great for many more years. I wish future versions of PostgreSQL will have some backwards compatibility for old system/data tables/datastructures and be able to do live migration when running a newer release. pg_upgrade will not work if the internal data structure for your data changes. It only recreates the system tables. https://www.postgresql.org/docs/14/pgupgrade.html https://www.postgresql.org/docs/14/pgupgrade.html

Tostino 5y ago
This is not really true for a number of reasons. pg_upgrade is absolutely the preferred method if you are able to spare small downtime. It will absolutely work to upgrade your existing data structures to be compatible with the new Postgres version, so not sure what you even mean. It doesn't automatically re-create indexes (to take advantage of new features) for example, but that is likely something you don't want to do right away (incurring extra downtime) when you are doing an upgrade anyways. You can easily just REINDEX after the upgrade is complete.
- olavgg 5y ago
  If you read the link about pg_upgrade, you're not guaranteed that pg_upgrade will work if the underlying data storage format changes. pg_upgrade may not work in a future version of PostgreSQL. It works for version 14 though.

jeremy_sikich 5y ago

Pglogical, replicate to a new database server. There will be minimal downtime and it allows you to test the process thoroughly.

bananaoomarang 5y ago
Yeah this is how we do it... Even with terrabytes of data it seems to be pretty efficient and robust, and easy to just keep up the replicating version until you're satisfied it all looks good.

jeltz 5y ago

If you can afford a short downtime I would recommend just running pg_upgrade and upgrade directly to 13. Preferably test it out first before doing it in production.

dylanz 5y ago

If you're on a single node, probably something like this. This is what my "friend" is doing later this week. He's migrating from 9.5 to 13: sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo apt-get update sudo apt install postgresql-13 sudo service postgresql stop sudo mkdir -p /secure/pgsql13/data sudo chown -R postgres:postgres /secure/pgsql13/data sudo -u postgres /usr/lib/postgresql/13/bin/initdb -D /secure/pgsql13/data sudo rm -rf /var/lib/postgresql/13/main sudo ln -s /secure/pgsql13/data /var/lib/postgresql/13/main sudo chown -hR postgres:postgres /var/lib/postgresql/13/main sudo ln -s /etc/postgresql/9.5/main/postgresql.conf /var/lib/postgresql/9.5/main/postgresql.conf sudo chown -hR postgres:postgres /etc/postgresql/9.5/main/postgresql.conf cd /tmp sudo -u postgres time /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir /usr/lib/postgresql/9.5/bin --new-bindir /usr/lib/postgresql/13/bin --old-datadir /var/lib/postgresql/9.5/main --new-datadir /var/lib/postgresql/13/main --link --check sudo -u postgres time /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir /usr/lib/postgresql/9.5/bin --new-bindir /usr/lib/postgresql/13/bin --old-datadir /var/lib/postgresql/9.5/main --new-datadir /var/lib/postgresql/13/main --link sudo service postgresql start 13 sudo -u postgres "/usr/lib/postgresql/13/bin/vacuumdb" --all -j 32 --analyze-only sudo -u postgres ./delete_old_cluster.sh

sharadov 5y ago
I second this, upgraded 100 odd instances using pg_upgrade. And it's lightning fast, takes less than a min whether the db cluster is a couple GB or a few TB. Just make sure that you always run the check mode to catch incompatibilities between versions.
throw0101a 5y ago
> wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - Is there a reason to do this rather than putting the key in /etc/apt/trusted.gpg.d/?
- idoubtit 5y ago