Re: PostgreSQL Database 'Information' Script - is there one out there?

[MichaelDBA <MichaelDBA@xxxxxxxxxxx>]

There are a bunch of tools, many in the form of free extensions like pgmetrics.

Regards,
Michael Vitale


Ron Johnson wrote on 1/30/2025 9:20 AM:

On Thu, Jan 30, 2025 at 7:14 AM Edwin UY <edwin.uy@xxxxxxxxx> wrote:

Hi,

Does anyone know of any existing script/s out there somewhere that generates some kind of database/instance inventory, like listing of what databases exist (\l), schema (\dn) in each databases, extensions (\dx), user lists (\du), replication slots, tables, views, parameter default/non-default settings etc?

Maybe there is already one out there, either a shell script or a SQL script that I can just run from psql and save the output to file.

At the moment, running manually via psql running \l, running \c to each DB and running \dn,  didn't realize I have to run \dn on each database :(, and running select statements from pg_tables and so on.

Why not put everything you currently do in a shell script?

For per-database commands, something like this:

export PGHOST=mumble

export PGUSER=postgres

DbList=`psql -AXtc "select datname from pg_database
                    where datistemplate=false and datname <> 'postgres'
                    order by datname ;"`
for DB in $DbList;
do
    psql -h $DB -ac '\dn'

    psql -h $DB -ac 'SELECT ... FROM pg_tables WHERE ...;'

done

--

Death to <Redacted>, and butter sauce.

Don't boil me, I'm still alive.

<Redacted> lobster!

Regards,

Michael Vitale

Michaeldba@xxxxxxxxxxx

703-600-9343