I’ve been working with logical replication in PostgreSQL recently, and I wanted to share a few thoughts on how to implement some guardrails to make things easier on operators.

Limitations

Logical replication in PostgreSQL has some notable limitations. To wit:

• Schema and DDL commands (e.g. ALTER TABLE, CREATE INDEX) are not replicated. Although logical replication follows the robustness principle and simply error, this can lead to inconsistencies between the publisher and subscriber. This is especially important when implementing blue/green or canary deployments of new clusters for upgrade purposes.
• Sequence data is not replicated, requiring manual intervention to ensure sequences are updated from the publisher to the subscriber. In a control plane, this might mean storage of all the sequences across a cluster to keep track of the correct value and ensure that the subscriber is up to date prior to a cutover.
• TRUNCATE is replicated but has some specific edge case handling that has to be considered.
• Large objects are not replicated.
• Care must be taken to set up partitions appropriately on the subscriber.

Lets look at approaching a solution for one of these limitations - DDL replication.

DDL Guardrails

Generally speaking, you want to ensure that DDL commands are not run on the publisher, or that you set some kind of sentinel value or file to indicate that a failover is not possible. This particular approach is what AWS takes with their RDS Blue/Green deployments.

In our case, we can look at taking it one step further with writing an extension to PostgreSQL that will prevent DDL commands from being run on the publisher. This is a simple extension that can be written in C and compiled into a shared object that can be loaded into PostgreSQL.

I’ve written an example extension that does this: ddl_guard.

Currently, it implements a GUC (ddl_guard.enabled) that can be set to on or off. When set to on, it will prevent DDL commands from being run on the publisher for non-superusers.

The actual implementation is that of an event trigger and corresponding event trigger function that prevents DDL command execution:

CREATE TABLE IF NOT EXISTS foobar (id serial PRIMARY KEY);
ERROR:  Non-superusers are not allowed to execute DDL statements
HINT:  ddl_guard.enabled is set.

Additionally, the GUC can only be modified by superusers:

SET ddl_guard.enabled TO off;
ERROR:  permission denied to set parameter "ddl_guard.enabled"

An alternative option would be to write a sentinel file to disk on detection of a DDL command, and have a control plane check for the existence of the file. The extension implements this mode:

SET ddl_guard.ddl_sentinel TO on;
CREATE TABLE IF NOT EXISTS foobar (id serial PRIMARY KEY);
WARNING:  ddl_guard: ddl detected, sentinel file written
CREATE TABLE

The control plane can then check for the existence of the sentinel file and prevent a failover from occurring.

Future Work

In the future, such an extension should be expanded to detect when the large object facility is used and write a similar sentinel file, or otherwise block operations that alter large objects.