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 1tIYRj-00F2eP-BL for pgsql-general@arkaria.postgresql.org; Tue, 03 Dec 2024 19:20:31 +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 1tIYRg-00CFEC-NK for pgsql-general@arkaria.postgresql.org; Tue, 03 Dec 2024 19:20:29 +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 1tICmO-0032Qc-KY for pgsql-general@lists.postgresql.org; Mon, 02 Dec 2024 20:12:25 +0000 Received: from mail-pg1-x52e.google.com ([2607:f8b0:4864:20::52e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tICmM-000hxK-U8 for pgsql-general@postgresql.org; Mon, 02 Dec 2024 20:12:25 +0000 Received: by mail-pg1-x52e.google.com with SMTP id 41be03b00d2f7-7fc340eb006so3404414a12.0 for ; Mon, 02 Dec 2024 12:12:23 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1733170341; x=1733775141; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=E9mDSPWeGudmc/MGHSrfDO0ZWB98NsqK7ewLqzvJD48=; b=b6NksGO+jT2pVdisZS7vjVglfUFXP1TmkuHXG5ZU8woAAo1EfCx88imNW3YVSk//kp lp06EjGhTf0vFCz+wwgtODjndF3VWCImXicSds/uNadZgoa7Ul7q6oS8MIsJV2J9x+t/ PoSlshOoi6NPgqn20LaD8jgbMldNy+HbtUphfp8YuEp008gyXhaNWiRO4gQcjjGNwxfw TN0F3R8lD6MV/7LXow0RwYmz3Fqr2xwcWWy7Ma7fSjSOfdENP4YPK8PROc4a4k7wDWx2 a+GxFZD5Iv4ZxOMtfDjA7QYdsYG6QUVLHakJ+zpqDkm/0WneI8WfoXvqOL9IfOpQtbaL nTkg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1733170341; x=1733775141; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=E9mDSPWeGudmc/MGHSrfDO0ZWB98NsqK7ewLqzvJD48=; b=YA6yKGcTKz10XngEH8gMW9n29tu4rH1aGfY7qW7+Zt0D3cVwPst3YJzdpocCoQKbHQ vKaemnYtwQosvdp0O7jwGPnmWvo1xsfNHh2PeZTq3VNuCrtu7+7g/Ulpe3p4HwG6f5V+ LNioE6yBDMvrkOcUk8Ivao+zNw4OAiN7x5lgfGCnM/G+EOUPPOtHZ/fK0Go2Xt2HqAOU iJopZc2eGTpWriHd8A03P89kdoYVy617YFWQr9KqP9HlDKqAoop7JAr/eGnHfKbEc60G 1DIeqe5ReMljsC7FKdHcWdft3l1HNTRa7ZjxOdDOEkuupRAQKfP1v6UH6RELFJ+OtHNN 0k5A== X-Gm-Message-State: AOJu0YwkUDyXQLmyxppmnrOJ6uxBG8Kn5ClknBxkXnkDPtjkaObEwTDb 23AQ3t7FG3SiWoT6bhRmFryn3Y6Df3E/2DK2jyu0vOTiU0ogn6FKFDLbJHibcrdICsFY1fZMbUj SnBPWLlSxLx/O/vPpuTtddPkyWZLrxaxdnWI= X-Gm-Gg: ASbGncvKfcFSJcMDPiuJBCb283p6k1JSRb33fZyeYUaqmv9wSMWWjZG0d5QOUy1wl7T tDfnxMxkWSUA0UxtDBvYua1Erz7vpJm4= X-Google-Smtp-Source: AGHT+IEi+9r1bz9xhcyWX3VHKM2YLgUjFCKWjVanBJJqNovKPLYVg63fC4U75zg+OUi7buxPc+hMmGNnruCF/GNrZMg= X-Received: by 2002:a17:90b:52c3:b0:2ee:5691:774e with SMTP id 98e67ed59e1d1-2ee56917798mr20510329a91.2.1733170340839; Mon, 02 Dec 2024 12:12:20 -0800 (PST) MIME-Version: 1.0 From: Scott Taylor Date: Mon, 2 Dec 2024 15:12:10 -0500 Message-ID: Subject: Unable to Recover a Deleted Database Using PITR To: pgsql-general@postgresql.org Content-Type: multipart/alternative; boundary="00000000000080491a06284f2a52" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000080491a06284f2a52 Content-Type: text/plain; charset="UTF-8" Database randomdata did not fully restore. This consistently does not work. Am I missing a step or concept about how PITR works? 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 a table, 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* 2024-12-02 14:01:27.243 EST [19148] LOG: statement: DROP DATABASE randomdata; *- Deleted Data folder, then restored from backup file and 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. --00000000000080491a06284f2a52 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Database randomdata did not fully restore.
Th= is consistently does not work.
Am I missing=C2=A0a step or = concept about how PITR works?
I am using postgres=C2=A0= version: 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 '= ;copy "%p" "C:\\PostgresArchive\\Wal-Archive\\%f"'<= br>=C2=A0 =C2=A0 log_statement =3D mod
=C2=A0 =C2=A0 summariz= e_wal =3D on

2) Re-started postgres server

3) Created a database "randomdata" and a table,= inserted data:
=C2=A0 =C2=A0=C2=A02024-12-02 13:57:24.049= EST [31268] LOG: =C2=A0statement: insert into somedata (serialnumber, firs= tname, lastname)

