Review my steps for rollback to restore point

[chandan Kumar <chandan.issyoga@xxxxxxxxx>]

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