From: | Kevin Bailey <kbailey(at)freewayprojects(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | backing and restoring whole database server - how does this script look |
Date: | 2009-04-10 09:34:44 |
Message-ID: | 49DF12B4.8080100@freewayprojects.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
|
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Bailey | 2009-04-10 09:58:44 | Further question RE dumping and reloading whole database server |
Previous Message | fatih ozturk | 2009-04-10 06:08:04 | Re: log_statements = mod or all |