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

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

>
> Or adapt it to put it into dated directories. Anyone got a tidier way? :S
>
> Thom
>