Database Disaster Recovery

CiviForm's production database is backed up daily, with snapshot names prefixed with the value set by POSTGRES_RESTORE_SNAPSHOT_IDENTIFIER. By default they are retained for 7 days, which can be configured byPOSTGRES_BACKUP_RETENTION_DAYS.

(AWS) Restoring the database to a previous snapshot

In the event of catastrophic data loss or corruption, the production database can be restored to a previous snapshot. To do this:

Identify the DB snapshot to restore to in the AWS console by navigating to RDS > Snapshots.
Copy the snapshot identifier (not the name, the identifier begins with rds:)
In your civiform_config.sh file, set POSTGRES_RESTORE_SNAPSHOT_IDENTIFIER to the identifier.
Remove deletion protection from the database by navigating to RDS > Databases, selecting your production database and removing deletion protection.
Run bin/deploy. You should see in the terraform plan that the RDS database will be replaced.

Note that once the database is restored, the POSTGRES_RESTORE_SNAPSHOT_IDENTIFIER should continue to reference the same snapshot identifier. Changing the value of it will trigger another database replacement which may result in data loss.

Making a full backup and restore of the database

As part of your backup strategy, you may want to dump the contents of the database outside of AWS in case something happens to your AWS account. There are a couple ways to do this.

The backup procedures listed here will create a dump of the entire database, including personally identifiable information. Ensure you take the utmost care in handling this data and store it in a secure location. Additionally, the restore procedures will overwrite the entire database with the contents of the dump file, including all applicant data. Ensure this is really what you want to do before proceeding.

Option 1: Use the pgadmin UI

Backup

Follow the instructions to access the database via pgadmin.
Right click the postgres database and choose Backup.

Pick any file name. All of the options can be left as default. If you'd like to only download some of the data, you can select which tables to download in the Objects tab. Note that this saves the file locally inside the pgadmin container.

When complete, in the top menu bar, click Tools -> Storage Manager.

Choose the file you just created, then click the download button.

Save this file in a secure location.

Restore

If your backup file is larger than 50MB, go to File -> Preferences -> Storage -> Options and change the maximum file upload size appropriately.

In the top menu bar, click Tools -> Storage Manager.

Click the Options button and choose Upload, then upload your backup file.

Right click the postgres database and choose Restore.

Under Filename, choose the file you just uploaded. In the Query Options tab, select Clean before restore. Then click Restore.

Inspect the tables under postgres -> Schemas -> Tables to verify the data was restored properly.

Option 2: Use the `dumpdb` and `restoredb` commands

This option utilizes the CiviForm Terraform-based deployment system. It will create a temporary EC2 host with access to the database, install the PostgreSQL client, run pg_dump or pg_restore, and then clean up the resources. These commands require the ssh, ssh-keygen, and scp binaries to be available on your system.