Thread: PITR with rsync
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"
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
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"
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
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
> 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
-----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-----