I need to take metadata from a shell script.

On 6 May 2010 08:57, Jaume Calm <jaume@isac.cat> 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.

You could try:

psql -l | cut -d "|" -f 1 | tail -n +4 | head -n -2

A bit hacky, and there might be a better way.

Regards

Thom

On Thu, May 6, 2010 at 3:57 AM, Jaume Calm <jaume@isac.cat> 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?

for line in `psql -t postgres -c "select datname from pg_database" `; do
printf "$line\n"; done

--Scott

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

On 6 May 2010 16:15, Andy Colson <andy@squeakycode.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

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

Thom

On Thu, May 6, 2010 at 11:30 AM, Thom Brown <thombrown@gmail.com> wrote:

On 6 May 2010 16:15, Andy Colson <andy@squeakycode.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

On 6 May 2010 16:52, Scott Mead <scott.mead@enterprisedb.com> wrote:

On Thu, May 6, 2010 at 11:30 AM, Thom Brown <thombrown@gmail.com> wrote:

On 6 May 2010 16:15, Andy Colson <andy@squeakycode.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

Le 06/05/2010 17:56, Thom Brown a �crit :

On 6 May 2010 16:52, Scott Mead <scott.mead@enterprisedb.com> wrote:

On Thu, May 6, 2010 at 11:30 AM, Thom Brown <thombrown@gmail.com> wrote:

On 6 May 2010 16:15, Andy Colson <andy@squeakycode.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

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.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us

On 6 May 2010 21:15, Greg Smith <greg@2ndquadrant.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@my.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