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
|
