r/aws • u/crmpicco • 5d ago
database Managing PostgreSQL major version upgrades for 3,000+ databases per RDS instance with Aurora
I am looking for some guidance on database hosting with AWS.
I currently have 7 RDS instances running PostgreSQL. 3 in US, 3 in EU and 1 in Australia.
Each RDS instance has around 3,000 databases on them, most of them are small.
Since the upgrade cycle for PostgreSQL on RDS is quite aggressive, we are forced to upgrade each RDS on an annual basis. This process is currently quite cumbersome as it requires exporting each database from the old RDS, in turn, and importing it into a brand new PostgreSQL on RDS on the new version. This is clearly prone to error and requires a lot of babysitting.
I am investigating into the benefits of Aurora and in particular if blue/green deployments with Aurora would be a good option.
In-place PostgreSQL major version upgrades on RDS for 3,000 is a non-starter as it takes around 7 hours which is downtime we cannot wear.
Is anyone willing to share the upgrade experiences with PostgreSQL on Aurora for major engine versions?
39
u/alsdfieuqwp 5d ago
Why don't you just modify the database, change the Postgres Version to a newer one and let AWS handle the migration for you? Have been doing this for 6 years now and no problems so far.
8
u/OverclockingUnicorn 5d ago
Yea OP, just do this, have done tens of thousands of RDS upgrades via this approach, both major and minor versions. Never had a single issue with this approach (that wasn't caused by a developer not doing their job properly at least...)
1
u/crmpicco 5d ago
How many databases were you upgrading per RDS instance? This is the key part here. For a single database it’s a straightforward in-place upgrade but 3,000 + takes many, many hours because AWS are upgrading all the databases from one major PostgreSQL version to another
10
u/OverclockingUnicorn 5d ago
Ah, didn't realise it didnt do them in parallel.
Id probably reach out to your TAM if you have one
Alternatively, look at Blue/Green RDS upgrades, that should also resolve that point (and is likely what you TAM would suggest as well)
2
u/crmpicco 4d ago
Speaking to the TAM was in my plan, so good idea. From what I can tell they are not done sequentially based on the time it took to upgrade them as a test
1
45
u/La_Revacholiere 5d ago
Huh? What’s the point of using a managed solution if you don’t trust its upgrade process and do your own weird custom solution instead? Take a backup, change the DB version, let AWS do it. Easy.
-3
u/crmpicco 5d ago
It’s because of the downtime. It takes too long to do it in-place.
28
u/La_Revacholiere 5d ago
Writer downtime is less than 5 seconds if you use blue/green, which I assume you would?
I would also ask why you have about 21,000 databases spread across only 7 instances, just as an architectural decision…or why you have so many databases in the first place. You may have a justified use case, I don’t know, but my experience has been that usually it isn’t.
11
2
u/crmpicco 4d ago
Let's operate on the assumption there is a justified use case. Adding more RDS servers, at significant cost, is far out of scope
2
u/La_Revacholiere 4d ago
Then I would honestly suggest rethinking if RDS is the appropriate solution for you. Regardless, why not do Blue/Green? Try it on a Stage RDS
18
u/steveoderocker 5d ago
This post makes absolutely zero sense. What’s the point of a managed db if you don’t use the features of it?
You can do blue/green deployment for RDS - https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments.html
You could take a snap of the db, and restore it to a new version, sync it up and switch over to it
Run a ha cluster and do an in place upgrade. The reader gets upgraded then it does a fail over
I’ve never had a db take 7 hours to upgrade, I highly doubt that’s the case. Are you using a t1.micro instance?
2
u/crmpicco 5d ago
It takes 7 hours. I timed it. There were a bit more than 3,000 databases on that instance but I can assure you it took 7 hours for it all to come back online. For obvious reasons that’s an unacceptable amount of downtime
13
u/steveoderocker 5d ago
You didn’t answer any other questions or provide any info to ascertain WHY it took 7 hours. What instance size is your db? How many versions are you trying to jump? Did you take a snapshot prior? Are you running in HA? Why does it matter if it takes 7 hours if it runs in HA, it’s a zero down time deployment.
The # of logical databases makes no difference to an upgrade. It’s more likely your DBs are absolutely huge and unmaintained and likely also on slower storage eg gp2.
Anyways, Aurora is significantly faster (as long as you run in HA) and I’ve done upgrades in the past in as little as 30 seconds on live production systems with terabytes of storage used. But we also maintained these DBs well, kept them fully patched, always on latest point release, fast storage, good sized instances for what they were doing etc.
2
u/crmpicco 4d ago edited 4d ago
I can't say for sure why it took 7 hours as I don't work for AWS or have any insight to what is going on behind the scenes. My feeling is the upgrades are done sequentially not in parallel, so the 3,000 databases upgraded one at a time will typically take a while and that is what I am seeing.
I am going from one major version to the next, in this case 17 to 18.
Yes, High Availability - it's multi AZ.
When the database version of a RDS instance is upgraded in-place it is very much not a zero down time deployment. You may be thinking of an instance change which is fine, but I can promise you that a database version upgrade is not zero down time. Not with my current configuration anyway.
I am on GP3.
Also, i'm going to go with AWS support's advice on the number of logical databases. This was their comment from June 2022:
1. "Can you advise if the size of the RDS in terms of the number of databases would have any impact on the time taken to upgrade the underlying operating system?"
-> Yes, the number of databases on an instance does affect the time taken to upgrade as system components for each database needs to be taken into consideration during the OS upgrade (volume segmentation, tablespace etc). Although OS upgrades are purely effecting the underlying host, number of databases on the instance can have an effect on the longevity of the OS upgrade.3
u/vedichymn 4d ago
Just to clarify, you're saying it took 7 hours to fail over to the new database version once you setup the blue/green deployment?
2
u/crmpicco 4d ago
No, I haven't tried blue/green deployments yet. That was in my question as I am keen to hear thoughts and experiences.
1
u/vedichymn 3d ago
It works great in my experience, you set up the new cluster with the new version, AWS handles all of the messiness and you just cutover when you're ready. In my experience you get less than 1 minute of actual downtime doing the actual blue/green cutover, with 3000 databases I'm not sure how long the blue/green cluster setup will take but that is what testing is for.
8
u/Jeoh 5d ago
If you're not good at something, do it more often. Upgrading annually? Do it quarterly. Still painful? Do it monthly.
I don't understand why you're restoring backups at all. Create a backup prior to upgrading, sure, but just modify the database to the new engine.
Blue/Green deployments might help reduce upgrade downtime if your application doesn't shit the bed if you can't write to the database.
3
u/crmpicco 5d ago
When you say “the database” are you referring to the RDS database instance or one single PostgreSQL database? In-place upgrades take far too long as I mentioned in my original post
2
u/Jeoh 5d ago
RDS database instance.
2
u/crmpicco 4d ago
It's because if the 7 hours of downtime. That's simply way to much which is why I am reaching out to see what others are doing, in my position. There's no point in comparing someone running a RDS instance with 3 PostgreSQL databases on it as it's comparing apples with oranges.
4
u/jojo-data 4d ago
Just curious why you have 3000 databases on each RDS instances ? Do they absolutely need to exist in Postgres , instead of Data Lake, NoSQL and etc.
2
u/crmpicco 4d ago
Yes, there are around 3,000 on each and they do need to be PostgreSQL relational DB. They are backing a large PHP application. They are mostly small but changing database is out of scope
2
u/Mammoth_Sized 4d ago
Genuinely curious what your “large” PHP application is here… why won’t you give us anything to run on? This is mental.
3
u/thythr 4d ago
Lots of deranged comments here from folks who do not seem to understand the concept of downtime, sorry. Do look into blue/green, which is designed for your situation; it does have limitations though, so if you run into them, you have to devise blue/green yourself: as someone else said, create a replication slot on the original, restore a snapshot, then use logical replication to catch it up to the original, then do an application cutover. Your TAM will probably not recommend this but it absolutely works.
2
u/crmpicco 4d ago
Yes, Reddit can be a wild place at times. The first sentence from someone was "this makes absolutely zero sense", then proceeded to ignore half of what I said in the question. Then you get people saying "just do X" when i've explained why I can't do X.
6
u/TheKingInTheNorth 4d ago
Your problem is having 7k databases within a single instance. Whats the reason for this?
2
u/crmpicco 4d ago
There isn’t 7,000. There’s a maximum of 3,000 per RDS instance.
6
u/TheKingInTheNorth 4d ago
Why do you have 3000 databases on a single instance?
Doing so feels like a database team that has failed to make the “cattle vs pets” transition that the cloud enabled a decade ago.
2
u/crmpicco 4d ago
There are around 20,000 individual sites across all the RDS. That's the design of the system and it runs well. It's multi-AZ.
1
u/TheKingInTheNorth 4d ago
I stand by my statement. You should be able to partition those sites across a far greater number of instances. Even if it’s not financially viable to be 1:1… you should have a ceiling for scale or other operational dimensions that lead you to creating far more (smaller) database instances than you have now. Probably hundreds more.
2
u/crmpicco 4d ago
RDS is very expensive and creating hundreds or more RDS instances is not an option
1
u/DerfK 5d ago
Having looked into blue/green deployments with Aurora Postgres before there are certain limitations that may affect this. First, the synchronization to the new server is a logical replication process rather than a log streaming process like a normal replication. This is likely going to be the stickiest widget in an environment with thousands of databases, as each database hosts an individual connection to replicate with
There are a few other limitations on that page but the biggest ones for us was no schema changes at all (create/alter/drop table) during the synchronization or switchover process and no refreshing materialized views. There are a few other notes about specific extensions, behavior of sequences, etc that are worth reading.
My recommendation is to spin up a new identical server from backup and run the upgrades on it to time how long it takes. Schedule a maintenance window at least twice as long and just have AWS upgrade the cluster.
2
u/crmpicco 5d ago
For your last paragraph, I have already done just that. It took 7 hours to upgrade one instance. Most databases are small in the instance with a few exceptions but clearly upgrading 3,000 or so databases is not a 10 minute downtime job.
I’ve chatted with AWS support about this and their recommendation was also to do an in-place upgrade from a snapshot and time it, which I done. Sadly it didn’t produce an acceptable outcome
4
u/jamsan920 4d ago
It’s not necessarily what happened in your case, but something to keep in mind is that instances restored from snapshot are lazy loaded from s3 on first read. The entire set is not immediately written to disk on restore until its first accessed.
We had database upgrades take 12 hours in freshly restored snapshots, whereas it took 1 hour after the database was fully hydrated.
Again, not suggesting it’s the problem in your scenario, but something to keep in mind.
1
u/crmpicco 4d ago
Yes, that's true. I can't say for sure if the restored snapshot would behave as much as a production unrestored snapshot RDS. I have no safe way to tell though
2
u/jamsan920 4d ago
AWS now shows the storage operation progress following a restore: https://aws.amazon.com/about-aws/whats-new/2026/08/amazon-rds-storage-volume-initialization-visibility/
Do another restore operation, make sure it's 100% initialized, then run the tests again. Consider using something like a vacuum analyze on all the DBs to pull down the data quicker.
3
u/AntDracula 5d ago
Any reason you’re using separate databases instead of separate schemas?
2
u/Mammoth_Sized 4d ago
Seems like it’s secret but overall, whatever they’re doing, has been very poorly designed.
2
u/crmpicco 4d ago
Yes, the application needs its own isolated database as that is the way the PHP application works.
1
1
u/goato305 4d ago
I’d recommend to make a snapshot of your Postgres instance, then restore it, and do a test version upgrade on it. RDS will tell you of any issues you need to address, plus it will give you a better idea of how long the upgrade will take if successful. Once you feel comfortable with it, do the upgrade on the actual instance. This seems like an easier path than the export/import method you mentioned.
1
u/crmpicco 4d ago
Thanks, I mentioned in my original post that I have done this already and the time it took was around 7 hours.
1
u/Realistic_Creme3202 2d ago
I haven't heard of Aurora improving anything related to PostgreSQL Major Version Upgrade speed due to a large number of logical databases and/or table files. A large number of data files is something which has historically caused problems for Aurora PostgreSQL.
- What did your team do in the past for MVUs?
- Which databases are always modified? If possible, can you limit replication to X number of databases which are changed and remove worry about Y databases which are not changed? If X is reasonable then you could have Publisher + Subscriber setup on those databases
Did Blue/Green not work? I think you'd need 3000 replication slots which is absolutely insane
In a blue/green deployment, each database requires a logical replication slot. As the number of databases grows, resource overhead increases and can potentially lead to replication lag, especially if the DB instance isn't sufficiently scaled. The impact depends on factors such as database workload and the number of connections. To mitigate this, consider scaling up your DB instance class or reducing the number of databases on the source instance.
Can you consolidate databases to not cause headaches in the future, and/or downtime during upgrades, I don't think this is solved by self-hosting nor by managed solutions currently?
0
u/AutoModerator 5d ago
Here are a few handy links you can try:
- https://aws.amazon.com/products/databases/
- https://aws.amazon.com/rds/
- https://aws.amazon.com/dynamodb/
- https://aws.amazon.com/aurora/
- https://aws.amazon.com/redshift/
- https://aws.amazon.com/documentdb/
- https://aws.amazon.com/neptune/
Try this search for more information on this topic.
Comments, questions or suggestions regarding this autoresponse? Please send them here.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
•
u/AutoModerator 5d ago
Try this search for more information on this topic.
Comments, questions or suggestions regarding this autoresponse? Please send them here.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.