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 1tpTkw-006DG8-BC for pgsql-general@arkaria.postgresql.org; Tue, 04 Mar 2025 15:00:26 +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 1tpTkv-00HDso-2X for pgsql-general@arkaria.postgresql.org; Tue, 04 Mar 2025 15:00:25 +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 1tpTku-00HDfe-J5 for pgsql-general@lists.postgresql.org; Tue, 04 Mar 2025 15:00:24 +0000 Received: from mail-yb1-xb2c.google.com ([2607:f8b0:4864:20::b2c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tpTks-000vZE-2i for pgsql-general@lists.postgresql.org; Tue, 04 Mar 2025 15:00:23 +0000 Received: by mail-yb1-xb2c.google.com with SMTP id 3f1490d57ef6-e60c4412127so2412165276.2 for ; Tue, 04 Mar 2025 07:00:22 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741100422; x=1741705222; 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=vvnwg9HRaeulktBsvQg7q9z2iuWUKo4WhXHoHy/Aa8o=; b=Y1tAGIJb3HBREpgBtELs5B810qClsDGn7JkQyTOz56Pqw167ORSprVENvg687PH2fJ 2NzIucS6vybkticq8hqdGoTduANcROc19mRbxaLSWP01OVKmethACrIjQtpIwsoTGTpH 7t0zIpuSsOcHALCCDk6FmTfz0/HBE4+je6WDUFNswL2NFS9YOHq41fOiCFO9srMjDm+j W4qpS9py1HabTiHjqI8Hko0fxUzT9FobjZQKFzoE9DHn3cm77UcNtWrI7av+xdBrhUWP A68UA58XbzFY4Rsl7OeuEkgbmdcFA48L8ZG1mBxz25yrdMazTQR+Zl0BHvYsKVZ9Nx6e QtRg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741100422; x=1741705222; 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=vvnwg9HRaeulktBsvQg7q9z2iuWUKo4WhXHoHy/Aa8o=; b=p16RAAlog8mRxIB3Mg5Vxekdoqd5Z2Yl3sQcUjIW1+zD0uzFrYOmLL/yocA2BBKODy enTnGh1UJfzjDWDGWncfphSzWmSXc0khTMWede4Xp3FxQ1GdCEKhuc1Ow2nglklEEsBa VeRV3+chzRlW6790uJv4IasVkwD7JZqbItBWiGjBJRMn3+7CdEfZZXDxlUsuIZ0OYCLZ T/dP+RgZ8rYTJGiwR1K8jsmRLnCohsgTU/Iq+Qqyh1nholTvevkk4VIDdTlH98CZeImO 2ECmVKLU1USteqyZA7ONxmJqvNYh3Az4S/cKLzIxNMZwCgbY3n1gAwZPHEZ/u5xH9PQ9 k6Jg== X-Gm-Message-State: AOJu0YxC8cMptdD5XXo9lBvWV1U9afgQBB7ZQAO72uszLuEDP0evBk20 //L174W2zG5ic2vlOrsyiG1vewPsGFRna918ahyLhirFsIYMlnRtDFlI8qARtMK2AKeW70Xbxaj K/EQGIMONDLe4emRJhzDUYZA1BII= X-Gm-Gg: ASbGncv46dQu15BR+jylb9fY53RT2HRYrmtQnsVTsC3nqJHWmfPGtoZ//Lj0BxJ88qt MNGT/j9z+nLNClklYyIMchtMCFvotgYLYzQyB0MMGfgvRLGLWSZn75oK0rQ+XHGHOhCLQj6ng54 nldrJXMnum4L6ch+7f9GVLSu6l438= X-Google-Smtp-Source: AGHT+IFl3Omr1+fyW0lBOoqtU0jcDxu1sJ8fpH3Ohqlx9I6Y6cTQpJcudA6oDcBInOoaikJEMZtJ/u1XZ19vSPyz4qE= X-Received: by 2002:a05:6902:993:b0:e60:9821:3a with SMTP id 3f1490d57ef6-e60b2e94123mr20498403276.6.1741100422046; Tue, 04 Mar 2025 07:00:22 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: chandan Kumar Date: Tue, 4 Mar 2025 20:30:09 +0530 X-Gm-Features: AQ5f1Jrn40E3S4-N9lmY7e56GbDO38nDm3bkskYRDp0P8AO4BLKTuLoYlb9uasw Message-ID: Subject: Re: Review my steps for rollback to restore point To: Laurenz Albe Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000002cae1e062f85883f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002cae1e062f85883f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFPM 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 hoste= d > 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 =3D > 'before_ddl_changes' > > recovery_target_action = =3D > 'pause' > > o Set restore_command to replay WAL logs: > > restore_command =3D '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 =3D 'promote'". > > Yours, > Laurenz Albe > --=20 *With warm regards* * Chandan* --0000000000002cae1e062f85883f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you for your answer.=C2=A0 I want to clarify one mor= e doubt.=C2=A0 Can PITR be achieved without applying Base Backup

<= div class=3D"gmail_quote gmail_quote_container">
On Tue, Mar 4, 2025 at 3:12=E2=80=AFPM Laurenz Albe <laurenz.albe@cybertec.at> wrote= :
On Tue, 2025-0= 3-04 at 14:26 +0530, chandan Kumar wrote:
> Hello,
> =C2=A0I hope you are doing fine. I need your expertise on below=C2=A0c= ase study.
> My current production environment is 2 node streaming replication host= ed on
> Ubuntu VM 's on Azure. I have performed below steps on primary dat= abase.
> 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 bac= kup, and apply
>=C2=A0 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 sys= temctl 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_i= n_recovery();
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 After verifica= tion, finalize recovery:
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 touch /var/lib= /postgresql/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/po= stgresql/data/recovery.conf
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Then restart Po= stgreSQL.
> 6. Reestablish replication=C2=A0
>
> Could you please help if my steps are correct ? Can we achieve rollbac= k from any
> other approach without restoring basebackup?

You have to create /var/lib/postgresql/data/recovery.signal *before* you start the server.=C2=A0 Forget about "recovery.conf", that was be= fore v12.

You can set "recovery_target_action =3D 'promote'".

Yours,
Laurenz Albe


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