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.
