In PostgreSQL, ensuring data integrity and ease of recovery during maintenance or deployment activities is crucial. One effective way to safeguard your database is by using the pg_create_restore_point function. This function creates a named, durable restore point which can be used to recover the database to a specific state using Point-in-Time Recovery (PITR). Here's a brief introduction on how to utilize pg_create_restore_point before performing maintenance or deploying changes.

Create 

Create a restore point named RP1:

SELECT pg_create_restore_point('RP1');

Get the Log Sequence Number (LSN) and the current timestamp, and save them as a reference:

SELECT pg_current_wal_lsn(), current_timestamp;

After doing this, you can restore that that point RP1 by following next steps:

Stop 

For restoring, first stop postgres:

systemctl stop postgresql.service

Restore 

pgbackrest restore --stanza=stanza_name --type=name --target=RP1

recovery_target_name 

Check the $PGDATA/postgres.auto.conf

restore_command = 'pgbackrest --stanza=stanza_name archive-get %f "%p"'
recovery_target_name = 'RP1'

Start 

Start postgresql and check the data:

systemctl start postgresql.service

promote 

After the recovery and if data is at the desired point:

SELECT pg_promote();