Usage ===== This section describes basic usage of the pglogical replication extension. Quick setup ----------- First the PostgreSQL server has to be properly configured to support logical decoding: :: wal_level = logical max_worker_processes = 10 # one per database needed on provider node # one per node needed on subscriber node max_replication_slots = 10 # one per node needed on provider node max_wal_senders = 10 # one per node needed on provider node shared_preload_libraries = pglogical If you are using PostgreSQL 9.5+ (this won’t work on 9.4) and want to handle conflict resolution with last/first update wins (see :ref:`Conflicts ` ), you can add this additional option to postgresql.conf: :: track_commit_timestamp = on # needed for last/first update wins conflict resolution # property available in PostgreSQL 9.5+ ``pg_hba.conf`` has to allow logical replication connections from localhost. Up until PostgreSQL 9.6, logical replication connections are managed using the ``replication`` keyword in ``pg_hba.conf`` . In PostgreSQL 10 and later, logical replication connections are treated by ``pg_hba.conf`` as regular connections to the provider database. Next the ``pglogical`` extension has to be installed on all nodes: :: CREATE EXTENSION pglogical; If using PostgreSQL 9.4, then the ``pglogical_origin`` extension also has to be installed on that node: :: CREATE EXTENSION pglogical_origin; Now create the provider node: :: SELECT pglogical.create_node( node_name := provider1, dsn := host=providerhost port=5432 dbname=db ); Add all tables in ``public`` schema to the ``default`` replication set. :: SELECT pglogical.replication_set_add_all_tables(default, ARRAY[public]); Optionally you can also create additional replication sets and add tables to them (see :ref:`Replication sets ` ). It’s usually better to create replication sets before subscribing so that all tables are synchronized during initial replication setup in a single initial transaction. However, users of bigger databases may instead wish to create them incrementally for better control. Once the provider node is setup, subscribers can be subscribed to it. First the subscriber node must be created: :: SELECT pglogical.create_node( node_name := subscriber1, dsn := host=thishost port=5432 dbname=db ); And finally on the subscriber node you can create the subscription which will start synchronization and replication process in the background: :: SELECT pglogical.create_subscription( subscription_name := subscription1, provider_dsn := host=providerhost port=5432 dbname=db ); SELECT pglogical.wait_for_subscription_sync_complete(subscription1); Creating subscriber nodes with base backups ------------------------------------------- In addition to the SQL-level node and subscription creation, pglogical also supports creating a subscriber by cloning the provider with ``pg_basebackup`` and starting it up as a pglogical subscriber. This is done with the ``pglogical_create_subscriber`` tool; see the ``--help`` output. Unlike ``pglogical.create_subscription`` ’s data sync options, this clone ignores replication sets and copies all tables on all databases. However, it’s often much faster, especially over high-bandwidth links. Node management --------------- Nodes can be added and removed dynamically using the SQL interfaces. - ``pglogical.create_node(node_name name, dsn text)`` Creates a node. 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(node_name name, ifexists bool)`` Drops the pglogical node. 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(node_name name, interface_name name, dsn text)`` Adds additional interface to a node. When 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(node_name name, interface_name name)`` Remove existing interface from a node. Parameters: - ``node_name`` - name of and existing node - ``interface_name`` - name of an existing interface Subscription management ----------------------- - ``pglogical.create_subscription(subscription_name name, provider_dsn text, replication_sets text[], synchronize_structure boolean, synchronize_data boolean, forward_origins text[], apply_delay interval)`` Creates a subscription from current node to the provider node. Command does not block, just initiates the action. Parameters: - ``subscription_name`` - name of the subscription, must be unique - ``provider_dsn`` - connection string to a provider - ``replication_sets`` - array of replication sets to subscribe to, these must already exist, default is “{default,default_insert_only,ddl_sql}” - ``synchronize_structure`` - specifies if to synchronize structure from provider to the subscriber, default false - ``synchronize_data`` - specifies if to synchronize data from provider to the subscriber, default true - ``forward_origins`` - array of origin names to forward, currently only supported values are empty array meaning don’t forward any changes that didn’t originate on provider node (this is useful for two-way replication between the nodes), or “{all}” which means replicate all changes no matter what is their origin, default is “{all}” - ``apply_delay`` - how much to delay replication, default is 0 seconds - ``force_text_transfer`` - force the provider to replicate all columns using a text representation (which is slower, but may be used to change the type of a replicated column on the subscriber), default is false The ``subscription_name`` is used as ``application_name`` by the replication connection. This means that it’s visible in the ``pg_stat_replication`` monitoring view. It can also be used in ``synchronous_standby_names`` when pglogical is used as part of :ref:`Synchronous Replication ` setup. Use ``pglogical.wait_for_subscription_sync_complete(sub_name)`` to wait for the subscription to asynchronously start replicating and complete any needed schema and/or data sync. - ``pglogical.drop_subscription(subscription_name name, ifexists bool)`` Disconnects the subscription and removes it from the catalog. Parameters: - ``subscription_name`` - name of the existing subscription - ``ifexists`` - if true, error is not thrown when subscription does not exist, default is false - ``pglogical.alter_subscription_disable(subscription_name name, immediate bool)`` Disables a subscription and disconnects it from the provider. Parameters: - ``subscription_name`` - name of the existing subscription - ``immediate`` - if true, the subscription is stopped immediately, otherwise it will be only stopped at the end of current transaction, default is false - ``pglogical.alter_subscription_enable(subscription_name name, immediate bool)`` Enables disabled subscription. Parameters: - ``subscription_name`` - name of the existing subscription - ``immediate`` - if true, the subscription is started immediately, otherwise it will be only started at the end of current transaction, default is false - ``pglogical.alter_subscription_interface(subscription_name name, interface_name name)`` Switch the subscription to use different interface to connect to provider node. Parameters: - ``subscription_name`` - name of an existing subscription - ``interface_name`` - name of an existing interface of the current provider node - ``pglogical.alter_subscription_synchronize(subscription_name name, truncate bool)`` All unsynchronized tables in all sets are synchronized in a single operation. Tables are copied and synchronized one by one. Command does not block, just initiates the action. Use ``pglogical.wait_for_subscription_sync_complete`` to wait for completion. Parameters: - ``subscription_name`` - name of the existing subscription - ``truncate`` - if true, tables will be truncated before copy, default false - ``pglogical.alter_subscription_resynchronize_table(subscription_name name, relation regclass)`` Resynchronize one existing table. The table may not be the target of any foreign key constraints. **WARNING: This function will truncate the table immediately, and only then begin synchronising it, so it will be empty while being synced** Does not block, use ``pglogical.wait_for_table_sync_complete`` to wait for completion. Parameters: - ``subscription_name`` - name of the existing subscription - ``relation`` - name of existing table, optionally qualified - ``pglogical.wait_for_subscription_sync_complete(subscription_name name)`` Wait for a subscription or to finish synchronization after a ``pglogical.create_subscription`` or ``pglogical.alter_subscription_synchronize`` . This function waits until the subscription’s initial schema/data sync, if any, are done, and until any tables pending individual resynchronisation have also finished synchronising. For best results, run ``SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL)`` on the provider after any replication set changes that requested resyncs, and only then call ``pglogical.wait_for_subscription_sync_complete`` on the subscriber. - ``pglogical.wait_for_table_sync_complete(subscription_name name, relation regclass)`` Same as ``pglogical.wait_for_subscription_sync_complete`` , but waits only for the subscription’s initial sync and the named table. Other tables pending resynchronisation are ignored. - ``pglogical.wait_slot_confirm_lsn`` ``SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL)`` Wait until all replication slots on the current node have replayed up to the xlog insert position at time of call on all providers. Returns when all slots’ ``confirmed_flush_lsn`` passes the ``pg_current_wal_insert_lsn()`` at time of call. Optionally may wait for only one replication slot (first argument). Optionally may wait for an arbitrary LSN passed instead of the insert lsn (second argument). Both are usually just left null. This function is very useful to ensure all subscribers have received changes up to a certain point on the provider. - ``pglogical.show_subscription_status(subscription_name name)`` Shows status and basic information about subscription. Parameters: :: - `subscription_name` - optional name of the existing subscription, when no name was provided, the function will show status for all subscriptions on local node - ``pglogical.show_subscription_table(subscription_name name, relation regclass)`` Shows synchronization status of a table. Parameters: - ``subscription_name`` - name of the existing subscription - ``relation`` - name of existing table, optionally qualified - ``pglogical.alter_subscription_add_replication_set(subscription_name name, replication_set name)`` Adds one replication set into a subscriber. Does not synchronize, only activates consumption of events. Parameters: - ``subscription_name`` - name of the existing subscription - ``replication_set`` - name of replication set to add - ``pglogical.alter_subscription_remove_replication_set(subscription_name name, replication_set name)`` Removes one replication set from a subscriber. Parameters: - ``subscription_name`` - name of the existing subscription - ``replication_set`` - name of replication set to remove There is also a ``postgresql.conf`` parameter, ``pglogical.extra_connection_options`` , that may be set to assign connection options that apply to all connections made by pglogical. This can be a useful place to set up custom keepalive options, etc. pglogical defaults to enabling TCP keepalives to ensure that it notices when the upstream server disappears unexpectedly. To disable them add ``keepalives = 0`` to ``pglogical.extra_connection_options`` . Replication sets ---------------- Replication sets provide a mechanism to control which tables in the database will be replicated and which actions on those tables will be replicated. Each replicated set can specify individually if ``INSERTs`` , ``UPDATEs`` , ``DELETEs`` and ``TRUNCATEs`` on the set are replicated. Every table can be in multiple replication sets and every subscriber can subscribe to multiple replication sets as well. The resulting set of tables and actions replicated is the union of the sets the table is in. The tables are not replicated until they are added into a replication set. There are three preexisting replication sets named “default”, “default_insert_only” and “ddl_sql”. The “default” replication set is defined to replicate all changes to tables in it. The “default_insert_only” only replicates INSERTs and is meant for tables that don’t have primary key (see :ref:`Limitations and restrictions ` section for details). The “ddl_sql” replication set is defined to replicate schema changes specified by ``pglogical.replicate_ddl_command`` The following functions are provided for managing the replication sets: - ``pglogical.create_replication_set(set_name name, replicate_insert bool, replicate_update bool, replicate_delete bool, replicate_truncate bool)`` This function creates a new replication set. Parameters: - ``set_name`` - name of the set, must be unique - ``replicate_insert`` - specifies if ``INSERT`` is replicated, default true - ``replicate_update`` - specifies if ``UPDATE`` is replicated, default true - ``replicate_delete`` - specifies if ``DELETE`` is replicated, default true - ``replicate_truncate`` - specifies if ``TRUNCATE`` is replicated, default true - ``pglogical.alter_replication_set(set_name name, replicate_inserts bool, replicate_updates bool, replicate_deletes bool, replicate_truncate bool)`` This function changes the parameters of the existing replication set. Parameters: - ``set_name`` - name of the existing replication set - ``replicate_insert`` - specifies if ``INSERT`` is replicated, default true - ``replicate_update`` - specifies if ``UPDATE`` is replicated, default true - ``replicate_delete`` - specifies if ``DELETE`` is replicated, default true - ``replicate_truncate`` - specifies if ``TRUNCATE`` is replicated, default true - ``pglogical.drop_replication_set(set_name text)`` Removes the replication set. Parameters: :: - `set_name` - name of the existing replication set - ``pglogical.replication_set_add_table(set_name name, relation regclass, synchronize_data boolean, columns text[], row_filter text)`` Adds a table to replication set. Parameters: - ``set_name`` - name of the existing replication set - ``relation`` - name or OID of the table to be added to the set - ``synchronize_data`` - if true, the table data is synchronized on all subscribers which are subscribed to given replication set, default false - ``columns`` - list of columns to replicate. Normally when all columns should be replicated, this will be set to NULL which is the default - ``row_filter`` - row filtering expression, default NULL (no filtering), see :ref:`Row Filtering ` for more info. **WARNING: Use caution when synchronizing data with a valid row filter.** Using ``synchronize_data=true`` with a valid ``row_filter`` is like a one-time operation for a table. Executing it again with modified ``row_filter`` won’t synchronize data to subscriber. Subscribers may need to call ``pglogical.alter_subscription_resynchronize_table()`` to fix it. - ``pglogical.replication_set_add_all_tables(set_name name, schema_names text[], synchronize_data boolean)`` Adds all tables in given schemas. Only existing tables are added, table that will be created in future will not be added automatically. For how to ensure that tables created in future are added to correct replication set, see :ref:`Automatic assignment of replication sets for new tables ` . Parameters: - ``set_name`` - name of the existing replication set - ``schema_names`` - array of names name of existing schemas from which tables should be added - ``synchronize_data`` - if true, the table data is synchronized on all subscribers which are subscribed to given replication set, default false - ``pglogical.replication_set_remove_table(set_name name, relation regclass)`` Remove a table from replication set. Parameters: - ``set_name`` - name of the existing replication set - ``relation`` - name or OID of the table to be removed from the set - ``pglogical.replication_set_add_sequence(set_name name, relation regclass, synchronize_data boolean)`` Adds a sequence to a replication set. Parameters: - ``set_name`` - name of the existing replication set - ``relation`` - name or OID of the sequence to be added to the set - ``synchronize_data`` - if true, the sequence value will be synchronized immediately, default false - ``pglogical.replication_set_add_all_sequences(set_name name, schema_names text[], synchronize_data boolean)`` Adds all sequences from the given schemas. Only existing sequences are added, any sequences that will be created in future will not be added automatically. Parameters: - ``set_name`` - name of the existing replication set - ``schema_names`` - array of names name of existing schemas from which tables should be added - ``synchronize_data`` - if true, the sequence value will be synchronized immediately, default false - ``pglogical.replication_set_remove_sequence(set_name name, relation regclass)`` Remove a sequence from a replication set. Parameters: - ``set_name`` - name of the existing replication set - ``relation`` - name or OID of the sequence to be removed from the set You can view the information about which table is in which set by querying the ``pglogical.tables`` view. Automatic assignment of replication sets for new tables ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ The event trigger facility can be used for describing rules which define replication sets for newly created tables. Example: :: CREATE OR REPLACE FUNCTION pglogical_assign_repset() RETURNS event_trigger AS $$ DECLARE obj record; BEGIN FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP IF obj.object_type = table THEN IF obj.schema_name = config THEN PERFORM pglogical.replication_set_add_table(configuration, obj.objid); ELSIF NOT obj.in_extension THEN PERFORM pglogical.replication_set_add_table(default, obj.objid); END IF; END IF; END LOOP; END; $$ LANGUAGE plpgsql; CREATE EVENT TRIGGER pglogical_assign_repset_trg ON ddl_command_end WHEN TAG IN (CREATE TABLE, CREATE TABLE AS) EXECUTE PROCEDURE pglogical_assign_repset(); The above example will put all new tables created in schema ``config`` into replication set ``configuration`` and all other new tables which are not created by extensions will go to ``default`` replication set. Additional functions -------------------- - ``pglogical.replicate_ddl_command(command text, replication_sets text[])`` Execute locally and then send the specified command to the replication queue for execution on subscribers which are subscribed to one of the specified ``replication_sets`` . Parameters: - ``command`` - DDL query to execute - ``replication_sets`` - array of replication sets which this command should be associated with, default “{ddl_sql}” - ``pglogical.synchronize_sequence(relation regclass)`` Push sequence state to all subscribers. Unlike the subscription and table synchronization function, this function should be run on provider. It forces update of the tracked sequence state which will be consumed by all subscribers (replication set filtering still applies) once they replicate the transaction in which this function has been executed. Parameters: :: - `relation` - name of existing sequence, optionally qualified Row Filtering ------------- PGLogical allows row based filtering both on provider side and the subscriber side. Row Filtering on Provider ^^^^^^^^^^^^^^^^^^^^^^^^^ On the provider the row filtering can be done by specifying ``row_filter`` parameter for the ``pglogical.replication_set_add_table`` function. The ``row_filter`` is normal PostgreSQL expression which has the same limitations on what’s allowed as the ``CHECK`` constraint. Simple ``row_filter`` would look something like ``row_filter := 'id > 0'`` which would ensure that only rows where values of ``id`` column is bigger than zero will be replicated. It’s allowed to use volatile function inside ``row_filter`` but caution must be exercised with regard to writes as any expression which will do writes will throw error and stop replication. It’s also worth noting that the ``row_filter`` is running inside the replication session so session specific expressions such as ``CURRENT_USER`` will have values of the replication session and not the session which did the writes. Row Filtering on Subscriber ^^^^^^^^^^^^^^^^^^^^^^^^^^^ On the subscriber the row based filtering can be implemented using standard ``BEFORE TRIGGER`` mechanism. It is required to mark any such triggers as either ``ENABLE REPLICA`` or ``ENABLE ALWAYS`` otherwise they will not be executed by the replication process.