DDL replication filtering v5

Suggest edits

See also DDL replication filtering

bdr.replication_set_add_ddl_filter

This function adds a DDL filter to a replication set.

Any DDL that matches the given filter is replicated to any node that's subscribed to that set. This also affects replication of PGD admin functions.

This doesn't prevent execution of DDL on any node. It only alters whether DDL is replicated to other nodes. Suppose two nodes have a replication filter between them that excludes all index commands. Index commands can still be executed freely by directly connecting to each node and executing the desired DDL on that node.

The DDL filter can specify a command_tag and role_name to allow replication of only some DDL statements. The command_tag is the same as those used by EVENT TRIGGERs for regular PostgreSQL commands. A typical example might be to create a filter that prevents additional index commands on a logical standby from being replicated to all other nodes.

You can filter the PGD admin functions used by using a tagname matching the qualified function name. For example, bdr.replication_set_add_table is the command tag for the function of the same name. In this case, this tag allows all PGD functions to be filtered using bdr.*.

The role_name is used for matching against the current role that is executing the command. Both command_tag and role_name are evaluated as regular expressions, which are case sensitive.

Synopsis

bdr.replication_set_add_ddl_filter(set_name name,
                                   ddl_filter_name text,
                                   command_tag text,
                                   role_name text DEFAULT NULL,
                                   base_relation_name text DEFAULT NULL,
                                   query_match text DEFAULT NULL,
                                   exclusive boolean DEFAULT FALSE)

Parameters

  • set_name — name of the replication set; if NULL then the PGD group default replication set is used
  • ddl_filter_name — name of the DDL filter; this must be unique across the whole PGD group
  • command_tag — regular expression for matching command tags; NULL means match everything
  • role_name — regular expression for matching role name; NULL means match all roles
  • base_relation_name — reserved for future use, must be NULL
  • query_match — regular expression for matching the query; NULL means match all queries
  • exclusive — if true, other matched filters are not taken into consideration (that is, only the exclusive filter is applied), when multiple exclusive filters match, we throw an error. This is useful for routing specific commands to specific replication set, while keeping the default replication through the main replication set.

Notes

This function uses the same replication mechanism as DDL statements. This means that the replication is affected by the ddl filters configuration. This also means that replication of changes to ddl filter configuration is affected by the existing ddl filter configuration.

The function takes a DDL global lock.

This function is transactional. You can roll back the effects with the ROLLBACK of the transaction. The changes are visible to the current transaction.

To view the defined replication filters, use the view bdr.ddl_replication.

Examples

To include only PGD admin functions, define a filter like this:

SELECT bdr.replication_set_add_ddl_filter('mygroup', 'mygroup_admin', $$bdr\..*$$);

To exclude everything apart from index DDL:

SELECT bdr.replication_set_add_ddl_filter('mygroup', 'index_filter',
				'^(?!(CREATE INDEX|DROP INDEX|ALTER INDEX)).*');

To include all operations on tables and indexes but exclude all others, add two filters: one for tables, one for indexes. This shows that multiple filters provide the union of all allowed DDL commands:

SELECT bdr.replication_set_add_ddl_filter('bdrgroup','index_filter', '^((?!INDEX).)*$');
SELECT bdr.replication_set_add_ddl_filter('bdrgroup','table_filter', '^((?!TABLE).)*$');

bdr.replication_set_remove_ddl_filter

This function removes the DDL filter from a replication set.

Replication of this command is affected by DDL replication configuration, including DDL filtering settings themselves.

Synopsis

bdr.replication_set_remove_ddl_filter(set_name name,
                                      ddl_filter_name text)

Parameters

  • set_name — Name of the replication set. If NULL then the PGD group default replication set is used.
  • ddl_filter_name — Name of the DDL filter to remove.

Notes

This function uses the same replication mechanism as DDL statements. This means that the replication is affected by the ddl filters configuration. This also means that replication of changes to the DDL filter configuration is affected by the existing DDL filter configuration.

The function takes a DDL global lock.

This function is transactional. You can roll back the effects with the ROLLBACK of the transaction. The changes are visible to the current transaction.

← Prev

Replication set membership

Next →

Testing and tuning commands