pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPITR with rsync
7+ messages / 5 participants
[nested] [flat]
* PITR with rsync
@ 2007-08-07 00:11 David Bear <David.Bear@asu.edu>
2007-08-07 00:27 ` Re: PITR with rsync Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: David Bear @ 2007-08-07 00:11 UTC (permalink / raw)
To: pgsql-admin
I've been looking at the pages on PITR and am wondering if anyone has
tried using rsync to accomplish this.
I'm still uncomfortable with using the file system style backup method
in PITR and am very interested in ' more information ' on how others
may be doing backups. Specifically, I assume that PITR methods would
also be accompanied with some combination of pg-dump. I am curious
about how frequently a pg-dump would be done if a PITR method was also
used (every 24 hours, every 7 days, not at all ) ??
Also, looking at the archive_command argument in postgresql.conf. Has
anyone use rsync there?
--
David Bear
phone: 602-496-0424
fax: 602-496-0955
College of Public Programs/ASU
University Center Rm 622
411 N Central
Phoenix, AZ 85007-0685
"Beware the IP portfolio, everyone will be suspect of trespassing"
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PITR with rsync
2007-08-07 00:11 PITR with rsync David Bear <David.Bear@asu.edu>
@ 2007-08-07 00:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-08-07 00:46 ` Re: PITR with rsync David Bear <David.Bear@asu.edu>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-08-07 00:27 UTC (permalink / raw)
To: David.Bear@asu.edu; +Cc: pgsql-admin
David Bear <David.Bear@asu.edu> writes:
> I'm still uncomfortable with using the file system style backup method
> in PITR and am very interested in ' more information ' on how others
> may be doing backups. Specifically, I assume that PITR methods would
> also be accompanied with some combination of pg-dump.
You assume wrong. Replaying WAL demands starting from a physical not
logical dump, because page and tuple numbers and so forth have to be the
same as in the master. A pg_dump backup will simply not work.
> I am curious
> about how frequently a pg-dump would be done if a PITR method was also
> used (every 24 hours, every 7 days, not at all ) ??
I'd personally be inclined to take a pg_dump every now and then just to
have an independent backup method. Especially if I wasn't comfortable
with PITR ;-). How often depends on how much bandwidth you can spare
for backup purposes.
> Also, looking at the archive_command argument in postgresql.conf. Has
> anyone use rsync there?
I suppose you could, but what's the point? Copying a single file that
doesn't currently exist on the destination plays to none of rsync's
strengths.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PITR with rsync
2007-08-07 00:11 PITR with rsync David Bear <David.Bear@asu.edu>
2007-08-07 00:27 ` Re: PITR with rsync Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-08-07 00:46 ` David Bear <David.Bear@asu.edu>
2007-08-07 00:53 ` Re: PITR with rsync Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: David Bear @ 2007-08-07 00:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
On Mon, Aug 06, 2007 at 08:27:13PM -0400, Tom Lane wrote:
> David Bear <David.Bear@asu.edu> writes:
> > I'm still uncomfortable with using the file system style backup method
> > in PITR and am very interested in ' more information ' on how others
> > may be doing backups. Specifically, I assume that PITR methods would
> > also be accompanied with some combination of pg-dump.
>
> You assume wrong. Replaying WAL demands starting from a physical not
> logical dump, because page and tuple numbers and so forth have to be the
> same as in the master. A pg_dump backup will simply not work.
>
> > I am curious
> > about how frequently a pg-dump would be done if a PITR method was also
> > used (every 24 hours, every 7 days, not at all ) ??
>
> I'd personally be inclined to take a pg_dump every now and then just to
> have an independent backup method. Especially if I wasn't comfortable
> with PITR ;-). How often depends on how much bandwidth you can spare
> for backup purposes.
okay, thanks. the pg-dump is not an augementation, but a completely
separate backup method.
>
> > Also, looking at the archive_command argument in postgresql.conf. Has
> > anyone use rsync there?
>
> I suppose you could, but what's the point? Copying a single file that
> doesn't currently exist on the destination plays to none of rsync's
> strengths.
well, I was using rysnc so I could copy to a different computer over
ssh, not using the rsync protocol to catch diffs. I don't have a
remote file system mounted so I can do just a cp. Am I still thinking
wrong here?
>
> regards, tom lane
--
David Bear
phone: 602-496-0424
fax: 602-496-0955
College of Public Programs/ASU
University Center Rm 622
411 N Central
Phoenix, AZ 85007-0685
"Beware the IP portfolio, everyone will be suspect of trespassing"
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PITR with rsync
2007-08-07 00:11 PITR with rsync David Bear <David.Bear@asu.edu>
2007-08-07 00:27 ` Re: PITR with rsync Tom Lane <tgl@sss.pgh.pa.us>
2007-08-07 00:46 ` Re: PITR with rsync David Bear <David.Bear@asu.edu>
@ 2007-08-07 00:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-08-07 02:28 ` Re: PITR with rsync Srinivas Kotapally <ksrinivas@bisil.com>
2007-08-07 15:37 ` Re: PITR with rsync Steve Crawford <scrawford@pinpointresearch.com>
2007-08-07 16:35 ` Re: PITR with rsync Greg Sabino Mullane <greg@turnstep.com>
0 siblings, 3 replies; 7+ messages in thread
From: Tom Lane @ 2007-08-07 00:53 UTC (permalink / raw)
To: David.Bear@asu.edu; +Cc: pgsql-admin
David Bear <David.Bear@asu.edu> writes:
> On Mon, Aug 06, 2007 at 08:27:13PM -0400, Tom Lane wrote:
>> I suppose you could, but what's the point? Copying a single file that
>> doesn't currently exist on the destination plays to none of rsync's
>> strengths.
> well, I was using rysnc so I could copy to a different computer over
> ssh, not using the rsync protocol to catch diffs. I don't have a
> remote file system mounted so I can do just a cp.
If you are going over an ssh connection then scp seems like the
appropriate tool. Yeah, rsync would work, but it's just a useless
extra layer of software...
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PITR with rsync
2007-08-07 00:11 PITR with rsync David Bear <David.Bear@asu.edu>
2007-08-07 00:27 ` Re: PITR with rsync Tom Lane <tgl@sss.pgh.pa.us>
2007-08-07 00:46 ` Re: PITR with rsync David Bear <David.Bear@asu.edu>
2007-08-07 00:53 ` Re: PITR with rsync Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-08-07 02:28 ` Srinivas Kotapally <ksrinivas@bisil.com>
2 siblings, 0 replies; 7+ messages in thread
From: Srinivas Kotapally @ 2007-08-07 02:28 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; David.Bear@asu.edu; +Cc: pgsql-admin
Would a simple diff between 2 pg_dumps work if only data is being added in the
database (i.e. no alters or tables are added)? So say I do a pg_dump once a day
and one every 3 hrs there after and in the script I do a diff and store as a
PITR dump. Will that work, or is it too simplistic?
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]
On Behalf Of Tom Lane
Sent: Monday, August 06, 2007 8:54 PM
To: David.Bear@asu.edu
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PITR with rsync
David Bear <David.Bear@asu.edu> writes:
> On Mon, Aug 06, 2007 at 08:27:13PM -0400, Tom Lane wrote:
>> I suppose you could, but what's the point? Copying a single file
>> that doesn't currently exist on the destination plays to none of
>> rsync's strengths.
> well, I was using rysnc so I could copy to a different computer over
> ssh, not using the rsync protocol to catch diffs. I don't have a
> remote file system mounted so I can do just a cp.
If you are going over an ssh connection then scp seems like the appropriate
tool. Yeah, rsync would work, but it's just a useless extra layer of
software...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PITR with rsync
2007-08-07 00:11 PITR with rsync David Bear <David.Bear@asu.edu>
2007-08-07 00:27 ` Re: PITR with rsync Tom Lane <tgl@sss.pgh.pa.us>
2007-08-07 00:46 ` Re: PITR with rsync David Bear <David.Bear@asu.edu>
2007-08-07 00:53 ` Re: PITR with rsync Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-08-07 15:37 ` Steve Crawford <scrawford@pinpointresearch.com>
2 siblings, 0 replies; 7+ messages in thread
From: Steve Crawford @ 2007-08-07 15:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David.Bear@asu.edu; pgsql-admin
> If you are going over an ssh connection then scp seems like the
> appropriate tool. Yeah, rsync would work, but it's just a useless
> extra layer of software...
Actually, rsync has one edge over scp even where its other attributes
are moot: atomicity.
Rsync keeps the data in a temporary location then moves the whole file
into place at the conclusion of a successful transfer. Scp, however,
creates the file at start of transfer and that file will continue to
grow as the transfer progresses. A failure part-way through a scp
transfer will leave an incomplete file on the receiving end.
For situations where a process on the receiving side watches for and
processes newly added files or situations where I require a reliable
all-or-none transfer such as distribution of configuration files, I use
rsync.
Of course this attribute of rsync means that updates of an existing file
require sufficient disk-space for both old and new versions of the file.
Cheers,
Steve
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PITR with rsync
2007-08-07 00:11 PITR with rsync David Bear <David.Bear@asu.edu>
2007-08-07 00:27 ` Re: PITR with rsync Tom Lane <tgl@sss.pgh.pa.us>
2007-08-07 00:46 ` Re: PITR with rsync David Bear <David.Bear@asu.edu>
2007-08-07 00:53 ` Re: PITR with rsync Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-08-07 16:35 ` Greg Sabino Mullane <greg@turnstep.com>
2 siblings, 0 replies; 7+ messages in thread
From: Greg Sabino Mullane @ 2007-08-07 16:35 UTC (permalink / raw)
To: pgsql-admin
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
Tom Lane wrote:
>> well, I was using rysnc so I could copy to a different computer over
>> ssh, not using the rsync protocol to catch diffs. I don't have a
>> remote file system mounted so I can do just a cp.
> If you are going over an ssh connection then scp seems like the
> appropriate tool. Yeah, rsync would work, but it's just a useless
> extra layer of software...
In addition to the atomicity advantage, rsync allows compression of the
data on the fly. While you can also pass the -C flag to scp, I've found
that the high compressibility of WAL files means that rsync -z is noticeably
faster than scp -C (not that I would use the latter due to the partial file
issue).
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200708071229
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFGuJ6FvJuQZxSWSsgRA9xdAKDL4VZONTcmuXJl7Cz4bqiWXWVuZQCfRJso
2QIy8xDl88G4AnMFNrADVhM=
=iuKm
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-08-07 16:35 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-07 00:11 PITR with rsync David Bear <David.Bear@asu.edu>
2007-08-07 00:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-08-07 00:46 ` David Bear <David.Bear@asu.edu>
2007-08-07 00:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-08-07 02:28 ` Srinivas Kotapally <ksrinivas@bisil.com>
2007-08-07 15:37 ` Steve Crawford <scrawford@pinpointresearch.com>
2007-08-07 16:35 ` Greg Sabino Mullane <greg@turnstep.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