Database Backup

Here are some sample scripts on how to backup and restore the Katalogue PostgreSQL repository database.

This might be useful when manually backing up the database before an upgrade, migrating the database to another instance or building a custom backup automation script.

The scripts uses the PostgreSQL pg_dump, pg_dumpall, pg_restore and psql command line tools. They must normally be installed before they become available on your computer. See the official documentation for the PostgreSQL pg_dump, pg_dumpall, pg_restore and psql command line tools for more details.

Backup

Backup Schema & Data

Flags description:

• -h hostname
• -p port
• -U username of the admin user used to connect to the instance
• -d database
• -C tell pg_dump to include “create database” statement in the backup
• -F backup file format. t means compressed (.tar) format
• -f output file path and name

pg_dump -h <HOSTNAME> -p 5432 -U postgres -C -d katalogue -F t -f ./katalogue_backup_schema_and_data_<ENV>.tar

Backup Roles

PostgreSQL users are stored as roles. This will include all roles in the postgres instance. Edit the resulting file to filter out roles.

Note that the roles are normally static over time, meaning that this step is normally only necessary to do once.

Flags description:

• -r include roles only

pg_dumpall -h <HOSTNAME> -p 5432 -U postgres -r -f ./katalogue_backup_roles_<ENV>.sql

Restore

Restore Roles

This might fail if the backup file includes superusers and if your user does not have permission to create other admin users (which is normally the case in Azure managed postgres databases).

Note

The -d flag cannot be ‘katalogue’, as that database does not yet exist (its create statement is in the backup file)

psql -h <HOSTNAME> -p 5432 -U postgres -d postgres -f ./katalogue_backup_roles_<ENV>.sql

Restore Schema & Data

Flags description:

• -h hostname
• -p port
• -U username of the admin user used to connect to the instance
• -d database
• -C tell pg_restore to look for create database statements. Omitting this flag causes everything to be restored to the database specified by the -d flag.

Note

The following two flags are only necessary if the admin role name differs between the backed up database instance and the instance the backup is restored on.

• —no-owner Tell pg_restore to not use the owner for tables etc. specified in the backup file
• —role Tells pg_restore which role to use as owner for tables etc.

Note

The -d flag cannot be ‘katalogue’, as that database does not yet exist (its create statement is in the backup file)

Danger

Do not forget the -C flag as everything will be restored to the database specified by the -d flag if it is omitted!

pg_restore -h <HOSTNAME> -p 5432 -U postgres -C -d postgres --no-owner --role=postgres ./katalogue_backup_schema_and_data_<ENV>.tar

Utility script to drop a PostgreSQL database

It is normally easiest to simply drop the database and re-run pg_restore if something went wrong during the restoration.

-- Terminate all connections to the database
-- Just executing "drop database ..." might not work because of active sessions by your IDE etc.
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'katalogue'
AND pid <> pg_backend_pid();

-- Drop the database
DROP DATABASE katalogue;