Backup

Provides an interface for database backup and restore management, scheduling, and reporting. Each backup will be assigned with a backup ID and ClusterControl creates a directory under Storage Directory to store the backups based on this ID. On top of the page, you can see 3 function tabs followed by the created backup list underneath it.

Create Backup

Creates or schedules a PostgreSQL backup.

Create Backup

Creates an on-demand backup.

Schedule Backup

Creates backup schedules of the database.

Scheduled Backups

List of scheduled backups. You can enable and disable the schedule by toggling it accordingly. The created schedule can be edited and deleted.

Backup Method

This section explains the backup method used by ClusterControl.

pg_dumpall

ClusterControl performs pg_dumpall against all databases together with --clean the option, which includes SQL commands to clean (drop) databases before recreating them. DROP commands for roles and tablespaces are added as well. The output will be .sql.gz extension and the file name contains the timestamp of the backup.

pg_basebackup

pg_basebackup is used to take base backups of a running PostgreSQL database cluster. These are taken without affecting other clients to the database and can be used both for point-in-time recovery and as the starting point for a log shipping or streaming replication standby server. It makes a binary copy of the database cluster files while making sure the system is put in and out of backup mode automatically. Backups are always taken of the entire database cluster; it is not possible to back up individual databases or database objects.

ClusterControl connects to the replication stream using the replication user (default is cmon_replication) with --wal-method=fetch option when creating the backup. The output will be base.tar.gz inside the backup directory.

pgBackRest

pgBackRest is an open-source software developed to perform efficient backup on PostgreSQL databases that measure in tens of terabytes and greater. It supports per-file checksums, compression, partial/failed backup resume, high-performance parallel transfer, asynchronous archiving, tablespaces, expiration, full/differential/incremental, local/remote operation via SSH, hard-linking, restore, and more. The tool is written in Perl and does not depend on rsync or tar but instead performs its own deltas which give it maximum flexibility.

Starting from ClusterControl 1.9.0, pgBackRest can be configured as follows:

1. Master-only: Install on the current master but not on slaves. The backup repository (host to store the backup data) will be configured to be on the master. There will be no SSH configuration for PgBackRest.
2. All database nodes: Install on all database nodes. The backup repository will be created on the current master. The backup will be made by using a standby node. PgBackRest will use SSH for communication between hosts.
3. All database nodes and a dedicated repository host: Install on all PostgreSQL database nodes. The backup repository will be made on a specified host. The backup will be made by using a standby node. PgBackRest will use SSH for communication between hosts.

During the first attempt of making pgBackRest backup, ClusterControl will re-configure the node to install and configure pgBackRest. Take note that this operation requires a database restart and might introduce downtime to your database. A configuration file will be created at /etc/pgbackrest.conf and will be configured according to the version used and location of the PostgreSQL data. The pgBackRest default repository path is located at /var/lib/pgbackrest. Additionally, ClusterControl will configure the following lines inside postgresql.conf (which explains why it requires restart during the first run):

archive_mode = on   # enables archiving; off, on, or always # (change requires restart)
archive_command = 'pgbackrest --stanza=clustercontrol-stanza archive-push %p'   # command to use to archive a logfile segment

Full Backup

Full backup of pgBackRest copies the entire contents of the database cluster to the backup. The first backup of the database cluster is always a Full Backup. pgBackRest is always able to restore a full backup directly. The full backup does not depend on any files outside of the full backup for consistency.

Differential Backup

For differential backup, pgBackRest copies only those database cluster files that have changed since the last full backup. pgBackRest restores a differential backup by copying all of the files in the chosen differential backup and the appropriate unchanged files from the previous full backup. The advantage of a differential backup is that it requires less disk space than a full backup, however, the differential backup and the full backup must both be valid to restore the differential backup.

For example, if a full backup is taken on Sunday and the following daily differential backups are scheduled, the data that is backed up will be:

• Monday – data from Sunday to Monday
• Tuesday – data from Sunday to Tuesday
• Wednesday – data from Sunday to Wednesday
• Thursday – data from Sunday to Thursday

Incremental Backup

For incremental backup, pgBackRest copies only those database cluster files that have changed since the last backup (which can be another incremental backup, a differential backup, or a full backup). As an incremental backup only includes those files changed since the prior backup, they are generally much smaller than full or differential backups. As with the differential backup, the incremental backup depends on other backups to be valid to restore the incremental backup. Since the incremental backup includes only those files since the last backup, all prior incremental backups back to the prior differential, the prior differential backup, and the prior full backup must all be valid to perform a restore of the incremental backup. If no differential backup exists then all prior incremental backups back to the prior full backup, which must exist, and the full backup itself must be valid to restore the incremental backup.

