On Wed, Jan 17, 2024 at 1:46 PM Atul Kumar <akumar14871@xxxxxxxxx> 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
