Creating a PostgreSQL data image from a custom backup
Published 02 November 2022
Custom backups are restored with the --no-owner
flag enabled. This means all objects restored will be owned by the auto generated admin user. If you need to, you may change the ownership of objects in subsequent data containers created from this image.
For custom backups, there's some additional configuration required. You'll need to specify the following items in the data image definition file:
- path
format
initialDatabaseName
Tutorial (step-by-step)
Create a backup
Assuming there is a database called pagila
running at prod-db.example.com
on port 5432
and is accessible to the user admin
, you can run the following pg_dump
command:
pg_dump -h prod-db.example.com -U admin --create pagila --format=custom > ./postgres-backup-image/pagila.dump
Upload the backup
You will then need to upload the pagila.dump
file to the fileshare setup by your administrator. This will be the value they have defined in the Admin Console configuration page.
Assuming that you have SSH access to the server containing the fileshare, you could use rsync to upload the file.
rsync -r ./postgres-backup-image <fileshare-server>:/backups/
Create a data image yaml file
Now that we've got a pagila.dump
file produced by pg_dump
we can create our data image definition YAML file with the following contents:
# ~/rgclone/postgres-backup-image/image.yaml sourceType: backup name: pagila engine: postgresql version: 14 backups: - path: backups/postgres-backup-image/pagila.dump format: custom initialDatabaseName: pagila tags: - production
This instructs Redgate Clone to create a data image called pagila
on Postgres 14. It will locate the file at backups/postgres-backup-image/pagila.dump
in the fileshare and restore that custom backup file using pg_restore
, snapshot the state of the restored database and then produce the data image ready for consumption.
The data image will also have a production
tag associated with it, so you later know that this data image represents a backup of production.
Create the image
Run:
rgclone create data-image --file=./image.yaml
This will create the data image from the backup defined in the image.yaml
file we created.