pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedunbale to list schema
4+ messages / 4 participants
[nested] [flat]
* unbale to list schema
@ 2024-01-17 18:46 Atul Kumar <akumar14871@gmail.com>
0 siblings, 3 replies; 4+ messages in thread
From: Atul Kumar @ 2024-01-17 18:46 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
Hi,
I am not able to find any solution to list all schemas in all databases at
once, to check the structure of the whole cluster.
As I need to give a few privileges to a user to all databases, their
schemas and schemas' objects (tables sequences etc.).
Please let me know if there is any solution/ query that will serve the
purpose.
Regards.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: unbale to list schema
@ 2024-01-17 20:41 Erik Wienhold <ewie@ewie.name>
parent: Atul Kumar <akumar14871@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Erik Wienhold @ 2024-01-17 20:41 UTC (permalink / raw)
To: Atul Kumar <akumar14871@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
On 2024-01-17 19:46 +0100, Atul Kumar wrote:
> I am not able to find any solution to list all schemas in all databases at
> once, to check the structure of the whole cluster.
You have to connect to each database and run the necessary statements
per database.
> As I need to give a few privileges to a user to all databases, their
> schemas and schemas' objects (tables sequences etc.).
>
> Please let me know if there is any solution/ query that will serve the
> purpose.
What is your environment? You can do that with psql and shell.
For example, in Bash I use the following pattern: select the relevant
database names and loop over the psql output to connect to each
database to run the relevant statements (\dn in this case to list all
schemas).
psql postgres --no-psqlrc -A -t -c "select datname from pg_database where datname <> 'template0'" |
while IFS= read -r dbname
do
psql -d "$dbname" -c '\dn'
done
--
Erik
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: unbale to list schema
@ 2024-01-17 23:04 Jim Nasby <jim.nasby@gmail.com>
parent: Atul Kumar <akumar14871@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Jim Nasby @ 2024-01-17 23:04 UTC (permalink / raw)
To: Atul Kumar <akumar14871@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>
On 1/17/24 12:46 PM, Atul Kumar wrote:
> Hi,
>
> I am not able to find any solution to list all schemas in all databases
> at once, to check the structure of the whole cluster.
Easiest way to do this is `pg_dumpall --schema-only`.
--
Jim Nasby, Data Architect, Austin TX
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: unbale to list schema
@ 2024-01-22 19:04 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Atul Kumar <akumar14871@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Ron Johnson @ 2024-01-22 19:04 UTC (permalink / raw)
To: pgsql-general
On Wed, Jan 17, 2024 at 1:46 PM Atul Kumar <akumar14871@gmail.com> wrote:
> Hi,
>
> I am not able to find any solution to list all schemas in all databases at
> once, to check the structure of the whole cluster.
>
> As I need to give a few privileges to a user to all databases, their
> schemas and schemas' objects (tables sequences etc.).
>
> Please let me know if there is any solution/ query that will serve the
> purpose.
>
Is this what you are looking for?
#!/bin/bash
declare DbHost=<some_host>
declare DB=<some_db>
declare Schemas="select schema_name from information_schema.schemata
where schema_name not like 'pg_%' and schema_name !=
'information_schema';"
for s in $(psql --host=$DbHost --dbname=$DB -AXtc "${Schemas}")
do
pg_dump --dbname=$DB --schema-only --schema=${s} >
schema_${DbHost}_${DB}_${s}.sql
done
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-01-22 19:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-17 18:46 unbale to list schema Atul Kumar <akumar14871@gmail.com>
2024-01-17 20:41 ` Erik Wienhold <ewie@ewie.name>
2024-01-17 23:04 ` Jim Nasby <jim.nasby@gmail.com>
2024-01-22 19:04 ` Ron Johnson <ronljohnsonjr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox