Additional Examples v2.6

This section lists examples of the following BART operations.

Restoring a database cluster with tablespaces.
Restoring an incremental backup.
Managing backups.
Managing incremental backups.

Restoring a Database Cluster with Tablespaces

The following code sample illustrates taking a backup and restoring a database cluster on a remote host containing tablespaces. For detailed information regarding using tablespaces, see the EDB Backup and Recovery User Guide available at the EDB website.

On an Advanced Server database running on a remote host, the following tablespaces are created for use by two tables:

edb=# CREATE TABLESPACE tblspc_1 LOCATION '/mnt/tablespace_1';
CREATE TABLESPACE
edb=# CREATE TABLESPACE tblspc_2 LOCATION '/mnt/tablespace_2';
CREATE TABLESPACE
edb=# \db
             List of tablespaces
Name        | Owner           | Location
------------+-----------------+-------------------
pg_default  | enterprisedb    |
pg_global   | enterprisedb    |
tblspc_1    | enterprisedb    | /mnt/tablespace_1
tblspc_2    | enterprisedb    | /mnt/tablespace_2
(4 rows)

edb=# CREATE TABLE tbl_tblspc_1 (c1 TEXT) TABLESPACE tblspc_1;
CREATE TABLE
edb=# CREATE TABLE tbl_tblspc_2 (c1 TEXT) TABLESPACE tblspc_2;
CREATE TABLE
edb=# \d tbl_tblspc_1
Table "enterprisedb.tbl_tblspc_1"
Column | Type | Modifiers
-------+------+-----------
c1     | text |
Tablespace: "tblspc_1"

edb=# \d tbl_tblspc_2
Table "enterprisedb.tbl_tblspc_2"
Column | Type | Modifiers
-------+------+-----------
c1     | text |
Tablespace: "tblspc_2"

The following code sample shows the OIDs assigned to the tablespaces and the symbolic links to the tablespace directories:

-bash-4.1$ pwd
/opt/PostgresPlus/9.6AS/data/pg_tblspc
-bash-4.1$ ls -l
total 0
lrwxrwxrwx 1 enterprisedb enterprisedb 17 Nov 16 16:17 16587 ->/mnt/tablespace_1
lrwxrwxrwx 1 enterprisedb enterprisedb 17 Nov 16 16:17 16588 ->/mnt/tablespace_2

The BART configuration file contains the following settings. Note that the tablespace_path parameter does not have to be set at this point.

[BART]
bart_host= enterprisedb@192.168.2.22
backup_path = /opt/backup
pg_basebackup_path = /usr/edb/as11/bin/pg_basebackup
logfile = /tmp/bart.log
scanner_logfile = /tmp/bart_scanner.log

[ACCTG]
host = 192.168.2.24
port = 5444
user = repuser
cluster_owner = enterprisedb
remote_host = enterprisedb@192.168.2.24
tablespace_path =
description = "Accounting"

After the necessary configuration steps are performed to ensure BART manages the remote database server, a full backup is taken as shown in the following code sample:

-bash-4.1$ bart BACKUP -s acctg

INFO: creating backup for server 'acctg'
INFO: backup identifier: '1447709811516'
54521/54521 kB (100%), 3/3 tablespaces

INFO: backup completed successfully
INFO: backup checksum: 594f69fe7d26af991d4173d3823e174f of 16587.tar
INFO: backup checksum: 7a5507567729a21c98a15c948ff6c015 of base.tar
INFO: backup checksum: ae8c62604c409635c9d9e82b29cc0399 of 16588.tar
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1447709811516
BACKUP NAME: none
BACKUP LOCATION: /opt/backup/acctg/1447709811516
BACKUP SIZE: 53.25 MB
BACKUP FORMAT: tar
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 3
ChkSum File
594f69fe7d26af991d4173d3823e174f 16587.tar
7a5507567729a21c98a15c948ff6c015 base.tar
ae8c62604c409635c9d9e82b29cc0399 16588.tar

TABLESPACE(s): 2
Oid Name Location
16587 tblspc_1 /mnt/tablespace_1
16588 tblspc_2 /mnt/tablespace_2
START WAL LOCATION: 00000001000000000000000F
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2015-11-16 16:36:51 EST
STOP TIME: 2015-11-16 16:36:52 EST
TOTAL DURATION: 1 sec(s)

Note that in the output from the preceding example, checksums are generated for the tablespaces as well as the full backup.

Within the backup subdirectory 1447709811516 of the BART backup catalog, the tablespace data is stored with file names 16587.tar.gz and 16588.tar.gz as shown below:

-bash-4.1$ pwd
/opt/backup/acctg
-bash-4.1$ ls -l
total 8
drwx------ 2 enterprisedb enterprisedb 4096 Nov 16 16:36 1447709811516
drwx------ 2 enterprisedb enterprisedb 4096 Nov 16 16:43 archived_wals
-bash-4.1$ ls -l 1447709811516
total 54536
-rw-rw-r-- 1 enterprisedb enterprisedb 19968 Nov 16 16:36 16587.tar
-rw-rw-r-- 1 enterprisedb enterprisedb 19968 Nov 16 16:36 16588.tar
-rw-rw-r-- 1 enterprisedb enterprisedb 949 Nov 16 17:05 backupinfo
-rw-rw-r-- 1 enterprisedb enterprisedb 55792640 Nov 16 16:36 base.tar

When you are ready to restore the backup, in addition to creating the directory to which the main database cluster is to be restored, you must prepare the directories to which the tablespaces are to be restored.

On the remote host, directories /opt/restore_tblspc_1 and /opt/restore_tblspc_2 are created and assigned the proper ownership and permissions as shown by the following example. The main database cluster is to be restored to /opt/restore.

[root@localhost opt]# mkdir restore_tblspc_1
[root@localhost opt]# chown enterprisedb restore_tblspc_1
[root@localhost opt]# chgrp enterprisedb restore_tblspc_1
[root@localhost opt]# chmod 700 restore_tblspc_1

[root@localhost opt]# mkdir restore_tblspc_2
[root@localhost opt]# chown enterprisedb restore_tblspc_2
[root@localhost opt]# chgrp enterprisedb restore_tblspc_2
[root@localhost opt]# chmod 700 restore_tblspc_2
[root@localhost opt]# ls -l
total 20
drwxr-xr-x 3 root daemon 4096 Nov 10 15:38 PostgresPlus
drwx------ 2 enterprisedb enterprisedb 4096 Nov 16 17:40 restore
drwx------ 2 enterprisedb enterprisedb 4096 Nov 16 17:40
restore_tblspc_1
drwx------ 2 enterprisedb enterprisedb 4096 Nov 16 17:41
restore_tblspc_2
drwxr-xr-x. 2 root root 4096 Nov 22 2013 rh

Set the tablespace_path parameter in the BART configuration file to specify the tablespace directories. The remote host user and IP address are specified by the remote_host configuration parameter.

[ACCTG]

host = 192.168.2.24
port = 5444
user = repuser
cluster_owner = enterprisedb
remote_host = enterprisedb@192.168.2.24
tablespace_path =
16587=/opt/restore_tblspc_1;16588=/opt/restore_tblspc_2

description = "Accounting"

The following code sample demonstrates invoking the RESTORE subcommand:

-bash-4.1$ bart RESTORE -s acctg -i 1447709811516 -p /opt/restore
INFO: restoring backup '1447709811516' of server 'acctg'
INFO: restoring backup to enterprisedb@192.168.2.24:/opt/restore
INFO: base backup restored
INFO: archiving is disabled
INFO: tablespace(s) restored

The following code sample shows the restored full backup (including the restored tablespaces):

bash-4.1$ pwd
/opt
-bash-4.1$ ls -l restore
total 104
-rw------- 1 enterprisedb enterprisedb 206 Nov 16 16:36 backup_label.old
drwx------ 6 enterprisedb enterprisedb 4096 Nov 10 15:38 base
drwx------ 2 enterprisedb enterprisedb 4096 Nov 16 17:46 global
drwx------ 2 enterprisedb enterprisedb 4096 Nov 10 15:38 pg_clog
-rw------- 1 enterprisedb enterprisedb 4438 Nov 10 16:23 pg_hba.conf
-rw------- 1 enterprisedb enterprisedb 1636 Nov 10 15:38 pg_ident.conf
drwxr-xr-x 2 enterprisedb enterprisedb 4096 Nov 16 17:45 pg_log
drwx------ 4 enterprisedb enterprisedb 4096 Nov 10 15:38 pg_multixact
drwx------ 2 enterprisedb enterprisedb 4096 Nov 16 17:45 pg_notify
drwx------ 2 enterprisedb enterprisedb 4096 Nov 10 15:38 pg_serial
drwx------ 2 enterprisedb enterprisedb 4096 Nov 10 15:38 pg_snapshots
drwx------ 2 enterprisedb enterprisedb 4096 Nov 16 17:47 pg_stat
drwx------ 2 enterprisedb enterprisedb 4096 Nov 16 17:47 pg_stat_tmp
drwx------ 2 enterprisedb enterprisedb 4096 Nov 10 15:38 pg_subtrans
drwx------ 2 enterprisedb enterprisedb 4096 Nov 16 17:42 pg_tblspc
drwx------ 2 enterprisedb enterprisedb 4096 Nov 10 15:38 pg_twophase
-rw------- 1 enterprisedb enterprisedb 4 Nov 10 15:38 PG_VERSION
drwx------ 3 enterprisedb enterprisedb 4096 Nov 16 17:47 pg_xlog
-rw------- 1 enterprisedb enterprisedb 23906 Nov 16 17:42 postgresql.conf
-rw------- 1 enterprisedb enterprisedb 61 Nov 16 17:45 postmaster.opts
-bash-4.1$
-bash-4.1$ ls -l restore_tblspc_1
total 4
drwx------ 3 enterprisedb enterprisedb 4096 Nov 16 16:18
PG_9.6_201306121
-bash-4.1$ ls -l restore_tblspc_2
total 4
drwx------ 3 enterprisedb enterprisedb 4096 Nov 16 16:18
PG_9.6_201306121

The symbolic links in the pg_tblspc subdirectory point to the restored directory location:

bash-4.1$ pwd
/opt/restore/pg_tblspc
-bash-4.1$ ls -l
total 0
lrwxrwxrwx 1 enterprisedb enterprisedb 21 Nov 16 17:42 16587 ->
/opt/restore_tblspc_1
lrwxrwxrwx 1 enterprisedb enterprisedb 21 Nov 16 17:42 16588 ->
/opt/restore_tblspc_2

psql queries also show the restored tablespaces:

edb=# \db

             List of tablespaces
Name        | Owner        |   Location
------------+--------------+-----------------------
pg_default  | enterprisedb |
pg_global   | enterprisedb |
tblspc_1    | enterprisedb | /opt/restore_tblspc_1
tblspc_2    | enterprisedb | /opt/restore_tblspc_2

Restoring an Incremental Backup

Restoring an incremental backup may require additional setup steps depending upon the host on which the incremental backup is to be restored. For more information, see the EDB Backup and Recovery User Guide available at the EDB website.

This section provides an example of creating backup chains and then restoring an incremental backup.

Creating a Backup Chain

A backup chain is the set of backups consisting of a full backup and all of its successive incremental backups. Tracing back on the parent backups of all incremental backups in the chain eventually leads back to that single, full backup.

In the following example, the allow_incremental_backups parameter is set to enabled in the BART configuration file to permit incremental backups on the listed database server:

[BART]

bart_host= enterprisedb@192.168.2.27
backup_path = /opt/backup
pg_basebackup_path = /usr/edb/as11/bin/pg_basebackup
logfile = /tmp/bart.log
scanner_logfile = /tmp/bart_scanner.log

[ACCTG]

host = 127.0.0.1
port = 5445
user = enterprisedb
cluster_owner = enterprisedb
allow_incremental_backups = enabled
description = "Accounting"

After the database server has been started with WAL archiving enabled to the BART backup catalog, the WAL scanner is started:

-bash-4.2$ bart-scanner --daemon

First, a full backup is taken.

-bash-4.2$ bart BACKUP -s acctg --backup-name full_1
INFO: creating backup for server 'acctg'
INFO: backup identifier: '1490649204327'\
63364/63364 kB (100%), 1/1 tablespace
INFO: backup completed successfully
INFO: backup checksum: aae27d4a7c09dffc82f423221154db7e of base.tar
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1490649204327
BACKUP NAME: full_1
BACKUP PARENT: none
BACKUP LOCATION: /opt/backup/acctg/1490649204327
BACKUP SIZE: 61.88 MB
BACKUP FORMAT: tar
BACKUP TIMEZONE: US/Eastern
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 1
ChkSum File
aae27d4a7c09dffc82f423221154db7e base.tar
TABLESPACE(s): 0
START WAL LOCATION: 00000001000000000000000E
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2017-03-27 17:13:24 EDT
STOP TIME: 2017-03-27 17:13:25 EDT
TOTAL DURATION: 1 sec(s)

A series of incremental backups are taken. The first incremental backup specifies the full backup as the parent. Each successive incremental backup then uses the preceding incremental backup as its parent.

-bash-4.2$ bart BACKUP -s acctg -F p --parent full_1 --backup-name
incr_1-a
INFO: creating incremental backup for server 'acctg'
INFO: checking mbm files /opt/backup/acctg/archived_wals
INFO: new backup identifier generated 1490649255649
INFO: reading directory /opt/backup/acctg/archived_wals
INFO: all files processed
NOTICE: pg_stop_backup complete, all required WAL segments have been
archived
INFO: incremental backup completed successfully
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1490649255649
BACKUP NAME: incr_1-a
BACKUP PARENT: 1490649204327
BACKUP LOCATION: /opt/backup/acctg/1490649255649
BACKUP SIZE: 16.56 MB
BACKUP FORMAT: plain
BACKUP TIMEZONE: US/Eastern
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 0
TABLESPACE(s): 0
START WAL LOCATION: 000000010000000000000010
STOP WAL LOCATION: 000000010000000000000010
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2017-03-27 17:14:15 EDT
STOP TIME: 2017-03-27 17:14:16 EDT
TOTAL DURATION: 1 sec(s)
-bash-4.2$ bart BACKUP -s acctg -F p --parent incr_1-a --backup-name
incr_1-b
INFO: creating incremental backup for server 'acctg'
INFO: checking mbm files /opt/backup/acctg/archived_wals
INFO: new backup identifier generated 1490649336845
INFO: reading directory /opt/backup/acctg/archived_wals
INFO: all files processed
NOTICE: pg_stop_backup complete, all required WAL segments have been
archived
INFO: incremental backup completed successfully
.
.
.
-bash-4.2$ bart BACKUP -s acctg -F p --parent incr_1-b --backup-name
incr_1-c
INFO: creating incremental backup for server 'acctg'
INFO: checking mbm files /opt/backup/acctg/archived_wals
INFO: new backup identifier generated 1490649414316
INFO: reading directory /opt/backup/acctg/archived_wals
INFO: all files processed
NOTICE: pg_stop_backup complete, all required WAL segments have been
archived
INFO: incremental backup completed successfully
.
.
.

The following output of the SHOW-BACKUPS subcommand lists the backup chain, which are backups full_1, incr_1-a, incr_1-b, and incr_1-c.

-bash-4.2$ bart SHOW-BACKUPS -s acctg
SERVER NAME BACKUP ID      BACKUP NAME   BACKUP PARENT BACKUP TIME ...
acctg       1490649414316  incr_1-c      incr_1-b      2017-03-27 17:16:55 ...
acctg       1490649336845  incr_1-b      incr_1-a      2017-03-27 17:15:37 ...
acctg       1490649255649  incr_1-a      full_1        2017-03-27 17:14:16 ...
acctg       1490649204327  full_1        none          2017-03-27 17:13:25 ...

For the full backup full_1, the BACKUP PARENT field contains none. For each incremental backup, the BACKUP PARENT field contains the backup identifier or name of its parent backup.

A second backup chain is created in the same manner with the BACKUP subcommand. The following example shows the addition of the resulting, second backup chain consisting of full backup full_2 and incremental backups incr_2-a and incr_2-b.

-bash-4.2$ bart SHOW-BACKUPS -s acctg
SERVER NAME BACKUP ID     BACKUP NAME  BACKUP PARENT  BACKUP TIME ...
acctg       1490649605607 incr_2-b     incr_2-a       2017-03-27 17:20:06 ...
acctg       1490649587702 incr_2-a     full_2         2017-03-27 17:19:48 ...
acctg       1490649528633 full_2       none           2017-03-27 17:18:49 ...
acctg       1490649414316 incr_1-c     incr_1-b       2017-03-27 17:16:55 ...
acctg       1490649336845 incr_1-b     incr_1-a       2017-03-27 17:15:37 ...
acctg       1490649255649 incr_1-a     full_1         2017-03-27 17:14:16 ...
acctg       1490649204327 full_1       none           2017-03-27 17:13:25 ...

The following additional incremental backups starting with incr_1-b-1, which designates incr_1-b as the parent, results in the forking from that backup into a second line of backups in the chain consisting of full_1, incr_1-a, incr_1-b, incr_1-b-1, incr_1-b-2, and incr_1-b-3 as shown in the following list:

-bash-4.2$ bart SHOW-BACKUPS -s acctg
SERVER NAME  BACKUP ID     BACKUP NAME   BACKUP PARENT   BACKUP TIME         ...
acctg        1490649791430 incr_1-b-3    incr_1-b-2      2017-03-27 17:23:12 ...
acctg        1490649763929 incr_1-b-2    incr_1-b-1      2017-03-27 17:22:44 ...
acctg        1490649731672 incr_1-b-1    incr_1-b        2017-03-27 17:22:12 ...
acctg        1490649605607 incr_2-b      incr_2-a        2017-03-27 17:20:06 ...
acctg        1490649587702 incr_2-a      full_2          2017-03-27 17:19:48 ...
acctg        1490649528633 full_2        none            2017-03-27 17:18:49 ...
acctg        1490649414316 incr_1-c      incr_1-b        2017-03-27 17:16:55 ...
acctg        1490649336845 incr_1-b      incr_1-a        2017-03-27 17:15:37 ...
acctg        1490649255649 incr_1-a      full_1          2017-03-27 17:14:16 ...
acctg        1490649204327 full_1        none            2017-03-27 17:13:25 ...

Restoring an Incremental Backup

Restoring an incremental backup is done with the RESTORE subcommand in the same manner as for restoring a full backup. Specify the backup identifier or backup name of the incremental backup to be restored as shown in the following example.

-bash-4.2$ bart RESTORE -s acctg -p /opt/restore -i incr_1-b
INFO: restoring incremental backup 'incr_1-b' of server 'acctg'
INFO: base backup restored
INFO: archiving is disabled
INFO: permissions set on $PGDATA
INFO: incremental restore completed successfully

Restoring incremental backup incr_1-b as shown by the preceding example results in the restoration of full backup full_1, then incremental backups incr_1-a and finally, incr_1-b.

Managing Backups

This section illustrates evaluating, marking, and deleting backups using the MANAGE subcommand using a redundancy retention policy and a recovery window retention policy. For detailed information about the MANAGE subcommand, see the EDB Backup and Recovery User Guide available at the EDB website.

Using a Redundancy Retention Policy

The following code sample uses a redundancy retention policy to evaluate, mark, and delete backups as shown by the following server configuration:

[ACCTG]
host = 127.0.0.1
port = 5444
user = enterprisedb
archive_command = 'cp %p %a/%f'
retention_policy = 3 BACKUPS
description = "Accounting"

The following list is the set of backups. Note that the last backup in the list has been marked as keep.

-bash-4.1$ bart SHOW-BACKUPS -s acctg
SERVER NAME  BACKUP ID       BACKUP TIME               BACKUP SIZE   WAL(s) SIZE
WAL FILES    STATUS
acctg        1428768344061   2015-04-11 12:05:46 EDT   5.72 MB       48.00 MB
3            active
acctg        1428684537299   2015-04-10 12:49:00 EDT   5.72 MB       272.00 MB
17           active
acctg        1428589759899   2015-04-09 10:29:27 EDT   5.65 MB       96.00 MB
6            active
acctg        1428502049836   2015-04-08 10:07:30 EDT   55.25 MB      96.00 MB
6            active
acctg        1428422324880   2015-04-07 11:58:45 EDT   54.53 MB      32.00 MB
2            active
acctg        1428355371389   2015-04-06 17:22:53 EDT   5.71 MB       16.00 MB
1            keep

Invoke the MANAGE subcommand with the -n option to perform a dry run to observe which active backups would be changed to obsolete according to the retention policy as shown in the following code sample:

-bash-4.1$ bart MANAGE -s acctg -n
INFO: processing server 'acctg', backup '1428768344061'
INFO: processing server 'acctg', backup '1428684537299'
INFO: processing server 'acctg', backup '1428589759899'
INFO: processing server 'acctg', backup '1428502049836'
INFO: marking backup '1428502049836' as obsolete
INFO: 6 WAL file(s) marked obsolete
INFO: processing server 'acctg', backup '1428422324880'
INFO: marking backup '1428422324880' as obsolete
INFO: 2 WAL file(s) marked obsolete
INFO: processing server 'acctg', backup '1428355371389'

The dry run shows that backups 1428502049836 and 1428422324880 would be marked as obsolete.

Note

A dry run does not change the backup status. The two backups that would be considered obsolete are still marked as active:

-bash-4.1$ bart SHOW-BACKUPS -s acctg
SERVER NAME  BACKUP ID       BACKUP TIME               BACKUP SIZE   WAL(s) SIZE
WAL FILES    STATUS
acctg        1428768344061   2015-04-11 12:05:46 EDT   5.72 MB       48.00 MB
3            active
acctg        1428684537299   2015-04-10 12:49:00 EDT   5.72 MB       272.00 MB
17           active
acctg        1428589759899   2015-04-09 10:29:27 EDT   5.65 MB       96.00 MB
6            active
acctg        1428502049836   2015-04-08 10:07:30 EDT   55.25 MB      96.00 MB
6            active
acctg        1428422324880   2015-04-07 11:58:45 EDT   54.53 MB      32.00 MB
2            active
acctg        1428355371389   2015-04-06 17:22:53 EDT   5.71 MB       16.00 MB
1            keep

Invoke the MANAGE subcommand omitting the -n option to change and mark the status of the backups as obsolete:

-bash-4.1$ bart MANAGE -s acctg
INFO: processing server 'acctg', backup '1428768344061'
INFO: processing server 'acctg', backup '1428684537299'
INFO: processing server 'acctg', backup '1428589759899'
INFO: processing server 'acctg', backup '1428502049836'
INFO: marking backup '1428502049836' as obsolete
INFO: 6 WAL file(s) marked obsolete
INFO: processing server 'acctg', backup '1428422324880'
INFO: marking backup '1428422324880' as obsolete
INFO: 2 WAL file(s) marked obsolete
INFO: processing server 'acctg', backup '1428355371389'

The obsolete backups can be observed in a number of ways. Use the MANAGE subcommand with the -l option to list the obsolete backups:

-bash-4.1$ bart MANAGE -s acctg -l
INFO: 6 WAL file(s) will be removed
SERVER NAME: acctg
BACKUP ID: 1428502049836
BACKUP STATUS: obsolete
BACKUP TIME: 2015-04-08 10:07:30 EDT
BACKUP SIZE: 55.25 MB
WAL FILE(s): 6
WAL FILE: 000000010000000100000003
WAL FILE: 000000010000000100000002
WAL FILE: 000000010000000100000001
WAL FILE: 000000010000000100000000
WAL FILE: 0000000100000000000000E3
WAL FILE: 0000000100000000000000E2
INFO: 2 WAL file(s) will be removed
SERVER NAME: acctg
BACKUP ID: 1428422324880
BACKUP STATUS: obsolete
BACKUP TIME: 2015-04-07 11:58:45 EDT
BACKUP SIZE: 54.53 MB
WAL FILE(s): 2
WAL FILE: 0000000100000000000000E1
WAL FILE: 0000000100000000000000E0

The STATUS field of the SHOW-BACKUPS subcommand displays the current status:

-bash-4.1$ bart SHOW-BACKUPS -s acctg
SERVER NAME  BACKUP ID      BACKUP TIME              BACKUP SIZE   WAL(s) SIZE
WAL FILES    STATUS
acctg        1428768344061  2015-04-11 12:05:46 EDT  5.72 MB       48.00 MB
3            active
acctg        1428684537299  2015-04-10 12:49:00 EDT  5.72 MB       272.00 MB
17           active
acctg        1428589759899  2015-04-09 10:29:27 EDT  5.65 MB       96.00 MB
6            active
acctg        1428502049836  2015-04-08 10:07:30 EDT  55.25 MB      96.00 MB
6            obsolete
acctg        1428422324880  2015-04-07 11:58:45 EDT  54.53 MB      32.00 MB
2            obsolete
acctg        1428355371389  2015-04-06 17:22:53 EDT  5.71 MB       16.00 MB
1            keep

The details of an individual backup can be displayed using the SHOW-BACKUPS subcommand with the -t option. Note the status in the BACKUP STATUS field.

-bash-4.1$ bart SHOW-BACKUPS -s acctg -i 1428502049836 -t
SERVER NAME : acctg
BACKUP ID : 1428502049836
BACKUP NAME : none
BACKUP STATUS : obsolete
BACKUP TIME : 2015-04-08 10:07:30 EDT
BACKUP SIZE : 55.25 MB
WAL(S) SIZE : 96.00 MB
NO. OF WALS : 6
FIRST WAL FILE : 0000000100000000000000E2
CREATION TIME : 2015-04-08 10:07:30 EDT
LAST WAL FILE : 000000010000000100000003
CREATION TIME : 2015-04-09 10:25:46 EDT

Use the MANAGE subcommand with the -d option to physically delete the obsolete backups including the unneeded WAL files.

-bash-4.1$ bart MANAGE -s acctg -d
INFO: removing all obsolete backups of server 'acctg'
INFO: removing obsolete backup '1428502049836'
INFO: 6 WAL file(s) will be removed
INFO: removing WAL file '000000010000000100000003'
INFO: removing WAL file '000000010000000100000002'
INFO: removing WAL file '000000010000000100000001'
INFO: removing WAL file '000000010000000100000000'
INFO: removing WAL file '0000000100000000000000E3'
INFO: removing WAL file '0000000100000000000000E2'
INFO: removing obsolete backup '1428422324880'
INFO: 2 WAL file(s) will be removed
INFO: removing WAL file '0000000100000000000000E1'
INFO: removing WAL file '0000000100000000000000E0'

The SHOW-BACKUPS subcommand now displays the remaining backups marked as active or keep:

-bash-4.1$ bart SHOW-BACKUPS -s acctg
SERVER NAME    BACKUP ID         BACKUP TIME               BACKUP SIZE   WAL(s) SIZE
WAL FILES      STATUS
acctg          1428768344061     2015-04-11 12:05:46 EDT   5.72 MB       48.00 MB
3              active
acctg          1428684537299     2015-04-10 12:49:00 EDT   5.72 MB       272.00 MB
17             active
acctg          1428589759899     2015-04-09 10:29:27 EDT   5.65 MB       96.00 MB
6              active
acctg          1428355371389     2015-04-06 17:22:53 EDT   5.71 MB       16.00 MB
1              keep

Using a Recovery Window Retention Policy

This section illustrates the evaluation, marking, and deletion of backup using a recovery window retention policy. To use the recovery window retention policy, set the retention_policy parameter to the desired length of time for the recovery window.

This section provides examples of the following:

How to view the calculated recovery window.
How to evaluate, mark, and delete backup using a recovery window retention policy.

Viewing the Recovery Window

You can view the actual, calculated recovery window by invoking any of the following subcommands:

MANAGE subcommand in debug mode (along with the -n option).
SHOW-SERVERS subcommand.

Viewing the Recovery Window Using the Manage Subcommand

When invoking BART in debug mode with the MANAGE subcommand and the -n option, the length of the recovery window is calculated based on the retention_policy setting and the current date/time.

For example, using the following retention_policy settings:

[ACCTG]

host = 127.0.0.1
port = 5444
user = enterprisedb
archive_command = 'cp %p %a/%f'
retention_policy = 3 DAYS
backup-name = acctg_%year-%month-%dayT%hour:%minute:%second
description = "Accounting"

[DEV]

host = 127.0.0.1
port = 5445
user = enterprisedb
archive_command = 'cp %p %a/%f'
retention_policy = 3 WEEKS
description = "Development"

[HR]

host = 127.0.0.1
port = 5432
user = postgres
retention_policy = 3 MONTHS
description = "Human Resources"

If the MANAGE subcommand is invoked in debug mode along with the -n option on 2015-04-17, the following results are displayed:

-bash-4.1$ bart -d MANAGE -n
DEBUG: Server: acctg, Now: 2015-04-17 16:34:03 EDT, RetentionWindow:
259200 (secs) ==> 72 hour(s)
DEBUG: Server: dev, Now: 2015-04-17 16:34:03 EDT, RetentionWindow:
1814400 (secs) ==> 504 hour(s)
DEBUG: Server: hr, Now: 2015-04-17 16:34:03 EDT, RetentionWindow:
7776000 (secs) ==> 2160 hour(s)

For server acctg, 72 hours translates to a recovery window of 3 days.

For server dev, 504 hours translates to a recovery window of 21 days (3 weeks).

For server hr, 2160 hours translates to a recovery window of 90 days (3 months).

For a setting of <max_number> MONTHS, the calculated total number of days for the recovery window is dependent upon the actual number of days in the preceding months from the current date/time. Thus, <max_number> MONTHS is not always exactly equivalent to <max_number> x 30 DAYS. For example, if the current date/time is in the month of March, a 1-month recovery window would be equivalent to only 28 days because the preceding month is February. Thus, for a current date of March 31, a 1-month recovery window would start on March 3. However, the typical result is that the day of the month of the starting recovery window boundary will be the same day of the month of when the MANAGE subcommand is invoked.

Viewing the Recovery Window Using the Show-Servers Subcommand

This section provides an example of viewing the recovery window using the SHOW-SERVERS subcommand; the RETENTION POLICY field displays the start of the recovery window.

In the following code sample, the recovery window retention policy setting considers the backups taken within a 3-day recovery window as the active backups.

[ACCTG]
host = 127.0.0.1
port = 5444
user = enterprisedb
archive_command = 'cp %p %a/%f'
retention_policy = 3 DAYS
description = "Accounting"

The start of the 3-day recovery window displayed in the RETENTION POLICY field is 2015-04-07 14:57:36 EDT when the SHOW-SERVERS subcommand is invoked on 2015-04-10.

At this current point in time, backups taken on or after 2015-04-07 14:57:36 EDT would be considered active. Backups taken prior to 2015-04-07 14:57:36 EDT would be considered obsolete except for backups marked as keep.

-bash-4.1$ date
Fri Apr 10 14:57:33 EDT 2015
-bash-4.1$
-bash-4.1$ bart SHOW-SERVERS -s acctg
SERVER NAME        : acctg
HOST NAME          : 127.0.0.1
USER NAME          : enterprisedb
PORT               : 5444
REMOTE HOST        :
RETENTION POLICY   : 2015-04-07 14:57:36 EDT
DISK UTILIZATION   : 824.77 MB
NUMBER OF ARCHIVES : 37
ARCHIVE PATH       : /opt/backup/acctg/archived_wals
ARCHIVE COMMAND    : cp %p /opt/backup/acctg/archived_wals/%f
XLOG METHOD        : fetch
WAL COMPRESSION    : disabled
TABLESPACE PATH(s) :
DESCRIPTION : "Accounting"

In the following code sample, the recovery window retention policy setting considers the backups taken within a 3-week recovery window as the active backups.

[DEV]
host = 127.0.0.1
port = 5445
user = enterprisedb
archive_command = 'cp %p %a/%f'
retention_policy = 3 WEEKS
description = "Development"

The start of the 3-week recovery window displayed in the RETENTION POLICY field is 2015-03-20 14:59:42 EDT when the SHOW-SERVERS subcommand is invoked on 2015-04-10.

At this current point in time, backups taken on or after 2015-03-20 14:59:42 EDT would be considered active. Backups taken prior to 2015-03-20 14:59:42 EDT would be considered obsolete except for backups marked as keep.

-bash-4.1$ date
Fri Apr 10 14:59:39 EDT 2015
-bash-4.1$
-bash-4.1$ bart SHOW-SERVERS -s dev
SERVER NAME : dev
HOST NAME : 127.0.0.1
USER NAME : enterprisedb
PORT : 5445
REMOTE HOST :
RETENTION POLICY : 2015-03-20 14:59:42 EDT
DISK UTILIZATION : 434.53 MB
NUMBER OF ARCHIVES : 22
ARCHIVE PATH : /opt/backup/dev/archived_wals
ARCHIVE COMMAND : cp %p /opt/backup/dev/archived_wals/%f
XLOG METHOD : fetch
WAL COMPRESSION : disabled
TABLESPACE PATH(s) :
DESCRIPTION : "Development"

In the following code sample, the recovery window retention policy setting considers the backups taken within a 3-month recovery window as the active backups.

[HR]
host = 127.0.0.1
port = 5432
user = postgres
retention_policy = 3 MONTHS
description = "Human Resources"

The start of the 3-month recovery window displayed in the RETENTION POLICY field is 2015-01-10 14:04:23 EST when the SHOW-SERVERS subcommand is invoked on 2015-04-10.

At this current point in time, backups taken on or after 2015-01-10 14:04:23 EST would be considered active. Backups taken prior to 2015-01-10 14:04:23 EST would be considered obsolete, except for backups marked as keep.

-bash-4.1$ date
Fri Apr 10 15:04:19 EDT 2015
-bash-4.1$
-bash-4.1$ bart SHOW-SERVERS -s hr
SERVER NAME : hr
HOST NAME : 127.0.0.1
USER NAME : postgres
PORT : 5432
REMOTE HOST :
RETENTION POLICY : 2015-01-10 14:04:23 EST
DISK UTILIZATION : 480.76 MB
NUMBER OF ARCHIVES : 26
ARCHIVE PATH : /opt/backup/hr/archived_wals
ARCHIVE COMMAND : scp %p
enterprisedb@192.168.2.22:/opt/backup/hr/archived_wals/%f
XLOG METHOD : fetch
WAL COMPRESSION : disabled
TABLESPACE PATH(s) :
DESCRIPTION : "Human Resources"

Evaluating, Marking, and Deleting Backup Using a Recovery Window Retention Policy

The following code sample uses a recovery window retention policy to evaluate, mark, and delete backups as shown by the following server configuration:

[DEV]
host = 127.0.0.1
port = 5445
user = enterprisedb
archive_command = 'cp %p %a/%f'
retention_policy = 3 DAYS
description = "Development"

The following is the current set of backups. Note that the last backup in the list has been marked as keep.

-bash-4.1$ bart SHOW-BACKUPS -s dev
SERVER NAME    BACKUP ID       BACKUP TIME               BACKUP SIZE   WAL(s) SIZE
WAL FILES      STATUS
dev            1428933278236   2015-04-13 09:54:40 EDT   5.65 MB       16.00 MB
1              active
dev            1428862187757   2015-04-12 14:09:50 EDT   5.65 MB       32.00 MB
2              active
dev            1428768351638   2015-04-11 12:05:54 EDT   5.65 MB       32.00 MB
2              active
dev            1428684544008   2015-04-10 12:49:06 EDT   5.65 MB       224.00 MB
14             active
dev            1428590536488   2015-04-09 10:42:18 EDT   5.65 MB       48.00 MB
3              active
dev            1428502171990   2015-04-08 10:09:34 EDT   5.65 MB       80.00 MB
5              keep

The current date and time is 2015-04-13 16:46:35 EDT as shown below:

-bash-4.1$ date
Mon Apr 13 16:46:35 EDT 2015

Thus, a 3-day recovery window would evaluate backups prior to 2015-04-10 16:46:35 EDT as obsolete except for those marked as keep.

Invoke the MANAGE subcommand with the -n option to perform a dry run to observe which active backups would be changed to obsolete according to the retention policy.

-bash-4.1$ bart MANAGE -s dev -n
INFO: processing server 'dev', backup '1428933278236'
INFO: processing server 'dev', backup '1428862187757'
INFO: processing server 'dev', backup '1428768351638'
INFO: processing server 'dev', backup '1428684544008'
INFO: marking backup '1428684544008' as obsolete
INFO: 14 WAL file(s) marked obsolete
INFO: 1 Unused WAL file(s) present
INFO: processing server 'dev', backup '1428590536488'
INFO: marking backup '1428590536488' as obsolete
INFO: 3 WAL file(s) marked obsolete
INFO: 1 Unused WAL file(s) present
INFO: processing server 'dev', backup '1428502171990'

The dry run shows that backups 1428684544008 and 1428590536488 would be marked as obsolete.

Also note that a dry run does not change the backup status. The two backups that would be considered obsolete are still marked as active:

-bash-4.1$ bart SHOW-BACKUPS -s dev\
  SERVER NAME  BACKUP ID       BACKUP TIME               BACKUP SIZE   WAL(s) SIZE
  WAL FILES    STATUS
  dev          1428933278236   2015-04-13 09:54:40 EDT   5.65 MB       16.00 MB
   1           active
  dev          1428862187757   2015-04-12 14:09:50 EDT   5.65 MB       32.00 MB
  2            active
  dev          1428768351638   2015-04-11 12:05:54 EDT   5.65 MB       32.00 MB
  2            active
  dev          1428684544008   2015-04-10 12:49:06 EDT   5.65 MB       224.00 MB
  14           active
  dev          1428590536488   2015-04-09 10:42:18 EDT   5.65 MB       48.00 MB
  3            active
  dev          1428502171990   2015-04-08 10:09:34 EDT   5.65 MB       80.00 MB
  5            keep

Invoke the MANAGE subcommand omitting the -n option to change and mark the status of the backups as obsolete:

-bash-4.1$ bart MANAGE -s dev
INFO: processing server 'dev', backup '1428933278236'
INFO: processing server 'dev', backup '1428862187757'
INFO: processing server 'dev', backup '1428768351638'
INFO: processing server 'dev', backup '1428684544008'
INFO: marking backup '1428684544008' as obsolete
INFO: 14 WAL file(s) marked obsolete
INFO: 1 Unused WAL file(s) present
INFO: processing server 'dev', backup '1428590536488'
INFO: marking backup '1428590536488' as obsolete
INFO: 3 WAL file(s) marked obsolete
INFO: 1 Unused WAL file(s) present
INFO: processing server 'dev', backup '1428502171990'

The obsolete backups can be observed in a number of ways. Use the MANAGE subcommand with the -l option to list the obsolete backups:

-bash-4.1$ bart MANAGE -s dev -l
INFO: 14 WAL file(s) will be removed
INFO: 1 Unused WAL file(s) will be removed
SERVER NAME: dev
BACKUP ID: 1428684544008
BACKUP STATUS: obsolete
BACKUP TIME: 2015-04-10 12:49:06 EDT
BACKUP SIZE: 5.65 MB
WAL FILE(s): 14
UNUSED WAL FILE(s): 1
WAL FILE: 00000001000000000000002E
WAL FILE: 00000001000000000000002D
WAL FILE: 00000001000000000000002C
WAL FILE: 00000001000000000000002B
WAL FILE: 00000001000000000000002A
WAL FILE: 000000010000000000000029
WAL FILE: 000000010000000000000028
WAL FILE: 000000010000000000000027
WAL FILE: 000000010000000000000026
WAL FILE: 000000010000000000000025
WAL FILE: 000000010000000000000024
WAL FILE: 000000010000000000000023
WAL FILE: 000000010000000000000022
WAL FILE: 000000010000000000000021
UNUSED WAL FILE: 00000001000000000000000F.00000028
INFO: 3 WAL file(s) will be removed
INFO: 1 Unused WAL file(s) will be removed
SERVER NAME: dev
BACKUP ID: 1428590536488
BACKUP STATUS: obsolete
BACKUP TIME: 2015-04-09 10:42:18 EDT\
BACKUP SIZE: 5.65 MB
WAL FILE(s): 3
UNUSED WAL FILE(s): 1
WAL FILE: 000000010000000000000020
WAL FILE: 00000001000000000000001F
WAL FILE: 00000001000000000000001E
UNUSED WAL FILE: 00000001000000000000000F.00000028

The STATUS field of the SHOW-BACKUPS subcommand displays the current status:

-bash-4.1$ bart SHOW-BACKUPS -s dev
SERVER NAME  BACKUP ID       BACKUP TIME               BACKUP SIZE  WAL(s) SIZE
WAL FILES    STATUS
dev          1428933278236   2015-04-13 09:54:40 EDT   5.65 MB     16.00 MB
1            active
dev          1428862187757   2015-04-12 14:09:50 EDT   5.65 MB     32.00 MB
2            active
dev          1428768351638   2015-04-11 12:05:54 EDT   5.65 MB     32.00 MB
2            active
dev          1428684544008   2015-04-10 12:49:06 EDT   5.65 MB     224.00 MB
14           obsolete
dev          1428590536488   2015-04-09 10:42:18 EDT   5.65 MB     48.00 MB
3            obsolete
dev          1428502171990   2015-04-08 10:09:34 EDT   5.65 MB     80.00 MB
5            keep

The details of an individual backup can be displayed using the SHOW-BACKUPS subcommand with the -t option. Note the status in the BACKUP STATUS field.

-bash-4.1$ bart SHOW-BACKUPS -s dev -i 1428684544008 -t
SERVER NAME    : dev
BACKUP ID      : 1428684544008
BACKUP NAME    : none
BACKUP STATUS  : obsolete
BACKUP TIME    : 2015-04-10 12:49:06 EDT
BACKUP SIZE    : 5.65 MB
WAL(S) SIZE    : 224.00 MB
NO. OF WALS    : 14
FIRST WAL FILE : 000000010000000000000021
CREATION TIME  : 2015-04-10 12:49:06 EDT
LAST WAL FILE  : 00000001000000000000002E
CREATION TIME  : 2015-04-11 12:02:15 EDT

Use the MANAGE subcommand with the -d option to physically delete the obsolete backups including the unneeded WAL files.

-bash-4.1$ bart MANAGE -s dev -d
INFO: removing all obsolete backups of server 'dev'
INFO: removing obsolete backup '1428684544008'
INFO: 14 WAL file(s) will be removed
INFO: 1 Unused WAL file(s) will be removed
INFO: removing WAL file '00000001000000000000002E'
INFO: removing WAL file '00000001000000000000002D'
INFO: removing WAL file '00000001000000000000002C'
INFO: removing WAL file '00000001000000000000002B'
INFO: removing WAL file '00000001000000000000002A'
INFO: removing WAL file '000000010000000000000029'
INFO: removing WAL file '000000010000000000000028'
INFO: removing WAL file '000000010000000000000027'
INFO: removing WAL file '000000010000000000000026'
INFO: removing WAL file '000000010000000000000025'
INFO: removing WAL file '000000010000000000000024'
INFO: removing WAL file '000000010000000000000023'
INFO: removing WAL file '000000010000000000000022'
INFO: removing WAL file '000000010000000000000021'
INFO: removing (unused) WAL file '00000001000000000000000F.00000028'
INFO: removing obsolete backup '1428590536488'
INFO: 3 WAL file(s) will be removed
INFO: removing WAL file '000000010000000000000020'
INFO: removing WAL file '00000001000000000000001F'
INFO: removing WAL file '00000001000000000000001E'

The SHOW-BACKUPS subcommand now displays the remaining backups marked as active or keep:

-bash-4.1$ bart SHOW-BACKUPS -s dev
SERVER NAME  BACKUP ID      BACKUP TIME               BACKUP SIZE     WAL(s) SIZE
WAL FILES    STATUS
dev          1428933278236   2015-04-13 09:54:40 EDT   5.65 MB         16.00 MB
1            active
dev          1428862187757   2015-04-12 14:09:50 EDT   5.65 MB         32.00 MB
2            active
dev          1428768351638   2015-04-11 12:05:54 EDT   5.65 MB         32.00 MB
2            active
dev          1428502171990   2015-04-08 10:09:34 EDT   5.65 MB         80.00 MB
5            keep

Managing Incremental Backups

This section illustrates evaluating, marking, and deleting incremental backups using the MANAGE and DELETE subcommands utilizing redundancy retention policy and recovery window retention policy. For detailed information about the MANAGE and DELETE subcommands, as well as the redundancy retention and recovery window retention policy, see the EDB Backup and Recovery User Guide available at the EDB website.

Using a Redundancy Retention Policy provides an example of using the MANAGE and DELETE subcommands when a 3 backup redundancy retention policy is in effect.
Using a Recovery Window Retention Policy provides an example of using the MANAGE and DELETE subcommands when a 1-day recovery window retention policy is in effect.

Using a Redundancy Retention Policy

The following code sample uses the MANAGE and DELETE subcommands to evaluate, mark, and delete incremental backups when a 3 backup redundancy retention policy is in effect. The example uses the following server configuration:

[ACCTG]

host = 192.168.2.24
port = 5445
user = enterprisedb
cluster_owner = enterprisedb
remote_host = enterprisedb@192.168.2.24
allow_incremental_backups = enabled
retention_policy = 3 BACKUPS
description = "Accounting"

The example uses the following set of backups. In these code samples, some columns have been omitted from the SHOW-BACKUPS output to display the relevant information in a more observable manner.

-bash-4.2$ bart SHOW-BACKUPS -s acctg
SERVER NAME  BACKUP ID       ... BACKUP PARENT   BACKUP TIME             ... STATUS
acctg        1481749696905   ... 1481749673603   2016-12-14 16:08:17 EST ... active
acctg        1481749673603   ... 1481749651927   2016-12-14 16:07:53 EST ... active
acctg        1481749651927   ... 1481749619582   2016-12-14 16:07:32 EST ... active
acctg        1481749619582   ... none            2016-12-14 16:07:00 EST ... active

There is one backup chain. The first backup is the initial full backup.

Backup chain: 1481749619582 => 1481749651927 => 1481749673603 => 1481749696905

The MANAGE subcommand is invoked as shown by the following:

-bash-4.2$ bart MANAGE -s acctg
INFO: processing server 'acctg', backup '1481749619582'
INFO: 2 Unused WAL file(s) present
INFO: 4 Unused file(s) (WALs included) present, use 'MANAGE -l' for the
list

The following code sample shows the resulting status of the backups:

-bash-4.2$ bart SHOW-BACKUPS -s acctg
SERVER NAME    BACKUP ID     ... BACKUP PARENT   BACKUP TIME             ... STATUS
acctg          1481749696905 ... 1481749673603   2016-12-14 16:08:17 EST ... active
acctg          1481749673603 ... 1481749651927   2016-12-14 16:07:53 EST ... active
acctg          1481749651927 ... 1481749619582   2016-12-14 16:07:32 EST ... active
acctg          1481749619582 ... none            2016-12-14 16:07:00 EST ... active

The status remains active for all backups. Even though the total number of backups exceeds the 3 backup redundancy retention policy, it is only the total number of full backups that is used to determine if the redundancy retention policy has been exceeded. Additional full backups are added including a second backup chain. The following example shows the resulting list of backups:

-bash-4.2$ bart SHOW-BACKUPS -s acctg
SERVER NAME  BACKUP ID     ... BACKUP PARENT     BACKUP TIME             ... STATUS
acctg        1481750365397 ... none              2016-12-14 16:19:26 EST ... active
acctg        1481750098924 ... 1481749997807     2016-12-14 16:14:59 EST ... active
acctg        1481749997807 ... none              2016-12-14 16:13:18 EST ... active
acctg        1481749992003 ... none              2016-12-14 16:13:12 EST ... active
acctg        1481749696905 ... 1481749673603     2016-12-14 16:08:17 EST ... active
acctg        1481749673603 ... 1481749651927     2016-12-14 16:07:53 EST ... active
acctg        1481749651927 ... 1481749619582     2016-12-14 16:07:32 EST ... active
acctg        1481749619582 ... none              2016-12-14 16:07:00 EST ... active

Second backup chain: 1481749997807 => 1481750098924

The MANAGE subcommand is invoked, but now with a total of four active full backups.

-bash-4.2$ bart MANAGE -s acctg
INFO: processing server 'acctg', backup '1481750365397'
INFO: processing server 'acctg', backup '1481749997807'
INFO: processing server 'acctg', backup '1481749992003'
INFO: processing server 'acctg', backup '1481749619582'
INFO: marking backup '1481749619582' as obsolete
INFO: 3 incremental(s) of backup '1481749619582' will be marked obsolete
INFO: marking incremental backup '1481749696905' as obsolete
INFO: marking incremental backup '1481749673603' as obsolete
INFO: marking incremental backup '1481749651927' as obsolete
INFO: 4 WAL file(s) marked obsolete
INFO: 2 Unused WAL file(s) present
INFO: 4 Unused file(s) (WALs included) present, use 'MANAGE -l' for the
list

The oldest full backup and its chain of incremental backups are now marked as obsolete.

-bash-4.2$ bart SHOW-BACKUPS -s acctg
SERVER NAME  BACKUP ID        ... BACKUP PARENT   BACKUP TIME             ... STATUS
acctg        1481750365397    ... none            2016-12-14 16:19:26 EST ... active
acctg        1481750098924    ... 1481749997807   2016-12-14 16:14:59 EST ... active
acctg        1481749997807    ... none            2016-12-14 16:13:18 EST ... active
acctg        1481749992003    ... none            2016-12-14 16:13:12 EST ... active
acctg        1481749696905    ... 1481749673603   2016-12-14 16:08:17 EST ... obsolete
acctg        1481749673603    ... 1481749651927   2016-12-14 16:07:53 EST ... obsolete
acctg        1481749651927    ... 1481749619582   2016-12-14 16:07:32 EST ... obsolete
acctg        1481749619582    ... none            2016-12-14 16:07:00 EST ... obsolete

Invoking the MANAGE subcommand with the -d option deletes the entire obsolete backup chain.

-bash-4.2$ bart MANAGE -s acctg -d
INFO: removing all obsolete backups of server 'acctg'
INFO: removing obsolete backup '1481749619582'
INFO: 4 WAL file(s) will be removed
INFO: 3 incremental(s) of backup '1481749619582' will be removed
INFO: removing obsolete incremental backup '1481749696905'
INFO: removing obsolete incremental backup '1481749673603'
INFO: removing obsolete incremental backup '1481749651927'
INFO: removing WAL file '000000010000000100000000'
INFO: removing WAL file '0000000100000000000000FF'
INFO: removing WAL file '0000000100000000000000FE'
INFO: removing WAL file '0000000100000000000000FD'
INFO: 16 Unused file(s) will be removed
INFO: removing (unused) file '000000010000000100000004.00000028.backup'
.
.
.
INFO: removing (unused) file
'0000000100000000FB00002800000000FC000000.mbm'

The following code sample shows the remaining full backups and the second backup chain.

-bash-4.2$ bart SHOW-BACKUPS -s acctg
SERVER NAME  BACKUP ID       ... BACKUP PARENT   BACKUP TIME             ... STATUS
acctg        1481750365397   ... none            2016-12-14 16:19:26 EST ... active
acctg        1481750098924   ... 1481749997807   2016-12-14 16:14:59 EST ... active
acctg        1481749997807   ... none            2016-12-14 16:13:18 EST ... active
acctg        1481749992003   ... none            2016-12-14 16:13:12 EST ... active

Using a Recovery Window Retention Policy

The following example demonstrates using the MANAGE and DELETE subcommands to evaluate, mark, and delete incremental backups when a 1-day recovery window retention policy is in effect. The example uses the following server configuration:

[ACCTG]

host = 192.168.2.24
port = 5445
user = enterprisedb
cluster_owner = enterprisedb
remote_host = enterprisedb@192.168.2.24
allow_incremental_backups = enabled
retention_policy = 1 DAYS
description = "Accounting"

The example uses the following set of backups. In the samples, some columns have been omitted from the SHOW-BACKUPS output to display the relevant information in a more observable manner.

-bash-4.2$ bart SHOW-BACKUPS -s acctg
SERVER NAME BACKUP ID ... BACKUP PARENT BACKUP TIME ... STATUS
acctg 1481559303348 ... 1481554203288 2016-12-12 11:15:03 EST ... active
acctg 1481559014359 ... 1481554802918 2016-12-12 11:10:14 EST ... active
acctg 1481554802918 ... 1481553914533 2016-12-12 10:00:03 EST ... active
acctg 1481554203288 ... 1481553651165 2016-12-12 09:50:03 EST ... active
acctg 1481553914533 ... 1481553088053 2016-12-12 09:45:14 EST ... active
acctg 1481553651165 ... none 2016-12-12 09:40:51 EST ... active
acctg 1481553088053 ... 1481552078404 2016-12-12 09:31:28 EST ... active
acctg 1481552078404 ... none 2016-12-12 09:14:39 EST ... active

There are two backup chains. In each of the following chains, the first backup is the initial full backup.

First backup chain: 1481552078404 => 1481553088053 => 1481553914533 => 1481554802918 => 1481559014359

Second backup chain: 1481553651165 => 1481554203288 => 1481559303348

The MANAGE subcommand is invoked when the first full backup 1481552078404 falls out of the recovery window. When the MANAGE subcommand is invoked, it is 2016-12-13 09:20:03 EST, thus making the start of the 1-day recovery window at 2016-12-12 09:20:03 EST exactly one day earlier. This backup was taken at 2016-12-12 09:14:39 EST, which is about 5 ½ minutes before the start of the recovery window, thus making the backup obsolete.

-bash-4.2$ date
Tue Dec 13 09:20:03 EST 2016
-bash-4.2$ bart MANAGE -s acctg
INFO: processing server 'acctg', backup '1481553651165'
INFO: processing server 'acctg', backup '1481552078404'
INFO: marking backup '1481552078404' as obsolete
INFO: 4 incremental(s) of backup '1481552078404' will be marked obsolete
INFO: marking incremental backup '1481559014359' as obsolete
INFO: marking incremental backup '1481554802918' as obsolete
INFO: marking incremental backup '1481553914533' as obsolete
INFO: marking incremental backup '1481553088053' as obsolete
INFO: 7 WAL file(s) marked obsolete
INFO: 1 Unused WAL file(s) present
INFO: 2 Unused file(s) (WALs included) present, use 'MANAGE -l' for the list

The incremental backup date and time are within the recovery window since they were taken after the start of the recovery window of 2016-12-12 09:20:03 EST, but all backups in the chain are marked as obsolete.

-bash-4.2$ bart SHOW-BACKUPS -s acctg\
SERVER NAME      BACKUP ID     ... BACKUP PARENT       BACKUP TIME
... STATUS
acctg            1481559303348 ... 1481554203288       2016-12-12 11:15:03 EST
... active
acctg            1481559014359 ... 1481554802918       2016-12-12 11:10:14 EST
... obsolete
acctg            1481554802918 ... 1481553914533       2016-12-12 10:00:03 EST
... obsolete
acctg            1481554203288 ... 1481553651165       2016-12-12 09:50:03 EST
... active
acctg            1481553914533 ... 1481553088053       2016-12-12 09:45:14 EST
... obsolete
acctg            1481553651165 ... none                2016-12-12 09:40:51 EST
... active
acctg            1481553088053 ... 1481552078404       2016-12-12 09:31:28 EST
... obsolete
acctg            1481552078404 ... none                2016-12-12 09:14:39 EST
... obsolete

The following code sample shows how the entire backup chain is changed back to active status by invoking the MANAGE subcommand with the -c nokeep option on the full backup of the chain.

-bash-4.2$ bart MANAGE -s acctg -c nokeep -i 1481552078404
INFO: changing status of backup '1481552078404' of server 'acctg' from
'obsolete' to 'active'
INFO: status of 4 incremental(s) of backup '1481552078404' will be
changed
INFO: changing status of incremental backup '1481559014359' of server
'acctg' from 'obsolete' to 'active'
INFO: changing status of incremental backup '1481554802918' of server
'acctg' from 'obsolete' to 'active'
INFO: changing status of incremental backup '1481553914533' of server
'acctg' from 'obsolete' to 'active'
INFO: changing status of incremental backup '1481553088053' of server
'acctg' from 'obsolete' to 'active'
INFO: 7 WAL file(s) changed

The backup chain has now been reset to active status.

