5 ms·

We did a similar migration (somewhat larger database) with ~20 seconds of downtime and much less work... using the magic of AWS RDS Blue-Green Deployments [1].

We did a similar migration (somewhat larger database) with ~20 seconds of downtime and much less work... using the magic of AWS RDS Blue-Green Deployments [1]. Surprised they aren't mentioned in the thread yet. Basically, you spin up a new Blue Green deployment with any desired changes (in our case, we were upgrading Postgres major from 13 to 15). While your blue configuration continues to serve traffic, AWS uses logical replication to keep the "green" deployment in-sync. You can keep modifying (or testing) the "green" deployment (eg you could load test it if you wanted to), as long as you don't do any writes to it (writes still have to go to your live, blue configuration, and are replicated to green). When you're ready, you run the "switch" command, and AWS does a few things for you: run checks to ensure blue/green are in sync, stops writes and connections, waits a few seconds to ensure replication is caught up, renames your database, then allows connections/writes again. We had less than 20 seconds of downtime, by our count. And, we had a primary and several read replicas and AWS successfully switched the full configuration over with no hiccups. You don't even need to switch your configuration because AWS swaps the database URLs for you. Green becomes blue, blue becomes old blue, and when you're ready, you delete "old blue". Highly recommend! They do have some restrictions (for instance, not sure if it would work if you're switching accounts, etc). 1. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-green-deployments.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/blue-...

sgarland 3y ago
+1 for B/G. That said, I imagine they couldn’t use it due to the cross-account shift. I’ve used it for both MySQL (with much higher QPS than TFA, by two orders of magnitude) and Postgres, and they both went flawlessly. Read the docs, especially the limitations. Re-read them. Do a test run in the dev environment, under load. Do it again in staging. Or just YOLO into prod ‘cause it’ll probably be fine, I guess.
- Ozzie_osman 3y ago
  We definitely did a dry run with a parity configuration a couple nights before. It gave us a lot more confidence.
troublebucket 3y ago
Has anyone encrypted the storage on a previously unencrypted RDS using Blue/Green?
- d1str0 3y ago
  Good question. This was a pain point for my small team (me, myself, and I) a little while back. We had an unencrypted db deployed with CDK, and then tried to get it encrypted without losing data.
  - troublebucket 3y ago
    Were you able to successfully complete the encryption and cut-over?
    - d1str0 3y ago
      Yes, we just ended up having to hardcode the db endpoint in the cdk stack after a manually restoration from snapshot into an encrypted db.
- bgschulman31 3y ago
  We recently did this on my team over Christmas this year. We opted not to use Blue/Green for this but instead spun up an encrypted snapshot and began replication from the old database to the new database using MySQL’s tools. Once traffic on our platform was sufficiently low, we cut connections to the database, waited for for replica lag to reach 0 and relaunch the servers with the new databases host info. Our downtime was around a minute.
  - troublebucket 3y ago
    This is how we're planning to encrypt our RDS Postgres DBs, but with PG's built-in logical replication. I tried it with DMS and it was so cumbersome. We would've needed to make a lot of schema changes to get DMS to function. Specifically DMS can't replicate a number of character column-types because they were considered CLOB.
cljacoby 3y ago
I also used RDS Blue/Green deployment to apply a MySQL major engine version upgrade from 5.7 to 8.0. With respect to downtime it worked fantastically, I think we measured 13 seconds of observable downtime from the API. However we did learn the hard way that RDS Blue/Green cannot be used to apply arbitrary changes. In our case, we discovered RDS Blue/Green can only be used to move up engine versions, not down. We discovered on MySQL 8.0 one of our stored procedures had very occasional failures, and considered the option of using RDS Blue/Green again to move back down to 5.7. Turns out that's not an option.
- Mavvie 3y ago
  I wonder if that could be because MySQL 8's replication is backwards compatible but MySQL 5.7's isn't forwards compatible. If so, it makes sense that you're only able to move forward.
