Skip to main content

Your submission was sent successfully! Close

Thank you for signing up for our newsletter!
In these regular emails you will find the latest updates from Canonical and upcoming events where you can meet our team.Close

Thank you for contacting us. A member of our team will be in touch shortly. Close

Note: All commands are written for juju >= v.3.0

If you are using an earlier version, check the Juju 3.0 Release Notes.

How to restore a local backup

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

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

Prerequisites

Summary

List backups

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

juju run postgresql/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) is a PostgreSQL feature that enables restorations to the database state at specific points in time.

After performing a PITR in a PostgreSQL cluster, a new timeline is created to track from the point to where the database was restored. They can be tracked via the timeline parameter in the list-backups output.

Restore backup

To restore a backup from that list, run the restore command and pass the parameter corresponding to the backup type.

When the user needs to restore a specific backup that was made, they can use the backup-id that is listed in the list-backups output.

juju run postgresql/leader restore backup-id=YYYY-MM-DDTHH:MM:SSZ

However, if the user needs to restore to a specific point in time between different backups (e.g. to restore only specific transactions made between those backups), they can use the restore-to-time parameter to pass a timestamp related to the moment they want to restore.

juju run postgresql/leader restore restore-to-time="YYYY-MM-DDTHH:MM:SSZ"

Your restore will then be in progress.

It’s also possible to restore to the latest point from a specific timeline by passing the ID of a backup taken on that timeline and restore-to-time=latest when requesting a restore:

juju run postgresql/leader restore restore-to-time=latest
Previous Create a backup Next Manage backup retention

Last updated 15 days ago. Help improve this document in the forum.