From: Stuart Bishop <stuart@stuartbishop.net>
To: Glyn Astill <glynastill@yahoo.co.uk>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Reindexing primary keys without locking
Date: Mon, 13 Jun 2011 12:47:51 +0700
Message-ID: <BANLkTim8drpsdqRrmDspsOLA1a7fTJyc7g@mail.gmail.com> (raw)
In-Reply-To: <35505.8950.qm@web26008.mail.ukl.yahoo.com>
References: <35505.8950.qm@web26008.mail.ukl.yahoo.com>
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 Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: stuart@stuartbishop.net, glynastill@yahoo.co.uk
Subject: Re: Reindexing primary keys without locking
In-Reply-To: <BANLkTim8drpsdqRrmDspsOLA1a7fTJyc7g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox