On Mon, Jun 13, 2011 at 6:47 AM, Stuart Bishop <stuart@xxxxxxxxxxxxxxxx> wrote: > On Wed, Jun 1, 2011 at 7:38 PM, Glyn Astill <glynastill@xxxxxxxxxxx> 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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
