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
----