ClusterControl Documentation
Use the menu below or the search below to learn everything you need to know about ClusterControl

5.5.7. Backup

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

5.5.7.1. Create Backup

Creates or schedules a PostgreSQL backup.

5.5.7.1.1. Create Backup

You can choose to create a full backup using pg_dumpall or pg_basebackup. Backups can be stored on the database host that is performing the backup, or the files can be streamed to the ClusterControl host. The backup created by this feature will be a full backup.

Note

The backup created by ClusterControl for PostgreSQL is always a full compressed backup.

  • Backup Method
    • pgdump - Full compressed backup using pg_dumpall. See pg_dumpall section.
    • pg_basebackup - Full compressed backup using pg_basebackup. See pg_basebackup section.
  • Backup Host
    • The target database host.
  • Storage Location
    • Store on Node - Stores the backup inside corresponding database node.
    • Store on Controller - Stores the backup inside ClusterControl node. This requires socat or netcat on source and destination host. By default, ClusterControl uses port 9999 to stream the backup created on the database node to ClusterControl node.
  • Storage Directory
    • You can opt to use another backup directory as you wish. If you leave this field blank, ClusterControl will use the default backup directory specified in the Settings > Default backup directory.
  • Backup Subdirectory
    • Set the name of the backup subdirectory. This string may hold standard %X field separators, the %06I for example will be replaced by the numerical ID of the backup in 6 field wide format that uses ‘0’ as leading fill characters. Default value: BACKUP-%I.
    Variable Description
    B The date and time when the backup creation was beginning.
    H The name of the backup host, the host that created the backup.
    i The numerical ID of the cluster.
    I The numerical ID of the backup.
    J The numerical ID of the job that created the backup.
    M The backup method (e.g. “pg_dumpall”).
    O The name of the user who initiated the backup job.
    S The name of the storage host, the host that stores the backup files.
    % The percent sign itself. Use two percent signs, %% the same way the standard printf() function interprets it as one percent sign.
  • Netcat Port
    • Specify the port number that will be used by ClusterControl to stream backup created on the database node. This port must be opened on both source and destination hosts. Only available if you choose Store on Controller in Storage Location.
  • Use Compression
    • Yes - Tells the chosen backup method to compress all output data, including the transaction log file and meta data files.
    • No - Do not use compression for the backup.
  • Compression Level
    • Specify the compression level for the backup. This is according to gzip compression level. 1 is the fastest compression (least compression) and 9 indicates the slowest compression (best compression).
  • Upload Backup to the cloud
    • Uploads the backup to the cloud storage. The upload process happens right after the backup is successfully created. This feature requires you to set up the cloud credentials first. See Cloud Providers.
  • Enable Encryption
    • Encrypts the generated backup. Backup is encrypted at rest using AES-256 CBC algorithm, where the encryption key will be created automatically and stored inside CMON configuration file for this cluster. See Backup Encryption and Decryption.
  • Retention
    • How long ClusterControl should keep this backup once successfully created. You can set a custom period in days or keep it forever. Otherwise, ClusterControl will use the default retention period.

5.5.7.1.2. Schedule Backup

Creates backup schedules of the database.

  • Schedule
    • Simple - Default scheduling option. This translates to the same output as the Advanced editor.
    • Advanced - Opens a cron-like editor. Formatting is similar to the standard cron.

Note

The backup time is in UTC time zone of the ClusterControl node.

  • Backup Method
    • pgdump - Full compressed backup using pg_dumpall. See pg_dumpall section.
    • pg_basebackup - Full compressed backup using pg_basebackup. See pg_basebackup section.
  • Backup Host
    • The target database host.
  • Storage Location
    • Store on Node - Stores the backup inside corresponding database node.
    • Store on Controller - Stores the backup inside ClusterControl node. This requires socat or netcat on source and destination host. By default, ClusterControl uses port 9999 to stream the backup created on the database node to ClusterControl node.
  • Storage Directory
    • You can opt to use another backup directory as you wish. If you leave this field blank, ClusterControl will use the default backup directory specified in the Settings > Default backup directory.
  • Backup Subdirectory
    • Set the name of the backup subdirectory. This string may hold standard %X field separators, the %06I for example will be replaced by the numerical ID of the backup in 6 field wide format that uses ‘0’ as leading fill characters. Default value: BACKUP-%I.
    Variable Description
    B The date and time when the backup creation was beginning.
    H The name of the backup host, the host that created the backup.
    i The numerical ID of the cluster.
    I The numerical ID of the backup.
    J The numerical ID of the job that created the backup.
    M The backup method (e.g. “pg_dumpall”).
    O The name of the user who initiated the backup job.
    S The name of the storage host, the host that stores the backup files.
    % The percent sign itself. Use two percent signs, %% the same way the standard printf() function interprets it as one percent sign.
  • Upload Backup to the cloud
    • Upload the backup to the cloud storage. The upload process happens right after the backup is successfully created. This feature requires you to set up the cloud credentials first. See Cloud Providers.
  • Netcat Port
    • Specify the port number that will be used by ClusterControl to stream backup created on the database node. This port must be opened on both source and destination hosts. Only available if you choose Store on Controller in Storage Location.
  • Use Compression
    • Yes - Tells the chosen backup method to compress all output data, including the transaction log file and meta data files.
    • No - Do not use compression for the backup.
  • Compression Level
    • Specify the compression level for the backup. This is according to gzip compression level. 1 is the fastest compression (least compression) and 9 indicates the slowest compression (best compression).
  • Failover backup if node is down
    • Yes - Backup will be run on any available node (or selected node based on the Backup Failover Host) if the target database node is down. If failover is enabled and the selected node is not online, the backup job elects an online node to create the backup. This ensures that a backup will be created even if the selected node is not available. If the scheduled backup is an incremental backup and a full backup does not exist on the new elected node, then a full backup will be created.
    • No - Backup will not run if the target database node is down.
  • Failover Host
    • List of database host to failover in case the target node is down during the scheduled backup.
  • Verify Backup
    • Verify the backup once successfully created. See Verify Backup.
  • Enable Encryption
    • Encrypts the generated backup. Backup is encrypted at rest using AES-256 CBC algorithm, where the encryption key will be created automatically and stored inside CMON configuration file for this cluster. See Backup Encryption and Decryption.
  • Retention
    • How long ClusterControl should keep this backup once successfully created. You can set a custom period in days or keep it forever. Otherwise, ClusterControl will use the default retention period.

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

5.5.7.3. Backup Method

This section explains backup method used by ClusterControl.

Note

Backup process performed by ClusterControl is running as a background thread (RUNNING3) which doesn’t block any other non-backup jobs in queue. If the backup job takes hours to complete, other non-backup jobs can still run simultaneously via the main thread (RUNNING). You can see the job progress at ClusterControl > Logs > Jobs.

5.5.7.3.1. pg_dumpall

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

5.5.7.3.2. 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 servers. 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.

5.5.7.4. Backup List

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

Status Description
Completed Backup was successfully created and stored in the chosen node.
Running Backup process is running.
Failed Backup was failed.
  • Restore
  • Log
    • Shows the output once ClusterControl executes the backup job.
  • Delete
    • Removes the backup set.
  • Upload
    • Manually upload the created backup to cloud storage. This will open “Upload Backup” wizard.

5.5.7.5. Verify Backup

Performs backup verification job.

  • Restore backup on
    • Specify the FQDN, hostname or IP address of the standalone host. The host must not be part of the cluster.
  • Install Database Software
    • A new PostgreSQL server will be installed and setup if this is enabled. If there is an existing PostgreSQL server installed or running, it will be stopped and removed before ClusterControl performs the installation. If unchecked, ClusterControl will not touch the existing installation and the existing PostgreSQL server (must be running) on the target host will be used.
  • Disable Firewall?
    • Check the box to disable firewall (recommended).
  • Disable SELinux/AppArmor?
    • Check the box to disable SELinux (RHEL/CentOS) or AppArmor (Ubuntu).
  • Shutdown the server after the backup have been completed
    • Select “Yes” if you want ClusterControl to shutdown the server after restoration completes. Select “No” if you want to let it run after restoration completes and the node will be listed under Nodes. You are then responsible for removing the PostgreSQL server.
  • Verify the backup after N hours after completion
    • Performs the backup verification after the specified hours once the backup is completed.

5.5.7.6. Restore Backup

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

5.5.7.6.1. 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. Backup 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 backup on
    • The backup will be restored to the selected server.
  • Tmp Dir
    • Temporary storage for ClusterControl to prepare the big. It must be as big as the expected PostgreSQL data directory.
  • Point In Time Recovery (PITR)
    • This option is only available if you want to restore a PITR-compatible backup (with WAL archiving enabled). If toggled, you will have to specify the time (folloing the server’s timezone) to recover the data up to that point. The restoration time must be in ‘YYYY-MM-DD HH:MM:SS’ format. E.g: “2018-08-22 21:00:00”.

Attention

The data directory must have enough space to accommodate the restored backup.

5.5.7.6.2. Restore and verify on standalone host

Performs restoration on a standalone host and verify the backup. This requires a dedicated host which 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 shutdown 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.

  • Restore backup on
    • Specify the FQDN, hostname or IP address of the standalone host. The host must not be part of the cluster.
  • Install Software
    • A new PostgreSQL server will be installed and setup if this is enabled. If there is an existing PostgreSQL server installed or running, it will be stopped and removed before ClusterControl performs the installation. If unchecked, ClusterControl will not touch the existing installation and the existing PostgreSQL server (must be running) on the target host will be used.
  • Disable Firewall
    • Check the box to disable firewall (recommended).
  • Shutdown the server after the backup have been restored
    • Select “Yes” if you want ClusterControl to shutdown the server after restoration completes. Select “No” if you want to let it run after restoration completes and the node will be listed under Nodes. You are then responsible for removing the PostgreSQL server.

5.5.7.6.3. Create cluster from backup

Note

This feature is introduced in version 1.7.1, specifically for Galera Cluster and PostgreSQL clusters only.

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 necessary softwares and dependencies on all PostgreSQL nodes.
  2. Start the first node.
  3. Stream and restore 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 ClusterControl cluster dashboard once the job completes.

5.5.7.7. Backup Encryption and Decryption

If encryption option is enabled for a particular backup, ClusterControl will uses OpenSSL to encrypt the backup using 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 CMON configuration for the particular cluster under backup_encryption_key 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-

5.5.7.8. Settings

Manages the backup settings.

  • Default Backup Directory
    • Default path for the backup directory. ClusterControl will create the backup directory on the destination host if doesn’t exist.
  • Default Backup Subdirectory
    • Set the name of the backup subdirectory. This string may hold standard %X field separators, the %06I for example will be replaced by the numerical ID of the backup in 6 field wide format that uses ‘0’ as leading fill characters. Default value: BACKUP-%I.
    Variable Description
    B The date and time when the backup creation was beginning.
    H The name of the backup host, the host that created the backup.
    i The numerical ID of the cluster.
    I The numerical ID of the backup.
    J The numerical ID of the job that created the backup.
    M The backup method (e.g. “pg_dumpall”).
    O The name of the user who initiated the backup job.
    S The name of the storage host, the host that stores the backup files.
    % The percent sign itself. Use two percent signs, %% the same way the standard printf() function interprets it as one percent sign.
  • Backup retention period (days)
    • The number of days ClusterControl keeps the existing backups. Backups older than the value defined here will be deleted. You can also customize the retention period per backup (default, custom or keep forever) under Backup Retention when creating or scheduling the backup.
  • Backup cloud retention period (days)
    • The number of days ClusterControl keeps the uploaded backups in the cloud. Backups older than the value defined here will be deleted.
  • Enable Point in time recovery (WAL Archiving)
    • Enables WAL archiving. If it is enabled and you click “Save”, the following steps will be performed on the master node:
    1. Enable the WAL archiving on the master node.
    2. Master node will be restarted.
  • Compress WAL Archive
    • Option to compress the WAL archives.
  • PITR Retention Hours
    • This setting specifies how long WAL files are kept. Default is 0 which means old WAL files will be kept forever.