Re: Table inheritance and partitioning

On Wed, 2009-05-27 at 15:52 -0500, Kevin Barnard wrote:
> I am making the move to partition a table. I am trying to figure out
> the best way to migrate data to the partitions. I would prefer to not
> have down time. Does anybody have advice to give on this?

One strategy is to create a trigger on the master table that sends the
records to the appropriate child table instead.

See this presentation from Robert Treat:
http://www.slideshare.net/xzilla/postgresql-partitioning-pgcon-2007-presentation

When that starts working, you can just slowly move records by doing an
INSERT to the child tables and a DELETE from the master in the same
transaction (probably in big batches).

> Is there
> any easy way to determine what records are in the master table and
> which ones are in child tables? I can think of a few migration ideas
> doing this.

If you do "SELECT ONLY ..." it doesn't descend into the child tables.

Regards,
Jeff Davis