pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCold backup with rsync -- WAL files?
17+ messages / 7 participants
[nested] [flat]
* Cold backup with rsync -- WAL files?
@ 2013-12-06 15:12 Craig James <cjames@emolecules.com>
0 siblings, 1 reply; 17+ messages in thread
From: Craig James @ 2013-12-06 15:12 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Cold backup with rsync -- WAL files?
@ 2013-12-06 15:24 Scott Whitney <scott@journyx.com>
parent: Craig James <cjames@emolecules.com>
0 siblings, 1 reply; 17+ messages in thread
From: Scott Whitney @ 2013-12-06 15:24 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: pgsql-admin
Why not use cascading replication for this? That's precisely what I'm doing.
----- Original Message -----
> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Cold backup with rsync -- WAL files?
@ 2013-12-06 15:37 Craig James <cjames@emolecules.com>
parent: Scott Whitney <scott@journyx.com>
0 siblings, 1 reply; 17+ messages in thread
From: Craig James @ 2013-12-06 15:37 UTC (permalink / raw)
To: Scott Whitney <swhitney@journyx.com>; +Cc: pgsql-admin
On Fri, Dec 6, 2013 at 7:24 AM, Scott Whitney <scott@journyx.com> wrote:
> Why not use cascading replication for this? That's precisely what I'm
> doing.
>
rsync is much simpler, and has one huge advantage: you can fire up the
backup and see if it works, and when you're done you don't have to take
another base backup. Just run rsync again and you're back in sync.
Back to my original question...
Craig
> ------------------------------
>
> 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
>
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Cold backup with rsync -- WAL files?
@ 2013-12-06 18:39 Craig James <cjames@emolecules.com>
parent: Craig James <cjames@emolecules.com>
0 siblings, 1 reply; 17+ messages in thread
From: Craig James @ 2013-12-06 18:39 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Cold backup with rsync -- WAL files?
@ 2013-12-06 18:54 Payal Singh <payal@omniti.com>
parent: Craig James <cjames@emolecules.com>
0 siblings, 1 reply; 17+ messages in thread
From: Payal Singh @ 2013-12-06 18:54 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: pgsql-admin
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
>
>
>
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Cold backup with rsync -- WAL files?
@ 2013-12-06 19:04 Craig James <cjames@emolecules.com>
parent: Payal Singh <payal@omniti.com>
0 siblings, 1 reply; 17+ messages in thread
From: Craig James @ 2013-12-06 19:04 UTC (permalink / raw)
To: Payal Singh <payal@omniti.com>; +Cc: pgsql-admin
On Fri, Dec 6, 2013 at 10:54 AM, Payal Singh <payal@omniti.com> wrote:
> 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.
>
Thanks, I did get your reply, but it didn't answer my question. I don't
want anything but a single point-in-time snapshot of a stopped database. (I
have good reasons for this decision.)
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?
Thanks,
Craig
>
> 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
>>
>>
>>
>>
>>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Cold backup with rsync -- WAL files?
@ 2013-12-06 19:34 Jeff Frost <jeff@pgexperts.com>
parent: Craig James <cjames@emolecules.com>
0 siblings, 1 reply; 17+ messages in thread
From: Jeff Frost @ 2013-12-06 19:34 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: Payal Singh <payal@omniti.com>; pgsql-admin
> 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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Cold backup with rsync -- WAL files?
@ 2013-12-06 21:11 Craig James <cjames@emolecules.com>
parent: Jeff Frost <jeff@pgexperts.com>
0 siblings, 1 reply; 17+ messages in thread
From: Craig James @ 2013-12-06 21:11 UTC (permalink / raw)
To: Jeff Frost <jeff@pgexperts.com>; +Cc: Payal Singh <payal@omniti.com>; pgsql-admin
On Fri, Dec 6, 2013 at 11:34 AM, Jeff Frost <jeff@pgexperts.com> wrote:
>
>
> > 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.
>
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.
Craig
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Cold backup with rsync -- WAL files?
@ 2013-12-06 21:19 Denish Patel <denish@omniti.com>
parent: Craig James <cjames@emolecules.com>
0 siblings, 2 replies; 17+ messages in thread
From: Denish Patel @ 2013-12-06 21:19 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: Jeff Frost <jeff@pgexperts.com>; Payal Singh <payal@omniti.com>; pgsql-admin
Payal is right. You don't need WAL for Cold backup.
my 2 cents.
On Fri, Dec 6, 2013 at 4:11 PM, Craig James <cjames@emolecules.com> wrote:
>
> On Fri, Dec 6, 2013 at 11:34 AM, Jeff Frost <jeff@pgexperts.com> wrote:
>
>>
>>
>> > 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.
>>
>
> 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.
>
> Craig
>
>
--
Denish Patel,
OmniTi Computer Consulting Inc.
Database Architect,
http://omniti.com/does/data-management
http://www.pateldenish.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Cold backup with rsync -- WAL files?
@ 2013-12-06 21:32 Jeff Frost <jeff@pgexperts.com>
parent: Denish Patel <denish@omniti.com>
1 sibling, 1 reply; 17+ messages in thread
From: Jeff Frost @ 2013-12-06 21:32 UTC (permalink / raw)
To: Denish Patel <denish@omniti.com>; Craig James <cjames@emolecules.com>; +Cc: Payal Singh <payal@omniti.com>; pgsql-admin
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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Cold backup with rsync -- WAL files?
@ 2013-12-06 21:40 Denish Patel <denish@omniti.com>
parent: Jeff Frost <jeff@pgexperts.com>
0 siblings, 1 reply; 17+ messages in thread
From: Denish Patel @ 2013-12-06 21:40 UTC (permalink / raw)
To: Jeff Frost <jeff@pgexperts.com>; +Cc: Craig James <cjames@emolecules.com>; Payal Singh <payal@omniti.com>; pgsql-admin
Jeff,
If you remove pg_xlogs, I do not expect Postgres to come back!
I think Craig is referring to WAL files , not pg_xlogs. He is still going
to take backup of pg_xlogs under $PGDATA when Postgres is still down.
I think there is some misunderstanding here!
On Fri, Dec 6, 2013 at 4:32 PM, Jeff Frost <jeff@pgexperts.com> wrote:
> 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,
OmniTi Computer Consulting Inc.
Database Architect,
http://omniti.com/does/data-management
http://www.pateldenish.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Cold backup with rsync -- WAL files?
@ 2013-12-06 21:46 Kevin Grittner <kgrittn@ymail.com>
parent: Denish Patel <denish@omniti.com>
1 sibling, 2 replies; 17+ messages in thread
From: Kevin Grittner @ 2013-12-06 21:46 UTC (permalink / raw)
To: Denish Patel <denish@omniti.com>; Craig James <cjames@emolecules.com>; +Cc: Jeff Frost <jeff@pgexperts.com>; Payal Singh <payal@omniti.com>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Cold backup with rsync -- WAL files?
@ 2013-12-06 21:56 Jeff Frost <jeff@pgexperts.com>
parent: Denish Patel <denish@omniti.com>
0 siblings, 0 replies; 17+ messages in thread
From: Jeff Frost @ 2013-12-06 21:56 UTC (permalink / raw)
To: Denish Patel <denish@omniti.com>; +Cc: Craig James <cjames@emolecules.com>; Payal Singh <payal@omniti.com>; pgsql-admin
On 12/06/13 13:40, Denish Patel wrote:
> Jeff,
>
> If you remove pg_xlogs, I do not expect Postgres to come back!
>
> I think Craig is referring to WAL files , not pg_xlogs. He is still going to
> take backup of pg_xlogs under $PGDATA when Postgres is still down.
>
> I think there is some misunderstanding here!
>
I think the misunderstanding might be that you mean *archived* WAL files, and
I mean WAL files in the PGDATA directory.
I think the latter is what the OP means, but I'm not certain.
So, no, you do not need archiving enabled to take a cold backup, but you do
need all the WAL files from the pg_xlog directory per mine and Kevin's quick test.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Cold backup with rsync -- WAL files?
@ 2013-12-06 21:56 Denish Patel <denish@omniti.com>
parent: Kevin Grittner <kgrittn@ymail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Denish Patel @ 2013-12-06 21:56 UTC (permalink / raw)
To: Kevin Grittner <kgrittn@ymail.com>; +Cc: Craig James <cjames@emolecules.com>; Jeff Frost <jeff@pgexperts.com>; Payal Singh <payal@omniti.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Cold backup with rsync -- WAL files?
@ 2013-12-06 22:01 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Kevin Grittner <kgrittn@ymail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Scott Ribe @ 2013-12-06 22:01 UTC (permalink / raw)
To: Kevin Grittner <kgrittn@ymail.com>; +Cc: pgsql-admin
On Dec 6, 2013, at 2:46 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
> 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?
No. I've often that it would be a nice feature for pg_start_backup to write a list of the required log files.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Cold backup with rsync -- WAL files?
@ 2013-12-07 01:16 Craig James <cjames@emolecules.com>
parent: Denish Patel <denish@omniti.com>
0 siblings, 1 reply; 17+ messages in thread
From: Craig James @ 2013-12-07 01:16 UTC (permalink / raw)
To: Denish Patel <denish@omniti.com>; +Cc: Kevin Grittner <kgrittn@ymail.com>; Jeff Frost <jeff@pgexperts.com>; Payal Singh <payal@omniti.com>; pgsql-admin
On Fri, Dec 6, 2013 at 1:56 PM, Denish Patel <denish@omniti.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@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
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Cold backup with rsync -- WAL files?
@ 2013-12-07 06:33 Payal Singh <payal@omniti.com>
parent: Craig James <cjames@emolecules.com>
0 siblings, 0 replies; 17+ messages in thread
From: Payal Singh @ 2013-12-07 06:33 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: Denish Patel <denish@omniti.com>; Kevin Grittner <kgrittn@ymail.com>; Jeff Frost <jeff@pgexperts.com>; pgsql-admin
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.
Payal Singh,
OmniTi Computer Consulting Inc.
Junior Database Architect,
Phone: 240.646.0770 x 253
On Fri, Dec 6, 2013 at 8:16 PM, Craig James <cjames@emolecules.com> wrote:
>
>
>
> On Fri, Dec 6, 2013 at 1:56 PM, Denish Patel <denish@omniti.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@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
>>
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2013-12-07 06:33 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-12-06 15:12 Cold backup with rsync -- WAL files? Craig James <cjames@emolecules.com>
2013-12-06 15:24 ` Scott Whitney <scott@journyx.com>
2013-12-06 15:37 ` Craig James <cjames@emolecules.com>
2013-12-06 18:39 ` Craig James <cjames@emolecules.com>
2013-12-06 18:54 ` Payal Singh <payal@omniti.com>
2013-12-06 19:04 ` Craig James <cjames@emolecules.com>
2013-12-06 19:34 ` Jeff Frost <jeff@pgexperts.com>
2013-12-06 21:11 ` Craig James <cjames@emolecules.com>
2013-12-06 21:19 ` Denish Patel <denish@omniti.com>
2013-12-06 21:32 ` Jeff Frost <jeff@pgexperts.com>
2013-12-06 21:40 ` Denish Patel <denish@omniti.com>
2013-12-06 21:56 ` Jeff Frost <jeff@pgexperts.com>
2013-12-06 21:46 ` Kevin Grittner <kgrittn@ymail.com>
2013-12-06 21:56 ` Denish Patel <denish@omniti.com>
2013-12-07 01:16 ` Craig James <cjames@emolecules.com>
2013-12-07 06:33 ` Payal Singh <payal@omniti.com>
2013-12-06 22:01 ` Scott Ribe <scott_ribe@elevated-dev.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox