- Newest
- Most votes
- Most comments
Yes, you can generally apply major version engine updates to your Aurora PostgreSQL database cluster without losing data. Major version upgrades are performed in-place and preserve your existing data.
When you perform a major version upgrade on an Aurora PostgreSQL cluster, the upgrade process uses the pg_upgrade utility internally to migrate your data to the new version. The upgrade is applied to the writer instance first, followed by each reader instance. Your data remains intact throughout this process.
However, there are important considerations:
Prerequisites and Testing:
- Major version upgrades are not applied automatically due to potential backward-incompatibilities
- You should always test the upgrade process first on a non-production environment
- Check for unsupported extensions or features that might not be compatible with the new version
- Ensure you have a recent backup before proceeding
Testing Methods: You can test the upgrade safely using:
- Clone your cluster using Aurora's fast database clone feature, then upgrade and test the clone
- Restore from a cluster snapshot to create a new cluster, then test the upgrade on that restored cluster
Upgrade Process: For CDK/CloudFormation deployments, you can update the engine version in your CDK code. The upgrade will be performed in-place without creating a new database or losing data. You'll need to:
- Update the engine version parameter in your CDK cluster definition
- Set the appropriate flag to allow major version upgrades
- Deploy the changes
Post-Upgrade Tasks: After upgrading, run the ANALYZE command on all databases to update statistics tables, and consider running REINDEX if needed.
The conflicting reports you've seen likely stem from users' different experiences with application compatibility issues or extension problems rather than actual data loss. The upgrade itself preserves data, but applications may need adjustments to work with the new PostgreSQL version.
Your approach of everything being controlled from CDK is the right one - you don't need to manually create clusters from snapshots. Simply update your CDK code with the new engine version and deploy.
Sources
Performing a major version upgrade - Amazon Aurora
Oracle and Aurora for PostgreSQL upgrades - Oracle to Aurora PostgreSQL Migration Playbook
Upgrading Amazon Aurora DB clusters - Amazon Aurora
If your workload is running on Amazon Aurora, I would recommend considering Blue/Green Deployments, as they provide a safer and simpler approach for performing database updates with minimal downtime. Database operations such as major version upgrades, instance scaling, parameter changes, and schema modifications can be challenging to execute without impacting application availability. Blue/Green Deployments help address these challenges by creating a separate, fully managed staging environment (green) that mirrors the production environment (blue).
The green environment includes a copy of the primary database and associated in-Region read replicas. Changes can be validated in the green environment while data remains synchronized with the production environment. Once testing is complete, a controlled switchover can be performed, minimizing downtime and reducing deployment risk. For Aurora PostgreSQL and RDS for PostgreSQL, synchronization is maintained using PostgreSQL logical replication.
For more information, please refer to the following AWS resources:
answered a month ago
Thanks for the suggestion! We tried that, but it had some trouble regarding the roles/parameter groups, perhaps due to differences between versions, and failed. Besides that, we’re unsure if using blue/green is possible when the cluster is defined by CDK - if we update it in Console, then what do we do from the code, will it become I synchronised? Either way, we need to update the version is the code and risk losing data if that isn’t supported.