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"

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: PITR with rsync

David Bear 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: PITR with rsync

On Mon, Aug 06, 2007 at 08:27:13PM -0400, Tom Lane wrote:
> David Bear 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"

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: PITR with rsync

David Bear 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

Re: PITR with rsync

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 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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: PITR with rsync

> 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



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: PITR with rsync

-----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=2529DF6AB8F79407E94445B4BC9B90 6714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFGuJ6FvJuQZxSWSsgRA9xdAKDL4VZONTcmuXJl7Cz4bqiWXWVuZQCf RJso
2QIy8xDl88G4AnMFNrADVhM=
=iuKm
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings