Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tq9ie-000JDI-Sb for pgsql-general@arkaria.postgresql.org; Thu, 06 Mar 2025 11:48:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tq9id-008hP0-LJ for pgsql-general@arkaria.postgresql.org; Thu, 06 Mar 2025 11:48:51 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tq9id-008hOd-2C for pgsql-general@lists.postgresql.org; Thu, 06 Mar 2025 11:48:51 +0000 Received: from mail-yb1-xb34.google.com ([2607:f8b0:4864:20::b34]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tq9ia-001I33-14 for pgsql-general@lists.postgresql.org; Thu, 06 Mar 2025 11:48:50 +0000 Received: by mail-yb1-xb34.google.com with SMTP id 3f1490d57ef6-e63504bedd0so273263276.0 for ; Thu, 06 Mar 2025 03:48:48 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741261727; x=1741866527; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=obE8N3K8PG7X/RlOR3UBquHVZ/Aru0Y5rUsDfbWzW04=; b=DKPFG5UT3IJRHo/o66oy43pJ9NZ5FMIjLEDNfNibOqFn/SQs6ng3/s6kwwAn67zvP/ AHetFwFdSOpX5mcHAUUHPqJBjRBL9dqsEzEfo+TGMmbfKXT4tsRfp5tPVat+Dy4F0a0U mY7ZdO67EHkS0oZLnrTY8kDRKxs+7CaIm8aRuC7mqdrwChflINm/qKFY9EtEZrUtz21t Ny4DFFAASgXUBIhxpg5lT0QQrAe5Uc5HPibU+BT1rKH7w/BRtPOOfWIaNR2yYiEpVKXB 0cDfexvGJlKzabFIUt3joFwo6xpTFAdXaAJG/tsRHVHBo6LgLzAo1o8r0w60DtJLCX8+ 0Krg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741261728; x=1741866528; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=obE8N3K8PG7X/RlOR3UBquHVZ/Aru0Y5rUsDfbWzW04=; b=LXd6pdPcviU1MNWvVXUK9mSRcRPuGdMLxx+85IhzG9Ea4GlSv6PkVatIBJ/AJMhGYq S5n7R2NJfWfYZh5A9AkxoD39XJGZkxglkbEbcpmol3OtaP8GaN24V7913JN3ROSE01dj QCljuPMviQGKMHVjk7jMC93Tc5h07xKznnmEXLm/YSLVXwVmExi5YrIg9BJI/QsoSmau Ix8CLuypWt5mT5i9KVN5pxP1x/mu2E+ZjSJY/hAGcgtCAm8DIetbaKZOKLi5OpPYQ8Em eNryDCKRpLMNtU/NY+BoYlLvvvblzpHvwVCS9mBJus3Tr2/X3wBg1AYUzmEKLTWkO3qT yFHw== X-Forwarded-Encrypted: i=1; AJvYcCWv5CPrp9qYEypkPNFUFVEUnRkzOnTcussNAdTXBOzpRawrrONcjpcoMdr558QNMGMzA8PyB4wI/T8uxnP1@lists.postgresql.org X-Gm-Message-State: AOJu0YzLf9XUqVDSy4pBc2zOSbJQiQCSq5vxAgDIyYBaT4fPNdXGhgDh r3CAUL/FYslH1yGLKIAdOfWtvlsjJynzkt4bLl4FYTv3KpbjPsQ+LOTf/WkIsrHZWP7Y3wxfKDP jP9EN+Mx45KsPXnR7cNpp2F0820k= X-Gm-Gg: ASbGncuQv/Z50ll+6YpI93D8zx0qPwOMON6ObolLMcur0ckxuUUMmycfbLGVMuTVbap wSaCSBuzAAusJoqJCqlih2TK3Gz1n/cPMpogfO+gJ0PUfNHlBm+XsV3+Y2BEiULd/sI3vqXd/zl 6UFfqnGIE7X7SLH0LboGGvlnyAmfI= X-Google-Smtp-Source: AGHT+IHtfSeuyr9e/02WuQIIH9fmIFvUCcuCSMiL+xNMHAiqzz8h56MMPyc+RbdniOGfgsFFcDPl2ibjavi4Sp6l4S8= X-Received: by 2002:a05:6902:2210:b0:e60:9d5b:b882 with SMTP id 3f1490d57ef6-e611e1c4b5fmr8942548276.27.1741261727572; Thu, 06 Mar 2025 03:48:47 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: chandan Kumar Date: Thu, 6 Mar 2025 17:18:36 +0530 X-Gm-Features: AQ5f1JrzYafbr3mG9URko1neHYmISfLonP2rk1uE2JyLKormu6sD3C6AUfv2MjI Message-ID: Subject: Re: Review my steps for rollback to restore point To: "David G. Johnston" Cc: Laurenz Albe , "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000bbaee3062fab1659" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000bbaee3062fab1659 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 =3D 'cp /var/lib/postgresql/wal_archive/%f %p'" >> /var/lib/postgresql/14/main/postgresql.auto.conf* *echo "recovery_target_name =3D 'before_database_update'" >> /var/lib/postgresql/14/main/postgresql.auto.conf* *echo "recovery_target_action =3D '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=E2=80=AFPM 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=E2=80=AFPM David G. Johnston < > david.g.johnston@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 stream= ing >>> replication >>> >> >> PostgreSQL doesn=E2=80=99t have a concept of =E2=80=9Crevert=E2=80=9D. >> >> 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 direct= ory >> 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=E2=80=99s like saying pg_dump and pg_basebackup are= similar. >> Sure, in some ways that is true - but the logical vs. physical distincti= on >> cannot be ignored fully. >> >> David J. >> >> > > -- > *With warm regards* > * Chandan* > --=20 *With warm regards* * Chandan* --000000000000bbaee3062fab1659 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Dear Team,
Thank you for all the responses I have rece= ived in this matter.=C2=A0 I would like to send my final steps that I am go= ing to follow=C2=A0during PITR.
kindly take some out to see if these ste= ps are correct or need any correction or advise.

Performing Database rollback using PITR

Steps:

Pre requisites

1)=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 Ensure WAL Archiving is Ena= bled

