TuX as LLG logo
Software
DMX4Linux
Driver Suite for Linux
csv2iif.pl suite
convert PayPal transactions to IIF, OFX, QIF
Hardware
DMX30 Interface
128Ch SPP
DMX43 Interface
2out 2in EPP
LED Hardware
for Linux and Windows
EPROM Sampler
for 8 bits of sound
Misc
CatWeasel
Linux drivers for MK3/4 PCI
pg_trompe
PostgreSQL replication
trycatch
C exception/signal handling lib
Patches
to various software
Tools
and small scripts
Docs
misc documents
Links
to lighting stuff

replication.pl

NAME

replication.pl - pg_trompe application to replicate database events between two databases


SYNOPSIS

replication.pl -local_dbname <databasename> [ -local_dbuser <username>] [ -local_dbpass <passwort>] [ -local_dbhost <hostname>] [ -local_dbport <port>] [ -remote_dbname <databasename>] [ -remote_dbuser <username>] [ -remote_dbpass <passwort>] [ -remote_dbhost <hostname>] [ -remote_dbport <port>] [ -syslog_fac <facility string>] [ -compare_db] [ -stats]


OPTIONS

local_dbname

Required option. Sets the local dbname.

local_dbuser

Username to use for the local db.

local_dbpass

Password to use for the local db.

local_dbhost

Hostname of local db.

local_dbport

TCP Port of local db.

remote_dbname

The remote dbname. If not set the name of local_dbname is used.

remote_dbuser

Username for the remote db.

remote_dbpass

Password for the remote db.

remote_dbhost

Hostname of the remote db.

remote_dbport

Password of the remote db.

syslog_fac

Syslog facility to use for reporting information and errors. Defaults to LOCAL6. If set to the empty string, syslog will not be used. Any string which is supported by Sys::Syslog can be used.

compare_db

If set all tables in replicated databases are compared after the replication run.

stats

If set number of outstanding replication events are shown for both databases.


RETURN VALUE

  1. success
  2. replication failed
  3. a row is missing on the remote db. Resulting from -compare_db
  4. a row is missing on the local db. Resulting from -compare_db
  5. a row is not equal on local and remote. Resulting from -compare_db


USAGE

Database Schema Requirements

(pseudo) primary key

Each replicated table must have a column of the following syntax: nr serial8 primary key or nr serial8 not null unique. Once those nr values are assigned with the initial INSERT statement they may not be altered.

large object support

If the replication should handle large objects, they must be managed by a replicated table which also contains a column of the following format: lo oid unique. Thus each row of this table can manage/contain at most one large object. The lo column may be NULL.

unique values

To prevent uniqueness conflicts the tables should not contain unique columns. If you need unique values you should either manage them at the application layer or be really careful to not insert two unique values in two databases at the same time. Otherwise the replication might fail and the databases need to be manually cleaned (the uniqueness conflict resolved) before replication can continue.

Replication Setup

Before replication data between databases they must be configured. The replicated tables may contain data before you run the steps described below and this data will be included in the initial replication.

Execute replication.sql in each database. This creates the replication schema which contains all functions and tables used by pg_trompe.
Set the Node number of the database with select replication.node_num(1, 'Node Description'); Replace the 1 with the node number. Each node needs to have a unique number. By default the numbers 0-9 are supported. To raise this limit see section More than 10 Nodes.
For replicated table do select replication.register('tablename'); or select replication.registerlo('tablename'); depending if the table manages large objects (and thus contains an lo column) or not.

Your database is now prepared for replication.

Drop replication

If you want to stop replication a database follow these steps.

Tell all neighbour databases that the node goes out of replication by executing select replication.drop_node(node num); on them. node num is the node number of the to-be dropped node.
Execute drop.sql on the dropped node to remove the replication schema.

Replication

To replicate between two nodes use the replication.pl program. You have to supply database names and possibly usernames, password, hostnames and TCP ports so the application knows how to connect to the two databases involved. Upon the first replication run the two databases are introduced to each other and their table contents are replicated. Afterwards every change on the replicated tables is tracked in the replication.log

TRUNCATE support

The command truncate currently does not raise triggers, so it can't be monitored by external applications. If you want to truncate replicated tables use select replication.truncate('<ablename') instead.

DDL support

You can issue DDL command on replicated tables by using the function replication.dll. As parameter you supply one string which contains the DDL command. On the local node the command is executed immediately and it is replicated like any other events to remote databases. This means that the order of DDL and non-DDL command is preserved. Example: select replication.ddl('alter table user alter name set not null');

More than 10 Nodes

By default pg_trompe supports up to 10 nodes. If you need more nodes you have to change the configuration variable maxnodes in the replication.config table before registering any tables. This has to be done on all databases and of course the value of maxnodes needs to be the same on all databases. Example: update replication.config set maxnodes=100;


CONSTANTS

The following perl constants influence the applications setup.

PRIMARY_KEY

The column name of (pseudo) primary keys.

LO_KEY

The column name of large object identifiers.


AUTHOR

Dirk Jagdmann <doj@cubic.org>

Homepage http://llg.cubic.org/pg_trompe


Search:
http://llg.cubic.org © 2001-2017 by Dirk Jagdmann