Re: Cold backup with rsync -- WAL files?

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(at)emolecules(dot)com> wrote:

>
>
>
> On Fri, Dec 6, 2013 at 1:56 PM, Denish Patel <denish(at)omniti(dot)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(at)ymail(dot)com> wrote:
>>
>>> Denish Patel <denish(at)omniti(dot)com> wrote:
>>> > Craig James <cjames(at)emolecules(dot)com> wrote:
>>> >> Jeff Frost <jeff(at)pgexperts(dot)com> wrote:
>>> >>>> Craig James <cjames(at)emolecules(dot)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(at)Kevin-Desktop:~/pg/master$ pg_ctl -D Debug/data -m fast -w stop
>>> waiting for server to shut down.... done
>>> server stopped
>>> kgrittn(at)Kevin-Desktop:~/pg/master$ mkdir Debug/data/xlog2
>>> kgrittn(at)Kevin-Desktop:~/pg/master$ mv Debug/data/pg_xlog/*
>>> Debug/data/xlog2/
>>> kgrittn(at)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
>>
>
>