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
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
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)
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.
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.