On Wed, Jan 17, 2024 at 1:46 PM Atul Kumar <[email protected]> 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
