Contents
pg_backup_start()
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
- PostgreSQL 15
- added (commit 58c41712)
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()?
References
- PostgreSQL documentation: Backup Control Functions