EDB Backup and Recovery Tool

EDB Backup and Recovery Tool

Backup and recovery for Postgres-based digital business solutions

EDB Backup and Recovery Tool (BART) is a key component of an enterprise-level Postgres-based data management strategy.  BART implements retention policies and point-in-time recovery requirements for large-scale Postgres deployments. Bart 1.2 (beta) provides block-level incremental backup.

Overview

Architecture

EDB Backup and Recovery Tool Architecture

Setting retention policies

Setting retention policies

The EnterpriseDB Backup and Recovery Tool (BART) allows the definition of retention policies at the level of the BART server - these apply to all database servers being backed up - or at the level of individual Postgres servers. BART stores the archive in a directory of your choosing.  As time goes by, archives tend to accumulate within the target directory tree. BART can expire old backups according to a set of rules that you define - each set of rules is called a retention policy.  You may define a single retention policy that applies to each database server managed by BART, or you can override the global retention policy on a server-by-server basis (for example, you may retain 10 backups for most of your database servers but choose to retain 2 weeks worth of backups for a few database servers).

 

To define a retention policy, modify the retention_policy option within the bart.cfg configuration file.  For example, to configure BART to retain the 10 most recent backups:

 

...

retention_policy = 10 BACKUPS

...

 

You can also configure BART to retain backups for a given time period, for example:

 

...

retention_policy = 2 WEEKS

...

 

To define the default retention policy (that is, the policy that applies to every database server, unless overridden by a given server),  specify the retention_policy option in the global section of the bart.cfg file (the section titled [BART]):

 

[BART]

bart-host          = bartuser@192.168.2.22

backup_path        = /opt/backup

...

retention_policy   = 10 BACKUPS

...

 

To define the retention policy for a specific server, add the retention_policy option in the bart.cfg section corresponding to that server, for example:

 

[WEBSERVER]

host             = 192.168.2.42

port             = 5444

...

retention_policy = 2 weeks

...

For more information about retention policies, see the EDB Backup and Recovery Utility Guide.

 

Using the command line interface

Using the command line interface

BART provides a command line interface (CLI). To run BART, use the bart command.  This command comes in three forms:

 

bart --version

bart --help

bart [--config-path path] [subcommand] args

 

The first form displays the BART version number.

The first form displays a usage summary that describes the complete syntax for the bart command:

$ bart --help

bart: backup and recovery tool

Usage:

bart [OPTIONS] [SUB-COMMAND] <args>

Options:

 -h, --help       Show this help message and exit

 -v, --version    Show version information and exit

 -d, --debug      Generate debugging output

 -c, --config-path

                  Path to the configuration file

Sub-Command:

 INIT             Initializes the backup catalog. Defaults

                  to 'all', specify '-s <server>' to limit

                  to specified server(s)

 BACKUP           Backup the database server.

                  Requires '-s <server>' to limit to

                  specified server(s) or all to backup all

 RESTORE          Restore the database backup.

                  Requires '-s <server>' and '-i <id>'

 

 DELETE           Delete a backup. Requires '-s <server>'

                  and '-i <backupid>'

 SHOW-BACKUPS     Shows the list of backups. Defaults to

                  'all', specify '-s <server>' to limit to

                  specified server(s)

 SHOW-SERVERS     Show server information. Defaults to

                  'all', specify '-s <server>' to limit to

                  specified server(s)

 VERIFY-CHKSUM    Verifies the backup checksum. Defaults to

                   'all', specify '-s <server>' to limit to

                  specified server(s)

 MANAGE           Enforce compression/retention policy on

                  backups. Defaults to 'all', specify

                  '-s <server>' to limit to specified

                  server(s)

See 'bart [SUB-COMMAND] --help' to read more about a specific command.

The third form is used to initialize the BART catalog, backup a database server, restore from a backup, manage a BART catalog, or view the content of the BART catalog.

When you use the third form, you specify a sub-command that determines the type of operation that you want BART to perform.  For example, to backup a server named seattle, use the following command:

$ bart BACKUP -s seattle

The -s server-name option specifies which database server you want to back up: the server-name must match a server specified in the bart.cfg configuration file.

To view the complete syntax for the BACKUP sub-command (or for any sub-command) include the --help option after the sub-command:

$ bart BACKUP --help

bart: backup and recovery tool

Usage:

bart BACKUP [OPTION]...

Options:

 -h, --help           Show this help message and exit

 -s, --server         Name of the server or 'all' to

                      specify all servers

 -F, --format=p|t     Backup output format (tar (default)

                      or plain)

 -i, --incremental    Incremental backup

 -z, --gzip           Enables gzip compression of tar files

 -c, --compress-level Specifies the compression level

                      (1 through 9, 9 = best compression)

 --backup-name        Specify a friendly name for the

                      current backup

 --parent             Specify parent backup for incremental

                      Backup
For a complete list of BART sub-commands, see Basic BART Subcommand Usage in the EnterpriseDB Backup and Recovery Guide.

Point in time recovery

Point in time recovery

PostgreSQL and EDB Advanced server maintain the write ahead logs  (WALs) in the pg_xlog subdirectory of the DATA folder. The WAL log records every change that is made to the data files.

In order to support recovery to the current state or to a point in time (PITR), BART requires archiving to be enabled on the backup database server and archive_command set to send the WAL files to the BARTHOST. Using the BART INIT command the user can automatically set the archive_command for the backup database server. On the database server the archive_command is set to send the WAL files from pg_xlog directory to BARTHOST.

In order to perform point in time recovery, the user needs to specify the point in time to which they want to restore. Using the BART restore command the user can specify on the following points for performing PITR :

  • Target Timestamp (Restore the system up to given target timestamp)
  • Target Transaction ID (Restore the system up to target transaction ID)
  • Target Timeline (Restore the system up to target timeline)

The BART restore help menu displayed below shows that user can provide -g, -x or -t switch in order to perform PITR using one of the above options.

$ bart restore --help

./bart: backup and recovery tool

Usage:

./bart RESTORE [OPTION]...

Options:

-h, --help           Show this help message and exit

-s, --server         Name of the server to restore

-i, --backupid       ID or Name of the backup to restore

-r, --remote-host    Host on which backup will be restored (in format USER@HOST)

-p, --restore-path   Path where data will be restored

-g, --target-timestamp

                     Target timestamp of restore

-x, --target-xid     Target XID of restore

-t, --target-tli     Target timeline ID of restore

-c, --copy-wals      Copy WALs to the 'restore-path' (instead of stream)

In order to explain the purpose of PITR, you can consider an example when a user takes a full backup on Sunday and on Monday at 10am he accidentally runs a drop table statement or truncate table statement on some critical data. The user would like to restore the database server to a point before 10am on Monday in order to not loose the critical table data.

This can be done using the following  BART restore command :

$ bart -c ~/bart.cfg restore -s ppas-9.5 -i 1473478624481 -p /home/ec2-user/restore/ppas-9.5 -g ‘26-SEP-16 09:30:00’

BART automatically creates the recovery.conf that is required in order to perform PITR, a sample recovery.conf file is given below :

restore_command = 'cp /home/ec2-user/backup/ppas-9.5/archived_wals/%f %p'

recovery_target_time = '26-SEP-16 09:30:00'

BART restore process restores the full backup, replays the WAL file and stops at the given point in time or transaction ID or timeline.

    Incremental backups

    Incremental backups

    In version 1.2, targeted to be available in Q4’2016 or Q1’2017, BART will provide block-level incremental backup capabilities.

    Resources