Steps to manually refresh DEV STG Database(s) with PROD data

In the event that the Jenkins task "Project Back up PROD DB and refresh STG and DEV DBs" fails, the following instructions should allow us to manually refresh the DEV/STG database(s) with PROD data.

Notice: All of the following steps will need to be performed on a GFE or CAG.

Semi-manual Process

1. Open up a browser window and go to Jenkins. Click on the "Project Back up PROD DB and refresh STG and DEV DBs" task, and then click configure. Below where it says "echo "Reboot STAGING/DEV RDS DB," comment out the following lines:

ssh ec2-user@$DEV_SERVER "sudo docker exec -u 0:0 diffusion-marketplace_app_1 rails runner \"client = Aws::RDS::Client.new(region: '${AWS_REGION}'); client.reboot_db_instance({db_instance_identifier: 'vaecdiffusionmarketplacedevenc'})\""
ssh ec2-user@$STG_SERVER "sudo docker exec -u 0:0 diffusion-marketplace_app_1 rails runner \"client = Aws::RDS::Client.new(region: '${AWS_REGION}'); client.reboot_db_instance({db_instance_identifier: 'diffusionmarketplacestaging'})\""

as well as the sleep 15 line. Save the changes.

1. Open up another window and go to AWS. In the search bar, type "RDS" and then click on "RDS."
2. Click on the "DB Instances" link.
3. In rapid succession, complete the following actions:

• Select the "vaecdiffusionmarketplacedevenc" DB instance, click "Actions", click "Reboot", and then click "Reboot" again on the confirmation page.
• Select the "diffusionmarketplacestagingc" DB instance, click "Actions", click "Reboot", and then click "Reboot" again on the confirmation page.
• Go back to Jenkins and click "Build Now" on the refresh project.

If the steps still failed to refresh the DBs, it's time to get serious!

UBER-manual process

1. In AWS, enter the session manager for the DEV EC2 instance.
2. Bash into the DEV container by writing:

sudo su ec2-user

then change directories

cd ~/diffusion-marketplace

and finally bash in

docker exec -u 0:0 diffusion-marketplace_app_1 bash

Dropping the DB

1. Open up another AWS window, search for "RDS" and then click on "RDS."
2. Click on the "DB Instances" link.
3. Complete the following actions, but wait approx. 10 seconds after finishing the first step before continuing on:

• Select the "vaecdiffusionmarketplacedevenc" DB instance, click "Actions", click "Reboot", and then click "Reboot" again on the confirmation page.
• Go back to the terminal session window and write:

PGPASSWORD=$POSTGRES_PASSWORD /usr/pgsql-12/bin/dropdb -U $POSTGRES_USER -h $POSTGRES_HOST $POSTGRES_DB

4. If there are no messages, but a new line appears in the terminal (something like bash-4.2), then it should've successfully dropped the DB.

Creating the DB

1. In the terminal, write:

PGPASSWORD=$POSTGRES_PASSWORD /usr/pgsql-12/bin/createdb -U $POSTGRES_USER -h $POSTGRES_HOST $POSTGRES_DB

2. It will probably ask you for the the password for postgres. If it does, simply write printenv, look for the POSTGRES_PASSWORD environment variable, and then copy and paste it.

Restoring the DB with PROD data

1. In another AWS window, search for "S3", click on "S3", and then click on the "vadm-db-backups" name.
2. This should bring you to the file objects for the bucket. Look for the "Last modified" column and click on it twice to get the latest file. Copy the whole name, EXCEPT for the .gz.
3. Back in the terminal, write:

PGPASSWORD=$POSTGRES_PASSWORD /usr/pgsql-12/bin/psql -U $POSTGRES_USER -h $POSTGRES_HOST $POSTGRES_DB < NAME_OF_THE_FILE_YOU_COPIED

4. You should see a bunch of data start to appear.
5. Once it's complete, confirm the DB has been refreshed by cross-referencing the data with PROD (e.g., going to /admin/practices and checking the practice counts).

Repeat all "UBER-manual process" steps, but for the STG Ec2 instance.

Happy DevOps'ing 😄 💻