Altering same table across multiple databases

Question

I have multiple databases on the same instance and I am in the process of updating a table schema that must propagate across all the databases.

I am not sure I know what the right procedure for this is but I think it should go a little something like this?

DO $$DECLARE r record;
BEGIN
  FOR r IN SELECT datname FROM pg_database WHERE datistemplate = false 
  LOOP
    EXECUTE 'ALTER TABLE public.' || quote_ident(r) || ' ALTER VARCHAR(200);';
    EXECUTE ...
  END LOOP;
END;
$$;

Any thoughts?

Answer 1

I think the fundamental question you need to ask yourself is how much of a guarantee you need that this has propagated to all db's. Do you need every change to commit or roll back together? If so you should probably write a script in a programming language of your choice (Perl, Python, or the like), send the DO block there, and then use PREPARE TRANSACTION on every db connection (you'd open up one connection per db). If all of these are successful you commit. If any one fails, you roll back.

This is actually one very helpful application of PostgreSQL's transactional ddl in that you can guarantee that all your databases have schemas which are kept in sync.

Answer 2

First of all, if you'd be using the data type text or varchar (without length modifier) instead of the mostly pointless varchar(n), you wouldn't be in this tight spot to begin with. If you really need to enforce a maximum length, use CHECK constraints for that, those can be altered without messing with the table structure.
More details in this related answer on SO.

Next, you cannot execute SQL command in any other DB than the one you are connected to in standard PostgreSQL at this time. This includes DBs in the same cluster. If you try you get an error like:

ERROR: cross-database references are not implemented: ...

To execute SQL commands in other (Postgres) databases, you could install the additional module dblink. I quote the manual here:

dblink executes a query (usually a SELECT, but it can be any SQL statement that returns rows) in a remote database.

Then you can go about refining your DO statement with dblink calls.

Answer 3

On Linux you can use this simple shell sniplet:

psql -tc "SELECT datname FROM pg_database WHERE NOT datname ~ '^(template[01]|postgres)$'" \
| xargs -I {} psql -d {} -c 'ALTER ...; ...'