Transferring RDS Data Between Containers on Different AWS Stacks
Quick bunch of notes on moving data between container on AWS using RDS. Here we have 2 stacks, each with a container sitting on an EC2 instance. The container is running a simple rails application connected to RDS specified in the DB_HOST environment variable. There are other shortcut ways to do this but this is the ‘pretty straightforward’ way 🙂
Transferring the SQL data
- Dump the database inside the container on the source EC2 instance
pg_dump -Fc my_database > my_database_data.sql
- Transfer the SQL file between instances
scp -i my_aws_key.pem my_username@source_ec2_ip:/path/to/file .
scp -i other_aws_key.pem . other_username@target_ec2_ip:/upload/path
Getting the SQL file into the target container
- ssh into target EC2 instance
ssh -i other_aws_key.pem other_username@target_ec2_ip
- Copy into docker
docker cp my_database_data.sql my_container:/tmp/my_database_data.sql
or, required for older dockers
cat my_database_data.sql | docker exec -i my_container bash -c 'cat > /tmp/my_database_data.sql'
Restoring the SQL data into the target database
- Import into database
docker exec -it my_container bash
- Get postgres tools, we’ll use pg_restore so we want this, on a ubuntu based container anyway
apt-get install -y postgresql-client
* Drop all connections, sometimes needed but be sure you *actually* want to drop these connections!
SELECT pid, pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'my_database' AND pid <> pg_backend_pid();
- Drop the data from rails application
rake db:drop db:create
- Restore the data
pg_restore --verbose --clean --no-acl --no-owner \
-h ${DB_HOST} \
-p ${DB_PORT} \
-U ${DB_USERNAME} \
-d my_database \
/tmp/my_database_data.sql
- Run the migrations
rake db:migrate