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 1qskIQ-009Bh1-KI for pgsql-general@arkaria.postgresql.org; Tue, 17 Oct 2023 13:39:42 +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 1qskIO-009gFk-7P for pgsql-general@arkaria.postgresql.org; Tue, 17 Oct 2023 13:39:40 +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 1qskIN-009gFc-S1 for pgsql-general@lists.postgresql.org; Tue, 17 Oct 2023 13:39:40 +0000 Received: from mail-yw1-x1136.google.com ([2607:f8b0:4864:20::1136]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qskIK-001BNt-Gf for pgsql-general@lists.postgresql.org; Tue, 17 Oct 2023 13:39:39 +0000 Received: by mail-yw1-x1136.google.com with SMTP id 00721157ae682-5a7b3d33663so77009817b3.3 for ; Tue, 17 Oct 2023 06:39:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1697549976; x=1698154776; darn=lists.postgresql.org; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=5Qzj9PfGD54axQrpSSYtNv3WTEABqXqL5VgTtaZVqgQ=; b=a4vDBRqLfApzwdKnilwlSKmRpLYBjjYTXsnLuUkP+WDz4WIaPq+AnVHnN7QeCdiwJa TS4tlXw6TGJ4Gn6o3e6nZBSuDlKWb8bG0ZKiUk6dro/blhMAKNXCnM2JN15b2qHMq1Ys LQsvn3257rdNIWNhHD0R/z9RniM8KreJaAa9msvCI2VWpWKNAympf1g2YszYF/VkRMj2 z2/7RvjhLX0a5rA+yFg/CNcNP3T91PEEjF26rJs4479BAP16+HcbA9WjkfRFjytpL/Fw y9wL6Cv+2CMSHWnzfQLUT5jmvbuzSFJcAMGf1TqE9Yq96RHJODhS8Sz+fMq+eotORffn QAog== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697549976; x=1698154776; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=5Qzj9PfGD54axQrpSSYtNv3WTEABqXqL5VgTtaZVqgQ=; b=cybAs7b4GyO92AXoCGtqj/uL7t5wf6JnQgomy3DmiDRRYXLxyAK+Cu5Q7kDA/fv1dc /5O50bTZbPrdBlfrLS8EOdNXMuzhrk3PsFJxIA2MkNmgOFSJVZOJIjPSuumUOXNWKXNc sc363oSSfpFoCO8AAAuhfzcEfu3JiF74zqw0zFtigGn6mqg3mx6qABbn/1ARMlmundmu JBfJ1nJlAylRf9ttHF52ThTVIyxdXRmz0t3EAwYKzi6zPK8WH2XOEjY0ofupUmmLyNyb Z34FiqMjs8I9jgwXRJI6PFmLX52lu3ebxGYaDafQU9sqATJI+STMiEWzVchGLudqrB7v mQBw== X-Gm-Message-State: AOJu0Yz+VFj0exCHljvnAhCktzDW3nZg6Gr/V/OJFBxOgyGaQfttEaHX TUMlBdr6Ycj79M93KCzMxGOOYSO/tR4= X-Google-Smtp-Source: AGHT+IFjFH5EvenfN43+q2MaxQhQWI4LiNU/zKVs32iPmgcaVZP3ITD+Ze7sO5GJVXMaUqhg/txA0g== X-Received: by 2002:a81:9182:0:b0:5a8:207b:876a with SMTP id i124-20020a819182000000b005a8207b876amr2575084ywg.14.1697549975788; Tue, 17 Oct 2023 06:39:35 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:7c18:e478:1f0:7aca? ([2600:1700:bd2c:b810:7c18:e478:1f0:7aca]) by smtp.googlemail.com with ESMTPSA id w128-20020a816286000000b00582fae92aa7sm615864ywb.93.2023.10.17.06.39.35 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 17 Oct 2023 06:39:35 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------TBbvNbsxHR0IsOUOP19hy9yg" Message-ID: Date: Tue, 17 Oct 2023 08:39:35 -0500 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: PITR partial timing Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------TBbvNbsxHR0IsOUOP19hy9yg Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Don't write your own PITR.  Use PgBackRest, barman, etc. On 10/16/23 12:08, Rama Krishnan wrote: > Hi team, > > I  m testing PITR > > 1.if I am doing full recovery what ever the new tables were create will > not be appear how to handle it > > 2. I have given my logs which time log I have to choose for PITR partial > recovery > > > 2023-10-16 16:22:35 UTC [16561]: [13-1] user=postgres,db=postgresLOG: >  statement: create database pumastore; > 2023-10-16 16:22:35 UTC [16561]: [14-1] user=postgres,db=postgresLOG: >  duration: 70.433 ms > 2023-10-16 16:23:02 UTC [16561]: [15-1] user=postgres,db=postgresLOG: >  statement: CREATE TABLE categories ( >    category integer NOT NULL, >    categoryname character varying(50) NOT NULL > ); > 2023-10-16 16:23:02 UTC [16561]: [16-1] user=postgres,db=postgresLOG: >  duration: 6.328 ms > 2023-10-16 16:23:28 UTC [16561]: [17-1] user=postgres,db=postgresLOG: >  statement: COPY categories (category, categoryname) FROM stdin; > 2023-10-16 16:23:41 UTC [16561]: [18-1] user=postgres,db=postgresLOG: >  duration: 12695.122 ms > 2023-10-16 16:23:52 UTC [16561]: [19-1] user=postgres,db=postgresLOG: >  statement: table categories; > 2023-10-16 16:23:52 UTC [16561]: [20-1] user=postgres,db=postgresLOG: >  duration: 0.573 ms > 2023-10-16 16:24:20 UTC [16561]: [21-1] user=postgres,db=postgresLOG: >  statement: select pg_switch_wal(); > 2023-10-16 16:24:20 UTC [16561]: [22-1] user=postgres,db=postgresLOG: >  duration: 61.904 ms > > 2023-10-16 16:26:25 UTC [16561]: [23-1] user=postgres,db=postgresLOG: >  statement: drop table categories; > 2023-10-16 16:26:25 UTC [16561]: [24-1] user=postgres,db=postgresLOG: >  duration: 5.165 ms > -- Born in Arizona, moved to Babylonia. --------------TBbvNbsxHR0IsOUOP19hy9yg Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Don't write your own PITR.  Use PgBackRest, barman, etc.

On 10/16/23 12:08, Rama Krishnan wrote:
Hi team,

I  m testing PITR 

1.if I am doing full recovery what ever the new tables were create will not be appear how to handle it

2. I have given my logs which time log I have to choose for PITR partial recovery


2023-10-16 16:22:35 UTC [16561]: [13-1] user=postgres,db=postgresLOG:  statement: create database pumastore;
2023-10-16 16:22:35 UTC [16561]: [14-1] user=postgres,db=postgresLOG:  duration: 70.433 ms
2023-10-16 16:23:02 UTC [16561]: [15-1] user=postgres,db=postgresLOG:  statement: CREATE TABLE categories (
   category integer NOT NULL,
   categoryname character varying(50) NOT NULL
);
2023-10-16 16:23:02 UTC [16561]: [16-1] user=postgres,db=postgresLOG:  duration: 6.328 ms
2023-10-16 16:23:28 UTC [16561]: [17-1] user=postgres,db=postgresLOG:  statement: COPY categories (category, categoryname) FROM stdin;
2023-10-16 16:23:41 UTC [16561]: [18-1] user=postgres,db=postgresLOG:  duration: 12695.122 ms
2023-10-16 16:23:52 UTC [16561]: [19-1] user=postgres,db=postgresLOG:  statement: table categories;
2023-10-16 16:23:52 UTC [16561]: [20-1] user=postgres,db=postgresLOG:  duration: 0.573 ms
2023-10-16 16:24:20 UTC [16561]: [21-1] user=postgres,db=postgresLOG:  statement: select pg_switch_wal();
2023-10-16 16:24:20 UTC [16561]: [22-1] user=postgres,db=postgresLOG:  duration: 61.904 ms

2023-10-16 16:26:25 UTC [16561]: [23-1] user=postgres,db=postgresLOG:  statement: drop table categories;
2023-10-16 16:26:25 UTC [16561]: [24-1] user=postgres,db=postgresLOG:  duration: 5.165 ms


--
Born in Arizona, moved to Babylonia.
--------------TBbvNbsxHR0IsOUOP19hy9yg--