Altering same table across multiple databases

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?

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.

StackExchange Database Administrators Q#35128, answer score: 3