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

success
replication failed
a row is missing on the remote db. Resulting from -compare_db
a row is missing on the local db. Resulting from -compare_db
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 https://llg.cubic.org/pg_trompe