Selecting across servers

I needed to be able to compare the contents of a table across
several databases and clusters.

Since it took me several hours to write this, I thought I'd share it with
you, in case anyone needs a starting point for anything similar.

BACKGROUND DETAILS:

These databases are all on sandbox servers, restored backups from
production.

We are querying the most recent entry in the log table. You'll see that
some have a date of 11/1 (from the backup) and some have a more recent
date. This is a problem as I've run the client against all the databases.
We conclude that the log entries are not all going to the "current"
database. I needed an easy way to see where they *were* going.

IMPLEMENTATION DETAILS:

In this case, all the databases are on the same server and the same DB name
but different ports. But you can obviously modify the connect string to hit
any combination you need.

This assumes a view exists with the same name on each database.
'LatestLogEntry' in our case.

As you'll see in the results, we are running

V9.4 on port 5432
V10 on 5433
V11 on 5434
V12 on 5435

It raises a NOTICE at the end to print out the query just for debugging
purposes.

Here is the text for LatestLogEntry

----

-- retrieve the most recent log entry

SELECT current_setting('port'::text) AS "Port",
current_setting('server_version'::text) AS "Version",
"System Log"."AddDate"
FROM "System Log"
ORDER BY "System Log"."AddDate" DESC
LIMIT 1

----

And the text for our routine to retrieve results from across clusters:

----

CREATE EXTENSION IF NOT EXISTS dblink;
BEGIN;
DO
$$
DECLARE
conn_template TEXT;
conn_string9 TEXT;
conn_string10 TEXT;
conn_string11 TEXT;
conn_string12 TEXT;

_query TEXT;
_cursor CONSTANT refcursor := '_cursor';

BEGIN

conn_template = 'user={user} password={password} dbname={DB} port=';

conn_string9 = conn_template || 5432;
conn_string10 = conn_template || 5433;
conn_string11 = conn_template || 5434;
conn_string12 = conn_template || 5435;

_query := 'select "Port", "Version", "AddDate" from dblink(''' ||
conn_string9 || ''', ''(select * from "LatestLogEntry")'') as t1("Port"
integer, "Version" text, "AddDate" timestamp)' ||
' UNION select "Port", "Version", "AddDate" from dblink(''' ||
conn_string10 || ''', ''(select * from "LatestLogEntry")'') as t1("Port"
integer, "Version" text, "AddDate" timestamp)' ||
' UNION select "Port", "Version", "AddDate" from dblink(''' ||
conn_string11 || ''', ''(select * from "LatestLogEntry")'') as t1("Port"
integer, "Version" text, "AddDate" timestamp)' ||
' UNION select "Port", "Version", "AddDate" from dblink(''' ||
conn_string12 || ''', ''(select * from "LatestLogEntry")'') as t1("Port"
integer, "Version" text, "AddDate" timestamp) ORDER BY "Port";';
OPEN _cursor FOR EXECUTE _query;
RAISE NOTICE '%', _query;

END
$$;

FETCH ALL FROM _cursor ;

COMMIT;
----

Results:

----

Port Version AddDate
5432 9.4.1 2022-12-09 16:44:08.091
5433 10.20 2022-11-01 17:01:33.322
5434 11.15 2022-12-16 12:43:31.679973
5435 12.10 2022-11-01 17:01:33.322

----