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 1tpULS-006NIE-IC for pgsql-general@arkaria.postgresql.org; Tue, 04 Mar 2025 15:38:10 +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 1tpULQ-000dmv-Ep for pgsql-general@arkaria.postgresql.org; Tue, 04 Mar 2025 15:38:08 +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 1tpULQ-000dmn-3Q for pgsql-general@lists.postgresql.org; Tue, 04 Mar 2025 15:38:08 +0000 Received: from mail-oa1-x2b.google.com ([2001:4860:4864:20::2b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tpULO-000w1V-0H for pgsql-general@postgresql.org; Tue, 04 Mar 2025 15:38:07 +0000 Received: by mail-oa1-x2b.google.com with SMTP id 586e51a60fabf-2c1ecda2910so705498fac.3 for ; Tue, 04 Mar 2025 07:38:06 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741102685; x=1741707485; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=Ui/b3P7xyODB7C8mvJLbJi5gqPoJt342MibliF/WBwM=; b=NlrJAXT1oOeMMFHqIwtVofD+PcUNoET3Ta0iHsPEQ6IFfDEJsBHut2heDF7qStKEHi 4ItYISiE14U/HsTamOH+jiMbRSjFPwfSuDhofC4d/LtN29ucNydO3k+vRbqM+0anPKsB 77QEwydtA3COo2mKl6NYD4zP3yFBQZkfnYYHON7f2xo0TwO2JmBEWXsD/EqWrnGpMde+ KpKu7ahTcxkGkn7J/BVjtS1CNCD+8RD6vod2zPgaAKeiwRgXfD8Z0thZrG9yR9VGsAgv FzflZpL9k9AwbAOdKDKqqbz6RxouM/iefqt3izfTxnkjiBIP9QURJZCY5GeloKnTzMYn sn3Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741102685; x=1741707485; h=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=Ui/b3P7xyODB7C8mvJLbJi5gqPoJt342MibliF/WBwM=; b=BQPuHBpSCFbZL/fgAbB4VVdN0Yd/c2WwZdL1XB7RiLocIxemn5P/Hm8leUCpZR9yMD /qSDeNMmK25L0y0Z9zOPn3L3GnPMqGv8Y6HcjzCUu7153P3XbpNuZ13v00viLs9nE7UU PARCeclroeRXInbKmDV81B8ZU+y6fuNxzP0+ooPWcSxQLOrflpI8wMPgI/ZQg/xYlfhj u5cnAyY/UBlYP08RhvArfDKbFOg13isnBX8Zt6KdIdktjLYMxxi+A097HHJRcFp4olUJ u9IeYxNPv/Fg6MDvogieBiif2s+WWi6zp7bqsxotAzBKr2dbAI5iIZ9wqIa1kmFXc7OF 6Q3w== X-Gm-Message-State: AOJu0YyHHCvsXZdDETtStG6MXbS26bOkaBrq+QY2vo5ntqCgEfh74g/w oYbd9NTgrmfcVF3HQ1Rv0Xmoe6KX2aaKxx8erDrXRg43Z2OpgnLEVNolx9cwmr28BGOtWj6j5Y8 N+hdpkciKvb5OuPgbw9Badmo6ojvRdQ== X-Gm-Gg: ASbGnct5FQThJI2FlTK/LmoVxUgjx1J/WWL/HNOv8wDIILAWSHpCnWfiktF9EgNK5m1 Tf5juBdj90csR558lZi+ifMxxZSN5SYqHdvN3RI3DjsqP5j4YkYFE0XY3CGBXtU3AD74opvq+ia 1KQ6zQsuDcd8wm9sVI+Jz2hR630J74vXHssAizjIQ5D75ToxMddfz+fYiIBFwi X-Google-Smtp-Source: AGHT+IFG3ae5Q6XehP1ccVzQ2TGaIYxoF4mk+WZlNRAxos/GMQDcgmWtpD2oMFd9ci7KrGrj7p+eOjlKlvnWGqSTPEY= X-Received: by 2002:a05:6870:dc48:b0:296:7b65:2fac with SMTP id 586e51a60fabf-2c1782c4c86mr9574595fac.3.1741102685089; Tue, 04 Mar 2025 07:38:05 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Tue, 4 Mar 2025 10:37:53 -0500 X-Gm-Features: AQ5f1JrMga9HtF-kLuYeJPKxtRyR6FoQyndNFh6Lbdbwi14daDN8i2xkb_1r4ig Message-ID: Subject: Re: Review my steps for rollback to restore point To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000000ffb36062f860f56" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000ffb36062f860f56 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFAM chandan Kumar wrote: > Thank you for your answer. I want to clarify one more doubt. Can PITR b= e > 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 >> hosted on >> > Ubuntu VM 's on Azure. I have performed below steps on primary databas= e. >> > 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] --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000000ffb36062f860f56 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Chandran,

    For PITR, you should use a tool like PgBackRest.=C2=A0 It handles all $PG= DATA and WAL archiving.=C2=A0 It's multithreaded, too,
  1. pg_resto= re is just for logical backups.
  2. Streaming Replication is for hot= standby, not backups.

On Tue, Mar 4, 20= 25 at 10:00=E2=80=AFAM chandan Kumar <chandan.issyoga@gmail.com> wrote:
Thank you for your ans= wer.=C2=A0 I want to clarify one more doubt.=C2=A0 Can PITR be achieved wit= hout applying Base Backup

On Tue, Mar 4, 2025 at 3:12=E2=80=AFPM Laurenz Alb= e <laurenz= .albe@cybertec.at> wrote:
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=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)
[snip]
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'= m still alive.
<Redacted> lobster!
--0000000000000ffb36062f860f56--