I am trying to restore data in my database. How can I restore data from WAL without any database backups? Can I generate a backup file from WAL logs?
2 Answers
If you only have the WAL, with no base backup (copy of the data directory, pg_basebackup, etc), you cannot restore. Full stop. And no, you cannot use a dump from pg_dump to restore WAL on top of.
WAL only contains changes to the data directory, and is meaningless without a base backup to apply it to.
Imagine you have a half page from one of your bank statements, without a running balance shown in a column. You want to use it to find out the balance on your account. You cannot possibly do that, since you don't know the starting or ending balance. Same issue here.
answered Aug 15, 2017 at 1:50
-
Not even expert PostgreSQL developers are likely to be able to recover much from nothing but WAL archives, by the way. Commented Aug 16, 2017 at 1:08
as soon as the wal records are behind the checkpoint, you cant "use" them. To have a checkpoint in past you need to have a basebackup (a cold copy with db down, pg_basebackup one, or pg_start_backup() + rsync + pg_stop_backup() one or smth alike) restored. If you don't have such copy with older checkpoint as last applied, you can't use WALs to roll changes on.
