copying databases w/ indexes

From: Yuji Shinozaki <ys2n(at)virginia(dot)edu>
To: pgsql-admin(at)postgresql(dot)org
Subject: copying databases w/ indexes
Date: 2003-08-14 17:35:34
Message-ID: Pine.LNX.4.44.0308141319120.11973-100000@atg2000.itc.virginia.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin


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(at)virginia(dot)edu Advanced Technologies Group
(434)924-7171 Information Technology & Communication
http://www.people.virginia.edu/~ys2n University of Virginia

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Sam Barnett-Cormack 2003-08-14 17:58:04 Re: copying databases w/ indexes
Previous Message Priya G 2003-08-14 17:01:45 forcce a table in memory