i had a similar (though not as complicated) question:
On Fri, Apr 10, 2009 at 5:34 AM, Kevin Bailey
<kbailey@freewayprojects.com> wrote:
> 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
the sysad on my end, does something like this (backs up the entire
directory) as a weekly cronjob:
/etc/init.d/postgresql stop > /dev/null
rsync -a -H --delete /var/lib/pgsql/ /scratch/pgsql
/etc/init.d/postgresql start > /dev/null
then /scratch/pgsql is backed up over to two different machines. the
DB that i run does not need to be live 24/7, so this stopping and
starting works with us. how safe is this method? in other words, when
things get screwed, will a rsync from /scratch/pgsql to /var/lib/pgsql
save a life? i know it DIDNOT when the server was upgraded from 32-bit
to 64-bit and i was forced to recreate the whole DB!!
--
Regards
PK
--------------------------------------
http://counter.li.org #402424