- 0xbadcafebee 3y ago
  Database changes are typically one-way. If your new change includes creating or modifying a table, such that there are new additional columns, and you populate those with data, then downgrading would destroy the changed columns and the data in them. Hence you can't downgrade once you upgrade or you'd potentially be breaking things. To downgrade safely you'd need to backup or snapshot the old database, and then restore your database back to the backup/snapshot, but that's not blue/green.
  - fulafel 3y ago
    DB schema migration script frameworks (at least in Python, Ruby & Java lands) do typically support both upgrade and downgrade directions. People skip implementing and testing the downgrade side if the development model doesn't need it but the problem of what happens to the data is controlled by what you put in the "down" migration script. I'd guess if you can't throw the data away, you won't do a down migration, you'll do an up migration that changes the db to save that data in your preferred way before undoing or reworking the previous schema change.
    - 3y ago
deleted 3y ago
[deleted]
yeswecatan 3y ago
How did you go about stopping and restarting applications which reach out to the database? We have a number of tasks running in ECS which can take a minute to spin down and a few minutes to spin back up.
- ThePowerOfFuet 3y ago
  Presumably you don't stop them, and they throw errors during the cutover.
- todd3834 3y ago
  You aren’t supposed to have to change anything in the application code. The same database URL should work.
- Ozzie_osman 3y ago
  For our web service, we didn't stop anything. They had a few seconds of errors though it seems like some sessions were just buffered or paused and experienced high latency. We also had background worker services. For the very high throughput ones, we spun down the # of tasks to a bare minimum for <5 minutes and let the queue build up, rather than have a massive amount of errors and retries. For the other ones where throughput wasn't high, we just let them be, and during the downtime they errored and retried and the retries mostly succeeded.
benmorris 3y ago
I used this about 2 months ago going from MySql 5.7->8.0 Really awesome feature.
sharadov 3y ago
Blue/green is new, so am guessing most folks don't know about it. It's the way for close to zero downtime upgrades. Am waiting for them to support upgrades from RDS Postgres to Aurora.
shayonj 3y ago
+1 for Route53 Groups and B/G setups. We did something similar with PG upgrades, no downtime with AWS R53 groups & retry inflight queries with a custom Rails ActiveRecord transaction patch. Trade off: For a few seconds some requests were slower. DNS Groups w/ retries is a nifty mechanism for these things. Tool used: https://github.com/shayonj/pg_easy_replicate https://github.com/shayonj/pg_easy_replicate
crucialfelix 3y ago
I'm planning to do this soon. Is there any problem skipping versions when upgrading? I would like to go from Postgres 12 to 16. Does Postgres handle that without worries?

sgarland 3y ago

+1 for B/G. That said, I imagine they couldn’t use it due to the cross-account shift. I’ve used it for both MySQL (with much higher QPS than TFA, by two orders of magnitude) and Postgres, and they both went flawlessly. Read the docs, especially the limitations. Re-read them. Do a test run in the dev environment, under load. Do it again in staging. Or just YOLO into prod ‘cause it’ll probably be fine, I guess.

Ozzie_osman 3y ago
We definitely did a dry run with a parity configuration a couple nights before. It gave us a lot more confidence.

troublebucket 3y ago

Has anyone encrypted the storage on a previously unencrypted RDS using Blue/Green?

d1str0 3y ago
Good question. This was a pain point for my small team (me, myself, and I) a little while back. We had an unencrypted db deployed with CDK, and then tried to get it encrypted without losing data.
- troublebucket 3y ago
  Were you able to successfully complete the encryption and cut-over?
  - d1str0 3y ago
    Yes, we just ended up having to hardcode the db endpoint in the cdk stack after a manually restoration from snapshot into an encrypted db.
