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.
I really need to know: What is the general flow for upgrading... can I go from 9.2 to 10? Or do I need to install and migrate to every intermediate version? If there are multiple approaches to this, which is considered the safest (even if the downtime is a little longer)?
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.
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.
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();
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.