public inbox for [email protected]help / color / mirror / Atom feed
Re: Review my steps for rollback to restore point 6+ messages / 3 participants [nested] [flat]
* Re: Review my steps for rollback to restore point @ 2025-03-04 15:07 David G. Johnston <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: David G. Johnston @ 2025-03-04 15:07 UTC (permalink / raw) To: chandan Kumar <[email protected]>; +Cc: Laurenz Albe <[email protected]>; [email protected] <[email protected]> On Tuesday, March 4, 2025, chandan Kumar <[email protected]> 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. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Review my steps for rollback to restore point @ 2025-03-04 15:30 chandan Kumar <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: chandan Kumar @ 2025-03-04 15:30 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Laurenz Albe <[email protected]>; [email protected] <[email protected]> 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 On Tue, Mar 4, 2025 at 8:37 PM David G. Johnston <[email protected]> wrote: > On Tuesday, March 4, 2025, chandan Kumar <[email protected]> > 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. > > -- *With warm regards* * Chandan* ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Review my steps for rollback to restore point @ 2025-03-04 15:38 David G. Johnston <[email protected]> parent: chandan Kumar <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: David G. Johnston @ 2025-03-04 15:38 UTC (permalink / raw) To: chandan Kumar <[email protected]>; +Cc: Laurenz Albe <[email protected]>; [email protected] <[email protected]> On Tuesday, March 4, 2025, chandan Kumar <[email protected]> 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. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Review my steps for rollback to restore point @ 2025-03-04 15:45 chandan Kumar <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: chandan Kumar @ 2025-03-04 15:45 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Laurenz Albe <[email protected]>; [email protected] <[email protected]> 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 <[email protected]> wrote: > On Tuesday, March 4, 2025, chandan Kumar <[email protected]> > 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* ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Review my steps for rollback to restore point @ 2025-03-06 11:48 chandan Kumar <[email protected]> parent: chandan Kumar <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: chandan Kumar @ 2025-03-06 11:48 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Laurenz Albe <[email protected]>; [email protected] <[email protected]> Dear Team, Thank you for all the responses I have received in this matter. I would like to send my final steps that I am going to follow during PITR. kindly take some out to see if these steps are correct or need any correction or advise. *Performing Database rollback using PITR* *Steps: * *Pre requisites* 1) Ensure WAL Archiving is Enabled 2) Ensure postgres have access to write WAL Files on archive location 3) Check WAL files are being generated in default directory 4) Check WAL files are being archived in archive directory 5) Ensure Replication is Running fine, check if any lag 6) Backup PostgreSQL config files (postgres.conf, hba, repmgr config file) *Implementation steps:* 1. Take a Base Backup (Before Making Any Changes) 2. Create a restore point *SELECT pg_create_restore_point('before_database_update');* 3. Execute DDL statements 4. Validate changes If the changes are *not* as expected, proceed to rollback (PITR). 5. Unregister the standby first *repmgr -f /etc/postgresql/14/main/repmgr.conf standby unregister* 6. Stop both servers (Primary & Standby) *sudo systemctl stop postgresql@14-main* 7. Move the Old Data Directory (Backup Just in Case) * mv /var/lib/postgresql/14/main /var/lib/postgresql/14/main_old_$(date +%F)* 8. Extract the Base Backup to the Data Directory 9. make sure Correct Ownership is granted to user postgres to data directory 10. Create the recovery.signal File *touch /var/lib/postgresql/14/main/recovery.signal* 11. Update postgresql.auto.conf *echo "restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'" >> /var/lib/postgresql/14/main/postgresql.auto.conf* *echo "recovery_target_name = 'before_database_update'" >> /var/lib/postgresql/14/main/postgresql.auto.conf* *echo "recovery_target_action = 'promote'" >> /var/lib/postgresql/14/main/postgresql.auto.conf* 8 Start PostgreSQL on primary (rollback is done) *sudo systemctl start postgresql@14-main* 9 Verify recovery status *psql -U postgres -c "SELECT pg_is_in_recovery();"* 10 Reestablish replication- Standby needs to rebuilt to match primary after PITR. 11 Create the replication slot on primary, it might gets deleted during PITR * select * from pg_create_physical_replication_slot('node_a_repslot');* 12. Move/rename standby signal , because standby signal will be created in next step *mv standby.signal standbyold.signal* 13. Start Standby 14. Register the standby 15. Check Replication Status On Tue, Mar 4, 2025 at 9:15 PM chandan Kumar <[email protected]> wrote: > 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 < > [email protected]> wrote: > >> On Tuesday, March 4, 2025, chandan Kumar <[email protected]> >> 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* > -- *With warm regards* * Chandan* ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Review my steps for rollback to restore point @ 2025-03-07 13:45 Greg Sabino Mullane <[email protected]> parent: chandan Kumar <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Greg Sabino Mullane @ 2025-03-07 13:45 UTC (permalink / raw) To: chandan Kumar <[email protected]>; +Cc: David G. Johnston <[email protected]>; Laurenz Albe <[email protected]>; [email protected] <[email protected]> On Thu, Mar 6, 2025 at 6:49 AM chandan Kumar <[email protected]> wrote: > need any correction or advise. > Honestly, this all seems overly complex and fragile. I'm not sure what the overall goal is, but if it's to have a general PITR solution, use pgBackRest. If it's just to have a fall back method for a particular change, an easier solution is to stop replication, apply changes, and promote the replica if something goes wrong. Either way, you should have a test system setup that you can try out your steps on. If a step fails and you do not understand why, this list is a great resource. Practice this a lot on test systems until it all becomes second nature. Cheers, Greg -- Crunchy Data - https://www.crunchydata.com Enterprise Postgres Software Products & Tech Support ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-03-07 13:45 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-03-04 15:07 Re: Review my steps for rollback to restore point David G. Johnston <[email protected]> 2025-03-04 15:30 ` chandan Kumar <[email protected]> 2025-03-04 15:38 ` David G. Johnston <[email protected]> 2025-03-04 15:45 ` chandan Kumar <[email protected]> 2025-03-06 11:48 ` chandan Kumar <[email protected]> 2025-03-07 13:45 ` Greg Sabino Mullane <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox