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 (labeltext[,fastboolean] ) →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
