CAJ CAJ wrote:
Hello,
I didn't get any response on the GENERAL list so i'm escalating this ....
We have several independent database servers with ~50GB+ databases
running postgres 8.0.x. We are planning to upgrade these databases to
postgres 8.2.x over the weekend
We plan to use the following steps to upgrade each server,
1. Dump the 8.0.x database cluster using 8.2.x pg_dumpall
% ./pg_dumpall > pgdumpall_backup.sql
2.Dump the 8.0.x database including large objects in compressed
custom format using 8.2.x pg_dump
% ./pg_dump -Fc -b -Z9 dbname > pgdump_lobs_backup
Restoring database
1. Initialize 8.2.x darabase
% initdb -D /data/pgdata
2. Restore template1 database from cluster dump
% ./psql -d template1 < pgdumpall_backup.sql
3. Delete database dbname else restoring will give error about
existing dbname
% dropdb dbname
4. Create fresh dbname
% createdb -O dbowner dbname
5. Restore database with lobs
% ./pg_restore -v -Fc -d dbname -e -U dbowner < pgdumpall_lobs_backup
Some of the problems we have are,
1. We are not sure if all of the data will be available after
dump/restore with above process
2. The dump and restore process is very very slow to be complete over
the weekend (takes approx 1GB/hr to dump on a dual G5 PPC 2Ghz with
1GB RAM and RAID 1 disks)
What is the fastest way to upgrade postgres for large databases that
has binary objects?
Thanks for all your help.
Your procedure dumps and restore the databases twice. This seems less
than sound. My prediction is that you could get a 50% speed improvement
by fixing that ...
The only thing you really need pg_dumpall for is the global tables. I
would just use pg_dumpall -g to get those, and then use pg_dump -F c +
pg_restore for each actual database.
Another thing is to make sure that pg_dump/pg_restore are not competing
with postgres for access to the same disk(s). One way to do that is to
run them from a different machine - they don't have to be run on the
server machine - of course then the network can become a bottleneck, so
YMMV.
cheers
andrew
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
