Suggestions for Large DB Dump/Reload

From: Chris Gamache <cgg007(at)yahoo(dot)com>
To: postgres list <pgsql-general(at)postgresql(dot)org>
Subject: Suggestions for Large DB Dump/Reload
Date: 2002-04-23 14:02:28
Message-ID: 20020423140228.33089.qmail@web13803.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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/

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ian Cass 2002-04-23 14:03:44 Re: Date indexing
Previous Message Tom Lane 2002-04-23 13:56:38 Re: Date indexing