For example, if a full backup is taken on Sunday and the following daily incremental backups are scheduled, the data that is backed up will be:

• Monday – data from Sunday to Monday
• Tuesday – data from Monday to Tuesday
• Wednesday – data from Tuesday to Wednesday
• Thursday – data from Wednesday to Thursday

Backup List

Provides a list of finished backup jobs. The backup status can be:

Depending on the backup method, every backup record should have the following functions:

Verify Backup

Performs backup verification job.

Restore Backup

Restores pg_dumpall or pg_basebackup backup file created by ClusterControl and listed in the Backup List. ClusterControl supports three restoration options:

• Restore on node.
• Restore and verify on standalone host.
• Create cluster from backup.

Restore on node

You can restore up to a certain incremental backup by clicking on the Restore button for the respective backup ID. The following steps will be performed:

For pgdump (online restore):

1. Copy backup files to the target server.
2. Checking disk space on the target server.
3. Restore the backup.
4. Follow the instruction in the ClusterControl → Activity → Jobs on how to rebuild the slaves.

For pg_basebackup (offline restore):

1. Stop the target node.
2. Back up the current PostgreSQL data directory.
3. Copy backup files to the target server.
4. Checking disk space on the target server.
5. Prepare and restore the backup.
6. Start the target node.
7. Follow the instruction in the ClusterControl → Activity → Jobs on how to rebuild the slaves.

Restore and verify on standalone host

Performs restoration on a standalone host and verify the backup. This requires a dedicated host that is not part of the cluster. ClusterControl will first deploy a PostgreSQL instance on the target host, start the service, stream the backup from the backup repository, and start performing the restoration. Once done, you can have an option either to shut down the server once restored or let it run so you can conduct further investigation on the server.

You can monitor the job progress under Activity → Jobs → Verify Backup where ClusterControl will report the restoration status (based on the exit code) at the end of the job.

Create cluster from backup

Creates a new cluster from the existing backup. A new PostgreSQL cluster will be created from the selected backup. The selected backup must be accessible from the nodes in the new cluster. The admin user password for this cluster must the same as the PostgreSQL admin password as included in the backup.

Choosing this option will open a new dialog where the selected backup will be used as a base dataset for the new cluster. The same deployment wizard for PostgreSQL will be shown to configure a new cluster. See PostgreSQL for reference.

Basically, ClusterControl performs the deployment job based on the following order:

1. Install the necessary software and dependencies on all PostgreSQL nodes.
2. Start the first node.
3. Stream and restore the backup on the first node (with auto-restart flag).
4. Configure and add the rest of the nodes.

A new PostgreSQL cluster will be listed under the ClusterControl cluster dashboard once the job completes.

Backup Encryption and Decryption

If the encryption option is enabled for a particular backup, ClusterControl will use OpenSSL to encrypt the backup using the AES-256 CBC algorithm. Encryption happens on the backup node. If you choose to store the backup on the controller node, the backup files are streamed over in encrypted format through socat or netcat.

If compression is enabled, the backup is first compressed and then encrypted resulting in smaller backup sizes. The encryption key will be generated automatically (if not exists) and stored inside the CMON configuration for the particular cluster under backup_encryption_key the option. This key is stored with base64 encoded and should be decoded first before using it as an argument to pass when decrypting the backup. The following command shows how to decode the key:

$ cat /etc/cmon.d/cmon_X.cnf | grep ^backup_encryption_key | cut -d"'" -f2 | base64 -d > keyfile.key

Where X is the cluster-ID. The above command will read the backup_encryption_key value and decode the value to a binary output. Thus, it is important to redirect the output to a file, as in the example, we redirected the output to keyfile.key. The key file which stores the actual encryption key can be used in the OpenSSL command to decrypt the backup, for example:

$ cat {BACKUPFILE}.aes256 | openssl enc -d -aes-256-cbc -pass file:/path/to/keyfile.key > backup_file.sql.gz

Or, you can pass the stdin to the respective restore command chain, for example:

$ cat {BACKUPFILE}.aes256 | openssl enc -d -aes-256-cbc -pass file:/path/to/keyfile.key | gunzip | psql -p5432 -f-

Settings

Manages the backup settings.

Backup Settings

PgBackRest Backup

If pgBackRest is configured, ClusterControl will list out all database nodes related to this feature. Otherwise, this page will show the “Install pgBackRest Backup” option.