MIME-Version: 1.0
From: chandan Kumar <chandan.issyoga@gmail.com>
Date: Tue, 4 Mar 2025 14:26:34 +0530
Message-ID: <CAHV6zV=XN-keoXn-Qi=vMBvfG5JGxjhdFKTksczizXSXC31fXg@mail.gmail.com>
Subject: Review my steps for rollback to restore point
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000e48d89062f807316"
Archived-At: <https://www.postgresql.org/message-id/CAHV6zV%3DXN-keoXn-Qi%3DvMBvfG5JGxjhdFKTksczizXSXC31fXg%40mail.gmail.com>
Precedence: bulk

--000000000000e48d89062f807316
Content-Type: text/plain; charset="UTF-8"

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*

--000000000000e48d89062f807316
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hello,</div><div>=C2=A0I hope you are doing fine. I n=
eed your expertise on below=C2=A0case study.</div><div>My current productio=
n environment is 2 node streaming replication hosted on Ubuntu VM &#39;s on=
 Azure. I have performed below steps on primary database.<br>1- Take Base b=
ackup</div><div>2-=C2=A0 Create a restore point using pg_create_restore_poi=
nt()<br>3-=C2=A0 executed some DDL statement (CREATE VIEW,ADD INDEX,DROP IN=
DEX)<br>4-=C2=A0 Perform=C2=A0rollback using restore point</div><div>=C2=A0=
To rollback, you must stop PostgreSQL, restore the last full backup, and ap=
ply WAL files until the restore point:</div>1.	Stop PostgreSQL Service<br>2=
.	Restore from Full Backup (Using pg_basebackup)<br>3.	Modify recovery.conf=
 (or postgresql.conf for newer versions)<br>o	Set recovery target name:<br>=
=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 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 recovery_target_name =3D &#39;before_ddl_changes&#39;<br>=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 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 recovery_target_action =3D &#39;pause&#39;<br>o	Set restore_comm=
and to replay WAL logs:<br>=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 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0restore_command =3D &#39;cp /path/to/=
wal_archive/%f %p&#39;<br>4.	Start PostgreSQL<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0sudo systemctl start postgresql<br>5.=
	Verify Recovery Status<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 SELECT pg_is_in_recovery();<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 After verification, finalize recovery:<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 touch /var/lib/postgresql/dat=
a/recovery.signal<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 or<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 rm /var/l=
ib/postgresql/data/recovery.conf<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0Then restart PostgreSQL.<div>6. Reestablish replication=C2=
=A0<br><div><br></div><div>Could you please help if my steps are correct ? =
Can we achieve rollback from any other approach without restoring basebacku=
p?</div><div><br></div><span class=3D"gmail_signature_prefix">-- </span><br=
><div dir=3D"ltr" class=3D"gmail_signature" data-smartmail=3D"gmail_signatu=
re"><div><font style=3D"background-color:#ffffff" face=3D"comic sans ms,san=
s-serif" color=3D"#000099"><strong>With warm regards</strong></font></div>
<div><font style=3D"background-color:#ffffff" face=3D"comic sans ms,sans-se=
rif" color=3D"#000099"><strong>=C2=A0=C2=A0=C2=A0=C2=A0 Chandan</strong></f=
ont></div></div></div></div>

--000000000000e48d89062f807316--