Contents

pg_backup_start()

A function preparing the server for an online backup
This entry relates to a PostgreSQL feature which is part of PostgreSQL 15, due to be released in late 2022.

pg_backup_start() is a system function which places the server in a state suitable for taking an online backup.

pg_backup_start() was added in PostgreSQL 15, replacing pg_start_backup().

Usage

pg_backup_start ( label text [, fast boolean ] ) → pg_lsn

The label parameter is mandatory but can be any arbitrary value.

The fast parameter (default: false) determines whether an immediate checkpoint should be forced. This will cause an I/O spike, but result in faster execution of the function.

The online backup state will be active until pg_backup_stop() is executed or the session is terminated, in which case the backup state will be aborted.

Change history

Examples

Basic execution example for pg_backup_start():

postgres=# SELECT pg_backup_start('foo');
 pg_backup_start 
-----------------
 0/2000028
(1 row)

Time: 7974.096 ms (00:07.974)

Execution with the fast option set to true will (assuming a checkpoint has not recently been completed) results in faster execution:

postgres=# SELECT pg_backup_start('foo', fast := true);
 pg_backup_start 
-----------------
 0/2000028
(1 row)

Time: 50.964 ms

PostgreSQL log output from execution of the first example:

[2022-05-13 15:55:32 UTC] psql postgres postgres LOG:  00000: statement: SELECT pg_backup_start('foo');
[2022-05-13 15:55:32 UTC]    LOG:  00000: checkpoint starting: force wait
[2022-05-13 15:55:40 UTC]    LOG:  00000: checkpoint complete: wrote 83 buffers (0.1%); 0 WAL file(s) added, 0 removed, 1 recycled; write=7.941 s, sync=0.001 s, total=7.956 s; sync files=0, longest=0.000 s, average=0.000 s; distance=11672 kB, estimate=11672 kB

compared to the second example:

[2022-05-13 15:57:01 UTC] psql postgres postgres LOG:  00000: statement: SELECT pg_backup_start('foo', fast := true);
[2022-05-13 15:57:01 UTC]    LOG:  00000: checkpoint starting: immediate force wait
[2022-05-13 15:57:01 UTC]    LOG:  00000: checkpoint complete: wrote 83 buffers (0.1%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.008 s, sync=0.001 s, total=0.021 s; sync files=0, longest=0.000 s, average=0.000 s; distance=11672 kB, estimate=11672 kB

It is not possible to start more than one backup in the same session:

postgres=# SELECT pg_backup_start('foo', fast := true);
 pg_backup_start 
-----------------
 0/3000028
(1 row)

postgres=# SELECT pg_backup_start('foo');
ERROR:  a backup is already in progress in this session

postgres=# SELECT pg_backup_start('bar');
ERROR:  a backup is already in progress in this session

If the session is terminated without calling pg_backup_stop(), the backup will be aborted:

[2022-05-13 16:05:25 UTC] psql postgres postgres WARNING:  01000: aborting backup due to backend exiting before pg_backup_stop was called
[2022-05-13 16:05:25 UTC] psql postgres postgres LOG:  00000: disconnection: session time: 0:01:41.427 user=postgres database=postgres host=127.0.0.1 port=58848

If the provided label name is NULL, NULL is returned and the server is not placed in a state suitable for making an online backup:

postgres=# SELECT pg_backup_start(NULL);
 pg_backup_start 
-----------------
 
(1 row)

postgres=# SELECT pg_backup_stop();
ERROR:  backup is not in progress
HINT:  Did you call pg_backup_start()?

Categories

Backup, System function

See also

pg_backup_stop(), pg_basebackup