PostgreSQL and TimescaleDB backup restoration using ClusterControl CLI

Data is probably the most valuable asset in a company, so you need to make sure it is safe and can be restored in case of failure. Backups are the basic way to keep it safe in a Disaster Recovery Plan (DRP), and you need to be prepared to restore them if needed, so a good backup strategy includes a restoration test from time to time to make sure your backup is usable.

In this blog, we will see how to restore a PostgreSQL and TimescaleDB backup from the ClusterControl CLI using the powerful s9s tool.

ClusterControl CLI

Also known as s9s, is a command-line tool introduced in ClusterControl version 1.4.1 to interact, control, and manage database clusters using the ClusterControl system. ClusterControl CLI opens a new door for cluster automation where you can easily integrate it with existing deployment automation tools like Ansible, Puppet, Chef, etc. The command-line tool is invoked by executing a binary called s9s added by default in the ClusterControl installation.

You can find more information in the Official Documentation or even by running the s9s command with the help parameter:

$ s9s --help

Usage:
  s9s COMMAND [OPTION...]

Where COMMAND is:
    account - to manage accounts on clusters.
      alarm - to manage alarms.
     backup - to view, create and restore database backups.
    cluster - to list and manipulate clusters.
 controller - to manage Cmon controllers.
        job - to view jobs.
maintenance - to view and manipulate maintenance periods.
   metatype - to print metatype information.
       node - to handle nodes.
    process - to view processes running on nodes.
replication - to monitor and control data replication.
     report - to manage reports.
     script - to manage and execute scripts.
     server - to manage hardware resources.
      sheet - to manage spreadsheets.
       user - to manage users.

Generic options:
  -c, --controller=URL       The URL where the controller is found.
  --config-file=PATH         Specify the configuration file for the program.
  --help                     Show help message and exit.
  -P, --controller-port INT  The port of the controller.
  -p, --password=PASSWORD    The password for the Cmon user.
  --private-key-file=FILE    The name of the file for authentication.
  --rpc-tls                  Use TLS encryption to controller.
  -u, --cmon-user=USERNAME   The username on the Cmon system.
  -v, --verbose              Print more messages than normally.
  -V, --version              Print version information and exit.


Formatting:
  --batch                    No colors, no human readable, pure data.
  --color=always|auto|never  Sets if colors should be used in the output.
  --date-format=FORMAT       The format of the dates printed.
  -l, --long                 Print the detailed list.
  --log-file=PATH            The path where the s9s client puts its logs.
  --no-header                Do not print headers.
  --only-ascii               Do not use UTF8 characters.
  --print-json               Print the sent/received JSon messages.
  --print-request            Print the sent JSon request message.


Job related options:
  --job-tags=LIST            Set job tags when creating a new job.
  --log                      Wait and monitor job messages.
  --recurrence=CRONTABSTRING Timing information for recurring jobs.
  --schedule=DATE&TIME       Run the job at the specified time.
  --timeout=SECONDS          Timeout value for the entire job.
  --wait                     Wait until the job ends.

Also, the s9s tool has a man page for each command to get more detailed information.

$ man s9s backup

Now you know what s9s is, let’s see how to restore a PostgreSQL or TimescaleDB backup using it.

Restoring Backups using ClusterControl CLI

The tool that you need to use for this job is s9s backup. It is used for viewing, creating, or restoring database backups using the ClusterControl CLI. 

Usage

s9s backup {options}

Where options are:

−−restore Restores an existing backup.
−−backup-id=ID The ID of the backup.
−−cluster-id=ID The ID of the cluster.
--verify                   Verify an existing backup on a test server.
--test-server=HOSTNAME     Verify the backup by restoring on this server.
-l, --long                 Print the detailed list.
--wait                     Wait until the job ends.
--log                      Wait and monitor job messages.

Examples

List all backups for cluster ID 42:

Here, you need to specify the Cluster ID to list the backups. You can omit this parameter to list the backup created in all clusters.

$ s9s backup --list 
--cluster-id=42 
--long

Restore backup ID 22 on cluster ID 42:

For this, you will need to specify Cluster-ID where to restore the backup, and Backup ID to be restored.

$ s9s backup --restore 
--cluster-id=42 
--backup-id=22 
--wait

Verifying Created Backups

Create a job to verify the given backup identified by the backup ID. The job will attempt to install the database software on the test server using the same settings as for the given cluster, then restore the backup on this test server. The job returns OK only if the backup is successfully restored.

$ s9s backup --verify 
--log 
--backup-id=26 
--test-server=10.10.10.138 
--cluster-id=42

If everything went fine, you will see:

Backup 26 was successfully verified.

Conclusion

As you can see, just by running a simple command, you can check, restore, or even verify your backups in an easy way using ClusterControl CLI.

For more information about the ClusterControl CLI and its usage, you can follow the Official Documentation.