Data retention has become a greater challenge for database administrators as volumes have exploded with new digital applications and data from mobile, web, and machine sources. The sheer volume of data can limit the number of backups DBAs can perform, increasing the importance of creating defined data retention policies in the database platform.
Enterprises typically take backups (full or incremental) on a daily basis (many take them more frequently) causing the number of backups for the database servers to grow rapidly. Retention policies are introduced to mark the older backups obsolete so they can be removed from the backup server and free up storage.
To give DBAs greater precision in controlling backups, EnterpriseDB® (EDB™) has created multiple important and differentiating Retention Management features for its EDB Postgres™ Backup and Recovery Tool. EDB Postgres Backup and Recovery Tool implements retention policies and point-in-time recovery requirements for large-scale Postgres deployments and is part of the Management Suite of tools that is integrated into the EDB Postgres™ Platform.
What follows is an exploration of how retention management is achieved for incremental backups as well as the different modes supported for retention management in the context of full backup. It’s important to note the same mechanisms will apply to incremental backup with minor differences, as explained toward the end of the blog.
First, to understand how EDB Postgres Backup and Recovery Tool addresses block-level incremental backup and how the feature works, read my blog, Data Backup Made Easy. The block-level incremental level feature provides users the ability to back up only the blocks that have changed since the last full or incremental backup, making the backup process super fast and much smaller. Further insight may be gained by watching the following videos: EDB Postgres Backup and Recovery Took Incremental Backup Part 1, Intro and Part 2, Demo.
The Retention Management feature in EDB Postgres Backup and Recovery Tool provides the ability to define policies for database server(s) in order to retain the backups for the given server based on the specified retention policy. A retention policy can also be configured globally, in which case the policy applies to all of the database servers configured in the EDB Postgres Backup and Recovery Tool configuration file. (More details on that below.)
EDB Postgres Backup and Recovery Tool supports the following two modes of backup retention:
Recovery Window
Recovery window is a period of time that starts with the current time and extends backward to the point of recoverability. The recovery window can be:
- A number of days
- A number of weeks
- A number of months
For example, if you have set the recovery window to seven days for a backup server then EDB Postgres Backup and Recovery Tool must retain those backups (and associated write ahead logs (WALs)) that were created between the current time and seven days in the past. EDB Postgres Backup and Recovery Tool ensures that the user will have a backup (and associated WALs) for the given server in case of a failure; backups older than the time period between the current time and seven days prior are marked obsolete, thus satisfying the recovery window retention policy.
In order to explain this mode further, consider an example of the database server called "westwing_prod". The recovery window for this server is defined in the configuration file as the following:
retention_policy = 7 DAYS
The backups for "westwing_prod" server were generated on the following dates with an interval of every five days:
- May 15
- May 10
- May 5
- Apr 31
If the current date is May 18 and the recovery window is set to seven days, EDB Postgres Backup and Recovery Tool would need to retain the May 10 backup in order to be restored up to seven days (counting backward from the current time). The user can perform PITR (point-in-time recovery) and only recover data from up to seven days from the current time. Note that a more recent backup taken on May 15 exists in the recovery window but EDB Postgres Backup and Recovery Tool will retain the May 10 backup so it can restore up to 7 days.
The backups older than seven days (and the associated WAL files) will be marked obsolete and may be deleted using the BART manage command by passing -d or —delete-obsolete: (More on this later.)
Backup Redundancy
A backup redundancy retention policy specifies the number of backups to retain for a given server. The recovery_window is the recommended approach. A redundancy-based policy is sometimes preferred because the number of backups that will be retained as part of the recovery window is not constant. A redundancy-based policy lets the user specify the number of backups. For example:
retention_policy = 2 BACKUPS
EDB Postgres Backup and Recovery Tool will ensure that two recent backups are always retained for the given server. Consider a case where backups are five days apart: if the EDB Postgres Backup and Recovery Tool manage command is executed on May 18 with the above retention policy, EDB Postgres Backup and Recovery Tool will retain the May 15 and May 10 backups (and the associated WALs).
Backups:
- May 15
- May 10
- May 5
- Apr 31
Configuration
Now that we have examined the two modes for Retention Management supported by EDB Postgres Backup and Recovery Tool, I will briefly discuss how these are configured. It is a simple process. The user needs to specify the retention mode in the bart.cfg file. The retention mode can be specified globally, in which case the policy will apply to all the backup servers in the bart.cfg file, or a policy can be applied to an individual server.
In the bart.cfg sample below, I have configured redundancy mode retention for the ppas96_34 database server, and recovery window retention for the PG_96 database server. Retention can be specified on the server level in the global section (under [BART] in the sample below) and it will apply to all the servers. The value specified on the server level takes precedence over the value specified in the global section.
[BART]
bart_host= ec2-user@127.0.0.1
backup_path = /home/ec2-user/backups
pg_basebackup_path =/home/ec2-user/PPAS96_binary/bin/pg_basebackup
logfile = /tmp/bart.log
scanner_logfile = /tmp/bart_scanner.log
[PG_96]
host = 127.0.0.1
port = 5432
user = postgres
cluster_owner = postgres
retention_policy = 7 Days
description = "Postgres server"
[ppas96_34]
host = 172.25.1.34
port = 5444
user = edb
cluster_owner = ec2-user
description = "EPAS server"
retention_policy = 1 backups allow_incremental_backups=enabled
That’s it! The retention policy is good to go.
Next, in order to invoke the retention policy, the user needs to run the “bart MANAGE" command or make the command part of a cron job so it can run after a specified interval. The help for “bart MANAGE" command looks like this:
[ec2-user@ip-172-25-1-34 bin]$ ./bart MANAGE –help
./bart: backup and recovery tool
Usage:
./bart MANAGE [OPTION]...
Options:
-h, --help Show this help message and exit
-s, --server Name of the server or 'all' to specify all servers
-i, --backupid ID or Name of the backup or specify 'all'.
(only valid with -c)
-l, --list-obsolete List backups marked obsolete
-d, --delete-obsolete Delete backups marked obsolete
-c, --change-status Change the status of a backup to (keep or nokeep)
-n, --dry-run Do everything except no changes are made
[ec2-user@ip-172-25-1-34 bin]$
Here I will run the bart MANAGE command for ppas96_34 server:
[ec2-user@ip-172-25-1-34 bin]$ ./bart manage -s ppas96_34
INFO: processing server 'ppas96_34', backup '1494509492492'
INFO: processing server 'ppas96_34', backup '1494509223047'
INFO: marking backup '1494509223047' as obsolete
INFO: 2 WAL file(s) marked obsolete
INFO: processing server 'ppas96_34', backup '1494508985643'
INFO: marking backup '1494508985643' as obsolete
INFO: 4 incremental(s) of backup '1494508985643' will be marked obsolete
INFO: marking incremental backup '1494509186211' as obsolete
INFO: marking incremental backup '1494509169195' as obsolete
INFO: marking incremental backup '1494509089030' as obsolete
INFO: marking incremental backup '1494509064743' as obsolete
INFO: 12 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
These were the backups for the ppas96_34 server before running the bart MANAGE command:
The backup status for ppas96_34 after running the bart MANAGE command is given below. As you can see, the bart MANAGE command has marked all the full backups and the corresponding incremental backups (except one full backup and corresponding incrementals) as obsolete. I will explain how this works for incremental backup in bit more detail later.
[ec2-user@ip-172-25-1-34 bin]$ ./bart show-backups
Setting Up KEEP/NO KEEP
Once a backup is marked obsolete by the bart MANAGE command, you can delete the backup by passing —delete-obsolete (or -d) to the bart MANAGE command:
-d, --delete-obsolete Delete backups marked obsolete
[ec2-user@ip-172-25-1-34 bin]$ ./bart manage -s ppas96_34 -d
INFO: removing all obsolete backups of server 'ppas96_34'
INFO: removing obsolete backup '1494509223047'
INFO: 2 WAL file(s) will be removed
INFO: removing WAL file '000000010000000B00000071'
INFO: removing WAL file '000000010000000B00000070'
INFO: removing obsolete backup '1494508985643'
INFO: 12 WAL file(s) will be removed
INFO: 4 incremental(s) of backup '1494508985643' will be removed
INFO: removing obsolete incremental backup '1494509186211'
INFO: removing obsolete incremental backup '1494509169195'
INFO: removing obsolete incremental backup '1494509089030'
INFO: removing obsolete incremental backup '1494509064743'
INFO: removing WAL file '000000010000000B0000006F'
INFO: removing WAL file '000000010000000B0000006E'
INFO: removing WAL file '000000010000000B0000006D'
INFO: removing WAL file '000000010000000B0000006C'
INFO: removing WAL file '000000010000000B0000006B'
INFO: removing WAL file '000000010000000B0000006A'
INFO: removing WAL file '000000010000000B00000069'
INFO: removing WAL file '000000010000000B00000068'
INFO: removing WAL file '000000010000000B00000067'
INFO: removing WAL file '000000010000000B00000066'
INFO: removing WAL file '000000010000000B00000065'
INFO: removing WAL file '000000010000000B00000064'
INFO: 2 Unused file(s) will be removed
INFO: removing (unused) file '000000010000000B00000063'
INFO: removing (unused) file
'000000010000000B630000280000000B64000000.mbm'
Before you decide to delete the obsolete backups, you can also list them using the bart MANAGE command using the -l switch:
[ec2-user@ip-172-25-1-34 bin]$ ./bart manage -s ppas96_34 -l
SERVER NAME: ppas96_34
BACKUP ID: 1494509223047
BACKUP STATUS: obsolete
BACKUP TIME: 2017-05-11 13:27:03 UTC
BACKUP SIZE: 61.74 MB
WAL FILE(s): 2
...........
Once EDB Postgres Backup and Recovery Tool has marked a backup as obsolete (based on your retention policy) you may later decide that you want to keep the backup. You can change the backup status using bart MANAGE command:
-c, --change-status Change the status of a backup to (keep or nokeep)
You must provide the server with -s server-name and -i backup-id options to specify the server and backups. Specify ‘all’ to these switches to change the status for all obsolete backups of all servers.
If you have changed the status to KEEP, the bart MANAGE command won’t delete the backup when it deletes obsolete backups.
Once you have marked a backup as KEEP and you later decide that you don’t want to keep it, the status of the backup can be changed using the same bart MANAGE command:
-c, --change-status Change the status of a backup to (keep or nokeep)
Specify NOKEEP status and appropriate values for –s server-name and -i backup-id.
Once you have marked the backups as NOKEEP, the next run of BART MANAGE command will mark those as obsolete and they can be deleted using the BART MANAGE command as explained above.
Retention Management for Incremental Backups
So far we have examined Retention Management in the context of full backups. While the same policies also apply to incremental backups, there are some subtle but important differences.
As mentioned earlier, EDB Postgres Backup and Recovery Tool supports block-level incremental backup. An incremental backup is a backup of those blocks that have changed since the last parent backup. The parent backup can be a full backup or it can be an incremental backup.
The bart show-backups command shows the active full and incremental backups. The parent for a full backup is none and the parent for an incremental backup is the backup ID of the parent (which, again, may be a full or incremental backup). Note that when you create an incremental backup, you specify the ID (or name) of the parent backup.
When EDB Postgres Backup and Recovery Tool manage command is executed for the ppas96_34 server with retention policy set to “1 BACKUP” (a redundancy-based policy), EDB Postgres Backup and Recovery Tool will mark the full backup, and its associated child backups, obsolete leaving one full backup and associated child backups as per the retention policy. It is important to note that the retention policy always applies to a full backup; the child backups and WALs associated with full backups are also marked obsolete.
As you can see below, running the bart MANAGE command for the ppas96_34 server, one full backup and all children of that full backup are retained; the other full backups and associated child backups are marked as obsolete.
To reiterate the point, the retention policy is always applied to full backups. The full backup and its associated incremental backups are treated as a single unit. When a full backup is marked obsolete by a retention policy, the associated incremental backups are also marked obsolete.
Changing the backup status to KEEP/NOKEEP follows the same approach. The status change is applied to full backup and, in turn, applies to all associated incremental backup as well.
The addition of Retention Management to EDB Postgres Backup and Recovery Tool provides DBAs with greater precision in plotting their overall data management strategies. As data volumes continue to soar, such features will become increasingly important.
For information on the EDB Postgres Platform and the Management Suite, which includes EDB Postgres Backup and Recovery Tool, contact info@edbpostgres.com.
Ahsan Hadi is Senior Director, Product Development, at EnterpriseDB.