Skip site navigation (1) Skip section navigation (2)

Re: Question on moving data to new partitions

From: Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
To: "Benjamin Krajmalnik" <kraj(at)illumen(dot)com>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Question on moving data to new partitions
Date: 2010-01-14 08:57:00
Message-ID: 873a29awtf.fsf@hi-media-techno.com (view raw or flat)
Thread:
Lists: pgsql-admin
"Benjamin Krajmalnik" <kraj(at)illumen(dot)com> writes:
> As a result, I have decided to partition the table and add to each record a partition id, which can be used to route it to the correct partition.
>
> Presently, all of the records reside on what will ultimately become the parent partition.
>
> What would be the best way of moving the data to the pertinent
> partitions?

What I use to do is to rename the current table partition_201001, say,
with a CHECK constraint date < 2010-01-01. In case of date based ranges
of course.

Then create the new parent table, empty, and set up the inheritance and
trigger. Then create some more future child tables, and commit.

New data is being routed, old data all packed. You can reshuffle the
archive like table later on, if needed.

Also, as noted down-thread, avoid rules, prefer triggers. One of the
reasons is locking behavior, where drop partition when using rules will
lock against running queries against parent table.

Regards,
-- 
dim

In response to

pgsql-admin by date

Next:From: Renato OliveiraDate: 2010-01-14 14:33:52
Subject: PITR online backups Setup
Previous:From: Daniel J. SummersDate: 2010-01-14 04:44:46
Subject: Re: Very simple password for DB administrator

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group