Re: Apply WAL logs after database restore

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

Brad Littlejohn <blittlejohn@posportal.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
filenamesthat were these original tables, then drop the column from the original tables. That worked fine, except that
forwhen he ran this twice, the first line in his query was 'drop table if exists <new table>', which killed all of the
datacontaining the filenames. I have the full backup restored, but it is current as of 7 days ago. I was hoping I could
getthe 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
thedeveloper 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
solelyfor the use of the individual or entity to whom they are addressed. If you are not the addressee or authorized to
receivethis for the addressee, you must not use, copy, disclose, or take any action based on this message or any
informationherein. If you have received this message in error, please advise the sender immediately by reply e-mail and
deletethis message.