Point in time Postgres restore

Question

I was trying to do Point in Time Restore, I had my placed by previously backed up WAL file in my directory to where my restore command (/var/lib/postgresql/16/main/pg_wal/wal_archive) in postgres.conf points to, however my WAL files changes have not been applied:

listen_addresses = '*'
wal_level = replica
max_connections = 100
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/16/main/pg_wal/wal_archive/%f && cp %p /var/lib/postgresql/16/main/pg_wal/wal_archive/%f'
restore_command = 'cp /var/lib/postgresql/16/main/pg_wal/wal_archive/%f %p'
#max_wal_senders = 10
#wal_log_hints = on

And I have created empty recovery.signal file, and here is logs from restarting

Postgres(my backed up file is named 000000010000000000000039):
2024年02月23日 17:25:02.199 UTC [68476] LOG: starting PostgreSQL 16.2 (Debian 16.2-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2024年02月23日 17:25:02.199 UTC [68476] LOG: listening on IPv4 address "0.0.0.0", port 5432
2024年02月23日 17:25:02.199 UTC [68476] LOG: listening on IPv6 address "::", port 5432
2024年02月23日 17:25:02.200 UTC [68476] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024年02月23日 17:25:02.204 UTC [68479] LOG: database system was shut down at 2024年02月23日 17:25:02 UTC
cp: cannot stat '/var/lib/postgresql/16/main/pg_wal/wal_archive/00000003.history': No such file or directory
2024年02月23日 17:25:02.207 UTC [68479] LOG: starting archive recovery
2024年02月23日 17:25:02.209 UTC [68479] LOG: restored log file "00000002.history" from archive
cp: cannot stat '/var/lib/postgresql/16/main/pg_wal/wal_archive/00000002000000000000003A': No such file or directory
2024年02月23日 17:25:02.216 UTC [68479] LOG: consistent recovery state reached at 0/3A0000A0
2024年02月23日 17:25:02.216 UTC [68479] LOG: invalid record length at 0/3A0000A0: expected at least 24, got 0
2024年02月23日 17:25:02.216 UTC [68479] LOG: redo is not required
2024年02月23日 17:25:02.217 UTC [68476] LOG: database system is ready to accept read-only connections
cp: cannot stat '/var/lib/postgresql/16/main/pg_wal/wal_archive/00000002000000000000003A': No such file or directory
cp: cannot stat '/var/lib/postgresql/16/main/pg_wal/wal_archive/00000003.history': No such file or directory
2024年02月23日 17:25:02.223 UTC [68479] LOG: selected new timeline ID: 3
2024年02月23日 17:25:02.263 UTC [68479] LOG: restored log file "00000002.history" from archive
2024年02月23日 17:25:02.264 UTC [68479] LOG: archive recovery complete
2024年02月23日 17:25:02.265 UTC [68477] LOG: checkpoint starting: end-of-recovery immediate wait
2024年02月23日 17:25:02.270 UTC [68477] LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.001 s, total=0.006 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB; lsn=0/3A0000A0, redo lsn=0/3A0000A0
2024年02月23日 17:25:02.275 UTC [68476] LOG: database system is ready to accept connections

• jjanes: The first line of your 'log' seems to be an editorial comment, and not something one would actually find in a log.
• jjanes: You describe putting some WAL into a directory, but you don't describe how that directory itself got there in order to put the WAL into it. You need to start by restoring a back backup, and you didn't describe doing that. I can't tell if you didn't do such a restoration, or just didn't describe doing it.

Answer 1

Clearly, your server went beyond the point to which you're trying to restore, so you can't revert it back to what it was at a prior point in time without restoring a base backup from an even earlier point.

Answer 2

The log looks like recovery went well. If you are missing some data, those were probably stored in the current WAL segment, which was not yet archived. You can either perform a clean shutdown, which will archive the current WAL segment, or you could use pg_receivewal instead of archive_command, which allows you to archive the active WAL segment as well.

/var/lib/postgresql/16/main/pg_wal/wal_archive is a horrible place to store WAL archives. You must not store them in the data directory, and you should store them on different hardware, as far away from your database as possible.