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[@]} - 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
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
