Thread: PostgreSQL Database 'Information' Script - is there one out there?
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.
On Thu, Jan 30, 2025 at 7:14 AM Edwin UY <edwin.uy@gmail.com> 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'
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!
There are a whole bunch of extension tools out there for this type of stuff. Check out pgmetrics. Regards, Michael Vitale Edwin UY wrote on 1/30/2025 7:13 AM: > 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. > >