Обсуждение: Cold backup with rsync -- WAL files?

Why not use cascading replication for this? That's precisely what I'm doing.

---

We need a "last chance" offsite backup of our backup server.  Since the backup server isn't accessed by customers, we have the luxury of being able to shut it off briefly once per day. Call server "A" the backup, server "B" is offsite backup-of-backup: The procedure I'd like to implement is:

  server B is always "cold" (Postgres not running)

  while A is running, rsync A --> B

  stop Postgres on A

  rsync A --> B

  restart Postgres on A

My question is: what about the WAL files?  Do I need to rsync them from A to B too, or can/should I just clean out B's WAL directory, or something else?

Thanks,
Craig

Not sure how/why you didn't get my reply:

If your offsite backup server has enough space to accommodate all backups that your retention period requires, backing up WAL archives is always a good idea as it could not only help you restore the standalone database, but also allow you to set up a slave if need be.

Another way to think about it would be a backup without WAL files would be ideally just a base backup, while the one with WAL files will give you point in time recovery. It all depends on what you want your backups to be used for.

Payal Singh,
OmniTi Computer Consulting Inc.
Junior Database Architect,
Phone: 240.646.0770 x 253

On Fri, Dec 6, 2013 at 1:39 PM, Craig James <cjames@emolecules.com> wrote:

While I appreciate suggestions of other ways to do replication, does anyone have an answer to my original question?

...The procedure I'd like to implement is:

  server B is always "cold" (Postgres not running)

  while A is running, rsync A --> B

  stop Postgres on A

  rsync A --> B

  restart Postgres on A

My question is: what about the WAL files?  Do I need to rsync them from A to B too, or can/should I just clean out B's WAL directory, or something else?

Thanks,
Craig

> On Dec 6, 2013, at 11:07, 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.

On 12/06/13 13:19, Denish Patel wrote:
> Payal is right. You don't need WAL for Cold backup.

I'm afraid you do.

I just did a test to confirm with 9.2.6:

pg_ctl -D . -m fast stop
rsync . ../data-test -avP
cd ../data-test/pg_xlog
rm -f *
cd ..
pg_ctl -D . start

LOG:  could not open file "pg_xlog/00000001000000020000009B" (log file 2,
segment 155): No such file or directory
LOG:  invalid primary checkpoint record
LOG:  could not open file "pg_xlog/00000001000000020000009B" (log file 2,
segment 155): No such file or directory
LOG:  invalid secondary checkpoint record
PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 13141) was terminated by signal 6: Aborted
LOG:  aborting startup due to startup process failure

Then, if I copy the WAL files back from the old stopped data dir:

rsync . ../data/pg_xlog/ pg_xlog/ -avP
pg_ctl -D . start

LOG:  database system was shut down at 2013-12-06 13:25:13 PST
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

then it happily starts up.

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

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

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