Create Better Postgres Backup Strategy

[Niicck]

Continued from https://github.com/cityofaustin/ctxfloods/issues/429

• Not having manual backup/restores is a blocker for updating RDS Properties that require replacement (i.e. deleting all of the data and backups for our database). After database is replaced and updated with new Properties, then we can restore with previous production data.
• There are a couple attributes that I'd like to update without losing all of our production data.
• It would be good to get a workflow for this process before our public launch.

The Plan: 1) Upgrade Production RDS to v10 (DONE) 2) Backup all data manually using pg_dump for schemas/data and pg_dumpall for roles. 3) Test homemade backup script thoroughly. Make sure that restore works on a dev environment. 4) Update RDS CloudFormation attributes that require replacement. This step will delete all data in Production RDS. 5) pg_restore production data onto Production RDS.

Creating and testing the manual backup and restore scripts will be the biggest task. But I think it's better than the alternatives I've found:

Alternative 1) Instead of a manual backup/restore, we could restore with a Snapshot Backup. This can be done by adding the DBSnapshotIdentifier Property to our CloudFormation template in serverless.yml. I don't like this idea for a couple reasons. That Property would have to remain in our serverless.yml forever, lest we want to replace(delete) our database again. Keeping that Property there could lead to some unwanted side effects. We would be prohibited from changing some Properties in our serverless.yml script (particularly the master password). Also, we might be forced to keep that snapshot forever, potentially crippling our CloudFormation Stack if it were ever to be accidentally deleted (https://serverfault.com/questions/762932/what-happens-when-we-remove-the-snapshotidentifier-from-our-cloudformation-confi).

Alternative 2) AWS has a function called RestoreDBInstanceFromDBSnapshot. However, we then would not be able to perform updates on that DB Instance within our current serverless.yml setup.

Feel free to comment with thoughts and ideas.

Documentation about RDS CloudFormation Properties can be found here: https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-properties-rds-database-instance.html

[Niicck]

Note: in a pinch, adding DBSnapshotIdentifier to the pgDB Properties in serverless.yml will work. This is a manual way to restore a downed database. DBName Property must NOT be included when using this method.

DBSnapshotIdentifier: '[rds snapshot identifier]'

[Niicck]

After we have an effective backup strategy, we can finally replace some of our configs where "Update Requires Replacement." Namely, DBInstanceIdentifier. We could implement human-readable names instead of the auto-generated names that AWS gives them. It would be really helpful when doing admin work/cleanup in the AWS GUI.