pgEdge Platform
Configuring Advanced Features
Using Auto DDL

Configuring Automatic DDL Replication

pgEdge Platform introduces automatic replication of DDL commands (auto-DDL). A DDL command is a statement that adds, alters, or drops a database object, such as CREATE TABLE, ALTER TRIGGER, or DROP MATERIALIZED VIEW. Some commands that make DDL changes are implemented in PostgreSQL as clauses of other SQL commands; an example of this is a clause that modifies a column.

The schema on each node in the cluster must match exactly when you enable auto-DDL. The best time to enable auto-DDL is after installing pgEdge Platform (with a pgEdge PostgreSQL database), but before you create the tables in a replicated database.

WARNING: Some DDL statements (for example DROP TABLE, or CREATE TABLE AS) can break replication in an operational cluster. If you plan to keep auto-DDL enabled on a replicating cluster, we recommend using a maintenance window to make any changes to your DDL.

To implement auto-DDL replication, connect to each node in the cluster with the psql client, and invoke the following commands:

ALTER SYSTEM SET spock.enable_ddl_replication=on;
ALTER SYSTEM SET spock.include_ddl_repset=on;
ALTER SYSTEM SET spock.allow_ddl_from_functions=on;
SELECT pg_reload_conf();

Note: The third SET statement is optional; it enables automatic DDL replication from within functions and anonymous code blocks (DO$$ ... $$).

Permissions Required to Make Changes that are Automatically Replicated

To make a change that is automatically replicated, the database user making the change must be a member of your replication role. When you create a cluster with pgEdge Platform, the installer creates a replication role with the same name as the OS user that installed pgEdge. You should GRANT (opens in a new tab) membership in that role to any user that will potentially creating or modifying DDL objects. Use the command:

GRANT OS_user_name TO app_dba WITH SET TRUE, INHERIT FALSE;

Where:

  • OS_user_name is the name of the OS user that invoked the pgEdge installer.

  • app_dba is the name of the database user that will be creating new DDL objects.

For example, if the OS user that installed pgEdge Platform is rocky and app_dba is the database superuser created during the installation, invoke the following command to grant privileges to perform DDL commands that will be automatically replicated:

GRANT rocky TO app_dba WITH SET TRUE, INHERIT FALSE

How Automatic DDL Replication Impacts Table Management

If automatic DDL replication is enabled:

  • Any tables created with a primary key are added to the default replication set.

  • Any tables created without a primary key are added to the default_insert_only replication set; if you add a primary key for a table, the table is moved into the default replication set.

  • If you remove a primary key from a table, the table is moved from the default replication set to the default_insert_only replication set.

  • Setting a table as unlogged will remove it from replication. Similarly, setting a table back to logged will add it to the replication set.

  • Detaching a partition does not remove that table from the replication set.

Limitations: The following DDL Statements are not Replicated

DDL StatementExplanation
CREATE TABLE ASThe DDL statement will replicate, but not the data that is inserted into the table. This means that on n1 if you do a CREATE TABLE t1 AS SELECT * FROM t; the table will be created on n1 with all the rows that are n1 at that time, and the DDL statement will be replicated to n2, where the select would happen again to populate the table. If at that moment the records are different on n1 and n2 in table t, then the records will be different in the new table t1.
Workaround:Instead of using CREATE TABLE AS, you should perform a CREATE TABLE t1…; and then an INSERT INTO t1 SELECT * FROM t2;
Maintenance CommandsMaintenance commands such as VACUUM, ANALYZE, CLUSTER, CHECKPOINT, and REINDEX can be problematic if run on more than one cluster node at a time.
Workaround:To safely perform maintenance, you should schedule maintenance functions for each node during a different maintenance window.

The following commands don't replicate or have the potential to be problematic, and should be performed on each node individually:

  • CREATE DATABASE
  • ALTER DATABASE
  • DROP DATABASE
  • ALTER SYSTEM
  • ALTER TABLE ... DETACH CONCURRENTLY
  • CREATE INDEX... CONCURRENTLY
Deploying a Cluster on your Local HostACE (the Anti-Chaos Engine)