How to restore a local backup

vmk8s

This is a guide on how to restore a locally made backup.

To restore a backup that was made from the a different cluster, (i.e. cluster migration via restore), see How to migrate a cluster.

Prerequisites

  • A PostgreSQL deployment scaled down to one unit (scale it up again after the backup is restored)

Apply cluster credentials

When restoring a backup that was taken from the same cluster and the operator, monitoring, replication, and rewind passwords have not changed since then, you do not need to do this step.

Create a secret with the password values you saved when creating the backup:

juju add-secret <secret name> monitoring=<password1> operator=<password2> replication=<password3> rewind=<password4>

where <secret name> can be any name you’d like for the restored secrets.

Then, grant the secret to the PostgreSQL application that will initiate the restore:

juju grant-secret <secret name> postgresql

juju grant-secret <secret name> postgresql-k8s

List backups

To view the available backups to restore, use the command list-backups:

juju run postgresql/leader list-backups

juju run postgresql-k8s/leader list-backups

This should show your available backups like in the sample output below:

list-backups: |-
  Storage bucket name: canonical-postgres
  Backups base path: /test/backup/

  backup-id            | action             | ... | timeline
  ---------------------------------------------------------------------------
  2024-07-22T13:11:56Z | full backup        | ... | 1
  2024-07-22T14:12:45Z | incremental backup | ... | 1
  2024-07-22T15:34:24Z | restore            | ... | 2
  2024-07-22T16:26:48Z | incremental backup | ... | 2
  2024-07-22T17:17:59Z | full               | ... | 2
  2024-07-22T18:05:32Z | restore            | ... | 3

Below is a complete list of parameters shown for each backup/restore operation:

  • backup-id: unique identifier of the backup.

  • action: indicates the action performed by the user through one of the charm action; can be any of full backup, incremental backup, differential backup or restore.

  • status: either finished (successfully) or failed.

  • reference-backup-id

  • LSN start/stop: a database specific number (or timestamp) to identify its state.

  • start-time: records start of the backup operation.

  • finish-time: records end of the backup operation.

  • backup-path: path of the backup related files in the S3 repository.

  • timeline: number which identifies different branches in the database transactions history; every time a restore or PITR is made, this number is incremented by 1.

Point-in-time recovery

Point-in-time recovery (PITR) enables restorations to the