As I prepare to rev up to the latest postgresql, I find my stomach twisting yet
again at the thought of dumping my one huge table of 27,000,000 rows (of
heavily indexed data) and reloading and reindexing it.
I'm looking for suggestions for streamlining my process... Most of the steps
are normal upgrading steps
1. disallow access to database (except from me)
2. drop all my indexes on the HUGE table
3. pg_dumpall > outputfile
(thankfully, I don't have large objects. I don't need to keep OID's)
wait... wait... wait...
4. kill -INT `cat /usr/local/pgsql/data/postmaster.pid`
5. mv /usr/local/pgsql /usr/local/pgsql.old
6. make new postgresql
7. /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
8. /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data
9. /usr/local/pgsql/bin/psql -d template1 -f outputfile
wait a whole lot more...
10. recreate the indexes on the huge table
many hours later....
11. vacuum analyze the whole database
12. go back into production
This takes _forever_ on a (dual) p2 450 with 256MB Ram and a 10000 RPM SCSI
filesystem... Besides upgrading the hardware, is there anything else I can do
process-wise to speed things up? The fsync is off, and I've increased WAL Files
to a good large number... Have I left any critical detail out of my problem
description? Do you need to see my actual config settings?
CG
__________________________________________________
Do You Yahoo!?
Yahoo! Games - play chess, backgammon, pool and more
http://games.yahoo.com/