Re: managing table partitions automatically

On Thu, Nov 13, 2008 at 08:42:28AM -0600, Ertel, Steve wrote:
>    *
>    Still haven't heard from anyone.
>
>    I could really use your help.
>
>    Do you have any suggestions?
>
>
>    Thanks,
>    Steve
>
>      ----------------------------------------------------------------------
>
>    From: pgsql-novice-owner@postgresql.org
>    [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Ertel, Steve
>    Sent: Wednesday, November 12, 2008 12:48 PM
>    To: pgsql-novice@postgresql.org
>    Subject: [NOVICE] managing table partitions automatically
>    Hello All,
>
>    I am beginning to partition some database tables and need some help
>    thinking things through.  After evaluating my data, it appears that the
>    best partition would be based on year and month.  I was planning to create
>    my tables in the format of tablename_yyyy_mm.
>
>    I planned to create a parent table and child tables (similar to the
>    example at
>    http://www.postgresql.org/docs/8.1/static/ddl-partitioning.html).  I would
>    create the child tables for the range of my data and maybe even a few
>    months in advance.  I have all of the insert, update, and delete rules
>    worked out to manage the data.  The data will be routed to tables based on
>    a date that is passed to the table (e.g. start_time).  The date can be in
>    the future or in the past.
>
>    The problem is that the databases are running on remote servers and I will
>    not have access to them.  I need to find a way to automatically create the
>    child tables, indexes, and rules.  Any suggestions would be helpful.  I am
>    stuck.
>
>    Thanks,
>    Steve
>

Your best bet is a really well-tested script to create the new
partitions periodically. PostgreSQL won't do it for you.

- Josh / eggyknap