@Gargron ahh, lame. and kinda surprising, postgres has a better reputation than that. Though to be fair I dont use postgres as a developer as much as I used to in a way where I need to maintain my own instance. I've been using amazon AWS's postgres for a while now in most professional settings and that is always seemless somehow on upgrades.

@freemo Mind that Postgres is like 3 major versions ahead of me. Latest is 13, I believe. More recent versions have logical replication which do not need both ends to be on the same version. I'm just stuck in the past.

@Gargron Ahh, weird choice on your part, but i guess its easy to ignore considering how much of a PITA it can be to do database upgrades.

Not sure what the postgres version was when i first moved over to AWS for most of my work. Wouldnt suprise me if they had a work around even on 9.x somehow.

@freemo What do you mean choice? This database started running in early 2016.

@Gargron I guess by choice i meant the choice not to upgrade it sooner :)

@Gargron @freemo It's pretty respectable that you've been keeping a Postgres 9 based instance running for that long, with all of its patches, and have been able to do zero downtime updates. 🥂

I've never had to do a zero downtime upgrade from major postgres versions. Since 2016 I've worked at shops that only use RDS (nice, but expensive/locks you to Amazon, which is a terrible company)

If some shop wrote a way to do streaming updates from 9->13, it's probably closed, internal and proprietary

@djsumdog @freemo You can do it with external tools like Slony, but, it might be faster and safer for me to do pg_dump/psql with some downtime than learning a completely new tool.

@Gargron @djsumdog @freemo pg_upgrade is faster and if you keep a replica / copy of the data around, just as safe

@szbalint @djsumdog @freemo Since that has to run on the same machine with two installations, you also need to have enough disk space for twice your database size though.

@Gargron @szbalint @freemo Can you add storage to prepare (an new block device if it's a VM or physical drive if it's a real server and has extra drive bays)?

@djsumdog @szbalint @freemo I mean, I'll just use a different machine with pg_dump/psql. Using SQL will remove table/index bloat as a nice side-effect. I don't know if it's time to get more disk space yet. There's 130 GB to go. Maybe more after the bloat is gone.

@Gargron @szbalint @freemo ah gothca. Yea just scheduling downtime seems like the best way to go. Schedule it for when the Aussies/JP/Korean are awake and your EU/US users are asleep 😅

@djsumdog @Gargron @szbalint @freemo

Don’t suppose the underlying storage is anything that supports snapshots? If so…

Down DB
Snapshot FS
pg_upgrade with link option to sub directory of parent
DB up
Cake
Coffee
Profit

Did this a while back and the whole process took less time than it did to type this out. This was on a DB just short of a TB and going from 9.6 to 12. Downtime was measured in seconds and way way less than pg_dump and avoided the double disk requirement.

If you are confident in your backups (You are doing streaming to a replica with wal file backup, aren’t you? ) then you can do the same and skip the snapshot and just accept the longer downtime in the unlikely event something goes sideways.

@Gargron @freemo

What about pg_upgradecluster? I've used it since 2012 to upgrade webcookies.org Postgres database (~95 GB) through all the versions, now on 12, it's fast and reliable.