Database maintenance

Intro

This page helps guide you through maintaining your local database.

Restart & Clean

When the DB schema changes, the backend may throw an error and fail to start.

Restarting the docker way:

1. run cd backend
2. Bring down the service by running docker-compose down
3. Wipe the DB by running docker system prune && docker images prune && docker volume prune
4. Restart the service docker-compose up --build

Restarting the SQL way:

1. run db-setup/nuke-db.sh
2. restart the spring app gradle bootRun --args='--spring.profiles.active=dev'

Rollbacks

The application uses the Liquibase plugin for Gradle to perform specific database management tasks.

To roll the database back to its state at a prior date:

docker compose run --rm backend gradle liquibaseRollbackToDate -PliquibaseCommandValue=${date}

or

$ ./gradlew liquibaseRollbackToDate -PliquibaseCommandValue=${date}

To roll back a certain number of migrations:

docker compose run --rm backend gradle liquibaseRollbackCount -PliquibaseCommandValue=${n}

or

$ ./gradlew liquibaseRollbackCount -PliquibaseCommandValue=${n}

To roll back to a specific tag:

docker compose run --rm backend gradle liquibaseRollback -PliquibaseCommandValue=${TAG}

or

$ ./gradlew liquibaseUpdateToTag -PliquibaseCommandValue=${TAG}

If you are required to roll back a non-local database, you may generate the necessary SQL to execute elsewhere. Use liquibaseRollbackToDateSQL or liquibaseRollbackCountSQL in the manner described above to write the rollback SQL to stdout.

Rollback gotchas

Our changelog contains some migrations that make use of get_census_dob_group(...) these migrations are not able to be rolled back because they need to specify the schema and thus be rewritten to ${database.defaultSchemaName}.get_census_dob_group(...), rewriting the changelog will require us to manually recompute the checksum inside the databasechangelog table for the affected migrations per every environment and will require some downtime.

To get around this issue, we decided only to test the last 20 rollbacks; that way, we don't hit the problematic migrations. As you can see here

We don't see a case where we will ever rollback to the problematic migrations. This compromise is acceptable.

Local database manipulation

Using Intellij

Using the database tool in IntelliJ, you can view and modify entries in your database. The database tool window can be opened by clicking Database on the right panel of IntelliJ or by going to View > Tool Window > Database

To add your database, go to + > Data Source > Postgres. Set the jdbc URL to jdbc:postgresql://localhost:5432/simple_report, the user to simple_report_app and the password to api123. In the schema tab, ensure the correct schema is selected. Typically you can just set the Schema Pattern to simple_report:simple_report. The connection may now be tested; you can apply the configuration if it's valid.

To access the data, you may go to simple_report@localhost > simple_report > tables and double-click the table you wish to view. To access data via query, you can open the query console and run any query. For example, to get all test_events you can use this query select * from simple_report.test_event;

Using other tools

To view tables/entries in your local database, team members currently use Postico and DataGrip. We recommend you use something similar to view/work with your local data.

To connect to the Postgres server, use the following credentials.

• username: simple_report_app
• password: api123
• port: 5432
• database: simple_report

---

Create a new db server from a backup

• Click into the database that needs to be restored
• Click into the "Backup and restore" blade
• Select the backup that you want to restore to (check the dates but the highest number is the latest)
• Click the "fast restore" link
• Set a name to the database you want to restore
• Click "Review + Create" OR update DB settings if you know what you're doing.
• Click "Create"

(DANGER) DB upgrades in deployed environments (DANGER)

• Wait for latest backup to prod
• Create a database server from latest backup using the instructions Create a db instance from a db server backup
  • Name it prod-db-backup
  • Wait until a backup is created for this instance before upgrading the prod db server
• Upgrade the production database server - If the upgrade goes well, delete the backup instance prod-db-backup - If the upgrade needs to be reverted
  • Delete the prod instance
  • Restore a new instance with the original prod db server name from the prod-db-backup backup