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.
On Fri, Dec 6, 2013 at 8:16 PM, Craig James <cjames@xxxxxxxxxxxxxx> wrote:
On Fri, Dec 6, 2013 at 1:56 PM, Denish Patel <denish@xxxxxxxxxx> 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_xlogFiles 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@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
