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

On 6 May 2010 21:15, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:

> Guillaume Lelarge wrote:
>
>> And rather than checking the database name, I usually prefer to filter
>> with datallowconn.
>> It'll save template1 and postgres, but the script won't give me an error
>> on template0.
>>
>>
>
> Template databases are labeled as such, so this excludes both template0 and
> template1 without having to hardcode their names:
>
> query="select datname from pg_database where not datistemplate"
>
>
> However, if you can't connect to them, you can't dump them either! So you
> really need both those things:
>
>
> query="select datname from pg_database where not datistemplate and
> datallowconn"
> for line in `psql -At -c "$query" postgres`
>
> do
> pg_dump -f /home/backups/`date +\%Y\%m\%d`/"$line".sql "$line"
> done
>
>
> It's also a good idea to use "-At" instead of just "-t" when writing
> scripts that parse the output from psql, as I modified the above. If you
> look carefully you'll see that without the "-A", there is a space before the
> name of each line output. Doesn't matter in this case, but using the
> default "aligned" mode can cause problems in more complicated scripts that
> output more than one field.
>
>
>
I've actually started using this myself, and have set myself up following in
a cron job (as couldn't decide on a nice location for the script):

mkdir /tmp/`date +\%Y\%m\%d` && pg_dumpall -c | gzip > /tmp/`date
+\%Y\%m\%d`/FULL.sql.gz && query="select datname from pg_database where not
datistemplate and datallowconn;";for line in `psql -U postgres -At -c
"$query" postgres`;do pg_dump -U postgres "$line" | gzip > /tmp/`date
+\%Y\%m\%d`/"$line".sql.gz;done;scp -r /tmp/`date +\%Y\%m\%d`
username(at)my(dot)location:/backups/location/ ; rm -rf /tmp/`date +\%Y\%m\%d`

This creates a directory named in yyyyddmm format in /tmp, then backs up the
whole database cluster into a file called FULL.sql.gz, then each individual
database as databasename.sql.gz, then copies it off to a remote location,
then deletes them from /tmp.

Thom