Can a whole server be restored over to another server in a single command? - Mailing list 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[@]} - 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