Re: Dropped table, no backup, restore from file system backup or WAL files? - Mailing list pgsql-novice

From Tom Lane
Subject Re: Dropped table, no backup, restore from file system backup or WAL files?
Date
Msg-id 2767.1278622698@sss.pgh.pa.us
Whole thread Raw
In response to Re: Dropped table, no backup, restore from file system backup or WAL files?  ("John T. Dow" <john@johntdow.com>)
Responses Re: Dropped table, no backup, restore from file system backup or WAL files?
List pgsql-novice
"John T. Dow" <john@johntdow.com> writes:
> We did not do pg_start_backup/pg_stop_backup.

Ugh.

> What if we never did pg_start_backup? Will it work anyway?

You could maybe make it work, if you had full_page_writes turned on
and have a continuous series of WAL files extending back to before
the manual filesystem backup was started.  What pg_start_backup mainly
does for you is to automate things and make sure there is a well-defined
spot at which a successful replay can be started.  It's *not* going to
"just work" without pg_start_backup, though.  You'd need to manually
fake up a suitable backup label file, and maybe some other hacking.
Otherwise what's likely to happen is that the recovery goes through
but leaves you with a corrupted database anyway.

If the data is worth this much trouble to you, I'd suggest hiring a
Postgres consultant who's experienced in data recovery.

> Also, it is unclear from the documentation whether you must have
> archived wal files to replay the wal files. There seems to be no way
> to replay them other than with recovery.conf, and recovery.conf seems
> to require a restore_command that actually does something.

You can just have it copy from pg_xlog, if all the files you need are
in pg_xlog.  That's a pretty uncommon situation though, so there's not
any special easy case for it.

            regards, tom lane

pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: Bypassing authentication
Next
From: "John T. Dow"
Date:
Subject: Re: Dropped table, no backup, restore from file system backup or WAL files?