8

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.

  • @RolandoMySQLDBA: Postgres instead of PostgreSQL is perfectly OK. – a_horse_with_no_name Jun 11 '13 at 12:16
10

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.

  • 1
    This 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 EXECUTE to run the statements as dynamic SQL. – Craig Ringer Jun 11 '13 at 6:08
9

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.