Re: Cold backup with rsync -- WAL files?

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