From: | Kevin Bailey <kbailey(at)freewayprojects(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Can a whole server be restored over to another server in a single command? |
Date: | 2009-04-11 09:37:24 |
Message-ID: | 49E064D4.1070300@freewayprojects.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Can a whole server be restored over to another server in a single command?
Or would it always need some sort of loop.
I've tried
pg_dumpall --inserts --clean -h $REMOTE -U admin | psql -U postgres
template1
but this produces errors such as
ERROR: role "admin" cannot be dropped because some objects depend on it
DETAIL: owner of database test1
owner of database test
7 objects in database test1
201 objects in database test
ERROR: role "admin" already exists
ERROR: current user cannot be dropped
ERROR: role "postgres" already exists
ERROR: role "sql-ledger" cannot be dropped because some objects depend on it
DETAIL: owner of database bean-demo
owner of database bean-cash
owner of database bean-41
owner of database bean-40
owner of database bean-39
owner of database bean-37
owner of database bean-36
owner of database bean-35
We're also looking at:
PG_DBs=(`for pg_db in /var/backups/pg_dump_*.pgd ; do basename ${pg_db}
.pgd | cut -f 3 -d\_ ; done`)
for index in $(seq 0 $((${#PG_DBs[(at)]} - 1)))
do
PG_SRC=${PG_DBs[$index]};
echo;
echo backing up PgSQL database file ${PG_SRC} to ${REMOTE_SRV};
psql -U admin -h ${REMOTE_SRV} --command \"DROP DATABASE ${PG_SRC}\"
template1;
psql -U admin -h ${REMOTE_SRV} --command \"CREATE DATABASE ${PG_SRC}\"
template1;
pg_restore -U admin -h ${REMOTE_SRV} --dbname=template1
/var/backups/pg_dump_${PG_SRC}.pgd;
done
Although I feel this can be simplified with using
psql -l -A -t
So - single command or loop?
And if we're using the loop how do we make sure that the ownership of
everything is OK?
Thanks,
Kevin Bailey
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Bailey | 2009-04-11 10:30:40 | Re: Can a whole server be restored over to another server in a single command? |
Previous Message | Kevin Bailey | 2009-04-11 01:34:34 | Re: backing and restoring whole database server - how does this script look |