Can a whole server be restored over to another server in a single command?

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

In response to

Responses

Browse pgsql-admin by date

  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