SQL transactions executing from VMSS
Hi Team, Greetings! Kindly help in below situation, where in I see slowness in response time while I do testing running SQL transaction in batches from VMSS to postgres database in flexible server(PaaS) 14.17 version. VMSS to IaaS(postgres db on ubuntu) is faster than VMSs to PaaS. 14 version is same on both the system. response size is same but response time is almost 2.5x more on PaaS. I see dynatrace is showing more time is on waiting time. Query plan is same on both the system. If i run single sql transaction from pg admin it is executing in same time. what can cause this slowness, any idea please. -- *With warm regards* * Chandan*
Fwd: SQL transactions executing from VMSS
Hello Team, I did not get any answer till now. If someone has any idea please help me is getting this resolved. The issue is - database deployment , sql query to database is slow when we run database script from vmss to postgres database in flexible server. both the servers are in US EAST under the same subnet -- Forwarded message - From: chandan Kumar Date: Tue, Apr 22, 2025 at 4:59 PM Subject: SQL transactions executing from VMSS To: Hi Team, Greetings! Kindly help in below situation, where in I see slowness in response time while I do testing running SQL transaction in batches from VMSS to postgres database in flexible server(PaaS) 14.17 version. VMSS to IaaS(postgres db on ubuntu) is faster than VMSs to PaaS. 14 version is same on both the system. response size is same but response time is almost 2.5x more on PaaS. I see dynatrace is showing more time is on waiting time. Query plan is same on both the system. If i run single sql transaction from pg admin it is executing in same time. what can cause this slowness, any idea please. -- *With warm regards* * Chandan* -- *With warm regards* * Chandan*
Re: Review my steps for rollback to restore point
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 wrote: > On Tuesday, March 4, 2025, chandan Kumar > 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*
Re: Review my steps for rollback to restore point
Thank you ! On Tue, Mar 4, 2025 at 9:08 PM Ron Johnson wrote: > 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 > 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 >> 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 , and butter sauce. > Don't boil me, I'm still alive. > lobster! > -- *With warm regards* * Chandan*
Re: Review my steps for rollback to restore point
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 wrote: > On Tuesday, March 4, 2025, chandan Kumar > 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*
Re: Review my steps for rollback to restore point
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 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) > > 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 > -- *With warm regards* * Chandan*
Review my steps for rollback to restore point
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*
Re: Review my steps for rollback to restore point
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*
8Start PostgreSQL on primary (rollback is done)
*sudo systemctl start postgresql@14-main*
9Verify 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
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 <
> david.g.johns...@gmail.com> wrote:
>
>> On Tuesday, March 4, 2025, chandan Kumar
>> 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*
