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
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@xxxxxxxxx> wrote:On Tuesday, March 4, 2025, chandan Kumar <chandan.issyoga@xxxxxxxxx> 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 replicationPostgreSQL 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 regardsChandan
