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
Re: Autovacuum on sys tables
Hi, Le lun. 19 déc. 2022 à 07:12, Inzamam Shafiq a écrit : > Thanks Thomas for the response, > > It means if I run VACUUM ANALYZE on pg_attribute or pg_class table, it is > completely safe? Can you please also please confirm what is meant by > "mid-level" vacuum? > To clarify my last message, VACUUM ANALYZE is a trade-off between autovacuum, that can be considered as a lazy non-blocking operation, and VACUUM FULL (eager & blocking one). The `mid-level` in my previous mail was used to pinpoint an intermediate blacking & resource consumption situation. VACUUM ANALYZE will: - remove dead tuples definition - refresh statistics (can improve execution plans for queries) Have a look at this website, it explains that better than me ;) : https://www.interdb.jp/pg/pgsql06.html > > Regards, > > *Inzamam Shafiq* > *Sr. DBA* > -- > *From:* Thomas Boussekey > *Sent:* Sunday, December 18, 2022 4:01 PM > *To:* Inzamam Shafiq > *Cc:* pgsql-general@lists.postgresql.org < > pgsql-general@lists.postgresql.org> > *Subject:* Re: Autovacuum on sys tables > > Hello Inzamam, > > Le sam. 17 déc. 2022 à 08:16, Inzamam Shafiq > a écrit : > > Dear Experts, > > Hope you are doing well. > > I have a question that autovacuum is running on sys tables like pg_class, > pg_attribute, is it a normal thing? Further, what is dead tuples are not > removed from Autovacuum, can we remove the dead tuples using FULL VACUUMM > or pg_repack on sys tables? > > > pg_repack cannot run on system tables, it will FAIL with an explicit error > message explaining the limitation. > > Each time you perform DDL operations (CREATE, DROP, ALTER), rows are > inserted/updated or deleted into the system tables : pg_class, pg_attribute > ... > Autovacuum operations perform "low-level" operations, it can be > interesting to perform "middle-level" vacuum with VACUUM ANALYZE... that is > not blocking, but will be more a resource-consuming operation than > autovacuum. > > Performing VACUUM FULL operation will block access to these pillar tables > of your database. > If your application/users can handle it, go ahead! > At work on this kind of operation, I set a statement_timeout, in order to > properly stop the process if it is over a defined amount of time. > > Hope this helps, > Thomas > > > Thank you. > > Regards, > > *Inzamam Shafiq* > *Sr. DBA* > >
