Backup or Restore Wagtail PostgreSQL database in dev, stage, or production - fecgov/fec-cms GitHub Wiki

You can connect to the RDS instance by using CF commands. Note that you may need to install the jq package in order to process the json in your command line. The following commands include using that package. You can install it using brew like this: brew install jq

Step 1: Exporting data

# Target the environment you're exporting from
cf target [-o <org name>] -s <space name>

# Set app GUID
cms_app_guid=$(cf app --guid cms)

# Establish the tunnel for the SSH session
tunnel=$(cf curl /v2/apps/$cms_app_guid/env \
  | jq -r '[.system_env_json.VCAP_SERVICES."aws-rds"[0].credentials 
  | .host, .port] 
  | join(":")')

# Check what PG services you are running locally and stop them
brew services list
brew services stop postgresql@[#]

# Start the tunnel. Leave this window open.
cf ssh -N -L 5432:$tunnel cms

# Open up a new terminal window in order to set credentials and database name to connect

cms_app_guid=$(cf app --guid cms)

creds=$(cf curl /v2/apps/$cms_app_guid/env \
  | jq -r '[.system_env_json.VCAP_SERVICES."aws-rds"[0].credentials 
  | .username, .password] 
  | join(":")')

dbname=$(cf curl /v2/apps/$cms_app_guid/env \
  | jq -r '.system_env_json.VCAP_SERVICES."aws-rds"[0].credentials 
  | .name')

# Optional, you can check to see if you can connect to the postgres RDS instance
psql postgres://$creds@localhost:5432/$dbname
# quit the instance once you confirmed you can connect
\q

# Locally make the back up file for the Wagtail database
pg_dump -F c --no-acl --no-owner -f fec_cms_gov_cloud_prod_[YYYYMMDD].dump postgresql://${creds}@localhost:5432/${dbname}

# If you stopped postgres locally in order to establish the SSH tunnel, you can restart it locally
brew services list
brew services start postgresql@[#]

Once these steps are complete, you need to upload a copy of the backup to the Drive folder 🔒.

Step 2: Importing data

# Target the environment you're restoring into
cf target [-o <org name>] -s <space name> 

# Check the current running applications
cf apps

# Unbind the database service connected to the app you're working with
cf us cms <service name>

# Delete the database service
cf ds <service name>

# Recreate the database service
# To see all available service plans, run `cf marketplace`
# Note:  Only production should have something other than shared-psql
cf cs aws-rds <service plan> <service name>

# rebind the service to the app
cf bs cms <service name>

# In this terminal window, follow the steps below to connect to the service to setup a direct SSH tunnel and leave it running
# query for the credentials again to set up the tunnel
# Set app GUID
cms_app_guid=$(cf app --guid cms)

# Establish the tunnel for the SSH session
tunnel=$(cf curl /v2/apps/$cms_app_guid/env \
  | jq -r '[.system_env_json.VCAP_SERVICES."aws-rds"[0].credentials 
  | .host, .port] 
  | join(":")')

# Check what PG services you are running locally and stop them
brew services list
brew services stop postgresql@[#]

# Start the tunnel. Leave this window open.
cf ssh -N -L 5432:$tunnel cms

# Open up a new terminal window in order to set credentials and database name to connect

cms_app_guid=$(cf app --guid cms)

creds=$(cf curl /v2/apps/$cms_app_guid/env \
  | jq -r '[.system_env_json.VCAP_SERVICES."aws-rds"[0].credentials 
  | .username, .password] 
  | join(":")')

dbname=$(cf curl /v2/apps/$cms_app_guid/env \
  | jq -r '.system_env_json.VCAP_SERVICES."aws-rds"[0].credentials 
  | .name')

# We use PostgresSQL@15 for restore
# Restore the database
# It's common to see "Error while INITIALIZING" errors - don't be alarmed

pg_restore --dbname postgres://$creds@localhost:5432/$dbname --no-acl --no-owner <path of the dump file>.dump

# In the window with the SSH Tunnel, close the SSH tunnel
Ctrl+C

# Restage the app associated with the service
# Don't worry about downtime because the application will throw errors 
# until the database is rebound to the app anyway.
cf restage cms

# For dev, stage, and feature spaces, we need to set a password on the site by making the homepage private. Go to edit the homepage, click "status" which is the i icon, and choose "Private, accessible with the following password". You may set the password from there.

# If you stopped postgres locally in order to establish the SSH tunnel, you can restart it locally
brew services list
brew services start postgresql@[#]
⚠️ **GitHub.com Fallback** ⚠️