Re: Review my steps for rollback to restore point

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(dot)albe(at)cybertec(dot)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*