Synopsis:

Description:

Must be the very first command in every slonik script. Defines the namespace in which all Slony-I specific functions, procedures, tables and sequences are defined. The namespace name is built by prefixing the given string literal with an underscore. This namespace will be identical in all databases that participate in the same replication group.

No user objects are supposed to live in this namespace and the namespace is not allowed to exist prior to adding a database to the replication system. Thus, if you add a new node using pg_dump -s on a database that is already in the cluster of replicated databases, you will need to drop the namespace via the SQL command DROP SCHEMA _testcluster CASCADE; .

Example:

CLUSTER NAME = 'testcluster';

Synopsis:

Description:

Describes how the slonik utility can reach a nodes database in the cluster from where it is run (likely the DBA's workstation). The conninfo string is the string agrument given to the PQconnectdb() libpq function. The user as to connect must be the special replication superuser, as some of the actions performed later may include operations that are strictly reserved for database superusers by PostgreSQL.

The slonik utility will not try to connect to the databases unless some subsequent command requires the connection.

Note: As mentioned in the original documents, Slony-I is designed as an enterprise replication system for data centers. It has been assumed throughout the entire development that the database servers and administrative workstations involved in replication and/or setup and configuration activities can use simple authentication schemes like trust. Alternatively, libpq can read passwords from .pgpass .

Note: If you need to change the DSN information for a node, as would happen if the IP address for a host were to change, you may submit the new information using this command, and that configuration will be propagated. Existing slon processes will need to be restarted in order to become aware of the configuration change.

Example:

NODE 1 ADMIN CONNINFO = 'dbname=testdb host=server1 user=slony';

Synopsis:

Description:

Prints the string literal on standard output.

Example:

ECHO 'Node 1 initialized successfully';

Synopsis:

Description:

Terminates script execution immediately, rolling back every open transaction on all database connections. The slonik utility will return the given value as its program termination code.

Example:

EXIT 0;

Synopsis:

Description:

Initialize the first node in a new Slony-I replication cluster. The initialization process consists of creating the cluster namespace, loading all the base tables, functions, procedures and initializing the node.

For this process to work, the SQL scripts of the Slony-I system must be installed on the DBA workstation (the computer currently executing the slonik utility), while on the system where the node database is running the shared objects of the Slony-I system must be installed in the PostgreSQL library directory. Also the procedural language PL/pgSQL is assumed to be installed in the target database already.

Example:

INIT CLUSTER (
    ID = 1,
    COMMENT = 'Node 1'
);

Synopsis:

Description:

Initialize a new node and add it to the configuration of an existing cluster.

The initialization process consists of creating the cluster namespace in the new node (the database itself must already exist), loading all the base tables, functions, procedures and initializing the node. The existing configuration of the rest of the cluster is copied from the event node.

The same installation requirements as for the init cluster command apply.

Example:

STORE NODE (
    ID = 2,
    COMMENT = 'Node 2'
);

Synopsis:

Description:

Drop a node. This command removes the specified node entirely from the replication systems configuration. If the replication daemon is still running on that node (and processing events), it will attempt to uninstall the replication system and terminate itself.

Example:

DROP NODE (
    ID = 2
);

Synopsis:

Description:

Restores all tables to the unlocked state, with all original user triggers, constraints and rules, eventually added Slony-I specific serial key columns dropped and the Slony-I schema dropped. The node becomes a standalone database. The data is left untouched.

Example:

UNINSTALL NODE (
    ID = 3
);

Synopsis:

Description:

Causes an eventually running replication daemon on the specified node to shutdown and restart itself. Theoretically this command should be obsolete. In practice, TCP timeouts can delay critical configuration changes to actually happen in the case where a former forwarding node failed and needs to be bypassed by subscribers.

Example:

RESTART NODE 2;

Synopsis:

Description:

Configures how the replication daemon of one node connects to the database of another node. If the replication system is supposed to use a special backbone network segment, this is the place to user the special IP addresses or hostnames. An existing configuration can be overwritten.

The conninfo string must contain all information to connect to the database as the replication superuser. The names server or client have nothing to do with the particular role of a node within the cluster configuration. It should be simply viewed as "the server has the message or data that the client is supposed to get". For a simple 2 node setup, paths into both directions must be configured.

It does not do any harm to configure path information from every node to every other node (full cross product). The connections are not established unless they are required to actually transfer events or confirmations because of listen entries or data because of subscriptions.

Example:

STORE PATH (
    SERVER = 1,
    CLIENT = 2,
    CONNINFO = 'dbname=testdb host=server1 user=slony'
);

Synopsis:

Description:

Remove the connection information between server and client.

Example:

DROP PATH (
    SERVER = 1,
    CLIENT = 2
);

Synopsis:

Description:

A listen entry causes a node (receiver) to query an event provider for events that originate from a specific node, as well as confirmations from every existing node. It requires a path to exist so that the receiver (as client) can connect to the provider (as server).

Every node in the system must listen for events from every other node in the system. As a general rule of thumb, a subscriber (see SUBSCRIBE SET) should listen for events of the set's origin on the same provider, where it receives the data from. In turn, the origin of the data set should listen for events from the origin in the opposite direction. A node can listen for events from one and the same origin on different providers at the same time. However, to process SYNC events from that origin, all data providers must have the same or higher sync status, so this will not result in any faster replication behaviour.

Example:

STORE LISTEN (
    ORIGIN = 1,
    RECEIVER = 2,
);

Synopsis:

Description:

Remove a listen configuration entry.

Example:

DROP LISTEN (
    ORIGIN = 1,
    RECEIVER = 2,
);

Synopsis:

Description:

In the Slony-I replication system, every replicated table is required to have at least one UNIQUE constraint who's columns are declared NOT NULL. Any primary key satisfies this requirement.

As a last resort, this command can be used to add such an attribute to a table that does not have a primary key. Since this modification can have unwanted side effects, it is strongly recommended that users add a unique and not null attribute by other means.

Example:

TABLE ADD KEY (
    NODE ID = 1,
    FULLY QUALIFIED NAME = 'public.history'
);

Synopsis:

Description:

In the Slony-I replication system, replicated tables are organized in sets. As a general rule of thumb, a set should contain all the tables of one application, that have relationships. In a well designed application, this is equal to all the tables in one schema.

The smallest unit one node can subscribe for replication from another node is a set. A set always has an origin. In classical replication terms, that would be the "master." Since in Slony-I a node can be the "master" over one set, while receiving replication data in the "slave" role for another at the same time, this terminology may easily become misleading and should therefore be replaced with set origin and subscriber.

Example:

CREATE SET (
    ID = 1,
    ORIGIN = 1,
    COMMENT = 'Tables of ticket system'
);

Synopsis:

Description:

Drop a set of tables from the Slony-I configuration. This automatically unsubscribes all nodes from the set and restores the original triggers and rules on all subscribers.

Example:

DROP SET (
    ID = 5,
    ORIGIN = 1
);

Synopsis:

Description:

Merge a set of tables and sequences into another one. This function is a workaround for the problem that it is not possible to add tables/sequences to already-subscribed sets. One may create a temporary set, add the new objects to that, subscribe all nodes currently subscribed to the other set to this new one, and then merge the two together.

This request will fail if the two sets do not have exactly the same set of subscribers.

Example:

MERGE SET (
    ID = 2,
    ADD ID = 9999,
    ORIGIN = 1
);

Synopsis:

Description:

Add an existing user table to a replication set. The set cannot currently be subscribed by any other node - that functionality is supported by theMERGE SET command.

Example:

SET ADD TABLE (
    SET ID = 1,
    ORIGIN = 1,
    ID = 20,
    FULLY QUALIFIED NAME = 'public.tracker_ticket',
    COMMENT = 'Support ticket'
);

Synopsis:

Description:

Add an existing user sequence to a replication set. The set cannot currently be subscribed by any other node - that functionality is supported by the MERGE SET command.

Example:

SET ADD SEQUENCE (
    SET ID = 1,
    ORIGIN = 1,
    ID = 21,
    FULLY QUALIFIED NAME = 'public.tracker_ticket_id_seq',
    COMMENT = 'Support ticket id sequence'
);

Synopsis:

Description:

Drop an existing user table to a replication set.

Note that this action will not drop a candidate primary key created using TABLE ADD KEY .

Example:

SET DROP TABLE (
    ORIGIN = 1,
    ID = 20,
);

Synopsis:

Description:

Drops an existing user sequence from a replication set.

Example:

SET DROP SEQUENCE (
    ORIGIN = 1,
    ID = 21
);

Synopsis:

Description:

Change the set a table belongs to. The current set and the new set must origin on the same node and subscribed by the same nodes. CAUTION: Due to the way subscribing to new sets works make absolutely sure that the subscription of all nodes to the sets is completely processed before moving tables. Moving a table too early to a new set causes the subscriber to try and add the table already during the subscription process, which fails with a duplicate key error and breaks replication.

Example:

SET MOVE TABLE (
    ORIGIN = 1,
    ID = 20,
    NEW SET = 3
);

Synopsis:

Description:

Change the set a sequence belongs to. The current set and the new set must origin on the same node and subscribed by the same nodes. CAUTION: Due to the way subscribing to new sets works make absolutely sure that the subscription of all nodes to the sets is completely processed before moving sequences. Moving a sequence too early to a new set causes the subscriber to try and add the sequence already during the subscription process, which fails with a duplicate key error and breaks replication.

Example:

SET MOVE SEQUENCE (
    ORIGIN = 1,
    ID = 54,
    NEW SET = 3
);

Synopsis:

Description:

By default, all user defined triggers and constraints are disabled on all subscriber nodes while a table is replicated. This command can be used to explicitly exclude a trigger from being disabled.

Example:

STORE TRIGGER (
    TABLE ID = 2,
    TRIGGER NAME = 'cache_invalidation'
);

Synopsis:

Description:

Remove the special handling for the specified trigger.

Example:

DROP TRIGGER (
    TABLE ID = 2,
    TRIGGER NAME = 'cache_invalidation'
);

Synopsis:

Description:

Causes a node (subscriber) to start replicating a set of tables either from the origin or from another provider node, which must be a currently forwarding subscriber itself.

The application tables contained in the set must already exist and should ideally be currently empty. The current version of Slony-I will not attempt to copy the schema of the set. The replication daemon will start copying the current content of the set from the given provider and then try to catch up with any update activity that happened during that copy process. After successful subscription, the tables are guarded on the subscriber using triggers against accidental updates by the application.

Note: If you need to revise subscription information for a node, you may submit the new information using this command, and the new configuration will be propagated throughout the replication network. The normal reason to revise this information is that you want a node to subscribe to a different provider node, or for a node to become a "forwarding" subscriber so it may later become the provider for a later subscriber.

Example:

SUBSCRIBE SET (
    ID = 1,
    PROVIDER = 1,
    RECEIVER = 3,
    FORWARD = YES
);

Synopsis:

Description:

Stops the subscriber from replicating the set. The tables are opened up for full access by the client application on the former subscriber. The tables are not truncated or otherwise modified. All original triggers, rules and constraints are restored.

Warning! Resubscribing an unsubscribed set requires a complete fresh copy of data from the provider to be transferred since the tables have been subject to possible independent modifications.

Example:

UNSUBSCRIBE SET (
    ID = 1,
    RECEIVER = 3
);

Synopsis:

Description:

Guards a replication set against client application updates in preparation for a MOVE SET command.

This command must be the first in a possible statement group (try). The reason for this is that it needs to commit the changes made to the tables (adding a special trigger function) before it can wait for every concurrent transaction to finish. At the same time it cannot hold an open transaction to the same database itself since this would result in blocking itself forever.

Example:

LOCK SET (
    ID = 1,
    ORIGIN = 1
);

Synopsis:

Description:

Unlock a previously locked set.

Example:

UNLOCK SET (
    ID = 1,
    ORIGIN = 1
);

Synopsis:

Description:

Changes the origin of a set from one node to another. The new origin must be a current subscriber of the set. The set must currently be locked on the old origin.

After this command, the set cannot be unlocked on the old origin any more. The old origin will continue as a forwarding subscriber of the set and the subscription chain from the old origin to the new origin will be reversed, hop by hop. As soon as the new origin has finished processing the event (that includes any outstanding sync events that happened before, i.e. fully catching up), the new origin will take over and open all tables in the set for client application update activity.

This is not failover, as it requires a fully functional old origin node.

Example:

MOVE SET (
    ID = 1,
    OLD ORIGIN = 1,
    NEW ORIGIN = 3
);

Synopsis:

Description:

WARNING: This command will abandon the status of the failed node. There is no possibility to let the failed node join the cluster again without rebuilding it from scratch as a slave slave.

The failover command causes the backup node to take over all sets that currently originate on the failed node. Slonik will contact all other direct subscribers of the failed node to determine which node has the highest sync status for each set. If another node has a higher sync status than the backup node, the replication will first be redirected so that the backup node replicates against that other node, before assuming the origin role and allowing update activity.

After successful failover, all former direct subscribers of the failed node become direct subscribers of the backup node. The failed node can and should be removed from the configuration with DROP NODE.

Example:

FAILOVER (
    ID = 1,
    BACKUP NODE = 1,
);

Synopsis:

Description:

Executes a script containing arbitrary SQL statements on all nodes that are subscribed to a set at a common controlled point within the replication transaction stream.

The specified event origin must be the origin of the set. The script file must not contain any START or COMMIT TRANSACTION calls. (This may change in PostgreSQL 8.0 once nested transactions, aka savepoints, are supported) In addition, non-deterministic DML statements (like updating a field with CURRENT_TIMESTAMP) must be avoided, since the data changes done by the script are explicitly not replicated.

Example:

EXECUTE SCRIPT (
    SET ID = 1,
    FILENAME = 'changes_20040510.sql',
    EVENT NODE = 1
);

Synopsis:

Description:

Waits for event confirmation.

Slonik remembers the last event generated on every node during script execution (events generated by earlier calls are currently not checked). In certain situations it is necessary that events generated on one node (such as CREATE SET) are processed on another node before issuing more commands (for instance, SUBSCRIBE SET). WAIT FOR EVENT may be used to cause the slonik script to wait until the subscriber node is ready for the next action.

WAIT FOR EVENT must be called outside of any try block in order to work, since new confirm messages don't become visible within a transaction.

Example:

REINIT NODE

Back to Index