Skip to main content
Tungsten Clustering

DDL and Replication

Most schema changes go to every node. Send a CREATE TABLE or an ALTER TABLE to the Connector, and the change reaches the whole cluster.

A few statements cannot work this way. The Connector refuses them. Without this, the statement would succeed on one node and the other nodes would stay behind.

Every refusal is a normal PostgreSQL error. The SQLSTATE is 0A000. The message names the statement class. The application sees an error, not a hang and not a false success.

Databases, tablespaces and subscriptions​

Replication carries the changes inside the replicated database. These three objects live outside it:

  • a database sits next to it
  • a tablespace is a directory on one machine
  • a subscription describes the replication setup of one node

There is nothing for replication to carry. So the Connector refuses these statements:

  • CREATE DATABASE, DROP DATABASE
  • CREATE TABLESPACE, DROP TABLESPACE
  • CREATE SUBSCRIPTION, ALTER SUBSCRIPTION, DROP SUBSCRIPTION

Create and drop these objects on each node directly. Connect to each server, not to the Connector. Treat this as a provisioning step for the whole cluster.

Avoid creating a database on one node only. After a switchover or a failover, the database is missing, and applications cannot connect.

A tablespace has one extra rule. Its directory must exist at the same path on every node. The tablespace points to a location on disk. A node without that path cannot hold the objects stored there.

Allowing these statements​

To manage these statements yourself, add connector-allow-shared-object-ddl=true to tungsten.ini

The default is false.

Set it on every Connector in the cluster. Then restart the Connector. The command connector reconfigure also applies the change, without a full restart.

Avoid setting it on some Connectors only. The result then depends on which Connector the client reached.

With the option on, the statement runs on one node: the node of your session. It does not run anywhere else. The option removes the refusal. It does not remove the limit. Run the same statement on the other nodes yourself.

Each allowed statement is written to connector.log at WARN level. Use this list to reconcile the cluster later.

The Connector refuses these statements itself. The error never reaches PostgreSQL, so no server log shows it. Look in connector.log.

Indexes with CONCURRENTLY​

The Connector also refuses these, with SQLSTATE 0A000:

  • CREATE INDEX ... CONCURRENTLY
  • DROP INDEX ... CONCURRENTLY

These statements should replicate. The limit is on the Tungsten side. There is no option to force them through.

The reason is simple. A missing index causes no error. Queries on the replicas only become slower. This is much harder to notice than a refused statement.

To replicate the index, remove the CONCURRENTLY keyword. Note the cost: the plain form locks the table. Writes wait until the index is built. Plan it like any other blocking maintenance.

REINDEX is different. It never replicates, with or without CONCURRENTLY. See the next section.

Node-local maintenance​

VACUUM, VACUUM FULL, VACUUM ANALYZE, CLUSTER, ANALYZE and REINDEX are accepted and run normally. They never replicate.

These commands rewrite physical storage and statistics. Every node keeps its own. Replaying them across the cluster would take heavy locks on the replicas and block replication behind them.

So maintenance sent to the Connector runs on whichever node the session is routed to, and on that node only. The node you meant to maintain may not be the one that gets the work.

Run this kind of maintenance on each node separately, against the local database. Use connector sql bypass, or psql -p 15432. Avoid sending it through the Connector.

Every node needs its own maintenance, not just the Primary. Reads may be routed to any replica, so bloat or stale statistics on a replica reach the application as slow queries.

Routine work is already covered. Autovacuum runs on each node and handles ordinary bloat and statistics. The commands above are for what autovacuum does not reach: a VACUUM FULL after a large delete, an ANALYZE after a bulk load, a REINDEX on a bloated index.

Known limit: ALTER SYSTEM and ALTER DATABASE ... SET TABLESPACE​

The Connector treats these two as normal DDL. PostgreSQL refuses to run them inside a transaction block, so they fail through the Connector with the PostgreSQL error "cannot run inside a transaction block".

Both change node-local state. Run them on each server directly.

This is a gap, not a deliberate refusal. So the error comes from PostgreSQL, not the clearer 0A000 message above.

Roles and users​

Role statements replicate: CREATE ROLE, ALTER ROLE, DROP ROLE, and the USER and GROUP forms.

Take care with these. A role belongs to the whole PostgreSQL instance, but replication covers one database only. So a replica can already hold a role that arrives over the channel. The replay then fails, and the replicator goes offline with an error.

Avoid creating or dropping roles on a node directly. Send every role change through the Connector, so all nodes stay in step. If a role must exist on one node only, create it with a name no replicated statement will use. To restart after such a failure, align the role by hand on the replica, then put the replicator back online.