On Mon, Jun 13, 2011 at 6:47 AM, Stuart Bishop <stuart@stuartbishop.net> wrote:
> On Wed, Jun 1, 2011 at 7:38 PM, Glyn Astill <glynastill@yahoo.co.uk> wrote:
>> Hi Guys,
>> Does anyone know of any tricks to replace primary key indexes without using
>> reindex? Or any other method that will not lock the table for an extended
>> amount of time on 8.4?
>> I've got a bloated primary key index on a table and I'd like to try and
>> clean it up without downtime.
>
> If you are brave, http://pqxx.org/development/libpqxx/wiki/PrimaryKeyTransplant
>
> If you go this route, ensure you have a test environment and ensure
> you can dump and restore your db schema after applying the patch, and
> that the diff of the before and after schemas checks out. If you screw
> up, you can end up with crazy things like multiple indexes with the
> same name and other stuff making your database unrecoverable.
Stuart,
That looks interesting. There's a similar process in the PostgreSQL
Admin Cookbook, though our work was independent.
I guess that's because in 9.0 and below it really is the only way to
make it work correctly.
Will look for your work in future, thanks.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services