replication.pl - pg_trompe application to replicate database events between two databases
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]
Required option. Sets the local dbname.
Username to use for the local db.
Password to use for the local db.
Hostname of local db.
TCP Port of local db.
The remote dbname. If not set the name of local_dbname is used.
Username for the remote db.
Password for the remote db.
Hostname of the remote db.
Password of the remote db.
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.
If set all tables in replicated databases are compared after the replication run.
If set number of outstanding replication events are shown for both databases.
- 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
- (pseudo) primary key
Each replicated table must have a column of the following syntax:
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
lo oid unique. Thus each row of this table can
manage/contain at most one large object. The lo column may be
- 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.
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
- Set the Node number of the database with
replication.node_num(1, 'Node Description'); Replace the
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
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.
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
node num is the node number of the to-be dropped node.
- Execute drop.sql on the dropped node to remove the
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
truncate currently does not raise triggers, so it can't
be monitored by external applications. If you want to truncate
replicated tables use
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');
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
to be the same on all databases. Example:
The following perl constants influence the applications setup.
The column name of (pseudo) primary keys.
The column name of large object identifiers.
Dirk Jagdmann <email@example.com>