Thank you for your answer. I want to clarify one more doubt. Can PITR be achieved without applying Base Backup
On Tue, Mar 4, 2025 at 3:12 PM Laurenz Albe <laurenz.a...@cybertec.at> wrote: > On Tue, 2025-03-04 at 14:26 +0530, chandan Kumar wrote: > > Hello, > > I hope you are doing fine. I need your expertise on below case study. > > My current production environment is 2 node streaming replication hosted > on > > Ubuntu VM 's on Azure. I have performed below steps on primary database. > > 1- Take Base backup > > 2- Create a restore point using pg_create_restore_point() > > 3- executed some DDL statement (CREATE VIEW,ADD INDEX,DROP INDEX) > > 4- Perform rollback using restore point > > To rollback, you must stop PostgreSQL, restore the last full backup, > and apply > > WAL files until the restore point: > > 1. Stop PostgreSQL Service > > 2. Restore from Full Backup (Using pg_basebackup) > > 3. Modify recovery.conf (or postgresql.conf for newer versions) > > o Set recovery target name: > > recovery_target_name = > 'before_ddl_changes' > > recovery_target_action = > 'pause' > > o Set restore_command to replay WAL logs: > > restore_command = 'cp > /path/to/wal_archive/%f %p' > > 4. Start PostgreSQL > > sudo systemctl start postgresql > > 5. Verify Recovery Status > > SELECT pg_is_in_recovery(); > > After verification, finalize recovery: > > touch /var/lib/postgresql/data/recovery.signal > > or > > rm /var/lib/postgresql/data/recovery.conf > > Then restart PostgreSQL. > > 6. Reestablish replication > > > > Could you please help if my steps are correct ? Can we achieve rollback > from any > > other approach without restoring basebackup? > > You have to create /var/lib/postgresql/data/recovery.signal *before* you > start the server. Forget about "recovery.conf", that was before v12. > > You can set "recovery_target_action = 'promote'". > > Yours, > Laurenz Albe > -- *With warm regards* * Chandan*
