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;
  FOR r IN SELECT datname FROM pg_database WHERE datistemplate = false 
    EXECUTE 'ALTER TABLE public.' || quote_ident(r) || ' ALTER VARCHAR(200);';
    EXECUTE ...

Any thoughts?


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.

  • Thanks this is very useful information. Since I am a newb to databases I had it in my head that I needed to write one long and extensive sql statement, but I now understand I can use a scripting language of my choice and I can use that to explore db names and table names before I begin executing the alter statements. Also, sadly I need to keep restraint because the schema is being pushed around frmo platform to platform.
    – GuidoS
    Feb 22 '13 at 21:57

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.

  • If I am using python and psycopg2 do I need to use the PREPARE statement? Don't these kind of libraries run in a prepare fashion requiring you to commit or rollback if your query fails? thoughts? thanks
    – GuidoS
    Feb 22 '13 at 21:53
  • Unless it has two-phase commit functionality directly built in, the prepare statement is going to be necessary because what you are asking essentially is "is this transaction ok to commit?" That causes all deferred constraints and triggers to be fired for example. Note at that point you cannot do anything more in the transaction. The next step must be commit or rollback. Feb 23 '13 at 1:16

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 ...; ...'
  • How can you be sure about that this runs successfully for every single DB?
    – dezso
    Feb 21 '13 at 7:46

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.