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 1tJd2k-004GGk-Uk for pgsql-hackers@arkaria.postgresql.org; Fri, 06 Dec 2024 18:27: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 1tJd2g-00DIAQ-AC for pgsql-hackers@arkaria.postgresql.org; Fri, 06 Dec 2024 18:27:07 +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 1tJd2f-00DIAI-Ug for pgsql-hackers@lists.postgresql.org; Fri, 06 Dec 2024 18:27:07 +0000 Received: from mail-pj1-x102c.google.com ([2607:f8b0:4864:20::102c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tJd2d-001OWZ-Iv for pgsql-hackers@lists.postgresql.org; Fri, 06 Dec 2024 18:27:06 +0000 Received: by mail-pj1-x102c.google.com with SMTP id 98e67ed59e1d1-2ee6c2d6db0so2144198a91.1 for ; Fri, 06 Dec 2024 10:27:03 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1733509621; x=1734114421; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=MoVZbapsCzt7/wLU9ltvhdeSD4y3iBE18VQK3oI4Kak=; b=fHU2YkfgH+bfGcidMgjyhrDbWtTvLMl9zgaZinx88Sg5ve8kvMTctNX5BKP9GimmX0 VOyxzIN//uQrqeLoVd/qITFScKEI4AwoiF8WkoPtKBh6SQ16MM7bjo9G/1X9WsCm2kaO qv1w8Rw7CN2nQ9P4JOm+NEe2n/qsQwAw9OKQUoD2NaMIc+YPJQXtBynNdodGbM0GgevT FLj9gukEj1kQrN0cbtSZUlpHHnb6pKrs61gsZepj4w8+DJIToEW/K9EUoKmSqRigSFib JgOvGPq/d6PjwjdtKi/MNwKDgeiTBt2JySXXdlUJCy+ZkwthkX/wuC1hUpCoF2J1RH08 JHXg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1733509621; x=1734114421; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=MoVZbapsCzt7/wLU9ltvhdeSD4y3iBE18VQK3oI4Kak=; b=lUgFNZiZpTxpOrgGXfK6JQSncqYsolFs0lq/2FuY/BgQxGapx3mcxYrW5ElIIbusY7 eASOFp1XyF8x4QRfV0YyMD9veDG0jq54RqnTL66UYWLIDazH2xrRTJb096cyNaScuGed gnAc17zB/f+loKkug1/VbZUu0B0CiD3oNkoyWkCPSIz9L0y9tI3ydbYaMFVTnkSFVY6H iwQHTvMZ6QZSsKhB06kHm9NhTdwolmUiqcof8vCIWg5qk20h1PWT0ZLuh9V4dwDFnaB1 BfZeGZBuvvfHJLhraS+9cqDf68NRgVPzvXlE9gnHeTWC4XGKU9AgCZsUB1S8LkbZmDex g1ow== X-Gm-Message-State: AOJu0YyAtz/qKN9EOpXoAxVPb1lQg4r8IAm97wAY/uEKNb3CZbqwsnAF pQc8aiocHK3sg35pTI37moI1KzMGlKuM1ztcHY+xSxNzZgtNsrFk+PWrRP2Q6Qb/VGZFAR1DKMM xWXxPi3H1JQOpmxPrfG0+6tq9SuVWyhOd X-Gm-Gg: ASbGncuKVlzST55MEhhp0PtN2TaFVTWmp73SgpGMQI+LH6LXbc/w9PvFIW3DCp+6dtD LAWb5wPW7bItXZ1DsQSv2sQIG8aoPRA== X-Google-Smtp-Source: AGHT+IHgkl8cfqTuAlE65B69JIeyV//n6vIfp5w/wIWQa0VLLKD1IbHVz1XpwEO+e9YMSf6jcPgnwbm/wpNhDGtApcs= X-Received: by 2002:a17:90a:e70c:b0:2ee:cddd:2454 with SMTP id 98e67ed59e1d1-2ef69e15294mr7237238a91.15.1733509621106; Fri, 06 Dec 2024 10:27:01 -0800 (PST) MIME-Version: 1.0 From: Scott Taylor Date: Fri, 6 Dec 2024 13:26:51 -0500 Message-ID: Subject: Unable to Recover a Deleted Database Using PITR To: pgsql-hackers@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000002e54b206289e2979" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002e54b206289e2979 Content-Type: text/plain; charset="UTF-8" Created database randomdata with one table for test purposes of point in time recovery. However, the database did not fully restore. This consistently does not work. Am I missing a step or concept about how PITR works or can you not restore a delete database using PITR? I am using postgres version: PostgreSQL 17.2 on x86_64-windows Steps: 1) Updated postgresql.conf: archive_mode = on archive_command = 'copy "%p" "C:\\PostgresArchive\\Wal-Archive\\%f"' log_statement = mod summarize_wal = on 2) Re-started postgres server 3) Created a database "randomdata" and table "somedata", inserted data: *2024-12-02 13:57:24*.049 EST [31268] LOG: statement: insert into somedata (serialnumber, firstname, lastname) 4) Ran pg_basebackup 5) Dropped database: * 2024-12-02 14:01:27*.243 EST [19148] LOG: statement: DROP DATABASE randomdata; 6) Stopped postgres server 7) Removed contents of data folder: C:\Program Files\PostgreSQL\17\data 8) Extracted base.tar.gz (from pg_basebackup) into C:\Program Files\PostgreSQL\17\data 9) Removed contents of pg_wal folder: C:\Program Files\PostgreSQL\17\data\pg_wal 10) Added to postgresql.conf: restore_command = 'copy "C:\\PostgresArchive\\Wal-Archive\\%f" "%p"' recovery_target_time = '*2024-12-02 13:57:24*' (time from log file when insert occured, see step 2) recovery_target_action = promote 11) Created recovery.signal file in postgres data folder 12) Started postgres server 13) Attempted to connect to database using psql: postgres=# \c randomdata; connection to server at "localhost" (::1), port 5432 failed: FATAL: cannot connect to invalid database "randomdata" HINT: Use DROP DATABASE to drop invalid databases. Previous connection kept 14) Noticed recovery.signal file was removed *Log file:* 2024-12-02 13:57:23.852 EST [32352] LOG: statement: create database randomdata; 2024-12-02 13:57:24.043 EST [31268] LOG: statement: create table somedata (id serial primary key, serialnumber integer, firstname text, lastname text); 2024-12-02 13:57:24.049 EST [31268] LOG: statement: insert into somedata (serialnumber, firstname, lastname) *- Ran pg_basebackup* *- Dropped database* 2024-12-02 14:01:27.243 EST [19148] LOG: statement: DROP DATABASE randomdata; *- Stopped server* *- Deleted Data folder* *- Restored from backup file* *- Added restore_command, recovery_target_time, recovery_target_action to postgresql.conf* *- Created reovery.signal file* *- Restarted server* 2024-12-02 14:04:21.630 EST [20156] LOG: database system was interrupted; last known up at 2024-12-02 14:00:22 EST 2024-12-02 14:04:21.630 EST [20156] LOG: creating missing WAL directory "pg_wal/archive_status" 2024-12-02 14:04:21.630 EST [20156] LOG: creating missing WAL directory "pg_wal/summaries" 2024-12-02 14:04:22.792 EST [20156] LOG: starting backup recovery with redo LSN 0/3000028, checkpoint LSN 0/3000080, on timeline ID 1 2024-12-02 14:04:22.805 EST [20156] LOG: restored log file "000000010000000000000003" from archive 2024-12-02 14:04:22.818 EST [20156] LOG: starting point-in-time recovery to 2024-12-02 13:57:24-05 2024-12-02 14:04:22.825 EST [20156] LOG: redo starts at 0/3000028 2024-12-02 14:04:22.826 EST [20156] LOG: completed backup recovery with redo LSN 0/3000028 and end LSN 0/3000120 2024-12-02 14:04:22.826 EST [20156] LOG: consistent recovery state reached at 0/3000120 2024-12-02 14:04:22.843 EST [20156] LOG: restored log file "000000010000000000000004" from archive 2024-12-02 14:04:22.871 EST [20156] LOG: recovery stopping before commit of transaction 969, time 2024-12-02 14:01:27.281164-05 2024-12-02 14:04:22.871 EST [20156] LOG: redo done at 0/40006F8 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.04 s 2024-12-02 14:04:22.879 EST [20156] LOG: selected new timeline ID: 2 2024-12-02 14:04:22.940 EST [20156] LOG: archive recovery complete 2024-12-02 14:04:22.941 EST [30656] LOG: checkpoint starting: end-of-recovery immediate wait 2024-12-02 14:04:22.952 EST [30656] LOG: checkpoint complete: wrote 4 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.001 s, sync=0.003 s, total=0.012 s; sync files=3, longest=0.001 s, average=0.001 s; distance=16385 kB, estimate=16385 kB; lsn=0/40006F8, redo lsn=0/40006F8 2024-12-02 14:05:38.142 EST [15876] FATAL: cannot connect to invalid database "randomdata" 2024-12-02 14:05:38.142 EST [15876] HINT: Use DROP DATABASE to drop invalid databases. --0000000000002e54b206289e2979 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Created database randomdata with one table for=C2=A0t= est purposes of point in time recovery.
However, the database did= not fully restore.
This consistently does not work.
Am I missing=C2=A0a step or concept about how PITR works or can you not r= estore a delete database using PITR?
I am using postgre= s=C2=A0version: PostgreSQL 17.2 on x86_64-windows

=
Steps:
1) Updated postgresql.conf:
=C2= =A0 =C2=A0 archive_mode =3D on
=C2=A0 =C2=A0 archive_command =3D 'co= py "%p" "C:\\PostgresArchive\\Wal-Archive\\%f"'
= =C2=A0 =C2=A0 log_statement =3D mod
=C2=A0 =C2=A0 summarize_w= al =3D on

2) Re-started postgres server
=
3) Created a database "randomdata" and table "= ;somedata", inserted data:
=C2=A0 =C2=A0=C2=A02024-12-02 = 13:57:24.049 EST [31268] LOG: =C2=A0statement: insert into somedata (se= rialnumber, firstname, lastname)

