public inbox for [email protected]
help / color / mirror / Atom feedPostgreSQL Database 'Information' Script - is there one out there?
4+ messages / 3 participants
[nested] [flat]
* PostgreSQL Database 'Information' Script - is there one out there?
@ 2025-01-30 12:13 Edwin UY <[email protected]>
2025-01-30 14:20 ` Re: PostgreSQL Database 'Information' Script - is there one out there? Ron Johnson <[email protected]>
2025-01-30 14:37 ` Re: PostgreSQL Database 'Information' Script - is there one out there? MichaelDBA <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: Edwin UY @ 2025-01-30 12:13 UTC (permalink / raw)
To: [email protected]
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL Database 'Information' Script - is there one out there?
2025-01-30 12:13 PostgreSQL Database 'Information' Script - is there one out there? Edwin UY <[email protected]>
@ 2025-01-30 14:20 ` Ron Johnson <[email protected]>
2025-02-01 13:03 ` Re: PostgreSQL Database 'Information' Script - is there one out there? MichaelDBA <[email protected]>
1 sibling, 1 reply; 4+ messages in thread
From: Ron Johnson @ 2025-01-30 14:20 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
On Thu, Jan 30, 2025 at 7:14 AM Edwin UY <[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!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL Database 'Information' Script - is there one out there?
2025-01-30 12:13 PostgreSQL Database 'Information' Script - is there one out there? Edwin UY <[email protected]>
2025-01-30 14:20 ` Re: PostgreSQL Database 'Information' Script - is there one out there? Ron Johnson <[email protected]>
@ 2025-02-01 13:03 ` MichaelDBA <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: MichaelDBA @ 2025-02-01 13:03 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL Database 'Information' Script - is there one out there?
2025-01-30 12:13 PostgreSQL Database 'Information' Script - is there one out there? Edwin UY <[email protected]>
@ 2025-01-30 14:37 ` MichaelDBA <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: MichaelDBA @ 2025-01-30 14:37 UTC (permalink / raw)
To: Edwin UY <[email protected]>; +Cc: [email protected]
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.
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-02-01 13:03 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-30 12:13 PostgreSQL Database 'Information' Script - is there one out there? Edwin UY <[email protected]>
2025-01-30 14:20 ` Ron Johnson <[email protected]>
2025-02-01 13:03 ` MichaelDBA <[email protected]>
2025-01-30 14:37 ` MichaelDBA <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox