PostgreSQL offers two types of backup methods:

1. Logical backups
2. Physical backups

Logical backups are like snapshots of a database. These are created using the pg_dump or pg_dumpall utility that ships with PostgreSQL. Logical backups:

• Back up individual databases or all databases
• Back up just the schemas, just the data, individual tables, or the whole database (schemas and data)
• Create the backup file in proprietary binary format or in plain SQL script
• Can be restored using the pg_restore utility which also ships with PostgreSQL
• Do not offer point-in-time recovery (PITR)

Physical backups are different from logical backups because they deal with binary format only and makes file-level backups. Physical backups:

• Offer point-in-time recovery
• Back up the contents of the PostgreSQL data directory and the WAL (Write Ahead Log) files
• Use the PostgreSQL pg_start_backup and pg_stop_backup commands. However, these commands need to be scripted, which makes physical backups a more complex process (pg_basebackup).

Physical backups are of two types.

• Backup using low level API.

Low level base backups can be made in a non-exclusive or an exclusive way. The non-exclusive method is recommended and the exclusive one is deprecated and will eventually be removed.

• Backup using pg_basebackup.

Exclusivelow level backup

The standard procedure to make a base backup using the low-level commands is shown below:

(1) Issue the pg_start_backup command

(2) Take a snapshot of the database cluster with the archiving command you want to use

(3) Issue the pg_stop_backup command

pg_start_backup

The pg_start_backup prepares for making a base backup. The recovery process starts from a REDO point, so the pg_start_backup must do checkpoint to explicitly create a REDO point at the start of making a base backup. Moreover, the checkpoint location of its checkpoint must be saved in a file other than pg_control because regular checkpoint might be done a number of times during backup. Hence the pg_start_backup performs the following four operations:

• Force into the full-page wirte mode.
• Switch to the current WAL segment file.
• Do checkpoint.
• Create a backup_label file – This file, created in the top level of the base directory, contains essential information about base backup itself, such as the checkpoint location of this checkpoint.

A backup_label file contains the following six items (version 11 or later, seven items):

1. CHECKPOINT LOCATION – This is the LSN location where the checkpoint created by this command has been recorded.
2. START WAL LOCATION – This is not used with PITR, but used with the streaming replication, which is described in Chapter 11. It is named ‘START WAL LOCATION’ because standby server in replication-mode reads this value only once at initial startup.
3. BACKUP METHOD – This is the method used to make this base backup. (Either ‘pg_start_backup’ or ‘pg_basebackup’.)
4. BACKUP FROM – This shows whether this backup is taken from primary or standby.
5. START TIME – This is the timestamp when the pg_start_backup has executed.
6. LABEL – This is the label specified at the pg_start_backup.
7. START TIMELINE – This is the timeline that the backup started. This is for a sanity check and has been introduced in Version 11.
8. when you recover a database using this base backup, PostgreSQL takes the ‘CHECKPOINT LOCATION’ out of the backup_label file to read the checkpoint record from the appropriate archive log, and then, gets the REDO point from its record and starts recovery process.

Take backup

Take the backup using native Linux commands like tar

pg_stop_backup

The pg_stop_backup performs the following five operations to complete the backup.

• Reset to non-full-page writes mode if it has been forcibly changed by the pg_start_backup.
• Write a XLOG record of backup end.
• Switch the WAL segment file.
• Create a backup history file – This file contains the contents of the backup_label file and the timestamp that the pg_stop_backup has been executed.
• Delete the backup_label file – The backup_label file is required for recovery from the base backup and once copied, it is not necessary in the original database cluster.

Once you issue pg_stop_backup a backup history file is created under pg_wal location in below format

{WAL segment}.{offset value at the time the base backup was started}.backup

Example.,
postgres=#
postgres=# select pg_start_backup(‘label’);
pg_start_backup
—————–
1/BA000028
(1 row)

[postgres@postgres02 data]$ cat backup_label
START WAL LOCATION: 1/BA000028 (file 0000000200000001000000BA)
CHECKPOINT LOCATION: 1/BA000060
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2019-10-26 18:00:51 IST
LABEL: label

Contents of Backup Label File

[postgres@postgres02 data]$ cd ../
[postgres@postgres02 pgsql]$ tar -cf exclusive.tar data
[postgres@postgres02 pgsql]$

postgres=# select pg_stop_backup();
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
pg_stop_backup
—————-
1/BB000050
(1 row)

[postgres@postgres02 pg_wal]$ ls -lrt *.backup
-rw——-. 1 postgrespostgres 292 Oct 26 18:10 0000000200000001000000BA.00000028.backup
[postgres@postgres02 pg_wal]$pwd
/u01/pgsql/data/pg_wal
[postgres@postgres02 pg_wal]$

Limitations of Exclusive low level backups and Introduction to Non-exclusive backups:

Limitations

• It is not possible to take multiple backups in parallel.

postgres=# select pg_start_backup(‘label’);

ERROR:  a backup is already in progress

HINT:  Run pg_stop_backup() and try again.

postgres=#

• In case of a crash of the tool taking the backup, the server remains stuck in backup mode and needs some cleanup actions.
• The backup_label file being created in the data folder, it is not possible to make the difference between a server that crashed while a backup is taken and a cluster restored from a backup.

Non-Exclusive backup
postgres=# SELECT pg_start_backup(‘my_backup’, true, false);
pg_start_backup
—————–
1/BE000060
(1 row)

[postgres@postgres02 pgsql]$tar -cf non_exclusive.tar data
tar: data/pg_wal: file changed as we read it
[postgres@postgres02 pgsql]$
[It allowed multiple copies]

postgres=# SELECT pg_start_backup(‘my_backup_new’, true, false);
pg_start_backup
—————–
1/BF000028
(1 row)

postgres=# select pg_stop_backup(‘false’);
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
pg_stop_backup
—————————————————————————–
(1/BF000130,”START WAL LOCATION: 1/BE000060 (file 0000000200000001000000BE)+
CHECKPOINT LOCATION: 1/BE000098 +
BACKUP METHOD: streamed +
BACKUP FROM: master +
START TIME: 2019-10-26 18:18:00 IST +
LABEL: my_backup +
“,””)
(1 row)

• Write the content of pg_stop_backup manually to backup.label file.
• Make sure to take archivelog backup to make your backup consistent and use recovery.conf file to restore the backups.
• conf file should look something like
• restore_command = ‘cp /u02/New_PostgreData/archivedlogs/%f %p’