Re: Dropped table, no backup, restore from file system backup or WAL files?

Tom

You've helped clarify things. Thanks.

Our situation is kind of a special case and I was trying to learn from it what can be done with the WAL files.

We only started the production system a few days ago, so we have all the WAL files. I don't care of we'd end up with a corrupted database because I wouldn't do a recovery to the original database but to a copy. The intention is to copy out the data for three small tables.

As I understand it, one should do a pg_start_backup, then do a file system backup, then do pg_stop_backup.

Several questions:

First, if the file system back is run automatically at 3AM, what's the best way to do the start/stop backup?

Second, what about doing a pg_dump instead of a file system backup?

Third, the manual in 22.3.2 says "It is also possible to make a backup dump while the postmaster is stopped. In this case, you obviously cannot use pg_start_backup or pg_stop_backup, and you will therefore be left to your own devices to keep track of which backup dump is which and how far back the associated WAL files go. It is generally better to follow the on-line backup procedure above." That implies that I can do what I'm trying to do, but it doesn't say how. You indicate that it's messy. I hoped that meant that one can manually replay wal files, one by one, but I guess not.

John

On Thu, 08 Jul 2010 16:58:18 -0400, Tom Lane wrote:

>"John T. Dow" <john(at)johntdow(dot)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
>
>--
>Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-novice