Re: Cold backup with rsync -- WAL files?

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