Re: review db dump script and difference between pg_dump & pg_dumpall

On Thu, Oct 09, 2008 at 02:29:30PM +1100, Chris Henderson wrote:
> I have written the following script to backup _all_ databases on
> various servers that are running postgresql. I don't want anything
> fancy - just be able to backup the database and restore when bad times
> come. Just posting it here to be reviewed to see if I have made any
> mistake:
> #!/bin/bash
> DIR=/backup
> [ ! $DIR ] && mkdir -p $DIR || :
> # ignore any name starting with ---, List, Name, ( or template 0 and 1
> LIST=$(psql -l | awk '{ print $1}' | grep -vE
> '^-|^List|^Name|^\(|template[0|1]')
> for d in $LIST
> do
> pg_dump $d | gzip -c > $DIR/$d.out.postgres.`hostname`.gz
> done

there are problems with this script. but they are not really imporant,
as you simplky try to replicate pg_dumpall. why not use pg_dumpall
instead?

> Also, I have read the respective manpages but never understood the
> difference between pg_dump & pg_dumpall. But I noticed pg_dumpall
> dumps the schema (?) and pg_dump dumps the actual tables and rows etc.

both pg_dump and pg_dumpall can dump schema and/or data.

pg_dump dumps 1 database.
pg_dumpall dumps all databases in given instance of postgresql.

pg_dumpall additionally dumps user information.

best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz(at)depesz(dot)com / aim:depeszhdl / skype:depesz_hdl / gg:6749007