Re: Apply WAL logs after database restore

-----Original Message-----
From: Kevin Grittner [mailto:Kevin(dot)Grittner(at)wicourts(dot)gov]
Sent: Thursday, February 18, 2010 2:50 PM
To: Brad Littlejohn; 'pgsql-admin(at)postgresql(dot)org'
Subject: Re: [ADMIN] Apply WAL logs after database restore

Brad Littlejohn <blittlejohn(at)posportal(dot)com> wrote:

>> Okay.. then let's ask this. If I take a file-based backup of the
>> source database now, the previous WAL logs should be irrelevant,
>> right? The reason I ask, is that one of my developers made a
>>change to 2 tables last night, didn't wrap his changes around a
>> begin/commit/rollback statement, and dropped a column he needs
>> back. The WAL logs are now the only place the column and the data
>> for that column exist. If I took a file-based backup of the
>> current database (read: today), could I apply the WAL logs (from
>> up to when they made that change) to that file-based backup to get
>> the data back that he needs?

> How much of the data was in that column when the pg_dump was run?

That's the facepalm question. This happened in two tables. The first table was 3500 rows. The second table is 13500 rows. What they were trying to do was create two new tables, populate one column there from the column containing filenames that were these original tables, then drop the column from the original tables. That worked fine, except that for when he ran this twice, the first line in his query was 'drop table if exists <new table>', which killed all of the data containing the filenames. I have the full backup restored, but it is current as of 7 days ago. I was hoping I could get the column and the data from that column restored to the secondary server from the log files, back up those two tables, ship them back over to the production server, and reconcile them there.

> You could certainly recover any of that. Data entered after that
> would be in the WAL file stream somewhere, but picking it out would
> be a very tedious and time-consuming process. I'm not aware of any
> tools which would make that easy, but capturing a file-based copy of
> your database as soon as possible, as well as keeping that old
> pg_dump output, would be important if you have any hope of sifting
> that out. I'd start by doing those, "just in case" -- but your best
> bet would be to try to find some other source to re-enter the data,
> if at all possible.

I wish the data could just be re-entered. Unfortunately, the deleted data actually were filenames used in batch processing, going back 2 years, and the timestamp was included in the filename.

> Then be sure to follow backup directions from the documentation more
> closely, and *never* believe that any backup technique is working
> until you've tested a restore. On any product. I don't like to
> trust that any particular *backup* is good until I've restored it,
>even if the process hasn't changed.

Agreed. Logically, it should be just a simple backup/restore/apply logs, and you're done. Not so, in this case. If the developer hadn't ran his query twice, this would be a non-issue! But definitely a learning experience.

Brad

* This e-mail and any files transmitted with it may contain confidential and/or privileged information and intended solely for the use of the individual or entity to whom they are addressed. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message.