PostgreSQL
PostgreSQL 12 through 18, each tested on every build.
bsm init --provider postgresql
bsm add create-product
bsm deploy
bsm.yamlprovider: 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
authentication | PostgreSQL |
|---|---|
password | User and password (SCRAM or MD5, whatever the server asks for) |
integrated | Kerberos (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 certificate | connectionProperties: { SSL Certificate: client.crt, SSL Key: client.key } alongside password or no password, as the server requires |
tls.mode | Npgsql SSL Mode |
|---|---|
disabled | Disable |
optional | Prefer |
mandatory | Require (encrypted, certificate not checked); VerifyCA with tls.serverCertificate |
strict | VerifyFull (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:
CREATE INDEX CONCURRENTLY IF NOT EXISTS product_name_idx ON product (name);
bsm.planproduct-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.
| psql | In bsm |
|---|---|
\i file, \include, \ir file, \include_relative | Included 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, \endif | Conditional sections, on true/false, on/off, yes/no or 1/0, usually with a variable: \if :is_prod |
\echo, \qecho, \warn | Shown as server messages (with -v) |
\gexec | Runs every value the query returns as a statement |
\g, \gx without an argument | Ends the statement, like ; |
\set ON_ERROR_STOP, \pset, \timing, \x, \d… and other display commands | Ignored: 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.
\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
| Code | Severity | Finding |
|---|---|---|
BSM001 | Error | CREATE/DROP INDEX CONCURRENTLY, REINDEX … CONCURRENTLY, VACUUM, CREATE/DROP DATABASE, ALTER SYSTEM, tablespace and subscription commands in a change that is not !notx |
BSM003 | Error | BEGIN, COMMIT, ROLLBACK or END in a transactional change: it would end bsm's transaction. (Inside function bodies and DO blocks they are fine.) |
BSM005 | Error | psql commands bsm cannot run (\connect, \gset, \copy … to, shell and interactive commands, see psql scripts), and unbalanced \if/\endif |
BSM007 | Warning | ALTER 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.yamlenvironments:
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
\connectand\gset(see psql scripts). - Deploying through a transaction-pooling connection pooler (see above).
- Always Encrypted,
entraIntegratedandentraPasswordare SQL Server features.