-bash-4.2$ bart SHOW-BACKUPS -s acctg
SERVER NAME    BACKUP ID        ... BACKUP PARENT   BACKUP TIME             ... STATUS
acctg          1481559303348    ... 1481554203288   2016-12-12 11:15:03 EST ... active
acctg          1481559014359    ... 1481554802918   2016-12-12 11:10:14 EST ... active
acctg          1481554802918    ... 1481553914533   2016-12-12 10:00:03 EST ... active
acctg          1481554203288    ... 1481553651165   2016-12-12 09:50:03 EST ... active
acctg          1481553914533    ... 1481553088053   2016-12-12 09:45:14 EST ... active
acctg          1481553651165    ... none            2016-12-12 09:40:51 EST ... active
acctg          1481553088053    ... 1481552078404   2016-12-12 09:31:28 EST ... active
acctg          1481552078404    ... none            2016-12-12 09:14:39 EST ... active

The following code sample shows usage of the DELETE subcommand on an incremental backup. The specified incremental backup 1481554802918 in the first backup chain as well as its successive incremental backup 1481559014359 are deleted.

-bash-4.2$ bart DELETE -s acctg -i 1481554802918
INFO: deleting backup '1481554802918' of server 'acctg'
INFO: deleting backup '1481554802918'
INFO: 1 incremental backup(s) will be deleted
INFO: deleting incremental backup '1481559014359'
INFO: WALs of deleted backup(s) will belong to prior backup(if any), or
will be marked unused
INFO: 2 Unused file(s) will be removed
INFO: removing (unused) file '0000000100000000000000BA'
INFO: removing (unused) file
'0000000100000000BA00002800000000BB000000.mbm'
INFO: backup(s) deleted

The results show that the backups 1481554802918 and 1481559014359 are no longer listed by the SHOW-BACKUPS subcommand.

-bash-4.2$ bart SHOW-BACKUPS -s acctg
SERVER NAME  BACKUP ID       ... BACKUP PARENT   BACKUP TIME               ... STATUS
acctg        1481559303348   ... 1481554203288   2016-12-12 11:15:03 EST   ... active
acctg        1481554203288   ... 1481553651165   2016-12-12 09:50:03 EST   ... active
acctg        1481553914533   ... 1481553088053   2016-12-12 09:45:14 EST   ... active
acctg        1481553651165   ... none            2016-12-12 09:40:51 EST   ... active
acctg        1481553088053   ... 1481552078404   2016-12-12 09:31:28 EST   ... active
acctg        1481552078404   ... none            2016-12-12 09:14:39 EST   ... active

The MANAGE subcommand is invoked again. This time both backup chains are marked obsolete since the full backups of both chains fall out of the start of the recovery window, which is now 2016-12-12 09:55:03 EST.

-bash-4.2$ date
Tue Dec 13 09:55:03 EST 2016
-bash-4.2$ bart MANAGE -s acctg
INFO: processing server 'acctg', backup '1481553651165'
INFO: marking backup '1481553651165' as obsolete
INFO: 2 incremental(s) of backup '1481553651165' will be marked obsolete
INFO: marking incremental backup '1481559303348' as obsolete
INFO: marking incremental backup '1481554203288' as obsolete
INFO: 38 WAL file(s) marked obsolete
INFO: processing server 'acctg', backup '1481552078404'
INFO: marking backup '1481552078404' as obsolete
INFO: 2 incremental(s) of backup '1481552078404' will be marked obsolete
INFO: marking incremental backup '1481553914533' as obsolete
INFO: marking incremental backup '1481553088053' as obsolete
INFO: 7 WAL file(s) marked obsolete

The following code sample shows both backup chains marked as obsolete.

-bash-4.2$ bart SHOW-BACKUPS -s acctg
SERVER NAME    BACKUP ID       ... BACKUP PARENT   BACKUP TIME
... STATUS
acctg          1481559303348   ... 1481554203288   2016-12-12 11:15:03 EST
... obsolete
acctg          1481554203288   ... 1481553651165   2016-12-12 09:50:03 EST
... obsolete
acctg          1481553914533   ... 1481553088053   2016-12-12 09:45:14 EST
... obsolete
acctg          1481553651165   ... none            2016-12-12 09:40:51 EST
... obsolete
acctg          1481553088053   ... 1481552078404   2016-12-12 09:31:28 EST
... obsolete
acctg          1481552078404   ... none            2016-12-12 09:14:39 EST
... obsolete

The following code sample demonstrates using the MANAGE subcommand with the -c keep option to keep a backup chain indefinitely. The MANAGE subcommand with the -c keep option must specify the backup identifier or backup name of the full backup of the chain, and not any incremental backup.

-bash-4.2$ bart MANAGE -s acctg -c keep -i 1481553651165
INFO: changing status of backup '1481553651165' of server 'acctg' from
'obsolete' to 'keep'
INFO: status of 2 incremental(s) of backup '1481553651165' will be
changed
INFO: changing status of incremental backup '1481559303348' of server
'acctg' from 'obsolete' to 'keep'
INFO: changing status of incremental backup '1481554203288' of server
'acctg' from 'obsolete' to 'keep'
INFO: 38 WAL file(s) changed

The full backup 1481553651165 and its successive incremental backups 1481554203288 and 1481559303348 have been changed to keep status:

-bash-4.2$ bart SHOW-BACKUPS -s acctg
SERVER NAME  BACKUP ID       ... BACKUP PARENT   BACKUP TIME
... STATUS
acctg        1481559303348   ... 1481554203288   2016-12-12 11:15:03 EST
... keep
acctg        1481554203288   ... 1481553651165   2016-12-12 09:50:03 EST
... keep
acctg        1481553914533   ... 1481553088053   2016-12-12 09:45:14 EST
... obsolete
acctg        1481553651165   ... none            2016-12-12 09:40:51 EST
... keep
acctg        1481553088053   ... 1481552078404   2016-12-12 09:31:28 EST
... obsolete
acctg        1481552078404   ... none            2016-12-12 09:14:39 EST
... obsolete

Finally, the MANAGE subcommand with the -d option is used to delete the obsolete backup chain:

-bash-4.2$ bart MANAGE -s acctg -d
INFO: removing all obsolete backups of server 'acctg'
INFO: removing obsolete backup '1481552078404'
INFO: 7 WAL file(s) will be removed
INFO: 2 incremental(s) of backup '1481552078404' will be removed
INFO: removing obsolete incremental backup '1481553914533'
INFO: removing obsolete incremental backup '1481553088053'
INFO: removing WAL file '0000000100000000000000C1'
INFO: removing WAL file '0000000100000000000000C0'
INFO: removing WAL file '0000000100000000000000BF'
INFO: removing WAL file '0000000100000000000000BE'
INFO: removing WAL file '0000000100000000000000BD'
INFO: removing WAL file '0000000100000000000000BC'
INFO: removing WAL file '0000000100000000000000BB'
INFO: 48 Unused file(s) will be removed
INFO: removing (unused) file '0000000100000000000000FA'
.
.
.
INFO: removing (unused) file '0000000100000000000000BB.00000028.backup'

Only the backup chain with the keep status remains as shown below:

-bash-4.2$ bart SHOW-BACKUPS -s acctg
SERVER NAME    BACKUP ID     ... BACKUP PARENT   BACKUP TIME
... STATUS
acctg          1481559303348 ... 1481554203288   2016-12-12 11:15:03 EST
... keep
acctg          1481554203288 ... 1481553651165   2016-12-12 09:50:03 EST
... keep
acctg          1481553651165 ... none            2016-12-12 09:40:51 EST
... keep

← Prev

BART Subcommand Syntax and Examples

Sample BART System with Local and Remote Database Servers

Additional Examples v2.6

Restoring a Database Cluster with Tablespaces

Restoring an Incremental Backup

Managing Backups

Using a Redundancy Retention Policy

Note

Using a Recovery Window Retention Policy

Viewing the Recovery Window

Viewing the Recovery Window Using the Manage Subcommand

Viewing the Recovery Window Using the Show-Servers Subcommand

Evaluating, Marking, and Deleting Backup Using a Recovery Window Retention Policy

Managing Incremental Backups

Using a Redundancy Retention Policy

Using a Recovery Window Retention Policy

← Prev

Next →