Re: Cold backup with rsync -- WAL files?

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
>