Creating and restoring PostgreSQL backups

Edit online

Tech preview Tenant users can create a manual backup of PostgreSQL data in the tenant clusters by using the pg_dump command. Tenant users can also restore backup data by using the pg_restore command. Because an S3-compatible object storage is not available in the current version of IBM® Sovereign Core, create backups locally and manage them as a tenant user.

Before you begin

  • You have access to the Red Hat® OpenShift cluster where the PostgreSQL instance is deployed.
  • You are logged in to the oc CLI with the required permissions.
  • You know the name of the PostgreSQL pod and the target database.
  • You have local storage available for saving backup files.

Procedure

  1. Identify the PostgreSQL pod name to create a manual backup. 
    oc get pods -n <namespace>
    Tip: You can automate backups with a CronJob, pipeline, or external scheduler, and validate them regularly.
  2. Use any of these backup commands:
    • To create a plain SQL backup: 
      oc exec -it <pod-name> -- pg_dump -U postgres -d <database> > backup.sql
    • To create a backup in custom format: 
      pg_dump -U postgres -Fc -d <database> -f backup.dump
  3. Verify that backup.sql or backup.dump is created on your local machine.

What to do next

Store the backup file securely
  1. Move the backup file to a secure local directory.
  2. Optional: Set up a regular backup routine by using a CronJob, script, or scheduling tool.
Restore a PostgreSQL backup
  • To restore from a plain SQL file, run: 
    oc exec -i <pod-name> -- psql -U postgres -d <database> < backup.sql
  • To restore from a custom-format backup, run: 
    pg_restore -U postgres -d <database> backup.dump