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
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
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:
- Restore the RDS snapshot to a new database (created when restoring the snapshot, it cannot restore to an existing database).
- Perform a Database Dump to pull the data out of the newly created database (Database Dump).
- 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