SQL over N database systems We have a PostgreSQL based system which is installed at 20 customers.

Sometimes I run a SQL query over all systems. Up to now I do this with the command line tool parallel like this:

parallel -q -j0 ssh {} "echo {}; psql -tAc \"select count(*) from auth_user\";echo" ::: $(ssh j list-systems prod)


I am missing the ability to sort the output. I would like to avoid dirty shell scripting, and use SQL wisdom.

How to get the N SQL queries into PostgreSQL, so that I can run ordinary SQL on it?

(Sub question: Is there a matching tag for this kind of question? I mean questions where the SQL spans several databases?)
If you need to do that very often and you can connect to all of those servers directly, I would setup one Postgres instance that acts as a "unified" view on all other servers. 

To do that create a new Postgres database (or server) and install the postgres_fdw extension. 

Then create one foreign server for each of the database you want to look at. 

Then create an (empty) table in your new database, that looks exactly like the corresponding table on the other servers, e.g. 

create table auth_user
(
  id integer, 
  user_name varchar(100)
);


Then for each server create a foreign table, e.g.:

create foreign table auth_user_server1 () 
inherits auth_user
server customer_1_server options (table_name 'user_auth');


It's probably a good idea to include the option updatable 'false' when defining the foreign table to prevent accidental updates from the integration server to the remote servers.

The above steps could all be scripted if necessary.

Once all tables are defined, you can do a simple:

select count(*)
from auth_users;


on your "integration" server and Postgres will automatically query all remote tables.