Nodes v3.7

Each database that participates in pglogical replication must be represented by its own node. Each node must have a name that is unique amongst all nodes that will interact with and communicate with each other.

A pglogical node associates an operator-supplied node name with a connection string that pglogical subscriber nodes may use to communicate with the node. It also serves to mark a database as pglogical-enabled and as something with which to associate other pglogical state like replication sets or subscriptions.

Creating a pglogical node in an existing PostgreSQL databse using the pglogical.create_node function causes pglogical to launch a manager process for the node but doesn't cause any sort of replication to begin.

A node can be set up as a pglogical provider by having replication sets created and/or tables and DDL filters added to the default replication set. A node can be set up as a pglogical subscriber by having a subscription created on it after the node is created. Any given node can be a provider, a subscriber, or both; nothing in node creation marks the node as one or the other.

Setting up a node as a provider

Newly created pglogical nodes have empty replication sets and do not have any DDL replication filters enabled.

Replication sets specify what data from which tables and sequences should be sent to any subscriptions that use this node as a provider.

DDL replication filters capture and replicate schema changes to subscribers so that subscribers' definitions of tables, types, etc remain in sync with the provider.

The simplest way to set up a new node as a provider is to re-create the 'default' replication set with all existing and future tables and sequences auto-added to it. Then add a DDL replication filter to it to enable automatic schema replication. For example:

SELECT pglogical.create_node('mynode');
SELECT pglogical.drop_replication_set('default');
SELECT pglogical.create_replication_set('default', autoadd_tables := true, autoadd_sequences := true, autoadd_existing := true);
SELECT pglogical.replication_set_add_ddl('default', 'all_ddl', '.*', NULL);

A replication set drop and create is used here because pglogical.alter_replication_set cannot autoadd existing tables.

Setting up a node as a subscriber

Newly created nodes do not have any subscriptions so they won't receive changes from anywhere. Use pglogical.create_subscription(...) to populate a node with initial data and start receiving streams of changes. Or use the separate pglogical_create_subscriber to create a node and subscription from a basebackup or physical streaming replica.

Creating subscriber nodes is discussed in detail in "Subscriptions".

Node information

pglogical.local_node

A view containing node information but only for the local node.

pglogical.node

This table lists all PGL nodes.

pglogical.node Columns

pglogical.node_interface

This is a view that elaborates the information in pglogical.node, showing the DSN and node interface information.

pglogical.node_interface Columns

Node management

Nodes can be added and removed dynamically using SQL calls to functions provided by pglogical.

pglogical.create_node

Creates a pglogical node. Only one pglogical node may exist on a given PostgreSQL database. There's nothing special to mark a node as a provider or subscriber - one node may be either, or both.

Synopsis

pglogical.create_node(node_name name, dsn text)

Parameters

• node_name - name of the new node; only one node is allowed per database
• dsn - connection string to the node. For nodes that are supposed to be providers; this should be reachable from outside

pglogical.drop_node

Removes the node.

Synopsis

pglogical.drop_node(node_name name, ifexists bool)

Parameters

• node_name - name of an existing node
• ifexists - if true, error is not thrown when subscription does not exist; default is false

pglogical.alter_node_add_interface

Adds an interface to a node.

When a node is created, the interface for it is also created with the dsn specified in the create_node and with the same name as the node.

If the address or connection options required for a subscriber to connect to a provider change, create a new interface for the provider on the subscriber, then associate it with the subscription so that the subscriber switches to the new connection string. For example:

SELECT pglogical.alter_node_add_interface('providername', 'providername_newconnname', 'connstr');
SELECT pglogical.alter_subscription_interface('subscription_name', 'providername_newconnname');

At this time creating a new interface on the provider side has no effect on any subscribers. Subscribers use the connection string specified at subscription creation time, not the connection string declared for a node.

Synopsis

pglogical.alter_node_add_interface (
    node_name name,
    interface_name name,
    dsn text
)

When a node is created, the interface for it is also created with the dsn specified in the create_node and with the same name as the node. This interface allows adding alternative interfaces with different connection strings to an existing node.

Parameters

• node_name - name of an existing node
• interface_name - name of a new interface to be added
• dsn - connection string to the node used for the new interface

pglogical.alter_node_drop_interface

Remove an existing named interface from a node.

Synopsis

pglogical.alter_node_drop_interface(node_name name, interface_name name)

Parameters

• node_name - name of an existing node
• interface_name - name of an existing interface

• On this page
• Setting up a node as a provider
• Setting up a node as a subscriber
• Node information
• pglogical.node
• Node management