Message-ID: <ddda8f051bfe265e74d50f17dd8a4227211b9633.camel@cybertec.at>
Subject: Re: Review my steps for rollback to restore point
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: chandan Kumar <chandan.issyoga@gmail.com>, 
	pgsql-general@lists.postgresql.org
Date: Tue, 04 Mar 2025 10:42:06 +0100
In-Reply-To: <CAHV6zV=XN-keoXn-Qi=vMBvfG5JGxjhdFKTksczizXSXC31fXg@mail.gmail.com>
References: 
	<CAHV6zV=XN-keoXn-Qi=vMBvfG5JGxjhdFKTksczizXSXC31fXg@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/ddda8f051bfe265e74d50f17dd8a4227211b9633.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-03-04 at 14:26 +0530, chandan Kumar wrote:
> Hello,
> =C2=A0I hope you are doing fine. I need your expertise on below=C2=A0case=
 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-=C2=A0 Create a restore point using pg_create_restore_point()
> 3-=C2=A0 executed some DDL statement (CREATE VIEW,ADD INDEX,DROP INDEX)
> 4-=C2=A0 Perform=C2=A0rollback using restore point
> =C2=A0To 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:
> =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 'before_ddl_changes'
> =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 'pause'
> o Set restore_command to replay WAL logs:
> =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 'cp /path/to/wal_archive/%f %p'
> 4. Start PostgreSQL
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0sudo system=
ctl start postgresql
> 5. Verify Recovery Status
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT pg_is_in_r=
ecovery();
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 After verificatio=
n, finalize recovery:
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 touch /var/lib/po=
stgresql/data/recovery.signal
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 or
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 rm /var/lib/postg=
resql/data/recovery.conf
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Then restart Postg=
reSQL.
> 6. Reestablish replication=C2=A0
>=20
> Could you please help if my steps are correct ? Can we achieve rollback f=
rom 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 =3D 'promote'".

Yours,
Laurenz Albe