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

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?

Thom