2)=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 Ensure postgres have access= to write WAL Files on archive location

3)=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Check WAL files ar= e being generated in default directory

4)=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 Check WAL files are being a= rchived in archive directory

5)=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 Ensure Replication is Runni= ng fine, check if any lag

6)=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 Backup PostgreSQL config fi= les (postgres.conf, hba, repmgr config file)

Implementation steps:

1.=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Take a Base Backup (Before Making Any Changes)

2.=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Create a restore point

SELECT pg_create_restore_po= int('before_database_update');

3.=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Execute DDL statements

4.=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Validate changes=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0

If the changes are not as expected, proceed to rollback (PITR).

5.=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 Unregister the standby first

repmgr -f /etc/postgresql/14/main/repmgr.conf standby unregister

6.=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 Stop both servers (Primary & Standby)

sudo systemctl stop postgre= sql@14-main

7.=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Move the Old Data Directory (Backup Just in Case)

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 =C2=A0mv /var/lib/postgresql/14/main /var/lib/postgresql/14/main_old_$(date +%F)

8.=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 Extract the Base Backup to the Data Directory

9. =C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 make sure Correct Ownersh= ip is granted to user postgres to data directory

10.=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 Create the recovery.signal File

touch /var/lib/postgresql/14/main/recovery.signal

11.=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 Update postgresql.auto.conf

echo "restore_command =3D 'cp /var/lib/postgresql/wal_archive/%f %p'= ;" >> /var/lib/postgresql/14/main/postgresql.auto.conf

echo "recovery_target_name =3D 'before_database_update'" >&= gt; /var/lib/postgresql/14/main/postgresql.auto.conf

echo "recovery_target_action =3D 'promote'" >> /var/lib/postgresql/14/main/postgresql.auto.conf

=C2=A0

8=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Start PostgreSQL on primary (rollback is done)<= /span>

sudo systemctl start postgresql@14-main

9=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Verify recovery status

psql -U postgres -c "SELECT pg_is_in_recovery();"

10=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 Reestablish replication- Standby= needs to rebuilt to match primary after PITR.=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0

11=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Create the replication sl= ot on primary, it might gets deleted during PITR

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select * from pg_create_physical_replication_slot('node_a_repslot');<= /span>

12. =C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Move/rename standby signal , because standby signal will be created in next step=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0

mv standby.signal standbyold.signal

13.=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 Start Standby

14.=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Register the standby

15. =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 Check Replication Status

=C2=A0

=C2=A0

=C2=A0

=C2=A0

=C2=A0


On Tue, Mar 4, 2025 at 9:15=E2=80=AFPM chandan Kumar <chandan.issyoga@gmail.com> wrote:
Hi D= avid,=C2=A0
You catched my word "revert". Thats so encouragin= g to see how this community helps. Your answer has cleared my 99% doubt. Th= anks again.
I wish I also contribute one day .=C2=A0 Have a good time!
On= Tue, Mar 4, 2025 at 9:08=E2=80=AFPM David G. Johnston <david.g.johnston@gmail.com<= /a>> wrote:
O= n Tuesday, March 4, 2025, chandan Kumar <chandan.issyoga@gmail.com> wrote:
Thank y= ou for your time and clarification.=C2=A0
Does PITR recreate database i= nternally ?=C2=A0 can i say it is not the same as pg_restore=C2=A0 or it is= same as pg_restore plus applying WAL on top of it.=C2=A0 I am asking becau= se can we revern DDL operations without PITR in streaming replication
=

PostgreSQL doesn=E2=80=99t have a co= ncept of =E2=80=9Crevert=E2=80=9D.

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

You cannot mix physical and logical image= s of the database so applying WAL on top of pg_restore is technically inval= id - but it does effective convey the idea.=C2=A0 It=E2=80=99s like saying = pg_dump and pg_basebackup are similar.=C2=A0 Sure, in some ways that is tru= e - but the logical vs. physical distinction cannot be ignored fully.
=

David J.



--
With warm regards
=C2=A0=C2=A0=C2=A0=C2=A0 Chandan


--
With warm regards
=C2=A0=C2=A0=C2=A0=C2=A0 Chandan
--000000000000bbaee3062fab1659--