PostgreSQL Multi-DB Query Runner

This repository contains scripts that can be used to run a SQL query on all databases within a PostgreSQL server. The scripts connect to the PostgreSQL server, retrieve a list of all databases, and execute the specified SQL query on each database. The results of the query will be written to an output file in a more readable format. These scripts have two implementation, one in Python and the other in Rust. Both implementations are useful command-line tools for running a query on all non-template databases of a PostgreSQL server, and writing the query output to a specified file in a readable format, such as XML, JSON or plain text. The Python implementation makes use of the popular psycopg2 library for connecting to the PostgreSQL server and the xml.etree.ElementTree library for generating the XML output. The Rust implementation uses the postgres library for connecting to the PostgreSQL server. The script is easy to use and can be run from the command-line by providing a configuration file, a SQL file, an output file and an optional format for the output file. Additionally, the script also handles errors that might occur while connecting to the server or running the query, making it a robust and reliable tool for automating the process of running a query on multiple databases

[postgresql]
host = <hostname>
port = <port>
user = <username>
password = <password>

The Python script is a command line application that can be used to run a SQL query on all databases within a PostgreSQL server. The script connects to the PostgreSQL server, retrieves a list of all databases, and executes the specified SQL query on each database. The results of the query will be written to an output file in a more readable format.

• psycopg2 library

python cross_db.py cross_db.conf cross_db.sql cross_db.out [xml|text|json]

• cross_db.conf: The configuration file containing the database credentials.
• cross_db.sql: The SQL file containing the query to be executed on each database.
• cross_db.out: The file to write the query output to in a more readable format.

WITH table_sizes AS (
    SELECT
        table_schema || '.' || table_name AS table_name,
        pg_total_relation_size(table_schema || '.' || table_name) AS table_size
    FROM
        information_schema.tables
    WHERE
        table_schema NOT LIKE 'pg_%'
        AND table_schema != 'information_schema'
    ORDER BY
        table_size DESC
)
SELECT
    table_name,
    pg_size_pretty(table_size) AS pretty_size
FROM
    table_sizes
LIMIT 3;

{
  "postgres": [
    [
      "public.pgbench_account_postgresl",
      "4486 MB"
    ],
    [
      "public.foo",
      "346 MB"
    ],
    [
      "public.pgbench_tellers",
      "256 kB"
    ]
  ],
  "testdb": [
    [
      "public.pgbench_accounts",
      "5981 MB"
    ],
    [
      "public.test_tb",
      "422 MB"
    ],
    [
      "public.pgbench_tellers",
      "312 kB"
    ]
  ]
}

SELECT 
    pg_stat_get_db_numbackends(oid) AS "connections", 
    pg_stat_get_db_xact_commit(oid) AS "commits", 
    pg_stat_get_db_xact_rollback(oid) AS "rollbacks", 
    pg_stat_get_db_blocks_fetched(oid) AS "blocks_fetched", 
    pg_stat_get_db_blocks_hit(oid) AS "blocks_hit", 
    pg_stat_get_db_tuples_returned(oid) AS "tuples_returned", 
    pg_stat_get_db_tuples_fetched(oid) AS "tuples_fetched", 
    pg_stat_get_db_tuples_inserted(oid) AS "tuples_inserted", 
    pg_stat_get_db_tuples_updated(oid) AS "tuples_updated", 
    pg_stat_get_db_tuples_deleted(oid) AS "tuples_deleted", 
    pg_stat_get_db_deadlocks(oid) AS "deadlocks" 
FROM pg_database; 

<?xml version="1.0" ?>
<databases>
  <database name="postgres">
    <columns>
      <column>connections</column>
      <column>commits</column>
      <column>rollbacks</column>
      <column>blocks_fetched</column>
      <column>blocks_hit</column>
      <column>tuples_returned</column>
      <column>tuples_fetched</column>
      <column>tuples_inserted</column>
      <column>tuples_updated</column>
      <column>tuples_deleted</column>
      <column>deadlocks</column>
    </columns>
    <row>(1, 11173, 65, 11744073, 10639318, 33910547, 130205, 40004003, 97, 396, 0)</row>
    <row>(0, 2581, 16, 11851686, 10376734, 41038722, 38563, 50004693, 414, 15, 0)</row>
    <row>(0, 9516, 0, 402756, 400992, 3845733, 90321, 16227, 743, 34, 0)</row>
    <row>(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)</row>
    <row>(0, 2310, 8, 1163983, 151786, 31012319, 30716, 30003535, 6, 0, 0)</row>
  </database>
  <database name="testdb">
    <columns>
      <column>connections</column>
      <column>commits</column>
      <column>rollbacks</column>
      <column>blocks_fetched</column>
      <column>blocks_hit</column>
      <column>tuples_returned</column>
      <column>tuples_fetched</column>
      <column>tuples_inserted</column>
      <column>tuples_updated</column>
      <column>tuples_deleted</column>
      <column>deadlocks</column>
    </columns>
    <row>(0, 11174, 65, 11744197, 10639442, 33910587, 130245, 40004003, 97, 396, 0)</row>
    <row>(1, 2582, 16, 11851717, 10376765, 41038737, 38577, 50004693, 414, 15, 0)</row>
    <row>(0, 9516, 0, 402756, 400992, 3845733, 90321, 16227, 743, 34, 0)</row>
    <row>(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)</row>
    <row>(0, 2310, 8, 1163983, 151786, 31012319, 30716, 30003535, 6, 0, 0)</row>
  </database>
</databases>

The Rust script is a command line application that can be used to run a SQL query on all databases within a PostgreSQL server. The script connects to the PostgreSQL server, retrieves a list of all databases, and executes the specified SQL query on each database. The results of the query will be written to an output file in a more readable format.

- cargo run -- main.rs cross_db.conf cross_db.sql cross_db.out

• cross_db.conf: The configuration file containing the database credentials.
• cross_db.sql: The SQL file containing the query to be executed on each database.
• cross_db.out: The file to write the query output to in a more readable format.

• Rust 1.51.0 or higher
• postgres crate
• config crate