DEV Community

Arun Kumar for AWS Community Builders

Posted on

How to do RDS Schema/Maintenance changes

RDS Access

  • RDS can be accessed locally via ssh tunnel. This can be achieved by following my below guide.


General Guide to Schema Changes

  • Try to make the schema changes backwards compatible.

  • Separated data sources for each deployment unit (no shared schemas or databases where possible)

Schema update and data migration

Relational databases: Use a migration tool to track changes and to automatically update the database.

  • Coordinated workflow for deployment and data migration during operation.

  • It’s easy without high availability constraints: Shutdown application, update database, deploy new application, restart application.

  • It’s hard when high availability is required: Multiple intermediate versions of the application and update application instances step by step.

Example 1:
Let’s say we want to add a column in a table. How can we add the column while still staying backwards compatible with applications that doesn’t know the new column? If the application reads entries from the table, we simply ignore the new column. If the application writes a new entry, we can use default values for the new column. Indeed, it’s up to our use case if this approach is acceptable, but this way we can change our database independently from the applications.

Example 2:
We want to remove a column from a table. First, we have to make the column optional. Now we can take some time to update the accessing applications so that they don’t write or read the column anymore. During this time our schema is backwards compatible. After we’ve updated all applications we can remove the column from the schema.

How to do Schema Changes

  1. Setup your tunnel to nonproduction.

  2. Take a snapshot of the database point in time.

  3. Deploy your schema change via SQL Developer Studio or similar tool.

  4. Test Results.

Once testing is complete and you are happy with the changes,

  • Setup your tunnel to production database.
  • Execute your change.
  • Perform testing to confirm change.

How to roll back

Because the data is not important but the schema change is, roll back to the snapshot taken or use your rollback steps to rollback the change.

Because the data is important, ensure that your changes can be rolled back.

If the change is permanent and can not be rolled back, then a new table should be used for the schema change and the applications should coordinate the migration of existing data to the new table as well as the strategy required should data need to be migrated back to the original schema.

Top comments (0)