Thread: Review my steps for rollback to restore point

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

On Tuesday, March 4, 2025, chandan Kumar <chandan.issyoga@gmail.com> wrote:

Thank you for your answer.  I want to clarify one more doubt.  Can PITR be achieved without applying Base Backup

The point-in-time you choose must be in the future relative to whatever data files you are applying WAL on top of.  That is only possible of you’ve backed up the data files at some point in the past and use that backup.

David J.

Chandran,

1. For PITR, you should use a tool like PgBackRest.  It handles all $PGDATA and WAL archiving.  It's multithreaded, too,
2. pg_restore is just for logical backups.
3. Streaming Replication is for hot standby, not backups.

On Tue, Mar 4, 2025 at 10:00 AM chandan Kumar <chandan.issyoga@gmail.com> wrote:

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.albe@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)

[snip]

--

Death to <Redacted>, and butter sauce.

Don't boil me, I'm still alive.

<Redacted> lobster!

On Tuesday, March 4, 2025, chandan Kumar <chandan.issyoga@gmail.com> wrote:

Thank you for your time and clarification. 

Does PITR recreate database internally ?  can i say it is not the same as pg_restore  or it is same as pg_restore plus applying WAL on top of it.  I am asking because can we revern DDL operations without PITR in streaming replication

PostgreSQL doesn’t have a concept of “revert”.

PITR just deals with raw bytes on disk for an entire cluster.  If a new file appears in the WAL that file is created.  That file can be a directory for a database.

You cannot mix physical and logical images of the database so applying WAL on top of pg_restore is technically invalid - but it does effective convey the idea.  It’s like saying pg_dump and pg_basebackup are similar.  Sure, in some ways that is true - but the logical vs. physical distinction cannot be ignored fully.

David J.

Hi David, 
You catched my word "revert". Thats so encouraging to see how this community helps. Your answer has cleared my 99% doubt. Thanks again.
I wish I also contribute one day .  Have a good time!

On Tue, Mar 4, 2025 at 9:08 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Tuesday, March 4, 2025, chandan Kumar <chandan.issyoga@gmail.com> wrote:

Thank you for your time and clarification. 

Does PITR recreate database internally ?  can i say it is not the same as pg_restore  or it is same as pg_restore plus applying WAL on top of it.  I am asking because can we revern DDL operations without PITR in streaming replication

PostgreSQL doesn’t have a concept of “revert”.

PITR just deals with raw bytes on disk for an entire cluster.  If a new file appears in the WAL that file is created.  That file can be a directory for a database.

You cannot mix physical and logical images of the database so applying WAL on top of pg_restore is technically invalid - but it does effective convey the idea.  It’s like saying pg_dump and pg_basebackup are similar.  Sure, in some ways that is true - but the logical vs. physical distinction cannot be ignored fully.

David J.

--

With warm regards

     Chandan