Aha! Thanks for the reply, because it got me thinking, and I reexamined
the schema and discovered that the problem was that we had a foreign key
constraint involving two columns of different type (varchar vs. integer!).
[Yeesh.] I think the type conversion was what was making reindexing (not
to mention inserts) slow!
Now the restore only takes about 20 minutes (yay!)
Thanks,
yuji
----
On Thu, 14 Aug 2003, Sam Barnett-Cormack wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
Yuji Shinozaki Computer Systems Senior Engineer
ys2n@virginia.edu Advanced Technologies Group
(434)924-7171 Information Technology & Communication
http://www.people.virginia.edu/~ys2n University of Virginia