4) Ran=C2=A0pg_ba= sebackup

5) Dropped database:
=C2=A0 =C2= =A0=C2=A02024-12-02 14:01:27.243 EST [19148] LOG: =C2=A0statement: D= ROP DATABASE randomdata;

6) Stopped postgres serve= r

7) Removed contents of data folder:=C2=A0C:\Prog= ram Files\PostgreSQL\17\data

8) Extracted=C2=A0bas= e.tar.gz (from pg_basebackup) into C:\Program Files\PostgreSQL\17\data

9) Removed contents of pg_wal folder:=C2=A0C:\Program = Files\PostgreSQL\17\data\pg_wal

10) Added to postg= resql.conf:
=C2=A0 =C2=A0=C2=A0restore_command =3D 'copy &quo= t;C:\\PostgresArchive\\Wal-Archive\\%f" "%p"'
= =C2=A0 =C2=A0 recovery_target_time =3D '2024-12-02 13:57:24'= (time from log file when insert occured, see step 2)
=C2=A0 =C2=A0 reco= very_target_action =3D promote

11) Created=C2=A0re= covery.signal file in postgres data folder

12) Sta= rted postgres server

13) Attempted to connect to d= atabase using psql:
=C2=A0 =C2=A0 postgres=3D# \c randomdata;=C2=A0 =C2=A0 connection to server at "localhost" (::1), port 5= 432 failed: FATAL: =C2=A0cannot connect to invalid=C2=A0 =C2=A0 database &q= uot;randomdata"
=C2=A0 =C2=A0 HINT: =C2=A0Use DROP DATABASE to drop= invalid databases.
=C2=A0 =C2=A0 Previous connection kept

14) Noticed recovery.signal file was removed

Log file:
2024-12-02 13:57:23.852 EST [32352] LOG:= =C2=A0statement: create database randomdata;
2024-12-02 13:57:24.043 ES= T [31268] LOG: =C2=A0statement: create table somedata (id serial primary ke= y, serialnumber integer, firstname text, lastname text);
2024-12-02 13:5= 7:24.049 EST [31268] LOG: =C2=A0statement: insert into somedata (serialnumb= er, firstname, lastname)

- Ran pg_basebackup
=

- Dropped database
2024-12-02 14:01:27= .243 EST [19148] LOG: =C2=A0statement: DROP DATABASE randomdata;

- Stopped server
- Deleted Data folder
- Restored from backup file
- Added restore_comma= nd, recovery_target_time, recovery_target_action to postgresql.conf
- Created reovery.signal=C2=A0file
- Restarted s= erver

2024-12-02 14:04:21.630 EST [20156] LOG:= =C2=A0database system was interrupted; last known up at 2024-12-02 14:00:2= 2 EST
2024-12-02 14:04:21.630 EST [20156] LOG: =C2=A0creating missing WA= L directory "pg_wal/archive_status"
2024-12-02 14:04:21.630 ES= T [20156] LOG: =C2=A0creating missing WAL directory "pg_wal/summaries&= quot;
2024-12-02 14:04:22.792 EST [20156] LOG: =C2=A0starting backup rec= overy with redo LSN 0/3000028, checkpoint LSN 0/3000080, on timeline ID 12024-12-02 14:04:22.805 EST [20156] LOG: =C2=A0restored log file "00= 0000010000000000000003" from archive
2024-12-02 14:04:22.818 EST [2= 0156] LOG: =C2=A0starting point-in-time recovery to 2024-12-02 13:57:24-05<= br>2024-12-02 14:04:22.825 EST [20156] LOG: =C2=A0redo starts at 0/3000028<= br>2024-12-02 14:04:22.826 EST [20156] LOG: =C2=A0completed backup recovery= with redo LSN 0/3000028 and end LSN 0/3000120
2024-12-02 14:04:22.826 E= ST [20156] LOG: =C2=A0consistent recovery state reached at 0/3000120
202= 4-12-02 14:04:22.843 EST [20156] LOG: =C2=A0restored log file "0000000= 10000000000000004" from archive
2024-12-02 14:04:22.871 EST [20156]= LOG: =C2=A0recovery stopping before commit of transaction 969, time 2024-1= 2-02 14:01:27.281164-05
2024-12-02 14:04:22.871 EST [20156] LOG: =C2=A0r= edo done at 0/40006F8 system usage: CPU: user: 0.00 s, system: 0.00 s, elap= sed: 0.04 s
2024-12-02 14:04:22.879 EST [20156] LOG: =C2=A0selected new = timeline ID: 2
2024-12-02 14:04:22.940 EST [20156] LOG: =C2=A0archive re= covery complete
2024-12-02 14:04:22.941 EST [30656] LOG: =C2=A0checkpoin= t starting: end-of-recovery immediate wait
2024-12-02 14:04:22.952 EST [= 30656] LOG: =C2=A0checkpoint complete: wrote 4 buffers (0.0%); 0 WAL file(s= ) added, 0 removed, 1 recycled; write=3D0.001 s, sync=3D0.003 s, total=3D0.= 012 s; sync files=3D3, longest=3D0.001 s, average=3D0.001 s; distance=3D163= 85 kB, estimate=3D16385 kB; lsn=3D0/40006F8, redo lsn=3D0/40006F8

20= 24-12-02 14:05:38.142 EST [15876] FATAL: =C2=A0cannot connect to invalid da= tabase "randomdata"
2024-12-02 14:05:38.142 EST [15876] HINT: = =C2=A0Use DROP DATABASE to drop invalid databases.

--0000000000002e54b206289e2979--