Re: Suggestions for Large DB Dump/Reload

On Tue, 23 Apr 2002 07:02:28 -0700 (PDT)
"Chris Gamache" <cgg007(at)yahoo(dot)com> wrote:
> 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

Steps 6 & 7 can be done prior to taking the production DB offline.
That will save a little time, at any rate.

> wait a whole lot more...
>
> 10. recreate the indexes on the huge table

When you recreate the indexes, how are you doing it? If you
run several index creations in parallel, that should probably
speed things up, especially on an SMP box. However, I haven't
checked what locks CREATE INDEX acquires, it may prevent
other concurrent index creations...

> 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?

Perhaps increase shared_buffers and wal_buffers?

Cheers,

Neil

--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC