PostgreSQL 9.6 introduced a new API for hot physical backups when not using streaming replication. This article goes over the difference between exclusive backup and non-exclusive (or concurrent) backups with PostgreSQL.
Prior to PostgreSQL 9.6, the only way to perform concurrent physical backups was through pg_basebackup, via the streaming replication protocol.
Low-level file system copy was only available in an exclusive mode, by calling pg_start_backup(), initiating the copy of data files, then finally calling pg_stop_backup(). With exclusive mode, the PostgreSQL data directory (PGDATA), has two files:
-
backup_label: backup label file with information about backup, such as start time, label string, WAL locations, and so on -
tablespace_map: map of tablespaces (optional)
Due to this technique, only one backup at a time can be taken per PostgreSQL instance.
Also, exclusive backups might create administration overhead when the backup is interrupted without properly invoking pg_stop_backup() or, even worse, if the instance is shut down while taking a backup (PostgreSQL refuses to start if there is a backup label file in PGDATA).
In any case, from PostgreSQL 9.6, the limitation of taking one backup at a time has been overcome by a new native API which redefines the pg_start_backup() method and adds a variant to pg_stop_backup(). For back-compatibility, the older signatures have been kept.
The new pg_start_backup function now accepts a third optional parameter, called 'exclusive', which is set by default to 'true' for backward compatibility.
Initiating a concurrent (or if you prefer, a non-exclusive) backup is rather simple:
SELECT pg_start_backup('my_label', false, false);If you are not familiar with pg_start_backup(), the first argument is a label for the backup, while the second is a request for a fast checkpoint operation. The third parameter, when set to false, requests a concurrent backup:
postgres=# SELECT pg_start_backup('my_label', true, false);
pg_start_backup | 0/F000028The PostgreSQL connection requesting the concurrent backup needs to remain active for the whole duration of the physical copy of data files (performed with your favourite tools such as rsync, cp, tar, SAN or LVM snapshots, ...).
NOTE: this operation follows the same procedure as with prior versions of PostgreSQL (for detailed information, please refer to the documentation about continuous archiving and Point-In-Time-Recovery).
When finished, we need to use the new version of pg_stop_backup() to specify that we are closing the current non-exclusive backup:
SELECT * FROM pg_stop_backup(false);The different signature has a mandatory parameter, called 'exclusive', which
needs to be set to 'false' for concurrent backup. The reason is that the function returns a different result, a row made up of three fields:
- LSN, for backup consistency, returned at
pg_stop_backup()time: identical to the previous version - the content of the label file: this needs to be saved as
'backup_label'in the main backup directory (introduced in 9.6) - the list of tablespaces: if not empty, this needs to be saved as
'tablespace_map'in the main backup directory (introduced in 9.6)
For example:
postgres=# SELECT * FROM pg_stop_backup(false);
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
lsn | 0/F000130
labelfile | START WAL LOCATION: 0/F000028 (file 00000001000000000000000F)+
| CHECKPOINT LOCATION: 0/F000060 +
| BACKUP METHOD: streamed +
| BACKUP FROM: master +
| START TIME: 2016-05-16 09:19:44 CEST +
| LABEL: my_label +
|
spcmapfile | 16386 /Users/gabriele/pg96/tbs1 +
| 16388 /Users/gabriele/pg96/tbs2 +
| As you can see, these two steps require users to change their customised backup scripts. However, users can simply rely on robust and maintained software for disaster recovery and backup such as our Barman, which transparently manages this new behaviour introduced in 9.6.
Also, the current in-core API has replaced the pgespresso package that was previously used with Barman 1.3.1 and above for PostgreSQL 9.5 and earlier. Barman supports both pgespresso for PG9.2-PG9.5 and the new 9.6 API.
Further information is available in the PostgreSQL documentation: