- Архив списков рассылки pgsql-general

That's the beatuy of rsync. You don't!!! It onlt transfers the modified data
(or a whole block that contains modified data.) When I was beta testing
Lycoris (a linux distrib) they were constantly updating the ISO image. Rsync
only took seconds to update the few kb or megs of changes. True, at 600 megs
there is some lag time as it generates the checksums, but overall it is very
efficient.

Anyway, my desire is to never take it down. This will be running on computers
where people won't know it's running, nor do they know anything aside from how
to check email and I don't want to spend my time figuring out why postmaster
didn't come back up, etc...

Another problem, and this is a big one, is I can't shutdown my master
postmaster here. I have queries running all the time on multiple databases on
1 postmaster. I can make sure that nothing is running in the database that is
being updated, but I can't lock people out of the 600-some other databases
under this postmaster. No way, uh-uh, never going to happen!

I should be able to perform a flush and have all transactions committed for a
particular database. This flushed state should be identical to postmaster
being down, without it being down. After master and slave are flushed, then
the slave will send changes to the master. When our audits are passed and any
modifications done (sometimes we make corrections to the data), then the
master copy is sync to the slave, and the slave is then allowed to modify data
again.

Does that help explain my situation?

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Tuesday, January 28, 2003 9:59 AM
To: Jason Hihn
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Using RSYNC for replication?


Jason Hihn <jhihn1@umbc.edu> writes:
> A sequence of events ocurred to me today that left me wondering if I can
> rsync the raw files as a form of replication.

In general, you can't.  There are very precise synchronization
requirements among the files making up the data directory, and there's
no way that a separate process like tar or rsync is going to capture a
consistent snapshot of all the files.

As an example: one of the recent reports of duplicate rows (in a table
with a unique index) seems to have arisen because someone tried to take
a tar dump of $PGDATA while the postmaster was running.  When he
restored the tar, two different versions of a recently-updated row both
looked to be valid, because the table's data file was out of sync with
pg_clog.

If you had a dump utility that was aware of the synchronization
requirements, it *might* be possible to dump the files in an order that
would work reliably (I'm not totally sure about it, but certainly data
files before WAL would be one essential part of the rules).  But out-of-
the-box tar or rsync won't get it right.

> I'd like to keep postmaster running, but flush and lock everything,
> then perform the copy via rsync so only the new data is propigated,
> all while postmaster is running.
> In general, data is only added to a few tables in the database, with
> updates occuring infrequently to the rest. Rarely are deletes ever done.
>    During the sync neither DB will change except as part of the rsync.

If you checkpoint before the rsync, and guarantee that no updates occur
between that and the conclusion of the rsync, and *take down the
destination postmaster* while it runs, then it might possibly work.
But I'd never trust it.  I'd also kinda wonder what's the point, if you
have to prevent updates; you might as well shut down the postmaster and
avoid the risk of problems.

A final note is that I doubt this would be very efficient: wouldn't
rsync have to ship entire table files (and entire WAL log files) for
even the most piddling change?

            regards, tom lane

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