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.
Is there a way to dump and restore the index itself so that the tables do
not need to be reindexed? The dumps and restores are between identical
installations of postgres, sometimes even the same running instance of
postgres.
And a tangentially related question:
2) Is there a way to rename a database? That is the other thing that I
occasionally need to do, and the only way I have found is dump/restore,
and again we pay the penalty of the 12-hour reindexing.
Any pointers?
Thanks!
yuji
----
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