DDL overview ============ DDL stands for data definition language, the subset of the SQL language that creates, alters, and drops database objects. Replicated DDL -------------- For operational convenience and correctness, PGD replicates most DDL actions, with these exceptions: - Temporary relations - Certain DDL statements (mostly long running) - Locking commands (``LOCK`` ) - Table maintenance commands (``VACUUM`` , ``ANALYZE`` , ``CLUSTER`` ) - Actions of autovacuum - Operational commands (``CHECKPOINT`` , ``ALTER SYSTEM`` ) - Actions related to databases Automatic DDL replication makes certain DDL changes easier without having to manually distribute the DDL change to all nodes and ensure that they’re consistent. In the default replication set, DDL is replicated to all nodes by default. Differences from PostgreSQL --------------------------- PGD is significantly different from standalone PostgreSQL when it comes to DDL replication. Treating it the same is the most common issue with PGD. The main difference from table replication is that DDL replication doesn’t replicate the result of the DDL. Instead, it replicates the statement. This works very well in most cases, although it introduces the requirement that the DDL must execute similarly on all nodes. A more subtle point is that the DDL must be immutable with respect to all datatype-specific parameter settings, including any datatypes introduced by extensions (not built in). For example, the DDL statement must execute correctly in the default encoding used on each node. Executing DDL on PGD systems ---------------------------- A PGD group isn’t the same as a standalone PostgreSQL server. It’s based on asynchronous multi-master replication without central locking and without a transaction coordinator. This has important implications when executing DDL. DDL that executes in parallel continues to do so with PGD. DDL execution respects the parameters that affect parallel operation on each node as it executes, so you might notice differences in the settings between nodes. It’s essential to prevent the execution of conflicting DDL statements. Otherwise, DDL replication conflicts occur and replication stops. PGD offers the following levels of protection against DDL conflicts: ``bdr.ddl_locking = 'all'`` is the strictest option and is best when DDL might execute from any node concurrently and you want to ensure correctness. ``bdr.ddl_locking = 'leader'`` locks on write-leaders only and doesn’t require a majority of nodes to participate in the locking operation. ``bdr.ddl_locking = 'auto'`` (the default) automatically selects a ``leader`` lock if possible, otherwise falls back to ``all`` . ``bdr.ddl_locking = 'dml'`` is an option that is safe only when you execute DDL from one node at any time. Use this setting only if you can completely control where DDL is executed. Executing DDL from a single node ensures that there are no inter-node conflicts. Intra-node conflicts are already handled by PostgreSQL. ``bdr.ddl_locking = 'off'`` is the least strict option and is dangerous in general use. This option skips locks altogether, avoiding any performance overhead, which makes it a useful option when creating a new and empty database schema. These options can be set only by the bdr_superuser, by the superuser, or in the ``postgres.conf`` configuration file. When using the `bdr.replicate_ddl_command `_ , you can set this parameter directly with the third argument, using the specified `bdr.ddl_locking `_ setting only for the DDL commands passed to that function. DDL and mixed PostgreSQL versions --------------------------------- PGD does not support DDL replication between different major Postgres versions in a cluster. Most of the time, this is not an issue because clusters will be running the same major version of Postgres. This is not the case though when performing a rolling upgrade of a cluster from one major version to another. In this case, DDL replication is not supported until all nodes have been upgraded to the same major version and should not be used. Special care should be taken in the upgrade process when updating extensions as the scripts may trigger DDL replication. In this case, if the scripts must be run before upgrading is complete, ``bdr.ddl_replication`` setting should be set to ``off`` while running the script.