Thread: PITR with rsync

PITR with rsync

From
David Bear
Date:
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"

Re: PITR with rsync

From
Tom Lane
Date:
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

Re: PITR with rsync

From
David Bear
Date:
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"

Re: PITR with rsync

From
Tom Lane
Date:
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

Re: PITR with rsync

From
"Srinivas Kotapally"
Date:
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


Re: PITR with rsync

From
Steve Crawford
Date:
> 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



Re: PITR with rsync

From
"Greg Sabino Mullane"
Date:
-----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-----