Skip to content

PostgreSQL management

Create a backup

When Benefits was hosted as an Application service, we downloaded a copy of the django.db SQLite file to backup the database prior to deploying new migrations. Now we generate a .json export from PostgreSQL.

The command (and all other commands in this guide) are run from the -web suffixed Container app console. It is accessible via Azure > Container App > Monitoring > Console > /bin/bash.

# writes the file to /calitp/app
python manage.py dumpdata --natural-foreign --natural-primary --indent=2 --output db_data.json

Info

--natural-foreign and --natural-primary are used to avoid serialization issues with the permission and authentication Django objects.

Copy a backup

To manage backups, use the app container’s web-storage mounted volume.

# copy a backup into the mounted volume
cp db_data.json /home/calitp/app/data/db_data_YYY_MM_DD.json

# copy a backup from the mounted volume
cp /home/calitp/app/data/db_data_YYY_MM_DD.json db_data.json

You can view the files in the mounted volume (and upload new ones) by navigating to Azure > Storage account > Data storage > Classic file shares > web-storage > Browse.

Restore from a backup

In order to restore from a .json backup, run the commands below from the Container app console.

# nuke existing data and rerun migrations
python manage.py ensure_db --reset

# seed data using the .json backup
python manage.py loaddata db_data.json

Monitor server health

The Overview page for the “Azure Database for PostgreSQL flexible server” database service contains a variety of helpful charts to visualize the health of the hosted DB.

PostgreSQL health

Restart the server

The same Overview page can also be used to ↻ Restart the database service.

Provision

Provisioning for the database service is codified via terraform. We currently use the Burstable compute tier with 32 GiB storage capacity, 1 vCore, and 2 GiB RAM. See the azurerm documentation for more information on configuring this service.

Upgrade

When the time comes for a major version upgrade, the Overview page for the database service also offers helpful utilities for validating and upgrading the database.

Generally we expect that the steps to be:

  1. Perform a dry-run upgrade in azure to confirm that the db passes “Pre-upgrade validation”
  2. Test locally with the same version of PostgreSQL to verify appropriate behavior in the application
  3. Trigger the upgrade in Azure
  4. Update azurerm_postgresql_flexible_server in terraform and terraform apply to avoid reversion

Use pgAdmin

pgAdmin provides a GUI to manage the PostgreSQL server. pgAdmin is setup slightly differently depending on the hosted environment.

Local development environment

If this is your first time using pgAdmin or if you have recently cleared your associated Docker volume (pgadmin_data), you will need to register the PostgreSQL server:

  1. Navigate to http://localhost:8081 (replace 8081 with your PGADMIN_PORT value if it was changed).
  2. Select Add New Server from the dashboard or navigate to Object > Register > Server.
  3. Under the General tab, provide any descriptive Name for the server.
  4. Under the Connection tab, configure the following properties based on your environment variables:
    • Host name/address: Value of POSTGRES_HOSTNAME (usually postgres)
    • Port: Value of POSTGRES_PORT (usually 5432)
    • Maintenance database: Value of POSTGRES_DB (usually postgres)
    • Username: Value of POSTGRES_USER (usually postgres)
    • Password: Value of POSTGRES_PASSWORD (usually postgres)
    • Save password?: Yes

Note that because PGADMIN_CONFIG_SERVER_MODE=false, pgAdmin runs in desktop mode which uses an automatic default login. After setting it up, you can start using pgAdmin to manage the database, e.g. going to Servers > Name > Databases > django > Schemas > public > Tables to view the application’s tables.

Cloud environments

To use pgAdmin in any of the cloud environments:

  1. Select the pgAdmin Container App resource in Azure Portal.
  2. Go to Networking > Ingress > IP Restrictions and add your local public IP as an allowed Source.
  3. Open pgAdmin by launching the Container App’s Application Url.
  4. On the pgAdmin landing page, use the value of the environment variable PGADMIN_DEFAULT_EMAIL for Email Address / Username and the value of the pgadmin-admin-password secret for Password.

You can then start using pgAdmin to manage the database since the Azure Database for PostgreSQL flexible server is already registered via Terraform, mostly through the configuration of the appropriate pgAdmin environment variables.