Sevenmachines

No words go here

niall

2-Minute Read

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

Series

Categories

Recent Posts