Subject: Review my steps for rollback to restore point

From: chandan Kumar <[email-masked]>
Date: Tue, 4 Mar 2025 14:26:34 +0530

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?

-- 
*With warm regards*
*     Chandan*

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 04 Mar 2025 10:42:06 +0100

On Tue, 2025-03-04 at 14:26 +0530, chandan Kumar wrote:
>  To rollback, you must stop PostgreSQL, restore the last full backup, and apply
>  WAL files until the restore point:
>                                               recovery_target_name = 'before_ddl_changes'
>                                               recovery_target_action = 'pause'
>                                              restore_command = 'cp /path/to/wal_archive/%f %p'
> 4. Start PostgreSQL
> 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