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.
2 Answers
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=<schema_name>
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.
-
1This is how I'd do it too, though I'd use the shell to loop over the schema list, rather than sed'ing it, so I had better error handling. Just for completeness, the other approach would be to write it as a PL/PgSQL procedure that used
EXECUTEto run the statements as dynamic SQL.Craig Ringer– Craig Ringer2013-06-11 06:08:23 +00:00Commented Jun 11, 2013 at 6:08
Just for completeness, another approach is to loop over all schemas and run the change with dynamic SQL in PL/PgSQL, eg:
DO
$$
DECLARE
schemaname name;
BEGIN
FOR schemaname IN SELECT nspname FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname <> 'information_schema' LOOP
EXECUTE format('ALTER TABLE %I.my_table ADD COLUMN blah blah;', schemaname);
END LOOP;
END;
$$ LANGUAGE plpgsql;
Postgresinstead ofPostgreSQLis perfectly OK.