Fastest way to reset an AWS RDS instance to an initial state?

Question

I'm experimenting with creating a staging version of a simple postgres RDS backed CRUD web app on elastic beanstalk. The aim of this staging version is to run integration tests against when implementing new code changes, before redeploying these changes to the production environment.

The current problem I have is reseting the staging stack. I want a blank DB instance at the start of my integration tests. Redeploying a new RDS instance however takes several minutes which means the integration tests take a long time (>20 minutes) to run.

Since it's on elastic beanstalk it's in a VPC and therefore to reset the DB with SQL commands it seems like the CI server would have to do some tricky stuff with ssh tunnels which doesn't (at least to me) that simple either.

Is there a fast way to reset a running RDS instance to a blank state for the purpose of testing?

Answer 1

I think that what you want is to have the blank database (vs the blank RDS instance) and here there are few options to do this:

1) Best option IMO, but may require some coding - postgresql supports savepoints, so you can structure your tests that they will always preserve the database clean (also useful for local tests). The idea is to create a savepoint before starting the tests and rollback to that savepoint at the end. This way all the changes made by tests will be removed.

2) When your tests start you can run SQL commands to drop / create the database. Your tests can connect to the database, so you can do this from your code, there is not need to do this via ssh. If data amount is small, you can create a new database for each test run. You can name it like "mydb20160115101112" (with current date and time).

3) Drop/create databse should actually be possible from the CI configuration too (also without ssh or other complex stuff), you staging instance already has access to the RDS, so you can just run some SQL commands using psql, something like this:

psql -c "drop database mydb20160115101112;" --dbname=template1

To use psql this way (without having to enter a username/password), you need to create a postgresql user with same name as you system user (make sure you create a postgresql for the system user the CI uses).

Answer 2

If you create your RDS instance with the help of Elastic Beanstalk then consider my solution. This will not work with an external created and connected RDS.

For my use case, I found a very simple solution by simply rebuilding an EB environment through the console. This automatically creates a new RDS database instance with the same settings but it does not apply any snapshots and thus the new database is empty. This was very useful when applying new migrations with Django.

Hope this is helpful, more information here: https://docs.aws.amazon.com/elasticbeanstalk/latest/dg/environment-management-rebuild.html .