Hacker News Reader

Comment by buro9 | Hacker News Reader

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?

View on HN

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

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.

> wget --quiet -O - 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/?

Putting the key in /etc/apt/trusted.gpg.d/ gives it too much power over all the repositories. A malicious (maybe compromised) third party repository could publish a package that replace an official Debian package.

So trusted.gpg.d/ is not the recommended method. For more information, see the official Debian wiki which states "The key MUST NOT be placed in /etc/apt/trusted.gpg.d" https://wiki.debian.org/DebianRepository/UseThirdParty

And while you're there, pay attention to the signed-by option. It's what allows you (the admin) to limit APT's trust in the keys you add.

Actually it states:

> The key MUST NOT be placed in /etc/apt/trusted.gpg.d or loaded by apt-key add.

And yet the snippet that I quoted has the latter command.

Further: the link has the example "[signed-by=/usr/share/keyrings/deriv-archive-keyring.gpg]". Perhaps it's my BSD upbringing showing through, but shouldn't only/mostly OS-provided stuff generally live in /usr/share? Shouldn't locally-added stuff go into /usr/local/share? Or perhaps creating a /etc/apt/local.gpg.d would be appropriate?

The idea is you put the keyring into the same place where the -keyring package will be installed, so the -keyring package will overwrite it, so you don't then have a temporary keyring you downloaded with wget to delete, because it was overwritten by the -keyring package.

Unless you are auditing all third-party packages before installing them, any package can modify /etc/apt/trusted.gpg.d/ by dropping a file in there, or from its postinst/etc scripts at installation time etc. So using the signed-by mechanism isn't much of a security enhancement without the auditing.

I think you can use -o '-D /etc/postgresql/9.5/main' -O '-D /etc/postgresql/13/main' to avoid making the symlink to postgresql.conf. This should also make it easier if postgres.conf tries to includes files from a conf.d subdirectory.

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.

Why would you first upgrade to PG 10?

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.

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.

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

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.

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.

pg_upgrade has a `link` option that when used reduces the time take to ~15 seconds.

IMO, that counts as "near-zero downtime".

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!

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.

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

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.

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

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.

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.

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.

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/ 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

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.

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.

Reply on news.ycombinator.com