Postgres - Run same query on all databases (same schemas)

Question

hoping this is a pretty straightforward question.

I have a straightforward SELECT query (with a few sub-queries built in). I have over 40 DBs and I need to run this query for all DBs (all have same schema) and return the result in a big table.

I'm imagining a loop sequence (like with javascript's i=0; i < 40; i++) with a defined variable that will automatically stop once it's run all the DBs.

(I am working in Navicat, tho that probably doesn't matter)

Thank you!

I have to ask... why do you have 40 databases all with the same schema? And, just to make sure, that's *server side* Javascript doing the query, right? — Schwern, Feb 24 '17 at 19:53
I'm just using the Javascript thing as an example, this post has nothing to with Javascript, sorry for the confusion. 40 DBs are for different clients that all have tons of data from multiple sources (various media sites + marketing data etc.) — Lorenzo, Feb 24 '17 at 19:57
When you say "different databases", they're still on the same server? — Schwern, Feb 24 '17 at 19:59
Possible duplicate of [Possible to perform cross-database queries with postgres?](http://stackoverflow.com/questions/46324/possible-to-perform-cross-database-queries-with-postgres) — Schwern, Feb 24 '17 at 20:00
Yeah, Redshift. I'm not sure why multiple DBs were set up, I didn't design it. I'll check out that other thread. Thanks — Lorenzo, Feb 24 '17 at 20:10

score 2 · Answer 1 · answered Apr 12 '19 at 13:55

In case someone needs a more involved example on how to do cross-database queries, here's an example that cleans up the databasechangeloglock table on every database that has it:

CREATE EXTENSION IF NOT EXISTS dblink;

DO 
$$
DECLARE database_name TEXT;
DECLARE conn_template TEXT;
DECLARE conn_string TEXT;
DECLARE table_exists Boolean;
BEGIN
    conn_template = 'user=myuser password=mypass dbname=';

    FOR database_name IN
        SELECT datname FROM pg_database
        WHERE datistemplate = false
    LOOP
        conn_string = conn_template || database_name;

        table_exists = (select table_exists_ from dblink(conn_string, '(select Count(*) > 0 from information_schema.tables where table_name = ''databasechangeloglock'')') as (table_exists_ Boolean));
        IF table_exists THEN
            perform dblink_exec(conn_string, 'delete from databasechangeloglock');
        END IF;     
    END LOOP;

END
$$

Postgres - Run same query on all databases (same schemas)

1 Answers1