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

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.

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.

 

 

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 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: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

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.