Thread: unbale to list schema

unbale to list schema

From
Atul Kumar
Date:
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.

Re: unbale to list schema

From
Erik Wienhold
Date:
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



Re: unbale to list schema

From
Jim Nasby
Date:
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




Re: unbale to list schema

From
Ron Johnson
Date:
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