I think it's time to understand/clarify problem before arguing further !!
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
I assumed you meant #1.
On Fri, Dec 6, 2013 at 4:46 PM, Kevin Grittner <kgrittn@xxxxxxxxx> wrote:
Denish Patel <denish@xxxxxxxxxx> wrote:
> Craig James <cjames@xxxxxxxxxxxxxx> wrote:
>> Jeff Frost <jeff@xxxxxxxxxxxxx> wrote:
>>>> Craig James <cjames@xxxxxxxxxxxxxx> 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
OmniTi Computer Consulting Inc.
Database Architect,
http://omniti.com/does/data-management
