Skip to content

PostgreSQL

dbwarden treats PostgreSQL as a first-class backend: every natively supported feature is reverse-engineered, diffed, and emitted as correct DDL.

"First-class" means the round-trip is verified: reverse-engineer a live database with generate-models, feed the output back into make-migrations, and get zero diff.

Implementation note: PostgreSQL diffs and SQL emission flow through the dbwarden.engine.backends.postgresql.handlers handler package. The handler pipeline is described in the Architecture Deep Dive.

$ dbwarden generate-models -d primary --tables users,orders,items
$ dbwarden make-migrations
# -> "No changes detected"

Feature Matrix

Category Features
Identity Columns GENERATED ALWAYS AS IDENTITY, GENERATED BY DEFAULT AS IDENTITY, sequence options
Collation Per-column COLLATE via pg.field(collation=...)
Storage Per-column STORAGE (PLAIN, MAIN, EXTERNAL, EXTENDED)
Compression Per-column COMPRESSION (pglz, zstd) via pg.field(compression=...) (PG 14+)
Generated Columns GENERATED ALWAYS AS (...) STORED
Table Properties Fillfactor, storage params, tablespace, unlogged, partitioning, inheritance
Renames Table rename, column rename
Constraints FK (MATCH FULL/PARTIAL/SIMPLE, ON DELETE/UPDATE, DEFERRABLE), unique (NULLS NOT DISTINCT, INCLUDE, DEFERRABLE), check (NO INHERIT, NOT VALID), exclude
Indexes B-tree, hash, GiST, GIN, BRIN, SP-GiST; partial, expression, INCLUDE, WHERE, opclasses, NULLS NOT DISTINCT, column sorting, CONCURRENTLY
RLS & Policies ENABLE/DISABLE/FORCE/NO FORCE row-level security; permissive/restrictive, role-scoped policies
Enums CREATE TYPE ... AS ENUM, ALTER TYPE ... ADD VALUE ... AFTER
Domains CREATE DOMAIN with base type, default, NOT NULL, CHECK
Composite Types CREATE TYPE ... AS (col1 type1, col2 type2, ...)
Sequences CREATE SEQUENCE with all options
Functions CREATE FUNCTION with language, arguments, body
Triggers CREATE TRIGGER with timing, events, FOR EACH ROW/STATEMENT
Roles CREATE ROLE with login, password, privileges
Default Privileges ALTER DEFAULT PRIVILEGES per schema/role/object-type
Extended Statistics CREATE STATISTICS with ndistinct, dependencies, MCV, expressions (PG 14+)
Event Triggers CREATE EVENT TRIGGER for DDL events
Views Regular CREATE OR REPLACE VIEW, materialized views with auto-refresh
Schema-level Grants GRANT USAGE ON SCHEMA, GRANT ALL ON SCHEMA
Table Grants GRANT SELECT/INSERT/UPDATE/DELETE
Type Mapping SQLAlchemy type → PostgreSQL native type normalization
Storage Parameters Table-level and index-level WITH options, autovacuum tuning

Documentation Sections

Migration locking

dbwarden uses session-level advisory locks for PostgreSQL migrations. This provides strong mutual exclusion without requiring external services.

How it works

When dbwarden migrate runs:

  1. Opens a dedicated migration connection
  2. Verifies the target is a primary (not a read replica) via pg_is_in_recovery()
  3. Detects transaction-pooling proxies (PgBouncer) and refuses if detected
  4. Acquires a session-level advisory lock via pg_try_advisory_lock()
  5. Holds the connection and lock for the entire migration run
  6. Releases the lock when the connection closes

Lock properties

  • Scope: Session-level (tied to the connection, not the transaction)
  • Release: Automatic on connection close (crash-safe)
  • Fencing: Stale workers cannot mutate; their next statement fails at the protocol layer
  • Re-entrancy: Refuses nested runs on the same namespace/connection

Primary vs replica

dbwarden refuses to acquire locks on read replicas:

SELECT pg_is_in_recovery()  -- Must return false

This prevents the common mistake of running migrations against a replica.

Transaction pooling detection

dbwarden detects transaction-pooling proxies like PgBouncer by calling pg_backend_pid() twice across a statement boundary. If the backend PIDs differ, the lock is refused because session-level advisory locks are meaningless under transaction pooling.

Configuration

database_config(
    database_name="primary",
    database_type="postgresql",
    database_url_sync="postgresql://...",
    pg_migration_lock_timeout=5000,  # ms, for DDL lock_timeout
)

The pg_migration_lock_timeout sets PostgreSQL's lock_timeout for DDL operations, preventing indefinite waits on table locks.