bgschulman31 3y ago
We recently did this on my team over Christmas this year. We opted not to use Blue/Green for this but instead spun up an encrypted snapshot and began replication from the old database to the new database using MySQL’s tools. Once traffic on our platform was sufficiently low, we cut connections to the database, waited for for replica lag to reach 0 and relaunch the servers with the new databases host info. Our downtime was around a minute.
- troublebucket 3y ago
  This is how we're planning to encrypt our RDS Postgres DBs, but with PG's built-in logical replication. I tried it with DMS and it was so cumbersome. We would've needed to make a lot of schema changes to get DMS to function. Specifically DMS can't replicate a number of character column-types because they were considered CLOB.

cljacoby 3y ago

I also used RDS Blue/Green deployment to apply a MySQL major engine version upgrade from 5.7 to 8.0. With respect to downtime it worked fantastically, I think we measured 13 seconds of observable downtime from the API. However we did learn the hard way that RDS Blue/Green cannot be used to apply arbitrary changes. In our case, we discovered RDS Blue/Green can only be used to move up engine versions, not down. We discovered on MySQL 8.0 one of our stored procedures had very occasional failures, and considered the option of using RDS Blue/Green again to move back down to 5.7. Turns out that's not an option.

Mavvie 3y ago
I wonder if that could be because MySQL 8's replication is backwards compatible but MySQL 5.7's isn't forwards compatible. If so, it makes sense that you're only able to move forward.
0xbadcafebee 3y ago
Database changes are typically one-way. If your new change includes creating or modifying a table, such that there are new additional columns, and you populate those with data, then downgrading would destroy the changed columns and the data in them. Hence you can't downgrade once you upgrade or you'd potentially be breaking things. To downgrade safely you'd need to backup or snapshot the old database, and then restore your database back to the backup/snapshot, but that's not blue/green.
- fulafel 3y ago
  DB schema migration script frameworks (at least in Python, Ruby & Java lands) do typically support both upgrade and downgrade directions. People skip implementing and testing the downgrade side if the development model doesn't need it but the problem of what happens to the data is controlled by what you put in the "down" migration script. I'd guess if you can't throw the data away, you won't do a down migration, you'll do an up migration that changes the db to save that data in your preferred way before undoing or reworking the previous schema change.
  - 3y ago

deleted 3y ago

[deleted]

yeswecatan 3y ago

How did you go about stopping and restarting applications which reach out to the database? We have a number of tasks running in ECS which can take a minute to spin down and a few minutes to spin back up.

ThePowerOfFuet 3y ago
Presumably you don't stop them, and they throw errors during the cutover.
todd3834 3y ago
You aren’t supposed to have to change anything in the application code. The same database URL should work.
Ozzie_osman 3y ago
For our web service, we didn't stop anything. They had a few seconds of errors though it seems like some sessions were just buffered or paused and experienced high latency. We also had background worker services. For the very high throughput ones, we spun down the # of tasks to a bare minimum for <5 minutes and let the queue build up, rather than have a massive amount of errors and retries. For the other ones where throughput wasn't high, we just let them be, and during the downtime they errored and retried and the retries mostly succeeded.

benmorris 3y ago

I used this about 2 months ago going from MySql 5.7->8.0 Really awesome feature.

sharadov 3y ago

Blue/green is new, so am guessing most folks don't know about it. It's the way for close to zero downtime upgrades. Am waiting for them to support upgrades from RDS Postgres to Aurora.

shayonj 3y ago

+1 for Route53 Groups and B/G setups. We did something similar with PG upgrades, no downtime with AWS R53 groups & retry inflight queries with a custom Rails ActiveRecord transaction patch. Trade off: For a few seconds some requests were slower. DNS Groups w/ retries is a nifty mechanism for these things. Tool used: https://github.com/shayonj/pg_easy_replicate https://github.com/shayonj/pg_easy_replicate

crucialfelix 3y ago

I'm planning to do this soon. Is there any problem skipping versions when upgrading? I would like to go from Postgres 12 to 16. Does Postgres handle that without worries?