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:20 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:

> I performed a backup of the database, using pg_dump

> I have WAL-based log shipping enabled on that server.
>
> I just created a second database server that will be a recovery
> server, compiled and installed PostgreSQL onto it, and restored
> the full backup taken from the primary database onto it. Since
> this wasn't a base backup (using tar, cpio, etc.), how would I
> apply the WAL logs to this secondary server, to get it up to
> current?

That can't be done -- pg_dump uses COPY or INSERT statements
(depending on your pg_dump options) which are *row* based, while WAL
files are *page* based. They are alternative techniques which can't
be mixed and matched.

> All of the documentation I've read so far uses a base backup. Is
> there any way to apply the logs generated since that backup
> created by pg_dump to get the secondary database up to current?

No, you can only apply WAL files to a file-based image of the source
database, not to a database created through other means which
happens to contain the same data.

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?

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.