Charmed PostgreSQL How-to - Create And List Backups

How to create and list backups

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

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

If you are using an earlier version, be aware that:

  • juju run replaces juju run-action --wait in juju v.2.9
  • juju integrate replaces juju relate and juju add-relation in juju v.2.9

For more information, check the Juju 3.0 Release Notes.

Prerequisites

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 restore a (foreign) backup | Manage cluster passwords.

Create backups

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

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

  • TLS enabled: disables the command from running on primary units.
  • TLS not enabled: disables the command from running on non-primary units.

List backups

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

juju run postgresql/leader list-backups

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

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

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!

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

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).

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.

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!

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