Connect to EC2

Create EC2 Instance

In order to backup an EDSC database you need to create an EC2 instance to connect to, where you will connect to RDS and execute the pg_dump command.

To create your EC2 instance, requirements can be found on this wiki page.

Be sure you select a t2.medium instance type, and add the private application subnet in order to connect.

You will then need to add the necessary security groups to your EC2 instance to enable communication to RDS. Look for the security groups with "DatabaseVpcSecurityGroup" and "LambdaSecurityGroup" in the name and add them to your instance.

Connect to EC2 Instance

Select your EC2 instance from the EC2 Instances list, then open the "Actions" dropdown and select "Connect". Select Session Manager and click on the "Connect" button. This should open a shell screen on your EC2 instance.

If this is your first time connecting you will need to install postgresql in order to connect to RDS:

$ sudo yum clean metadata
$ sudo yum install postgresql

(may need to specify version such as sudo yum install postgresql15) we can find which version is currently supported with

sudo yum search "postgres"

Connect to RDS from EC2 Instance

You will need to lookup the host, username, password and dbname from Secrets Manager. Open the secret that starts with "DbPasswordSecret" in Secrets Manager. Within the "Secret value" section click on "Retrieve secret value" and you will see the necessary values. DO NOT SHARE THIS VALUES

To connect to RDS to perform SQL queries:

$ psql --host=HOST_VALUE --port=5432 --username=USERNAME_VALUE --dbname=DBNAME_VALUE --password

You will be prompted for the database password

Backup Database

Database Dump

You will need to lookup the host, username, password and dbname from Secrets Manager. Open the secret that starts with "DbPasswordSecret" in Secrets Manager. Within the "Secret value" section click on "Retrieve secret value" and you will see the necessary values. DO NOT SHARE THIS VALUES

To dump the database into a sql file:

$ pg_dump --host=HOST_VALUE --port=5432 --username=USERNAME_VALUE --dbname=DBNAME_VALUE --password --no-owner --if-exists --clean -W --file=FILENAME.sql

If you want just a specific table from a database you can pass the --table "<table-name>" to the pg_dump cmd to only dump tables that match the regex

You will be prompted for the database password

This will create "FILENAME.sql" on your EC2 instance. This file can be used to restore an RDS database with all of the values dumped from the previous database, or transferred to S3 in order to download

In order to transfer the file to S3:

$ aws s3 cp FILENAME.sql s3://S3_BUCKET_NAME/FILENAME.sql

Restore Database

In order to restore a database dump sql file you will need an EC2 instance.

You will need to ensure that a full EDSC stack is up and running before restoring the dump file.

If for some reason you don't have a dump file, but you do have a RDS snapshot:

1. Restore the RDS snapshot to a new database (created when restoring the snapshot, it cannot restore to an existing database).
2. Perform a Database Dump to pull the data out of the newly created database (Database Dump).
3. Proceed to Restore Dump File below.

Restore Dump File

You will need to lookup the host, username, password and dbname from Secrets Manager. Open the secret that starts with "DbPasswordSecret" in Secrets Manager. Within the "Secret value" section click on "Retrieve secret value" and you will see the necessary values. DO NOT SHARE THIS VALUES

To restore the sql file:

$ psql --host=HOST_VALUE --port=5432 --username=USERNAME_VALUE --dbname=DBNAME_VALUE --password --file FILENAME.sql

You will be prompted for the database password