[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Selecting across servers
From: Brad White <b55white () gmail ! com>
Date: 2022-12-19 18:14:55
Message-ID: CAA_1=90sd0KkRPNerUV-R+N54MBk3Z9dg__=z2d82PZ0H+hcng () mail ! gmail ! com
[Download RAW message or body]
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
----
[Attachment #3 (text/html)]
<div dir="ltr">
<div>
<p>I needed to be able to compare the contents of a table across several \
databases and clusters.</p><p>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.</p><p>BACKGROUND DETAILS: </p><p>These databases are all on sandbox \
servers, restored backups from production.</p><p>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.</p><p>IMPLEMENTATION DETAILS:</p><p>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.<br></p><p>This assumes a view \
exists with the same name on each database. 'LatestLogEntry' in our \
case.</p><p>As you'll see in the results, we are running </p><p>V9.4 on port \
5432<br>V10 on 5433<br>V11 on 5434<br>V12 on 5435</p><p>It raises a NOTICE at the end \
to print out the query just for debugging purposes.</p><p>Here is the text for \
LatestLogEntry</p><p>----</p><p>-- retrieve the most recent log entry</p><p> SELECT \
current_setting('port'::text) AS "Port",<br> \
current_setting('server_version'::text) AS "Version",<br> \
"System Log"."AddDate"<br> FROM "System Log"<br> \
ORDER BY "System Log"."AddDate" DESC<br> LIMIT \
1<br></p><p>----</p><p>And the text for our routine to retrieve results from across \
clusters:</p><p>----</p><p>CREATE EXTENSION IF NOT EXISTS dblink;<br>BEGIN;<br>DO \
<br>$$<br>DECLARE <br> conn_template TEXT;<br> conn_string9 TEXT;<br> \
conn_string10 TEXT;<br> conn_string11 TEXT;<br> conn_string12 TEXT;<br><br> \
_query TEXT;<br> _cursor CONSTANT refcursor := \
'_cursor';<br><br>BEGIN<br><br> conn_template = 'user={user} \
password={password} dbname={DB} port=';<br><br> conn_string9 = conn_template || \
5432;<br> conn_string10 = conn_template || 5433;<br> conn_string11 = conn_template \
|| 5434;<br> conn_string12 = conn_template || 5435;<br><br>_query := 'select \
"Port", "Version", "AddDate" from \
dblink(''' || conn_string9 || ''', ''(select * from \
"LatestLogEntry")'') as t1("Port" integer, \
"Version" text, "AddDate" timestamp)' ||<br> ' \
UNION select "Port", "Version", "AddDate" from \
dblink(''' || conn_string10 || ''', ''(select * from \
"LatestLogEntry")'') as t1("Port" integer, \
"Version" text, "AddDate" timestamp)' ||<br> ' \
UNION select "Port", "Version", "AddDate" from \
dblink(''' || conn_string11 || ''', ''(select * from \
"LatestLogEntry")'') as t1("Port" integer, \
"Version" text, "AddDate" timestamp)' ||<br> ' \
UNION select "Port", "Version", "AddDate" from \
dblink(''' || conn_string12 || ''', ''(select * from \
"LatestLogEntry")'') as t1("Port" integer, \
"Version" text, "AddDate" timestamp) ORDER BY \
"Port";';<br> OPEN _cursor FOR EXECUTE _query;<br>RAISE NOTICE \
'%', _query;<br><br>END<br>$$;<br><br>FETCH ALL FROM _cursor \
;</p><p>COMMIT;<br> ----</p><p>Results:</p><p>----</p><p><font face="monospace">Port \
Version AddDate<br>5432 9.4.1 2022-12-09 16:44:08.091<br>5433 10.20 \
2022-11-01 17:01:33.322<br>5434 11.15 2022-12-16 12:43:31.679973<br>5435 \
12.10 2022-11-01 17:01:33.322</font><br></p><p>----</p> </div>
</div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic