Setting up Aurora database instance version 10.7 for testing (database creation and data loading)

[JonellaCulmer]

Summary

What we're after:
Aurora database (an advanced version of postgresql database on AWS) was suggested by AWS Envision team to help alleviate the slow query problem we had encountered with increasing amount of data. The first step to migrate from our current postgresql database to Aurora database instance is to set up test Aurora database instance and load real data as the base for test out all applications that load/query data from it.

Related issues

• Perform loading tests (Java, Python, Flyway migration, GoldenGate) #4287
• Hook up testing data loading with CircleCI #4288
• Perform data query test (API, data export, PGBadger) #4289
• Perform locust load testing #4290
• Initial performance tuning #4291

Completion criteri

• Create a Aurora version 10.7 database instance
• Create database, set up database roles/permission
• Backup data from current postgresql database and create/load databases objects and data into the new Aurora database
• Create replicas for developers to connect and test

Future work

• Perform loading tests (Java, Python, Flyway migration, GoldenGate) #4287
• Hook up testing data loading with CircleCI #4288
• Perform data query test (API, data export, PGBadger) #4289
• Perform locust load testing #4290
• Initial performance tuning #4291

[fecjjeng]

• From AWS console, an aurora database cluster with version 10.7 had been created. This is the highest version supported in gov cloud and also the python package support is compatible with our current code.
• A master database instance (read/write) was created.
• A user generation script had been created by querying current dev database for users and permissions. This script were used to re-create users in the current dev database in the new dev-Aurora database.
• An ec2 server had been setup in gov cloud and was used to run pg_dump from the current postgres dev database and pg_restore into the new dev-Aurora database.
• NOTE: Since the purpose of this load is to provide reasonable amount of data for testing purpose, data load to the postgresql dev database such as GoldenGate was not stopped.
  In the situation during the real switch, data load to the source database will be stopped until the switch complete.
• To provide a reference in estimation on the time required during real switch time:
  The pg_dump takes about 3.5 hours. The pg_restore, with 5 jobs running, takes about 16 hours.
• Read replica instance created after initial load of the master database had been completed
  (2 read-replicas had been created for our initial testing. At the end of this initial setup testing, only 1 read-replica is kept for further user testing)