backing and restoring whole database server - how does this script look

[Kevin Bailey <kbailey@xxxxxxxxxxxxxxxxxxx>]

We're trying to get a script to backup and restore a whole database server.

So far we're looking at something like

for db in `psql -U postgres -At -c 'select datname from pg_database where not datistemplate' -d template1`; do
    pg_dump -U ${PGSQL_USER} -h localhost --blobs --format=c --file="/var/backups/pg_dump_${db}.pgd" -d template1;

    psql -U statraining -h localhost --command "DROP DATABASE ${db};" template1;

    psql -U aplus2admin -h localhost --command "CREATE DATABASE ${db} WITH OWNER ${db};" template1;

    psql -U ${db} -h localhost -d ${db} -f /home/statraining/${db}_pg_backup.pgd;

done


which is obviously a rough-cut - and the main problem we have is that a client has set up a DB where the owner names do not match up with the DB names.

Or - isn't there something along the lines of:

$ pg_dumpall > all.dbs.out

(transfer file and then on other server...)

$ psql -f all.dbs.out postgres


Any pointers gratefully received.

Just to make thigs more interesting, we have to do this for a version 8.1 server and an older 7.4 server which has blobs in it.

bailey86