Обсуждение: Apply WAL logs after database restore
Hi all.
I’m pretty sure that this is a FAQ here, but I’ll ask it, just to get confirmation on what I need to do.
I have a database sitting on a box running PostgreSQL 8.4, checking in at 190GB. I performed a backup of the database, using pg_dump to get the data out of the database. This is a full level 0 backup of the database. 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? 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?
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.
Le 18/02/2010 22:56, Brad Littlejohn a écrit : > [...] > I'm pretty sure that this is a FAQ here, but I'll ask it, just to get confirmation on what I need to do. > > I have a database sitting on a box running PostgreSQL 8.4, checking in at 190GB. I performed a backup of the database,using pg_dump to get the data out of the database. This is a full level 0 backup of the database. I have WAL-basedlog shipping enabled on that server. > > I just created a second database server that will be a recovery server, compiled and installed PostgreSQL onto it, andrestored 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? All of the documentation I've readso far uses a base backup. Is there any way to apply the logs generated since that backup created by pg_dump to get thesecondary database up to current? > Nope, you cannot do that. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Brad Littlejohn <blittlejohn@posportal.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. -Kevin
-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: Thursday, February 18, 2010 2:20 PM
To: Brad Littlejohn; 'pgsql-admin@postgresql.org'
Subject: Re: [ADMIN] Apply WAL logs after database restore
Brad Littlejohn <blittlejohn@posportal.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
beirrelevant, right? The reason I ask, is that one of my developers made a change to 2 tables last night, didn't wrap
hischanges around a begin/commit/rollback statement, and dropped a column he needs back. The WAL logs are now the only
placethe 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
backthat he needs?
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.
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? 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. 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. -Kevin
-----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.