Apply WAL logs after database restore

--_000_FC2125863502344C9B615565CF826F7CD9B323A2posexch1pospo rt_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi all.

I'm pretty sure that this is a FAQ here, but I'll ask it, ju=
st to get confirmation on what I need to do.

I have a database sitting on a box running PostgreSQL 8.4, c=
hecking 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 dat=
abase. I have WAL-based log shipping enabled on that server.

I just created a second database server that will be a recov=
ery server, compiled and installed PostgreSQL onto it, and restored the ful=
l 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 seco=
ndary server, to get it up to current? All of the documentation I've read s=
o far uses a base backup. Is there any way to apply the logs generated sinc=
e that backup created by pg_dump to get the secondary database up to curren=
t?

Brad

* This e-mail and any files transmitted with it may contain confidential an=
d/or privileged information and intended solely for the use of the individu=
al or entity to whom they are addressed. If you are not the addressee or au=
thorized to receive this for the addressee, you must not use, copy, disclos=
e, or take any action based on this message or any information herein. If y=
ou have received this message in error, please advise the sender immediatel=
y by reply e-mail and delete this message.

--_000_FC2125863502344C9B615565CF826F7CD9B323A2posexch1pospo rt_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
..org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf=3D"http:/=
/schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss=3D"http://sche=
mas.microsoft.com/office/2006/digsig-setup" xmlns:dssi=3D"http://schemas.mi=
crosoft.com/office/2006/digsig" xmlns:mdssi=3D"http://schemas.openxmlformat=
s.org/package/2006/digital-signature" xmlns:mver=3D"http://schemas.openxmlf=
ormats.org/markup-compatibility/2006" xmlns:m=3D"http://schemas.microsoft.c=
om/office/2004/12/omml" xmlns:mrels=3D"http://schemas.openxmlformats.org/pa=
ckage/2006/relationships" xmlns:spwp=3D"http://microsoft.com/sharepoint/web=
partpages" xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/service s/20=
06/types" xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/service s/200=
6/messages" xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/ Sli=
deLibrary/" xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPor tal=
Server/PublishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" xmlns:=
st=3D"" xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>

</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<o:p> </o:p>

 =
 Hi
all.<o:p></o:p>

<o:p> </o:p>

 =
 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.<o:p></o:p>

<o:p> </o:p>

 =
 I
have a database sitting on a box running PostgreSQL 8.4, checking in at 190=
GB. 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 l=
og
shipping enabled on that server.<o:p></o:p>

<o:p> </o:p>

 =
 I
just created a second database server that will be a recovery server, compi=
led
and installed PostgreSQL onto it, and restored the full backup taken from t=
he
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 ge=
t 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 crea=
ted
by pg_dump to get the secondary database up to current?<o:p></o:p>

<o:p> </o:p>

 =
 Brad<o:p></o:p>

<o:p> </o:p>

</div>

=0D
* This e-mail and any files transmitted with it may contain confidential an=
d/or privileged information and intended solely for the use of the individu=
al or entity to whom they are addressed. If you are not the addressee or au=
thorized to receive this for the addressee, you must not use, copy, disclos=
e, or take any action based on this message or any information herein. If y=
ou have received this message in error, please advise the sender immediatel=
y by reply e-mail and delete this message.=0D

</body>

</html>

--_000_FC2125863502344C9B615565CF826F7CD9B323A2posexch1pospo rt_--

Le 18/02/2010 22:56, Brad Littlejohn a =E9crit :
> [...]
> I'm pretty sure that this is a FAQ here, but I'll ask it, just to get c=
onfirmation 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 f=
ar 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 curre=
nt?
>

Nope, you cannot do that.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Brad Littlejohn <blittlejohn [at] 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

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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

Brad Littlejohn <blittlejohn [at] 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 da=
tabase 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, di=
dn'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 curre=
nt 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 n=
eeds?

Brad

* This e-mail and any files transmitted with it may contain confidential an=
d/or privileged information and intended solely for the use of the individu=
al or entity to whom they are addressed. If you are not the addressee or au=
thorized to receive this for the addressee, you must not use, copy, disclos=
e, or take any action based on this message or any information herein. If y=
ou have received this message in error, please advise the sender immediatel=
y by reply e-mail and delete this message.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Brad Littlejohn <blittlejohn [at] 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

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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

Brad Littlejohn <blittlejohn [at] 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 conta=
ining filenames that were these original tables, then drop the column from =
the original tables. That worked fine, except that for when he ran this twi=
ce, the first line in his query was 'drop table if exists <new table>', whi=
ch 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 product=
ion 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 t=
he 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, a=
nd 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 an=
d/or privileged information and intended solely for the use of the individu=
al or entity to whom they are addressed. If you are not the addressee or au=
thorized to receive this for the addressee, you must not use, copy, disclos=
e, or take any action based on this message or any information herein. If y=
ou have received this message in error, please advise the sender immediatel=
y by reply e-mail and delete this message.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Apply WAL logs after database restore

Re: Apply WAL logs after database restore

Re: Apply WAL logs after database restore

Re: Apply WAL logs after database restore

Re: Apply WAL logs after database restore

Re: Apply WAL logs after database restore