Dump all databases to corresponding files

On 11/6/06, CSN <cool_screen_name90001@yahoo.com> wrote:

Anybody know of a script that dumps all databases into corresponding dump files, e.g.

$ ./dump
template0 -> template0.sql
template1 -> template1.sql
db1 -> db1.sql
db2 -> db2.sql
...

Also, would this approach add up to equal the output of pg_dumpall, or does pg_dumpall dump
additional things (if so, please describe how they'd also be dumped)?

there are many ways to do this, but this would be a fun exercise for pl/sh.

merlin

CSN <cool_screen_name90001@yahoo.com> writes:

Also, would this approach add up to equal the output of pg_dumpall, or
does pg_dumpall dump additional things (if so, please describe how
they'd also be dumped)?

You'd be missing roles (user/group definitions) and tablespace
definitions. pg_dump doesn't emit either.

regards, tom lane

On Sun, 05 Nov 2006, CSN wrote:

Anybody know of a script that dumps all databases into
corresponding dump files

I've written this one in bash:

#########################################################
#!/bin/bash

pg_dumpall -g > /var/lib/pgsql/backups/globals.sql
for dbname in `psql -qXtc "
select
datname from pg_catalog.pg_database
where datname<>'template0'
" template1`
do
pg_dump -b -F t "$dbname" > "/var/lib/pgsql/backups/$dbname.dump"
done
#########################################################

This would break if any database name has white space.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh

# tometzky@batory.org.pl / 2006-11-06 12:26:43 +0100:

On Sun, 05 Nov 2006, CSN wrote:

Anybody know of a script that dumps all databases into
corresponding dump files

I've written this one in bash:

#########################################################
#!/bin/bash

pg_dumpall -g > /var/lib/pgsql/backups/globals.sql
for dbname in `psql -qXtc "
select
datname from pg_catalog.pg_database
where datname<>'template0'
" template1`
do
pg_dump -b -F t "$dbname" > "/var/lib/pgsql/backups/$dbname.dump"
done
#########################################################

This would break if any database name has white space.

Why don't you use "while" then?

psql -qXtc "$query" template1 | while read dbname; do
pg_dump -b -F t "$dbname" > "/var/lib/pgsql/backups/$dbname.dump"
done

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

On Mon, 06 Nov 2006, Roman Neuhauser wrote:

# tometzky@batory.org.pl / 2006-11-06 12:26:43 +0100:

On Sun, 05 Nov 2006, CSN wrote:

Anybody know of a script that dumps all databases into
corresponding dump files

I've written this one in bash:
[snip]
This would break if any database name has white space.

Why don't you use "while" then?
psql -qXtc "$query" template1 | while read dbname; do
pg_dump -b -F t "$dbname" > "/var/lib/pgsql/backups/$dbname.dump"
done

It won't work if a database name have white space as first or last
letter... Or when database name has a newline somewhere (also
possible). It's hard to do it right so I did it simply wrong :-)

The way to do it right would be somehow forcing psql to output rows
separated by nulls ("\0") and use "xargs --null -i". But I don't know
how to do it.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh