On 04/25/2011 10:10 AM, Vick Khera wrote:
Basically, you create your partitions and set up the necessary triggers you want (I re-write the app to insert directly into the correct partition). Then all new data starts going into the partitions. Next, write a program that loops over the current master table, and moves the data into each partition some small hunk at a time, in a transaction. This can take a long time. For us, it took about 7 days to move O(100m) rows. Then, when you're done, truncate the master table, and enforce that no new data is allowed to be inserted into it.
Vick's presentation at http://cdn.mailermailer.com/documents/PGCon2008TablePartitioningCaseStudy.pdf is still one of the best case studies of how to do this sort of migration around. I was inspired by several of the ideas there when doing the partitioning chapter of my book, which is the only place I'm aware of covering this in even more detail than his case study.
Cedric's idea for how to do this even more aggressively (multiple workers) is what you want if this is a one-time operation you're taking the system down for. In other situations, the gradual migration strategy Vick is suggesting is more appropriate.
Some of the other ideas suggested in this thread won't work at all, so be careful who you listen to here. You can't leave copies of the data in the parent and put it into the child partition without all sorts of potential downsides. And you really, really want to do this as a proper database transaction, which is easiest to express using INSERT instead of COPY. If any step of the migration goes wrong, being able to do ROLLBACK and undo the recent bad steps is vital.
-- Greg Smith 2ndQuadrant US greg@xxxxxxxxxxxxxxx Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
