Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question

3 Answers

up vote 1 down vote

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 ...; ...'
share|improve this answer
How can you be sure about that this runs successfully for every single DB? – dezso Feb 21 at 7:46
up vote 1 down vote

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.

share|improve this answer
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 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. – Chris Travers Feb 23 at 1:16
up vote 2 down vote

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.

share|improve this answer
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 at 21:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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