[Pgpool-general] What level of difference is checked in replication?

[Yoshiyuki Asaba]

Hi,

From: "A P" <afpalsson at gmail.com>
Subject: [Pgpool-general] What level of difference is checked in replication?
Date: Wed, 27 Feb 2008 09:13:53 +0100

> A few days ago I was looking around for a tool to keep 2
> postgresql-servers contain the same data so I found PGPool-II. It was
> ridiculously easy to set up by just setting a few parameters.
> Now my databases seem to be nicely synchronized. Excellent tool! :)

Thanks! :)


> Is it true that PGPool-II will only detect mismatches on number of
> tables/rows and not row-data?
> 
> I manually added a new row in the second database but not in the first.
> Querying via pgpool didn't give any indication that there was data mismatch?

You can check mismatches to execute the following query.

  /*REPLICATION*/ SELECT ...

The above query is replicated and pgpool checks the number of rows.

However, I don't recommend to use /*REPLICATION*/ hint for checking.
Because it may cause a performance issue and data misdetection.


> Is the 'replication_stop_on_mismatch'-setting broken?

If a result of a query is different, pgpool stop replication with
replication_stop_on_mismatch.

  % psql -p 9999 -c 'CREATE TABLE t (a int PRIMARY KEY)' replication
  % psql -p 5432 -c 'INSERT INTO t VALUES(1)' replication
  % psql -p 9999 -c 'INSERT INTO t VALUES(1)' replication

Master returns "duplicate key" error and secondary does not return
error. In this case, pgpool detaches secondary node.

Regards,
--
Yoshiyuki Asaba
y-asaba at sraoss.co.jp