pg_create_restore_point()

A function for creating a restore point

pg_create_restore_point() is a system function for creating a restore point, i.e. a named point in the WAL stream at which a standby's recovery will halt.

pg_create_restore_point() was added in PostgreSQL 9.1.

Usage

pg_create_restore_point ( name TEXT ) → pg_lsn

Change history

Examples

Creating a restore point:

postgres=# SELECT pg_create_restore_point('test_restore_point');
 pg_create_restore_point
-------------------------
 2/BDFE0260
(1 row)

This will generate an entry like the following in the PostgreSQL logfile:

[2021-04-06 13:46:06 UTC] psql postgres postgres LOG:  00000: statement: SELECT pg_create_restore_point('test_restore_point');
[2021-04-06 13:46:06 UTC] psql postgres postgres LOG:  00000: restore point "test_restore_point" created at 2/BDFE0260

The corresponding WAL record is:

rmgr: XLOG        len (rec/tot):     98/    98, tx:          0, lsn: 2/BDFE01F8, prev 2/BDFE01C0, desc: RESTORE_POINT test_restore_point

On a standby, setting recovery_target_name to test_restore_point will cause recovery to halt when the named restore point is reached:

[2021-04-07 14:08:53 UTC]    LOG:  00000: recovery stopping at restore point "test_restore_point", time 2021-04-06 13:46:06.943405+0
[2021-04-07 14:08:53 UTC]    LOG:  00000: pausing at the end of recovery
[2021-04-07 14:08:53 UTC]    HINT:  Execute pg_wal_replay_resume() to promote.

This corresponds to the LSN returned when the restore point was created, 2/BDFE0260:

postgres=# SELECT * FROM pg_control_recovery()\gx
-[ RECORD 1 ]-----------------+-----------
min_recovery_end_lsn          | 2/BDFE0260
min_recovery_end_timeline     | 1
backup_start_lsn              | 0/0
backup_end_lsn                | 0/0
end_of_backup_record_required | f

Categories

Backup, Replication, WAL

See also

recovery_target_name