Skip to content

Database dumps and imports

Database dumps

If you have PostgreSQL installed locally, a dump of the production database can be obtained with the following command:

pg_dump -Fc -v -h 10.184.26.4 -p 5432 -U wgtail_dbadmin -d aztate-prd-ew-dg-wgtail-pgdb > database_dumps/prod.sql
  • -Fd: Use the special compressed format
  • -v: Output verbose logging (to reassure the process is not hanging)
  • -h, -p, -U, -d: Specify the host, port, user and database name

You will need the password, which can be found in the DATABASE_URL environment variable in the Azure App Service configuration, or in the Tate Digital password manager.

Database imports

Pre-prod

To import the dump to the pre-prod Postgres DB, run the following:

pg_restore -v -h 10.182.26.5 -p 5432 -U wgtail_postgrs -d aztate-prprd-ew-dg-wgtail-pgdb -c -O database_dumps/prod.sql
  • -v: Output verbose logging (to reassure the process is not hanging)
  • -h, -p, -U, -d: Specify the host, port, user and database name
  • -c: Tell pg_restore to clean (drop database objects) before recreating them from the dump
  • -O: Ignore ownership commands (as the prod DB owner differs to the preprod)

Local

To import a DB dump to your local dev environment, you have two options:

The easiest way is to use the fab restore-db command, which handles all the steps automatically:

fab restore-db --source-filename={{dump_filename}}

This command will: - Look for the file in the database_dumps/ directory - Copy it to the database container - Stop connected services - Drop and recreate the database - Restore the dump - Clean up the temporary file

Additional options: - Use --no-clean to preserve existing database objects during restore

Option 2: Manual steps

If you prefer to do it manually, follow these steps:

1) Ensure you have Docker running and have started the containers:

fab start

2) List the containers so we can get the name of the database container from the NAMES column:

docker ps

CONTAINER ID    IMAGE    ...        NAMES
a585ad88980a    tate-wagtail-web    tate-wagtail-web
69eefa209e6f    postgres:12.3       tate-wagtail-db
1310eadf53ac    redis:6.2           tate-wagtail-redis

3) Copy the DB dump to the container so named:

docker cp database_dumps/prod.sql tate-wagtail-db:/

4) Run the pg_restore command in the container:

docker exec -it tate-wagtail-db /bin/bash -c "pg_restore -c -U tate -d tate -v /prod.sql -w"

5) Delete the DB dump in the container:

docker exec -it tate-wagtail-db /bin/bash -c "rm -f /prod.sql"

Connecting to the database

You can connect to the local Postgres DB by running: 

fab sh -s db
This is a fab shortcut for the command: 
docker compose exec db bash
You may then enter the Postgres CLI via: 
psql -U tate -d tate

PostgreSQL commands

When connected to the DB in Postgres CLI mode, you can use these basic commands:

\l                     # List databases
\l+                    # List with extra info
\c tate                # Connect to `tate` database
\dt                    # Describe (list) tables
\d home_homepage       # Describe home_homepage table
\x                     # Turn on expanded view (display rows nicely)
\q                     # Quit the CLI

In the CLI you may run regular SQL queries, like:

SELECT * FROM events_eventpage;