AWS AuroraDB for PostgreSQL dump and restore through Bastion SSH tunnel

aws_pg_dump_and_restore.md

AWS AuroraDB for PostgreSQL dump and restore through Bastion SSH tunnel

AWS Bastion SSH Tunneling

The following ssh tunneling example can be used when network access to an AWS AuroraDB cluster is allowed only from certain AWS EC2 instances which themselves can not be accessed directly but only through an SSH Bastion host.

Open SSH tunnel to EC2 instance behind network firewall through bastion host

Command template

ssh -L 9997:<closed-ec2-ip-address>:22 -N <bastion-user>@<bastion-ip-address>

Example

ssh -L 9997:192.168.0.123:22 -N admin@34.123.123.123

Now, port 9997 on localhost is forwarded to the EC2 instance behind the network firewall.

# This connects to EC2 instance => user@192.168.0.123
ssh -p 9997 user@localhost

Open SSH tunnel for DB connection

While leaving the previous ssh tunnel open. In another terminal window, another ssh tunnel needs to be opened that forwards a local port to the DB server.

Command template

# Forwarding local port 5444 to db host port 5432 through previously opened ssh tunnel on port 9997
ssh -L localhost:5444:<db-host>:5432 -N -p 9997<user>@localhost

Example

ssh -L localhost:5444:my-app-production-db-auroradbcluster-qweasdzxc.cluster-zxcasdqwe.eu-west-1.rds.amazonaws.com:5432 -N -p 9997 user@localhost

Now, port 5444 on localhost can be used to connect to the DB server with commands like psql/pg_dump/pg_restore.

# This commend now connects to AWS DB
psql --host=localhost --port 5444 --username=<db-user> --dbname=<db-name>

DB dump and restore

Now that we can access the remote DB from our local machine, these are the steps to perform a dump and restore of the DB using pg_dump/pg_restore commands.

A few things to know:

1. The --create flag for pg_dump includes the commands to create the database in dump
2. The --create flag for pg_restore creates the target database in the process so the DB is not required to exist prior to running the restore
3. Before restoring the dump in the target DB server. You either have to create a user with the same name and privileges as the one owning the DB in the source DB server or use the --no-owner flag with pg_restore which skips restoration of object ownership. The objects restored will be owned by the user used on pg_restore command.

Create DB dump

Command template

pg_dump --host=<db-host> --port=<db-port> --format=directory --create --dbname=<db-name> --username=<user> --file=<path-to-save-dump>

Example

pg_dump --host=localhost --port=5444 --format=directory --create --dbname=my_app_production --username=my_app_db_user --file=./my_app_production_dump

Restore

Command template

pg_restore --host=<db-host>  --port=5432 --format=directory --create --dbname=<db-name> --username=<user> <path-to-dump>

Example:

pg_restore --host=localhost  --port=5432 --format=directory --create --dbname=my_app_production --username=my_app_db_user ./my_app_production_dump