Step‐by‐Step Guide to Setting Up Point‐in‐Time Recovery in PostgreSQL 16 with Scripts - shiviyer/Blogs GitHub Wiki

Implementing Point-in-Time Recovery (PITR) in PostgreSQL 16 with scripts involves several steps:

1. Configure WAL Archiving (postgresql.conf):

   wal_level = replica
   archive_mode = on
   archive_command = 'cp %p /path_to_wal_archive/%f'  # Replace with your archive path
   
   

2. Take a Base Backup:

   pg_basebackup -h localhost -D /path_to_backup_directory -U postgres -Fp -Xs -P
   
   

3. Restore Procedure:

   • Stop the PostgreSQL server if it's running.

   • Create a recovery.conf file or use standby.signal and postgresql.conf for PostgreSQL 12+:

     touch /var/lib/postgresql/data/standby.signal
     echo "restore_command = 'cp /path_to_wal_archive/%f %p'" >> /var/lib/postgresql/data/postgresql.conf
     echo "recovery_target_time = '2023-04-13 14:55:00'" >> /var/lib/postgresql/data/postgresql.conf
     
     

   • Start the PostgreSQL server.

4. Monitor Recovery:

   • Check PostgreSQL logs for the recovery process.

This script-based approach automates the setup for PITR, ensuring you have a consistent and reliable method for disaster recovery.