0

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
2
  • The first line of your 'log' seems to be an editorial comment, and not something one would actually find in a log.
    – jjanes
    Commented Feb 23 at 19:48
  • 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.
    – jjanes
    Commented Feb 23 at 19:52

2 Answers 2

0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.