Lone Badger Tech

PostgreSQL

PostgreSQL 12 through 18, each tested on every build.

bsm init --provider postgresql
bsm add create-product
bsm deploy
bsm.yaml
provider: postgresql
defaultEnvironment: local
environments:
  local:
    host: localhost
    port: 5432
    database: shop
    authentication: password
    user: postgres
    password: ${env:BSM_LOCAL_PASSWORD}
    tls:
      mode: optional
  prod:
    host: shop-prod.postgres.database.azure.com
    database: shop
    authentication: entraDefault        # an Entra token is the password
    user: shop-deployers                # the Entra user, group or managed identity the server knows
    tls:
      mode: strict
    connectionProperties:               # any Npgsql connection-string keyword
      Search Path: app,public
      Command Timeout: 120

Everything else is the same as on SQL Server: the plan, hash certification, revert from stored scripts, environments, variables, bundles, diff-schema, generated changes (add --from-diff, diff-schema --script), rework and repair, and imports.

Versions and services

Every build runs the provider's tests against PostgreSQL 12, 13, 14, 15, 16, 17 and 18 (the official Docker images). Managed services that run standard PostgreSQL, such as Azure Database for PostgreSQL, Amazon RDS and Aurora PostgreSQL, Google Cloud SQL, Supabase and Neon, use the same protocol and catalog and are expected to work, but have not been tested yet. Databases that only speak PostgreSQL's protocol (CockroachDB, YugabyteDB, Redshift) are not supported: they lack advisory locks or parts of the catalog bsm reads.

Sign-in and TLS

authenticationPostgreSQL
passwordUser and password (SCRAM or MD5, whatever the server asks for)
integratedKerberos (GSSAPI); run kinit first on macOS and Linux
entraDefault, entraInteractive, entraManagedIdentity, entraWorkloadIdentity, entraAzureCli, … Azure Database for PostgreSQL: bsm gets a token for https://ossrdbms-aad.database.windows.net and sends it as the password, following Azure's documented flow. user is required. (Not yet tested against a live Azure server.)
Client certificateconnectionProperties: { SSL Certificate: client.crt, SSL Key: client.key } alongside password or no password, as the server requires
tls.modeNpgsql SSL Mode
disabledDisable
optionalPrefer
mandatoryRequire (encrypted, certificate not checked); VerifyCA with tls.serverCertificate
strictVerifyFull (certificate and host name checked)

tls.serverCertificate is the CA certificate file to trust. SSH tunnels work as on SQL Server; with one, use mandatory, because the host name no longer matches the certificate.

For a primary with standbys, list the hosts in connectionProperties and let Npgsql pick the primary: Host: pg1,pg2,pg3 and Target Session Attributes: primary. The {db.host} variable and SSH tunnels use the first host.

Statements and transactions

bsm splits scripts into statements at semicolons, the way psql does: semicolons inside strings, quoted identifiers, comments and $$ dollar-quoted bodies (functions, DO blocks) do not count. A change still runs in one transaction, and PostgreSQL's transactional DDL means a failure leaves nothing behind. Errors name the line of the statement that failed.

Statements PostgreSQL refuses in a transaction block need a !notx change, which runs each statement on its own:

changes/product-name-index/deploy.sql
CREATE INDEX CONCURRENTLY IF NOT EXISTS product_name_idx ON product (name);
bsm.plan
product-name-index [create-product] !notx

psql scripts

