PostgreSQL: Executing DDL on every schema

I've a multi-tenant DB setup, and need to add some columns. I'm using schemas (and search_path) to partition my users, so I'm looking for a ubiquitous way to apply a DDL-schema change to all my databases. Initially, I'd thought I might be able to do it as a single query (cursor on pg_catalog), but thinking a command-line invocation of psql -f might be the preferred way.

I would prefer the latter solution. You can collect the schema names into a file (one schema per line) in psql:

\o change_schema.sql
\t on

SELECT n.nspname
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema';

-- reset the output
\o
\t off

Then you can easily do the following:

Have a DDL changing script (for example, change_schema.sql), without reference to the including schema

SET search_path TO :schema;

BEGIN;
...
...
ALTER TABLE orders
ADD COLUMN last_modified timestamp;
...
...
COMMIT;

Then you can turn every line of the schema list into a line like

psql -h dbhost -d targetdb -f change_schema.sql -v schema=

with a simple sed command, for example - then you just have to run these commands. Of course, you can turn it into a proper shell script if you like.

StackExchange Database Administrators Q#44188, answer score: 11