Databases on GOV.UK
Database engines
GOV.UK uses mostly PostgreSQL and some MySQL for apps that require a relational database. There’s no particular reason why we use both, and the fact that we do is considered tech debt.
GOV.UK uses MongoDB or DocumentDB for apps that require a document database. Whilst there are some big differences between the two, they’re broadly compatible, and we tend to use the term “Mongo” to apply to both.
Finally, GOV.UK uses an ElasticSearch database for search.
Hosting
Instead of running these databases locally on the same instance as the application, they’re hosted in separate infrastructure (with one exception).
Each Postgres and MySQL database runs in its own RDS instance. Whilst RDS instances are capable of hosting multiple databases, we decided to grant each database its own instance in RFC-143.
Mongo databases are hosted either in DocumentDB clusters (managed by AWS) or MongoDB clusters (managed by us on self-hosted EC2 instances). On production, there is currently one DocumentDB cluster for Licensify and one ‘shared’ DocumentDB cluster, each with three instances. There is also one Mongo cluster of three EC2s. We have agreed that we should move apps from MongoDB to DocumentDB.
ElasticSearch is hosted in AWS’s OpenSearch service. It has two types of node - “master” and “data” (instance).
DB admin
Until November 2023, we used to use db_admin bastion hosts for tasks such as:
- running backups/restores
- copying production data to the staging and integration environments
- managing Postgres user accounts via Puppet
We no longer run these db_admin bastion instances.
The jobs that used to run on db_admin instances now run as Kubernetes cronjobs, configured in the db-backup and search-index-env-sync charts. You can view job status in the Argo CD web UI (and of course kubectl on the command line).
Open a database commmand-line session
For a Rails app:
k exec deploy/content-publisher -it -- rails db -p
For a non-Rails app:
k exec deploy/bouncer -it -- sh -c 'psql $DATABASE_URL'
For the data.gov.uk CKAN Postgres database:
k exec -n datagovuk deploy/ckan-ckan -it -- sh -c 'psql $CKAN_SQLALCHEMY_URL'