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 1tpUQJ-006ODR-IF for pgsql-general@arkaria.postgresql.org; Tue, 04 Mar 2025 15:43:11 +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 1tpUQI-000oiF-CI for pgsql-general@arkaria.postgresql.org; Tue, 04 Mar 2025 15:43:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tpUQH-000oi3-Vp for pgsql-general@lists.postgresql.org; Tue, 04 Mar 2025 15:43:10 +0000 Received: from mail-yb1-xb29.google.com ([2607:f8b0:4864:20::b29]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tpUQE-000vN1-2B for pgsql-general@postgresql.org; Tue, 04 Mar 2025 15:43:09 +0000 Received: by mail-yb1-xb29.google.com with SMTP id 3f1490d57ef6-e53a91756e5so4719402276.1 for ; Tue, 04 Mar 2025 07:43:07 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741102986; x=1741707786; darn=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=4nG3Od0z3GsGIGKcvskQQV5QI+DtmnQR1uZijRMvers=; b=FBUl8pmy9NN5xPCeu97XWPaXvaZaInbFc+mxe0cleOJQMpDwCZv1mAbNu/iW0Kx+Kz g7lKp9pMVMBFu8SsHBK8dfNCIwSbMjT5XFLRijn/x5BO3UeyjFyXFEYSV52cVcKkKZ0q x7g2fCa+s9Ko+mYtR8ESZsXCB2pyHJkdwYzzSXTLEePBDHlnflezpIXZ0RmiIa5vtFOC Y2aUaymkM8wsA/abka+wrGhOA17GceppznpHJUGPiUoy8EjLpj5N16yX1FdrW5ymbXy1 gL7mIzRALbN5J2v6sPCl0r8ZWQlffS0OF7wMgt7W1+UYbLH6C69keOczqygaXlbAhxU3 rd5Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741102986; x=1741707786; 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=4nG3Od0z3GsGIGKcvskQQV5QI+DtmnQR1uZijRMvers=; b=OWgvPbA3lDVl3A8+iB6yX5PcK8VIXGa51AMPKt3hYvcs1KwFlK65FvgtdSQB9xmojV wySlQVRl67AX5H+TqOjhHsam7CBlkffy7kUvDiTl7ygPxFmbI9oAmketduyWAISy+8lu rTX8thm/HN+rgOQOQ428TllieQ6nV7ZHYSTaKZj16DdNTeW22L5Y4uxC5SBGGmy0Tm53 aK/hpjpEJoyH9Gwh0xjxMWT5LTyfymue4H+PpCbfep8iXZonZfKvBLJzPOLKtdLMVvXY RnYFZtpkI3rdpgoE5kupbO6Eo/LRBFpTriG+/HtIprQyU6eiGNeoDlDdZMH/KD3PQbpt V+bw== X-Gm-Message-State: AOJu0YyWJml//xBCm+EcyeozLvDT3BNvAEI5RYr2J291iMEdPEUeQ5IU YhE1oVsQSFGahECmTQrEDfmSaINgbgaKNLISV/hYhf2PdQZDVp7lL5l6myCW2wTEVgbljA9Jbx2 jruPui2WVa147gV3KUmZivw9KS7s= X-Gm-Gg: ASbGnctpOeZa944Y0b7aEAbFq0rQSX51SvRBdscNMhuu27nlCMO3RYd74cAng9nfP2g TBrkOcHoN5kMYLTWMJgqDdDKEBdAVcynx2NiaV+DzL1mEUbvAu4b2nxu3/fOqAhq/FRvqs4urHK k5F0N5EqU5xUwMI7L7BGzapxP6CQI= X-Google-Smtp-Source: AGHT+IF26+wpZaaE9MTm4o4Y3P3CiW3UKkPW8/PJzZNv6I8NjLVYqBrbeWTbk8vJiJUcYjOeptoXv94ABPdsZFY5XwI= X-Received: by 2002:a05:6902:986:b0:e20:25bb:7893 with SMTP id 3f1490d57ef6-e60b2f57455mr22857385276.46.1741102986348; Tue, 04 Mar 2025 07:43:06 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: chandan Kumar Date: Tue, 4 Mar 2025 21:12:54 +0530 X-Gm-Features: AQ5f1Jqc1EMyN_pLBmqJ_dqZn2q9BSHVuOCqt71g0OT9sFm8LLJcoYwHC2O7bkE Message-ID: Subject: Re: Review my steps for rollback to restore point To: Ron Johnson Cc: pgsql-general Content-Type: multipart/alternative; boundary="00000000000004d3c4062f86214e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000004d3c4062f86214e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thank you ! On Tue, Mar 4, 2025 at 9:08=E2=80=AFPM Ron Johnson wrote: > 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 >> 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 >>> 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) >>> >> [snip] > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --=20 *With warm regards* * Chandan* --00000000000004d3c4062f86214e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you !

On Tue, Mar 4, 2025 at 9:= 08=E2=80=AFPM Ron Johnson <ro= nljohnsonjr@gmail.com> wrote:
Chandran,

  1. For PITR, you should use a tool like PgBackRest.=C2= =A0 It handles all $PGDATA and WAL archiving.=C2=A0 It's multithreaded,= too,
  2. pg_restore is just for logical backups.
  3. Streaming Rep= lication 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:
Than= k you for your answer.=C2=A0 I want to clarify one more doubt.=C2=A0 Can PI= TR be achieved without applying Base Backup

On Tue, Mar 4, 2025 at 3:12=E2= =80=AFPM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Tue, 2025-03-04 at 14:26 +0530, cha= ndan 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!


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