Re: I need to take metadata from a shell script.

Le 06/05/2010 17:56, Thom Brown a écrit :
> On 6 May 2010 16:52, Scott Mead <scott(dot)mead(at)enterprisedb(dot)com> wrote:
>
>>
>> On Thu, May 6, 2010 at 11:30 AM, Thom Brown <thombrown(at)gmail(dot)com> wrote:
>>
>>> On 6 May 2010 16:15, Andy Colson <andy(at)squeakycode(dot)net> wrote:
>>>
>>>> On 5/6/2010 2:57 AM, Jaume Calm wrote:
>>>>
>>>>> Hi! I was searching for a command like pg_dumpall but with the
>>>>> difference that I don’t want a single file for all databases, i would
>>>>> like to have a file for each one.
>>>>>
>>>>> I couldn’t fins such command, so the only option I see is to write a
>>>>> shell script with a loop for all the DBs. The problem is that I’m unable
>>>>> to find the way to obtain the DBs’ names in a shell script. Can someone
>>>>> help me with this?
>>>>>
>>>>> Best regards and thank you all for your time.
>>>>>
>>>>>
>>>> Depending on what version of PG you are on, try:
>>>>
>>>> psql -ltA
>>>>
>>>> a little read, cut, awk, perl, etc action and you should be good.
>>>>
>>>> -Andy
>>>>
>>>>
>>> Aha, yes, I should really look at the psql options more.
>>>
>>> You could extend that to exclude templates and the postrgres database and
>>> database attributes:
>>>
>>> psql -ltA | cut -d "|" -f 1 | grep -v "\( template0 \| template1 \|
>>> postgres \| : \)"
>>>
>>> And using Scott's loop:
>>>
>>> for line in `psql -lt | cut -d "|" -f 1 | grep -v "\( template0 \|
>>> template1 \| postgres \| : \)" | head -n -1 `; do pg_dump -f
>>> /home/backups/`date +\%Y\%m\%d`/"$line".sql; done
>>>
>>
>> Slightly:
>>
>> for line in `psql -t postgres -c "select datname from pg_database where
>> datname not in ('template0','template1','postgres')" `; do pg_dump -f
>> /home/backups/`date +\%Y\%m\%d`/"$line".sql ; done
>>
>>
>
> Yeah, that'll work better. It's certainly more legible. Could that
> potentially choke on database names with spaces or weird characters in do
> you reckon?
>

Not if you put it between quotes. BTW, your pg_dump command misses a
$line at the end to indicate the database to dump.

And rather than checking the database name, I usually prefer to filter
with datallowconn. This way:

query="select datname from pg_database where datallowconn=true"
for line in `psql -t -c "$query" postgres`
do
pg_dump -f /home/backups/`date +\%Y\%m\%d`/"$line".sql "$line"
done

It'll save template1 and postgres, but the script won't give me an error
on template0.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com