dbmirror revisions

On Thursday April 3 2003 4:37, Ed L. wrote:

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?

Clarification: sequence objects for serial columns get out of sync easily,
but the replication maintains correct serial column values.

Ed

On Thursday April 3 2003 4:37, Ed L. wrote:

AFAICS, there are still at least a few major drawbacks to this approach:

* DML statements are not replicated (same for eRServer, AFAIK).

D'oh. I meant DDL, not DML.

Ed

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

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.

Aside from problems dealing with the loss of the communications link,
wouldn't it be better to implement a function call from the slave(s) to
the master to query the master's sequence? This can be done with pgperlu.

If there are INDEPENDENT sequences on the master and the slave, what's to
guarantee uniqueness?
--
Mike Nolan

On Thursday April 3 2003 4:37, some yahoo wrote:

* Eliminated bug in transaction ordering for replay. Updates
cannot be replicated in the order of the transactions (see archives for
discussion of why).

Upon further review, this bug report was the result of a misunderstanding of
the replication ordering. Both replication orderings, old and new, seem to
work fine so far.

Ed

On Friday April 4 2003 3:41, nolan@celery.tssi.com wrote:

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.

Aside from problems dealing with the loss of the communications link,
wouldn't it be better to implement a function call from the slave(s) to
the master to query the master's sequence? This can be done with
pgperlu.

Why do you think that would be better? It is already done in a perl
function that launches SQL ...

If there are INDEPENDENT sequences on the master and the slave, what's to
guarantee uniqueness?

Not sure I understand the question. Uniqueness in what respect?

Ed

I am probably out of my depth here as this seems to be getting into the
internals of the pgsql replication project. I'm not part of that project,
and I've only been using pgsql for a few months, though I have nearly
10 years of experience with Oracle, including master/slave replication.
(In retrospect, this is probably a thread I should not have jumped into.)

Why do you think that would be better? It is already done in a perl
function that launches SQL ...

If there are INDEPENDENT sequences on the master and the slave, what's to
guarantee uniqueness?

Not sure I understand the question. Uniqueness in what respect?

Correct me if I'm wrong, but isn't the primary goal of a sequence to
ensure uniqueness of the sequence values?

It's atomic and non-transactional (ie, sequences cannot be rolled back),
and in a data replication environment there must be some method of ensuring
that the sequence remains atomic, which is to say that there is really
only ONE sequence that is shared among the replication nodes.
--
Mike Nolan

On Saturday April 5 2003 10:38, nolan@celery.tssi.com wrote:

I am probably out of my depth here as this seems to be getting into the
internals of the pgsql replication project. I'm not part of that...

Just fyi, dbmirror is not a part of the official pgsql replication project.
It's an asyncronous replication contribution from Steven Singer. Some may
find it possibly useful between now and when pgsql replication is released,
which may not be for some number of months still. I would expect that
project to be more useful given the considerable effort devoted to it.

If there are INDEPENDENT sequences on the master and the slave,
what's to guarantee uniqueness?

Not sure I understand the question. Uniqueness in what respect?

Correct me if I'm wrong, but isn't the primary goal of a sequence to
ensure uniqueness of the sequence values?

It's atomic and non-transactional (ie, sequences cannot be rolled back),
and in a data replication environment there must be some method of
ensuring that the sequence remains atomic, which is to say that there is
really only ONE sequence that is shared among the replication nodes.

My context is asyncronous master-slave replication. Here, the slaves do not
take updates, except from the master replicator. If someone is accessing
the slave and incrementing the sequences outside the replicator process,
that's a problem for this approach. I think your concern is valid for
multi-master, but irrelevant for my master-slave interests.

Steven had some ideas about how to do a limited multi-master with dbmirror.
Then I suppose the uniqueness concern would come into play.

Ed

Are any of these changes ready for CVS for 7.4?

---------------------------------------------------------------------------

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.

There are enough changes that I would hardly consider this a patch, closer
to an overhaul, since I've removed files, renamed others, and added new
files. Among the changes I've made so far:

* Added script for easier setup of many tables/dbs/slaves;
* Added initial support for multiple master replicating distinct data to a
single slave;
* Added batching to minimize load on master and net traffic. You can grab
a configurable number of updates to replicate before hitting the master
again.

* Added port specification;
* Wrapped all replication in transactions;
* Bulletproofed against downed master or slave;
* Started modularization of DB access layer, added some error
handling;
* Added a number of config vars for sync delays, etc;
* Eliminated bug in transaction ordering for replay. Updates cannot
be replicated in the order of the transactions (see archives for discussion
of why).

* Eliminated need for clear_pending.pl by making dbmirror.pl
self-clearing;
* Collasped schema into 1 queue table for performance;
* Changed sequence ID column types to BIGINT for 64-bit sequence;
* Added reconnection handling for robustness;
* Added local tracking of last seq_id to help with recovery
robustness;
* Added master/slave compatibility checking;
* Enabled slave setup during production service so master does not
have to stop serving.
* Renamed tables to minimize namespace conflicts;
* Added lots of logging/debug messages;

* Maybe a few other things I've forgotten...

AFAICS, there are still at least a few major drawbacks to this approach:

* DML statements are not replicated (same for eRServer, AFAIK).

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

* Mass updates/deletes/inserts of 5000 rows with a single SQL command on
the master will result in 5000 individual trigger-firings, and 5000
individual replication inserts on the slave. Rumor has it eRServer's
snapshot gets around this problem.

The code is here:

http://bluepolka.net/dbmirror/dbmirror-20030403-1605.tar.gz

Ed

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Ed, where are you on these dbmirror improvements?

---------------------------------------------------------------------------

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.

There are enough changes that I would hardly consider this a patch, closer
to an overhaul, since I've removed files, renamed others, and added new
files. Among the changes I've made so far:

* Added script for easier setup of many tables/dbs/slaves;
* Added initial support for multiple master replicating distinct data to a
single slave;
* Added batching to minimize load on master and net traffic. You can grab
a configurable number of updates to replicate before hitting the master
again.

* Added port specification;
* Wrapped all replication in transactions;
* Bulletproofed against downed master or slave;
* Started modularization of DB access layer, added some error
handling;
* Added a number of config vars for sync delays, etc;
* Eliminated bug in transaction ordering for replay. Updates cannot
be replicated in the order of the transactions (see archives for discussion
of why).

* Eliminated need for clear_pending.pl by making dbmirror.pl
self-clearing;
* Collasped schema into 1 queue table for performance;
* Changed sequence ID column types to BIGINT for 64-bit sequence;
* Added reconnection handling for robustness;
* Added local tracking of last seq_id to help with recovery
robustness;
* Added master/slave compatibility checking;
* Enabled slave setup during production service so master does not
have to stop serving.
* Renamed tables to minimize namespace conflicts;
* Added lots of logging/debug messages;

* Maybe a few other things I've forgotten...

AFAICS, there are still at least a few major drawbacks to this approach:

* DML statements are not replicated (same for eRServer, AFAIK).

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

* Mass updates/deletes/inserts of 5000 rows with a single SQL command on
the master will result in 5000 individual trigger-firings, and 5000
individual replication inserts on the slave. Rumor has it eRServer's
snapshot gets around this problem.

The code is here:

http://bluepolka.net/dbmirror/dbmirror-20030403-1605.tar.gz

Ed

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Saturday August 16 2003 5:20, Bruce Momjian wrote:

Ed, where are you on these dbmirror improvements?

My changes are complete as of late April, and I have not worked much on it
since. The changes are not in your CVS. Need to work with the dbmirror
author to see if that makes sense at this point.

Ed