Re: copying databases w/ indexes

On Thu, 14 Aug 2003, Yuji Shinozaki wrote:

>
> I have two questions about restoring and moving databases (in pg 7.3), to
> which I have not been able to find a suitable answers in the documentation
> (and searching the list archives has been painfully slow and fruitless).
>
> 1) What is the fastest way to restore a database *with indexes*?
>
> We have situation where I need to make a duplicate of a production
> database for development work, so that we can develop code without
> interfering with the production database. I use pg_dump for this, but I
> have not been able to get pg_restore to work (usually because of
> constraint problems). So I do the restores from a text dump piped into
> psql (with all the constraints applied last). Then the problem is that
> reindexing takes a very long time. One particular index is taking on the
> order of 12 hours to finish.

I've been dumping and restoring a very very big database, with indexes,
with no such problems. The table details that get dumped setup the
indexes, and AIUI the indexes are kept up-to-date as items are added.

--

Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University