I have a production application running PostgreSQL 12 and I need to upgrade to version 15. I've been reading about pg_dump but I'm not sure if it's the best option for a database that's almost 50GB. Has anyone done this before without downtime?
Don't do a simple pg_dump and restore into an empty database if you don't want hours of downtime - instead use pg_upgrade which runs on the same server without needing to export/import, or if you want to migrate to another server use logical replication to keep the app running while it syncs. With 50GB you'll save a ton of time and stress doing it that way.
Can your application handle being without database writes for a bit, or do you need absolute zero downtime? If you can stop writes even for just 5-10 minutes, pg_upgrade is your best friend because it converts the files on the same disk without exporting anything, so with 50GB you'll save yourself hours. Now, if you need to avoid downtime completely, there's a trick that doesn't get mentioned much: you can use logical replication to sync the old database with the new one in parallel while it keeps running, and then you switch over when everything's caught up - it's a bit more complicated to set up but it's gold if you can't stop the service. That said, before doing anything test it in a staging environment even if it's just with a dump of a few GB to see how long it takes on your specific setup.
Have you already checked whether your version 12 is directly compatible with 15, or do you need to go through intermediate versions? What nobody mentions is that besides pg_upgrade, you should consider doing a full backup before anything else, because even though pg_upgrade is pretty reliable, having that backup gives you peace of mind - especially with 50GB. If you really need to avoid total downtime, there's logical replication where you set up a secondary instance with version 15, sync it in parallel and then do a failover when it's ready, but that's more complex. Most people I know end up going with pg_upgrade and a short maintenance window because it's the most practical and fastest approach for a volume like that.
With 50GB you have to be careful but pg_upgrade is definitely the right option if you can tolerate that 5-10 minute downtime. What most people don't mention is that PostgreSQL 12 to 15 is a jump that pg_upgrade handles without issues in a single pass, so you don't need intermediate versions. My advice: before anything else, do a full backup with pg_basebackup or pg_dump in parallel (use pg_dump with the -j option to parallelize, it cuts down the time quite a bit), and then go ahead and run pg_upgrade on your production server during a maintenance window. That said, after the upgrade run ANALYZE on the large tables because some internal statistics change and you could end up with slow queries if you don't do it.
Look, if you can tolerate even 10-15 minutes of downtime, pg_upgrade is definitely your way to go because it does the migration on the same server without exporting/importing all the data (with 50GB that saves you a ton of time). What nobody mentioned here is that before anything else, you need to check if there are any installed extensions that aren't compatible between versions, because that can stall the whole process at the last minute and leave you with nasty surprises when you're already in the conversion. My recommendation: do a full backup with pg_basebackup anyway (just in case), test everything on a staging server first, and then go ahead and launch pg_upgrade in production.
Log into answer.
