Re: dbmirror revisions

On Thursday April 3 2003 4:37, Ed L. wrote:
> I've been modifying dbmirror and wanted to offer my changes to anyone
> that cared to experiment, FWIW.  My effort is ongoing, the docs aren't
> perfect, I make no claims of production readiness, and testing of this
> latest version has been minimal, so I strongly advise you to conduct your
> own thorough testing before considering a production deployment.  That
> said, it's a significantly improved solution for our async master-slave
> needs, with a few caveats below, and shouldn't be too hard to setup.
> ...
> AFAICS, there are still at least a few major drawbacks to this approach:
>
>     *  SEQUENCE objects are not handled;  nextval() will not be replicated,
> so sequence objects (and serial columns) between master and slave can
> easily get out of sync.  I wonder if eRServer has this same issue?

I've added code for brute-force replication of sequences in the tgz ball
below.  At each sync, the replicator contacts both master and slave and
compares every important aspect of every sequence object on the master with
that of the slave.  It then replicates any new sequence object or sequence
object change.  This causes dbmirror to hit both master and slave at least
N times on each sync, where N is the number of sequence objects; the
queries are quick.  I hate to hit the master like that, but I haven't
thought of a better option short of WAL-log replays.  It'd be a nice boost
to query for all sequence values in one SQL query, but I don't know how to
do it in a generalized manner.

    http://bluepolka.net/dbmirror/dbmirror-20030404-1446.tar.gz

I think a consistent view on the slave during active replication is not
quite guaranteed with this approach.  Sequence updates are not
transactional, we really don't know how to order them with respect to tuple
updates.  So someone reading the slave DB might possibly not see sequence
changes appear in the order in which they occurred on the master.  For our
warm spare/slave needs, it appears adequate.

Re DDL statement detection, I am thinking about incorporating a schema-only
pg_dump from both master and slave to compare schemas to alert to DDL
changes that could foul replication.  Maybe run it only every so often in
dbmirror.  None too elegant, but maybe better than nothing...

Ed