I am attempting to transfer a cluster of 28 databases from one machine to
another. The former machine runs PostgreSQL 7.2.3; the latter 7.4.2.
I first tried a filesystem-level backup. I stopped the old server, copied
its data directory, restarted the old server, transferred the copied data
to the new machine, and attempted to start the new server. The new server
complained about the version mismatch. Fair enough.
I then tried pg_dump and its ilk. As we rely on OIDs and BLOBs, I didn't
use pg_dumpall for everything, but rather used a shell script to grab the
global stuff and to iterate through the databases with pg_dump:
#!/bin/sh
export PGUSER=postgres
export PGPASSWORD=************
pg_dumpall -g > globals.sql
for i in $(psql template1 -t -A -c \
"select datname from pg_database;"); do
echo "Dumping catalog [$i] ..."
pg_dump -b -o -Ft $i > $i.tar
done
I then attempted to restore this data on the new machine:
#!/bin/sh
export PGUSER=postgres
psql template1 -f globals.sql
for i in *.tar; do
echo "Restoring from [$i] ..."
pg_restore -d template1 -C $i.tar
done
The new server's pg_hba.conf's first line is "local all all trust".
Most of the databases are owned by usesysid #1 ("postgres") and were
restored properly. The ones owned by a different user did not:
Restoring from [ajp.tar] ...
pg_restore: [archiver (db)] could not execute query: ERROR:
permission denied to create database
Given that (a) I'm using PGUSER=postgres and (b) my pg_hba.conf should be
sufficiently liberal anyway, I'm wondering why this is not working.
Also, template0 failed due to a null tar file, and template1 failed due to
it already existing. Would I be correct in assuming that the globals.sql
step takes care of the necessary items in template1, and that I can leave
template0 alone altogether?
--
Kevin DeGraaf
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
