Cybertec Unique Cluster extensions
----------------------------------

We introduced three extensions:
* Configurable Two Phase Commit
* Activity monitoring
* Consistency checker

Configurable Two Phase Commit
-----------------------------
This feature is controlled by the following
option in postgresql.conf:

  replication_use_2pc = true

When it's "yes" then whenever a COMMIT is seen by
the replication, it will be broken up into two phases
(PREPARE TRANSACTION and COMMIT PREPARED) so the
replicated nodes will use Two Phase Commit on
the transaction. As it is in postgresql.conf
is is configurable per-node.

Activity monitoring
-------------------
The replication now counts the DDL and DML
statements per node and a small command line
program is provided that can report these
statistics.

$ pgrplmon 
RPLMonitor v1.0
Usage: ./pgrplmon hostname port

The statistics show that whenever an SQL
statement is issued on one node, it will
be counted against that node.

The pgreplicate process listens on the port
controlled by the option
  <RPLMonitor_Port> 8401 </RPLMonitor_Port>

E.g. there are two nodes and we issued some
CREATE TABLE/DROP TABLE statements (counted
as "# of DDL"), 3 INSERTs and 1 UPDATE on host1
and only 1 UPDATE on host2.

# pgrplmon host1.localdomain 8401
RPLMonitor v1.0
DB server #1
        Hostname: host1.localdomain
        Resolved host: 192.168.0.1
        Port: 5432
        # of started transactions: 0
        # of COMMITed transactions: 0
        # of ROLLBACKed transactions: 0
        # of INSERT statements: 3
        # of UPDATE statements: 1
        # of DELETE statements: 0
        # of DDL statements: 3
DB server #2
        Hostname: host2.localdomain
        Resolved host: 192.168.0.2
        Port: 5432
        # of started transactions: 0
        # of COMMITed transactions: 0
        # of ROLLBACKed transactions: 0
        # of INSERT statements: 0
        # of UPDATE statements: 1
        # of DELETE statements: 0
        # of DDL statements: 0
2 active cluster members were found

Consistency checker
-------------------
The consistency checker enabled the user
to check whether there are any difference
between database tables across replicated
nodes. Differences may occur if the nodes
are writable when standalone and the
repliation is down for some reason:

  <When_Stand_Alone> read_write </When_Stand_Alone>

The pgreplicate process listens on a new
port called:
  <RPLConsistencyChecker_Port> 8501 </RPLConsistencyChecker_Port>

A command line utility is provided to initiate
a check on a table:

$ ./pgrplcc 
RPLMonitor v1.0
Usage: ./pgrplcc hostname port dbname table

The output of the checker may indicate the
following conditions:

- success (no difference)
- failure and the possible causes, like:
  - Connection failed on a node
  - START TRANSACTION/DECLARE CURSOR failed for the table
  - FETCH failed for the table
  - Different number of records compared to
    the master (the first node listed in
    pgreplicate.conf). The message indicate
    the number of the secondary node and the
    number of the detected records. (The number
    of the records that were fetched up to
    the point of the error.)
  - Different number of fields compared to
    the master node. The number of fields
    and the number of differing nodes are
    indicated.
  - Different order of fields (different field
    names)
  - Different type of fields.
  - Actual data differences.

The data difference is detected on the assumption
that PostgreSQL returns the records in tuple order
if no ORDER BY clause is givem. The data is compared
record-by-recod and field-by-field.

The check is performed with the following sequence:

BEGIN
DECLARE CURSOR
FETCH 1000 FROM cursor in a loop
ROLLBACK

Because of the cursor usage it won't allocate
too much memory but the actual field-by-field
check may require large amount of CPU power
as the replication is done with the check
in parallel.