4) Ran=C2=A0pg_basebackup

5) Dropped database:
=C2=A0 =C2=A0=C2=A02024= -12-02 14:01:27.243 EST [19148] LOG: =C2=A0statement: DROP DATABASE ran= domdata;

6) Stopped postgres server

=
7) Removed contents of data folder:=C2=A0C:\Program Files\Postgr= eSQL\17\data

8) Extracted=C2=A0base.tar.gz (from p= g_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 postgresql.conf:
=C2=A0 =C2=A0=C2=A0restore_command =3D 'copy "C:\\PostgresAr= chive\\Wal-Archive\\%f" "%p"'
=C2=A0 =C2=A0 re= covery_target_time =3D '2024-12-02 13:57:24' (time from log = file when insert occured, see step 2)
=C2=A0 =C2=A0 recovery_target_acti= on =3D promote

11) Created=C2=A0recovery.signal fi= le in postgres data folder

12) Started postgres se= rver

13) Attempted to connect to database using ps= ql:
=C2=A0 =C2=A0 postgres=3D# \c randomdata;
=C2=A0 =C2=A0 = connection to server at "localhost" (::1), port 5432 failed: FATA= L: =C2=A0cannot connect to invalid=C2=A0 =C2=A0 database "randomdata&q= uot;
=C2=A0 =C2=A0 HINT: =C2=A0Use DROP DATABASE to drop invalid databas= es.
=C2=A0 =C2=A0 Previous connection kept

14) Notice= d 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 EST [31268] LOG: = =C2=A0statement: create table somedata (id serial primary key, serialnumber= integer, firstname text, lastname text);
2024-12-02 13:57:24.049 EST [3= 1268] LOG: =C2=A0statement: insert into somedata (serialnumber, firstname, = lastname)

- Ran pg_basebackup

=
2024-12-02 14:01:27.243 EST [19148] LOG: =C2=A0statement: DROP DATABAS= E randomdata;

- Deleted Data folder, then restored fro= m backup file and restarted server

2024-12-02 = 14:04:21.630 EST [20156] LOG: =C2=A0database system was interrupted; last k= nown up at 2024-12-02 14:00:22 EST
2024-12-02 14:04:21.630 EST [20156] L= OG: =C2=A0creating missing WAL directory "pg_wal/archive_status"<= br>2024-12-02 14:04:21.630 EST [20156] LOG: =C2=A0creating missing WAL dire= ctory "pg_wal/summaries"
2024-12-02 14:04:22.792 EST [20156] L= OG: =C2=A0starting 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: =C2= =A0restored log file "000000010000000000000003" from archive
2= 024-12-02 14:04:22.818 EST [20156] LOG: =C2=A0starting point-in-time recove= ry to 2024-12-02 13:57:24-05
2024-12-02 14:04:22.825 EST [20156] LOG: = =C2=A0redo starts at 0/3000028
2024-12-02 14:04:22.826 EST [20156] LOG: = =C2=A0completed backup recovery with redo LSN 0/3000028 and end LSN 0/30001= 20
2024-12-02 14:04:22.826 EST [20156] LOG: =C2=A0consistent recovery st= ate reached at 0/3000120
2024-12-02 14:04:22.843 EST [20156] LOG: =C2=A0= restored log file "000000010000000000000004" from archive
2024= -12-02 14:04:22.871 EST [20156] LOG: =C2=A0recovery 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: =C2=A0redo done at 0/40006F8 system usage: CPU: use= r: 0.00 s, system: 0.00 s, elapsed: 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 ES= T [20156] LOG: =C2=A0archive recovery complete
2024-12-02 14:04:22.941 E= ST [30656] LOG: =C2=A0checkpoint starting: end-of-recovery immediate wait2024-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.00= 1 s, sync=3D0.003 s, total=3D0.012 s; sync files=3D3, longest=3D0.001 s, av= erage=3D0.001 s; distance=3D16385 kB, estimate=3D16385 kB; lsn=3D0/40006F8,= redo lsn=3D0/40006F8

2024-12-02 14:05:38.142 EST [15876] FATAL: =C2= =A0cannot connect to invalid database "randomdata"
2024-12-02 = 14:05:38.142 EST [15876] HINT: =C2=A0Use DROP DATABASE to drop invalid data= bases.
--00000000000080491a06284f2a52--