Upgrading application schemas v6.4.0

Rolling application schema upgrades let you apply DDL changes across the cluster one node at a time, keeping the cluster available throughout. PGD is designed to keep replication running even when nodes are temporarily at different schema versions, though each type of schema change has specific constraints and preparation requirements.

A common alternative is a multi-step approach: deploy DDL changes that only add new structures to the schema, such as new nullable columns, update the application to populate them, backfill existing rows if needed, and then apply cleanup DDL such as adding constraints and dropping columns. This approach avoids configuring conflict resolvers but requires careful coordination between DDL and application changes.

For schema changes that aren't compatible with either approach, fall back to a full-downtime upgrade instead.

Preparing for a rolling application schema upgrade

Before making any schema changes, agree with your application team on the exact schema changes and the sequence in which they'll be applied, then configure the appropriate resolvers on each node.

PGD classifies schema mismatches as conflict types and handles each one using a conflict resolver. Configure the value of the resolver for each conflict type with bdr.alter_node_set_conflict_resolver. For the full list of conflict types and their resolvers, see Conflicts.

Adding tables

When one node runs DDL that adds a new table, nodes that haven't yet received the DDL need to handle writes to a table they don't have. PGD raises a target_table_missing conflict.

Before performing this type of schema change, configure all nodes to apply the skip resolver for target_table_missing conflicts. With skip, nodes that haven't yet received the DDL silently discard rows destined for the missing table without raising an error.

Run this query separately on each node, replacing <node_name> with the actual node name:

SELECT bdr.alter_node_set_conflict_resolver('<node_name>', 'target_table_missing', 'skip');

Adding columns

When one node runs DDL that adds a column, nodes that haven't yet received the DDL need to handle writes that include a column they don't have. PGD raises a target_column_missing conflict.

Before performing this type of schema change, configure all nodes to apply the ignore resolver for target_column_missing conflicts. With ignore, nodes that haven't yet received the DDL drop the value for the missing column without raising an error.

Run this query separately on each node, replacing <node_name> with the actual node name:

SELECT bdr.alter_node_set_conflict_resolver('<node_name>', 'target_column_missing', 'ignore');

Removing columns

When one node runs DDL that drops a column, nodes that haven't yet received the DDL still send data for it. PGD raises a source_column_missing conflict.

The default resolver for this conflict type is use_default_value. It fills the missing value with the column's default or NULL and lets replication continue.

No action is required as long as the column accepts NULL or has a default value. If the column has a NOT NULL constraint and no default value, replication halts. Remove the NOT NULL constraint or add a default value on all nodes before dropping the column:

-- Option 1: remove the NOT NULL constraint
ALTER TABLE <table_name> ALTER COLUMN <column_name> DROP NOT NULL;

-- Option 2: add a default value
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET DEFAULT <default_value>;

Changing column types

When one node runs DDL that changes a column type, nodes that haven't yet received the DDL still send data in the old type.

If the types are binary-compatible, PGD handles the change as a metadata update without rewriting the underlying table and replication continues. No action is required. To check whether two types are binary-compatible, query pg_cast:

SELECT * FROM pg_cast
WHERE castsource = '<old_type>'::regtype
  AND casttarget = '<new_type>'::regtype
  AND castmethod = 'b';

If the type change requires a table rewrite, it can't be performed using the conflict-resolver approach described in this page and requires a more complex multi-step process. Transform triggers can help in these cases by converting incoming data from the old type to the new type on the target node. See Using transform triggers to bridge schema differences.

Performing a rolling application schema upgrade

Once the conflict resolvers are configured on all nodes, apply the DDL changes one node at a time. Before running the DDL change on each node, disable DDL replication for the session so the change applies locally only and isn't replicated to other nodes.

1. Connect to the first node, disable DDL replication, and run the DDL change:

   → WrapCopy

   SET bdr.ddl_replication = off;
   -- DDL change here

2. Verify that replication has caught up on all other nodes before proceeding. On each node, check that catchup_interval has cleared:

   → WrapCopy

   SELECT target_name, catchup_interval
   FROM bdr.node_replication_rates;

3. Repeat on each remaining node until all nodes have the new schema.

Using transform triggers to bridge schema differences

Use transform triggers to reshape incoming rows on the target node before they're applied. For more complex schema changes that require data to be reshaped rather than simply discarded or filled with a default value, transform triggers are the appropriate tool. Examples include splitting a single column into two, or computing a derived value for a newly added column. See Stream triggers for syntax, examples, and execution order.