Thread: unbale to list schema
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.
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
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
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