1

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

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.

  • Not even expert PostgreSQL developers are likely to be able to recover much from nothing but WAL archives, by the way. – Craig Ringer Aug 16 '17 at 1:08
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy