Charmed PostgreSQL How to | Create a backup

charm
, , ,
1

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 create and list backups

This guide contains recommended steps and useful commands for creating and managing backups to ensure smooth restores.

Prerequisites

Summary

Save your current cluster credentials

For security reasons, charm credentials are not stored inside backups. So, if you plan to restore to a backup at any point in the future, you will need the operator, replication, and rewind user passwords for your existing cluster.

You can retrieve them with:

juju run postgresql/leader get-password username=operator
juju run postgresql/leader get-password username=replication
juju run postgresql/leader get-password username=rewind

For more context about passwords during a restore, check How to migrate a cluster > Manage cluster passwords.

Create a backup

Once you have a three-node cluster with configurations set for S3 storage, check that Charmed PostgreSQL is active and idle with juju status.

Once Charmed PostgreSQL is active and idle, you can create your first backup with the create-backup command:

juju run postgresql/leader create-backup

By default, backups created with the command above will be full backups: a copy of all your data will be stored in S3. There are 2 other supported types of backups (available in revision 416+, currently in channel 14/edge only):

  • Differential: Only modified files since the last full backup will be stored.
  • Incremental: Only modified files since the last successful backup (of any type) will be stored.

To specify the desired backup type, use the type parameter:

juju run postgresql/leader create-backup type={full|differential|incremental}

Tip: To avoid unnecessary service downtime, always use non-primary units for the action create-backup. Keep in mind that:

  • When TLS is enabled, create-backup can only run on replicas (non-primary)
  • When TLS is not enabled, create-backup can only run in the primary unit

List backups

You can list your available, failed, and in progress backups by running the list-backups command:

juju run postgresql/leader list-backups
2

I tried this, but when I ran juju run-action postgresql/leader create-backup --wait I got a message saying Unit cannot perform backups as it is the cluster primary.

Also, it says you can list your “available, failed and in progress backups” with list-backups, but I had a failed backup as follows:

$ juju run-action postgresql/3 create-backup --wait
unit-postgresql-3:
  UnitId: postgresql/3
  id: "8"
  message: Failed to backup PostgreSQL
  results: {}
  status: failed
  timing:
    completed: 2023-05-26 14:09:27 +0000 UTC 
    enqueued: 2023-05-26 14:09:23 +0000 UTC  
    started: 2023-05-26 14:09:24 +0000 UTC

And this doesn’t show in the list:

$ juju run-action postgresql/3 list-backups --wait
unit-postgresql-3:
  UnitId: postgresql/3
  id: "10"
  results:
    backups: |-
      backup-id             | backup-type  | backup-status
      ----------------------------------------------------
  status: completed
  timing:
    completed: 2023-05-26 14:12:18 +0000 UTC
    enqueued: 2023-05-26 14:12:17 +0000 UTC
    started: 2023-05-26 14:12:17 +0000 UTC
3

(I also tried running the list-backups on the leader and got the same result).

4

Hi Tom, thank you for the feedback.

It is possible to create backup on primary/leader in case of single unit installation. If you have a cluster (2+ units) the charm will force you to use non-primary unit. I will improve the documentation to mention this details.

As for Failed to backup PostgreSQL can you please share the debug-log with the error (if any). You can contact Marcelo directly to trace it in place on your side. Could be something new RadosGW related. Tnx!

5

Yep, I’ve shared it with him on Mattermost and he’s taking a look now. Thanks!

6

The issue was related to a bug in the charm that prevented a backup to be performed in the primary unit (when TLS is not enabled). It was fixed and the revision in the 14/edge channel should work now (for backups requested to the primary unit when TLS is disabled and in the replicas when TLS is enabled).

7

Is it worth mentioning that an operator also needs to take copies of the operator, replication and rewind user passwords from their existing cluster if they want to restore to another cluster at any point in the future? This certainly took me by surprise and isn’t something we were doing until a member of my team pointed me at the “Prerequisites” section of Charmhub | Deploy Charmed PostgreSQL VM using Charmhub - The Open Operator Collection.

8

Thank you for reporting @mthaddon , it is mentioned in the “Manage cluster passwords” of “Restoring the foreign backup”: VM: Charmhub | Deploy Charmed PostgreSQL VM using Charmhub - The Open Operator Collection K8s: Charmhub | Deploy Charmed PostgreSQL K8s using Charmhub - The Open Operator Collection

@avgomes please improve this section or create a reference from here to “restore [your] and [foreign] backup” to prevent negative UX in the future. Tnx!

9

Will do! Thanks for bringing this up, @mthaddon.