So you have two easy options. You can do an in-place upgrade with pg_upgrade: https://www.postgresql.org/docs/10/static/pgupgrade.html I've personally never used this tool.

I normally schedule a maintenance period, and use pg_dump / pg_dumpall: https://www.postgresql.org/docs/10/static/upgrading.html

Then I create a new server and install the new version of Postgres, and apply all custom configurations, turn off the database to external access, use pg_dump / pg_dumpall to create logical backups, restore them to the new server, configure a new replica, test my application internally, then when i'm happy i'll turn everything back on for external access.

V. helpful, thank you. Probably going to go the pg_dump route.

> I have 100GB of data in Postgres 9.2 on a single machine. I have it backed up to Tarsnap, and a tonne of spare disk space locally, and can make a snapshot of the machine at the beginning.

Are you backing up the database data directory via tarsnap while the database is running? If so then it's possible your backup wouldn't be a consistent snapshot. Snapshotting the data directory is an option, but you'd can't blindly do it without stopping the cluster first.

If you haven't already, I'd suggest setting up automated jobs to backup the database via pg_dump for a logical backup. That'd be a fallback in case you run into any issues upgrading the database directly.

Not necessarily true. Postgres is designed to always have a consistent (or rebuildable) on-disc format. That is, at an instant the entire fileset is consistent with itself.

The main problem when backing up the entire database directory is that a backup program will read the files over an extended period of time, and you can't guarantee that Postgres won't have changed stuff in-between.

The main problem is with the WAL. Everything[1] gets written to the WAL before going to the actual database tables. Stuff that has definitely made it to the actual database tables then drops off the end of the WAL, because Postgres knows that it is safe.

However, you can tell Postgres that a backup is in progress[2], and it will stop stuff dropping off the WAL. Then, no matter how many database writes happen, nothing disappears from the database directory[3]. You can take a database backup by just copying the files. Just make sure you tell Postgres when you have finished taking your backup[4], and it will then drop the old WAL entries. Otherwise, the WAL will grow until you run out of disc space.

When you restore your backup, Postgres will rerun all the actions recorded in the WAL in the state it was in when the backup program read it.

[1] Well, not necessarily everything, but close enough.

[2] Using SELECT pg_start_backup('label'); See https://www.postgresql.org/docs/9.1/static/continuous-archiv...

[3] Well, yes, stuff will change in the database directory, but the WAL says that it is going to change, and that record is still there, so it's all good.

[4] Using SELECT pg_stop_backup();

Awesomely said. also, tools like pgbarman(from 2ndQuadrant) and other PG backup tools, do all of these steps for you.

I'd go with a pg_dump to a plaintext SQL file, then run `psql < backup.sql` and see what happens. I'd run pg10 concurrently in a docker container or something to try before trying it live.

If 10 fails, try 9.6 (docker run postgres:9.6) and do the same, to see if it's an issue exclusively with 10.

I think pg_restore supports pg_dump custom format (-f) from all the way down to 8.4, that way you can do faster parallel import (pg_restore -j).

I dunno, I've got segfaults restoring different versions of pg_dump before. I might have been restoring a later version on an older version though.

Please report the next time round, that really shouldn't be the case. Thanks!