Postgres Pro
Downloads
Home   >   mailing lists

Thread: Dump all databases to corresponding files

Dump all databases to corresponding files

From
CSN
Date:
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)?

Thanks,
csn



____________________________________________________________________________________
Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates
(http://voice.yahoo.com)

Re: Dump all databases to corresponding files

From
"Merlin Moncure"
Date:
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

Re: Dump all databases to corresponding files

From
Tom Lane
Date:
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

Re: Dump all databases to corresponding files

From
Tomasz Ostrowski
Date:
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

Re: Dump all databases to corresponding files

From
Roman Neuhauser
Date:
# 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

Re: Dump all databases to corresponding files

From
Tomasz Ostrowski
Date:
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