Scripts written for psql (Sqitch's PostgreSQL scripts, psql -f files) run as they are. bsm does not shell out to psql: it understands the parts of psql's syntax that change what a script does, and runs the SQL itself.

psqlIn bsm
\i file, \include, \ir file, \include_relativeIncluded when the project loads (\i relative to the project folder, \ir relative to the script). The included text is part of the change: hashed, stored in the journal for revert, and packed into bundles. Files must be inside the project folder.
\copy table from 'file.csv' with (format csv)The file's rows are loaded with COPY … FROM STDIN and are part of the change, like an include
COPY … FROM STDIN; followed by data and \.The data is streamed to the server
\set name value, \unset, :name, :'name', :"name", :{?name}psql variables, interpolated before each statement runs. bsm's variables are psql variables too, so :'schema' and {schema} both work.
\if, \elif, \else, \endifConditional sections, on true/false, on/off, yes/no or 1/0, usually with a variable: \if :is_prod
\echo, \qecho, \warnShown as server messages (with -v)
\gexecRuns every value the query returns as a statement
\g, \gx without an argumentEnds the statement, like ;
\set ON_ERROR_STOP, \pset, \timing, \x, \d… and other display commandsIgnored: they only change how psql shows results

Commands that would act outside the script are errors that the linter reports before anything runs: \connect, \gset, \copy … to, \copy … from program, \o, \!, \prompt, \e and backquoted shell commands.

changes/reference-data/deploy.sql
\set ON_ERROR_STOP on
\ir ../shared/lookup-tables.sql

\if :is_prod
  \echo skipping demo data in production
\else
  \copy public.country (code, name) from 'data/countries.csv' with (format csv, header)
\endif

SELECT format('GRANT SELECT ON %I.%I TO reporting', schemaname, tablename)
FROM pg_tables WHERE schemaname = :'schema' \gexec

Linter

CodeSeverityFinding
BSM001ErrorCREATE/DROP INDEX CONCURRENTLY, REINDEX … CONCURRENTLY, VACUUM, CREATE/DROP DATABASE, ALTER SYSTEM, tablespace and subscription commands in a change that is not !notx
BSM003ErrorBEGIN, COMMIT, ROLLBACK or END in a transactional change: it would end bsm's transaction. (Inside function bodies and DO blocks they are fine.)
BSM005Errorpsql commands bsm cannot run (\connect, \gset, \copy … to, shell and interactive commands, see psql scripts), and unbalanced \if/\endif
BSM007WarningALTER TYPE … ADD VALUE in a transaction: the new value cannot be used until it commits

Schema fingerprint

The fingerprint covers:

  • schemas and extensions (with their versions);
  • tables (partitioning, inheritance, row-level security, replica identity, storage parameters, tablespace), foreign tables, columns (types, defaults, identity, generated, collations, storage settings);
  • constraints, indexes, statistics objects, rules, triggers, event triggers and row-level security policies;
  • views and materialized views, functions, procedures, aggregates, operators and casts;
  • sequences; enum, domain, composite and range types; collations; text search configurations and dictionaries;
  • publications and subscriptions; foreign data wrappers, servers and user mappings;
  • comments, and privileges: GRANTs on tables, columns, sequences, functions, schemas and types, and default privileges.

Definitions come from PostgreSQL's own pg_get_*def functions with an empty search_path (set inside a read-only transaction, so your session is untouched), so names are always schema-qualified and identical databases fingerprint identically. Left out on purpose: objects that belong to extensions, identity sequences, the bsm journal schema, object owners (they usually differ between environments) and sequence values (they are data).

Two parts can be left out per environment:

bsm.yaml
environments:
  prod:
    schemaFingerprint:
      partitionBoundaries: ignore   # partitions a job creates and drops (pg_partman, cron) are not drift
      privileges: ignore            # GRANTs managed outside bsm are not drift

With partitionBoundaries: ignore, partitions and everything on them are left out; the partitioned table and its partition key still count.

Journal and lock

The journal tables are the same as on SQL Server, in schema bsm (stateSchema). The deploy lock is a session-level advisory lock (pg_try_advisory_lock), as Flyway and Sqitch use too, so deploys must hold one server connection for the whole run: connect directly, or through PgBouncer in session pooling mode, not transaction pooling.

Not available

  • psql commands that act outside the script, such as \connect and \gset (see psql scripts).
  • Deploying through a transaction-pooling connection pooler (see above).
  • Always Encrypted, entraIntegrated and entraPassword are SQL Server features.