public inbox for [email protected]  

help / color / mirror / Atom feed

From: MichaelDBA <[email protected]>
To: Ron Johnson <[email protected]>
Cc: Pgsql-admin <[email protected]>
Subject: Re: PostgreSQL Database 'Information' Script - is there one out there?
Date: Sat, 1 Feb 2025 08:03:32 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CANzqJaAvQ2eLeVzQ48PpHRDC9BcFXDj8z1YnhLDqGAXKbMK4dg@mail.gmail.com>
References: <CA+wokJ-C+M8Kw8JrdoH5qn25B2fz0beyLOxVSMTN5g6_NUqDAA@mail.gmail.com>
	<CANzqJaAvQ2eLeVzQ48PpHRDC9BcFXDj8z1YnhLDqGAXKbMK4dg@mail.gmail.com>

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 <[email protected]
> <mailto:[email protected]>> 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

[email protected] <mailto:[email protected]>

703-600-9343






Attachments:

  [image/jpeg] pgadvanced3.jpg (20.6K, 3-pgadvanced3.jpg)
  download | view image

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: PostgreSQL Database 'Information' Script - is there one out there?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox