[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: unbale to list schema
From:       Ron Johnson <ronljohnsonjr () gmail ! com>
Date:       2024-01-22 19:04:21
Message-ID: CANzqJaC+5O1mS8bKhP_qNkGFEyRBiwS0dc2hS+Xsfhx2HGq3Bw () mail ! gmail ! com
[Download RAW message or body]

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

[Attachment #3 (text/html)]

<div dir="ltr"><div dir="ltr">On Wed, Jan 17, 2024 at 1:46 PM Atul Kumar &lt;<a \
href="mailto:akumar14871@gmail.com">akumar14871@gmail.com</a>&gt; \
wrote:<br></div><div class="gmail_quote"><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr">Hi,<br><div><br></div><div>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.</div><div><br></div><div>As I need to give a few \
privileges to a user to all databases, their schemas and schemas&#39; objects (tables \
sequences etc.).</div><div><br></div><div>Please let me know if there is any \
solution/  query that will serve the \
purpose.</div></div></blockquote><div><br></div><div>Is this what you are looking \
for?</div><div><br></div><div><font face="monospace">  #!/bin/bash</font></div><font \
face="monospace">declare DbHost=&lt;some_host&gt;<br>declare \
DB=&lt;some_db&gt;<br>declare Schemas=&quot;select schema_name from \
information_schema.schemata <br>                          where schema_name not like \
&#39;pg_%&#39; and schema_name != &#39;information_schema&#39;;&quot;<br>for s in \
$(psql --host=$DbHost --dbname=$DB -AXtc &quot;${Schemas}&quot;)<br>do<br>      \
pg_dump --dbname=$DB --schema-only --schema=${s} &gt; \
schema_${DbHost}_${DB}_${s}.sql<br></font><div><font face="monospace">done  \
</font></div></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]