Custom Schema

Summary

A tenant's tables live in public, and most apps never change that. If a tenant needs another schema, name it first in the tenant connection string's Search Path:

Host=db;Database=erp;Username=erp;Password=...;Search Path=erp

Core creates the schema and its tables. Upgrades, feature versions and sequences use the same schema.

Rules

  • Use lower-case letters, digits and underscores, up to 52 characters. Don't start the name with tempschema.
  • Put only the tenant schema in Search Path.
  • Write subscriber SQL without a schema name. Use current_schema() when a query needs it.
  • Tenants that share a database each need their own schema.
  • A new database still has an empty public schema. PostgreSQL creates it, and Core leaves it empty.

Existing Tenants

Core doesn't move tables. When a tenant's tables are still in public, Core stops at startup instead of creating empty tables in the new schema. Move the tables once, then restart:

ALTER SCHEMA public RENAME TO erp;
CREATE SCHEMA public;

The rename works only while the erp schema doesn't exist yet.

Logical Replication

Logical replication matches tables by schema name, so give the tenant the schema that the subscriber uses.

  • Publish with FOR TABLES IN SCHEMA erp, not FOR ALL TABLES. Core's temporary upgrade schema then stays out.
  • Schema changes don't replicate. Upgrade the subscriber before the publisher.
  • If Core also manages the subscriber, list the tables with FOR TABLE instead. Leave out DatabaseLockKey and __FeatureVersion, because Core writes its own rows to both.