[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-admin
Subject: Re: [ADMIN] Cold backup with rsync -- WAL files?
From: Payal Singh <payal () omniti ! com>
Date: 2013-12-07 6:33:42
Message-ID: CANUg7LBKJA2At-V-13jjtZ=3AHnrLCPcaMViLJ_aHxORmvgy4g () mail ! gmail ! com
[Download RAW message or body]
If it is the question of files in the pg_xlog directory, you must back them
up. The reason is that WAL files ensure that every transaction activity is
logged before actual data is changed. So assume that when you stopped the
server, some statements were issued that have been recorded in the log
(xlog) while those data changes haven't yet been saved to the disk. In such
cases, when you restore or refresh the database from its backup, postgres
will need the files in pg_xlog to make sure that the database is in a
consistent state by checking if any WALs need to be replayed or not.
Payal Singh,
OmniTi Computer Consulting Inc.
Junior Database Architect,
Phone: 240.646.0770 x 253
On Fri, Dec 6, 2013 at 8:16 PM, Craig James <cjames@emolecules.com> wrote:
>
>
>
> On Fri, Dec 6, 2013 at 1:56 PM, Denish Patel <denish@omniti.com> wrote:
>
>> I think it's time to understand/clarify problem before arguing further !!
>>
>> Craig,
>>
>> Would you please clarify which of these files you want to ignore in using
>> cold backup ?
>> 1. WAL files (copied using archive_command to different location)
>> 2. Files under pg_xlog
>>
>
> Files under pg_xlog.
>
> I don't understand why if Postgres is shut done completely that it has
> anything to replay. I'm not saying it's wrong -- of course I'll copy the
> files if needed -- but I just like to understand these things.
>
> Thanks,
> Craig (OP, not the other Craig)
>
>
>>
>> I assumed you meant #1.
>>
>> On Fri, Dec 6, 2013 at 4:46 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
>>
>>> Denish Patel <denish@omniti.com> wrote:
>>> > Craig James <cjames@emolecules.com> wrote:
>>> >> Jeff Frost <jeff@pgexperts.com> wrote:
>>> >>>> Craig James <cjames@emolecules.com> wrote:
>>> >>>>
>>> >>>> After I rsync the postgres data directory, do I need the WAL
>>> >>>> files from the source (they're on a separate disk, not part of
>>> >>>> the postgres data directory)? Can/should I erase the WAL
>>> >>>> directory of the destination?
>>> >>>
>>> >>> Yes, you need the WAL files.
>>> >>
>>> >> Now I have two directly conflicting answers. You say the WAL
>>> >> files are needed even though the DB is shut off, and Payal Singh
>>> >> says I don't need the WAL files (but I don't think he cc'd the
>>> >> mailing list).
>>> >>
>>> >> Why are the WAL files necessary if the DB is shut off? Why
>>> >> can't they just be erased?
>>> >>
>>> >> It's a gigabyte of copying that I was hoping to avoid.
>>>
>>> > Payal is right. You don't need WAL for Cold backup.
>>>
>>> This experiment took far less time than reading the thread:
>>>
>>> kgrittn@Kevin-Desktop:~/pg/master$ pg_ctl -D Debug/data -m fast -w stop
>>> waiting for server to shut down.... done
>>> server stopped
>>> kgrittn@Kevin-Desktop:~/pg/master$ mkdir Debug/data/xlog2
>>> kgrittn@Kevin-Desktop:~/pg/master$ mv Debug/data/pg_xlog/*
>>> Debug/data/xlog2/
>>> kgrittn@Kevin-Desktop:~/pg/master$ rm -f Debug/data/logfile ; pg_ctl -D
>>> Debug/data -l Debug/data/logfile -w start || cat Debug/data/logfile
>>> waiting for server to start........ stopped waiting
>>> pg_ctl: could not start server
>>> Examine the log output.
>>> LOG: database system was shut down at 2013-12-06 15:31:27 CST
>>> LOG: creating missing WAL directory "pg_xlog/archive_status"
>>> LOG: invalid primary checkpoint record
>>> LOG: invalid secondary checkpoint record
>>> PANIC: could not locate a valid checkpoint record
>>> LOG: startup process (PID 15471) was terminated by signal 6: Aborted
>>> LOG: aborting startup due to startup process failure
>>>
>>> Now, if you're very clever you might be able to copy just
>>> *selected* WAL files. You've gotta ask yourself a question: "Do I
>>> feel lucky?" Well, do ya?
>>>
>>> --
>>> Kevin Grittner
>>> EDB: http://www.enterprisedb.com
>>> The Enterprise PostgreSQL Company
>>>
>>
>>
>>
>> --
>> Denish Patel,
>> OmniTi Computer Consulting Inc.
>> Database Architect,
>> http://omniti.com/does/data-management
>> http://www.pateldenish.com
>>
>
>
[Attachment #3 (text/html)]
<div dir="ltr">If it is the question of files in the pg_xlog directory, you must back \
them up. The reason is that WAL files ensure that every transaction activity is \
logged before actual data is changed. So assume that when you stopped the server, \
some statements were issued that have been recorded in the log (xlog) while those \
data changes haven't yet been saved to the disk. In such cases, when you restore \
or refresh the database from its backup, postgres will need the files in pg_xlog to \
make sure that the database is in a consistent state by checking if any WALs need to \
be replayed or not.</div>
<div class="gmail_extra"><br clear="all"><div><div dir="ltr"><span \
style="color:rgb(136,136,136);font-family:Helvetica">Payal Singh,</span><span \
style="color:rgb(136,136,136);font-family:Helvetica"><br></span><span \
style="color:rgb(136,136,136);font-family:Helvetica">OmniTi Computer Consulting \
Inc.</span><span style="color:rgb(136,136,136);font-family:Helvetica"><br>
</span><span style="color:rgb(136,136,136);font-family:Helvetica">Junior Database \
Architect,</span><span \
style="color:rgb(136,136,136);font-family:Helvetica"><br></span><span \
style="color:rgb(136,136,136);font-family:Helvetica">Phone: </span><a \
value="+12406460770" style="color:rgb(17,85,204)">240.646.0770</a><span \
style="color:rgb(136,136,136);font-family:Helvetica"> x 253</span><br>
</div></div>
<br><br><div class="gmail_quote">On Fri, Dec 6, 2013 at 8:16 PM, Craig James <span \
dir="ltr"><<a href="mailto:cjames@emolecules.com" \
target="_blank">cjames@emolecules.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">
<div dir="ltr"><br><div class="gmail_extra"><br><br><div class="gmail_quote"><div \
class="im">On Fri, Dec 6, 2013 at 1:56 PM, Denish Patel <span dir="ltr"><<a \
href="mailto:denish@omniti.com" target="_blank">denish@omniti.com</a>></span> \
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr"><div>I think it's time to \
understand/clarify problem before arguing further !! </div><div><br></div>
Craig,<div><br></div><div>Would you please clarify which of these files you want to \
ignore in using cold backup ?</div>
<div> 1. WAL files (copied using archive_command to different location)</div><div> \
2. Files under pg_xlog</div></div></blockquote><div><br></div></div><div>Files under \
pg_xlog.<br><br></div><div>I don't understand why if Postgres is shut done \
completely that it has anything to replay. I'm not saying it's wrong -- of \
course I'll copy the files if needed -- but I just like to understand these \
things.<br>
</div><div><br></div><div>Thanks,<br>Craig (OP, not the other Craig)<br> \
<br></div><div><div class="h5"><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">
<div><br></div><div>I assumed you meant #1.</div>
<div><br></div><div class="gmail_extra">
<div class="gmail_quote">On Fri, Dec 6, 2013 at 4:46 PM, Kevin Grittner <span \
dir="ltr"><<a href="mailto:kgrittn@ymail.com" \
target="_blank">kgrittn@ymail.com</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">
Denish Patel <<a href="mailto:denish@omniti.com" \
target="_blank">denish@omniti.com</a>> wrote:<br> > Craig James <<a \
href="mailto:cjames@emolecules.com" target="_blank">cjames@emolecules.com</a>> \
wrote:<br> >> Jeff Frost <<a href="mailto:jeff@pgexperts.com" \
target="_blank">jeff@pgexperts.com</a>> wrote:<br> >>>> Craig James \
<<a href="mailto:cjames@emolecules.com" \
target="_blank">cjames@emolecules.com</a>> wrote:<br> >>>><br>
>>>> After I rsync the postgres data directory, do I need the WAL<br>
>>>> files from the source (they're on a separate disk, not part \
of<br> >>>> the postgres data directory)? Can/should I erase the \
WAL<br> >>>> directory of the destination?<br>
>>><br>
>>> Yes, you need the WAL files.<br>
>><br>
>> Now I have two directly conflicting answers. You say the WAL<br>
>> files are needed even though the DB is shut off, and Payal Singh<br>
>> says I don't need the WAL files (but I don't think he cc'd \
the<br> >> mailing list).<br>
>><br>
>> Why are the WAL files necessary if the DB is shut off? Why<br>
>> can't they just be erased?<br>
>><br>
>> It's a gigabyte of copying that I was hoping to avoid.<br>
<br>
> Payal is right. You don't need WAL for Cold backup.<br>
<br>
This experiment took far less time than reading the thread:<br>
<br>
kgrittn@Kevin-Desktop:~/pg/master$ pg_ctl -D Debug/data -m fast -w stop<br>
waiting for server to shut down.... done<br>
server stopped<br>
kgrittn@Kevin-Desktop:~/pg/master$ mkdir Debug/data/xlog2<br>
kgrittn@Kevin-Desktop:~/pg/master$ mv Debug/data/pg_xlog/* Debug/data/xlog2/<br>
kgrittn@Kevin-Desktop:~/pg/master$ rm -f Debug/data/logfile ; pg_ctl -D Debug/data -l \
Debug/data/logfile -w start || cat Debug/data/logfile<br> waiting for server to \
start........ stopped waiting<br>
pg_ctl: could not start server<br>
Examine the log output.<br>
LOG: database system was shut down at 2013-12-06 15:31:27 CST<br>
LOG: creating missing WAL directory "pg_xlog/archive_status"<br>
LOG: invalid primary checkpoint record<br>
LOG: invalid secondary checkpoint record<br>
PANIC: could not locate a valid checkpoint record<br>
LOG: startup process (PID 15471) was terminated by signal 6: Aborted<br>
LOG: aborting startup due to startup process failure<br>
<br>
Now, if you're very clever you might be able to copy just<br>
*selected* WAL files. You've gotta ask yourself a question: "Do I<br>
feel lucky?" Well, do ya?<span><font color="#888888"><br>
<br>
--<br>
Kevin Grittner<br>
EDB: <a href="http://www.enterprisedb.com" \
target="_blank">http://www.enterprisedb.com</a><br> The Enterprise PostgreSQL \
Company<br> </font></span></blockquote></div><span><font color="#888888"><br><br \
clear="all"><div><br></div>-- <br><div dir="ltr"><span \
style="font-family:Helvetica">Denish Patel,</span><span \
style="font-family:Helvetica"><br> </span><span style="font-family:Helvetica">OmniTi \
Computer Consulting Inc.</span><span style="font-family:Helvetica"><br>
</span><span style="font-family:Helvetica">Database Architect,</span><span \
style="font-family:Helvetica"><br></span><a \
href="http://omniti.com/does/data-management" \
target="_blank">http://omniti.com/does/data-management</a><br>
<div><a href="http://www.pateldenish.com" \
target="_blank">http://www.pateldenish.com</a><br></div></div> \
</font></span></div></div> </blockquote></div></div></div><br></div></div>
</blockquote></div><br></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic