Dear Community,
We are in the process of planning a PostgreSQL upgrade and would greatly appreciate any insights, best practices, or lessons learned from your experiences.
Current Setup:
- PostgreSQL version: 9.5
- Django version: 2.0
- Database size: ~3 TB
- One Master 4 Replicas(Streaming Replication)
We are taking a phased approach to this upgrade. In Phase 1, our goal is to upgrade PostgreSQL from v9.5 to v12, ensuring compatibility with our current Django 2.0 application.
As part of this, I would like to understand:
- What is the highest PostgreSQL version reliably supported with Django 2.0?
- What upgrade strategies or methods have worked well for you in similar environments?
- Any gotchas or performance considerations to keep in mind with large databases during such upgrades?
Your recommendations and experiences will be extremely valuable in helping us plan and execute this upgrade smoothly.
If you have (or can add) 3-4TB on your existing server, then pg_upgrade to 12.22 is possible. (You can also do a hard-links pg_upgrade, but there's no going back once you start the instance with v12. That's why I like upgrading to a new disk.) A 3TB instance should take no more than a few hours (depending on disk and controller speed.)
From there, you can (eventually) use logical replication from v12 to your new server running PG17 (which I suggest, since it's the latest, and you run servers for a long time).
One notable issue with pg_upgrade is extensions. It _can_ be as easy as ALTER EXTENSION <extension> UPDATE but if there are dependencies, you might have to DROP EXTENSION <extension> CASCADE and then run the DDL to recreate the dependencies. Tha' can get messy if the end of the dependency chain is a table. Only you know for sure.
-- Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
