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?
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.
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.
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.
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).
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.
PostgreSQL 12 to 15 works directly without going through intermediate versions, so pg_upgrade is your way to go. What does matter here is that before running pg_upgrade, you need to do a full backup with pg_dump or a physical backup - not for the migration itself, but as a safety net. A lot of people skip this step because they trust that pg_upgrade will work, but if something goes wrong with 50GB of data, you don't want to be praying.
The process is pretty straightforward: you install PostgreSQL 15 on the same server (or on a parallel one if you prefer), pause writes to the database, run pg_upgrade with the `-k` flag to keep the old files as a backup, and let it run. With 50GB it might take 5-15 minutes depending on your hardware, but then you have everything converted in-place without exporting/importing anything. Once you confirm everything works fine, only then do you delete the old files from version 12.
The important thing you don't see mentioned is verifying extensions and custom configurations. If you have extensions installed, pg_upgrade can have issues - you need them to exist in PostgreSQL 15 as well. Before doing anything, check `SELECT extname FROM pg_extension;` in your current database and confirm that all those extensions support version 15. That's where things tend to fail most, not in the data migration itself.
Log into answer.
