Thread: COPY and partitioning

COPY and partitioning

From
Colton A Smith
Date:
Hi

   I understand v8.1 implements table partitioning.  Very exciting!

   I have a big table (30 G) that is a real headache.  I'd like to
partition it.  My plan is the following: (1) use pg_dump -t to
dump the data; (2) create a partitioning schema with check constraints;
and (3) repopulate using the COPY utility.

   My question: does the COPY utility consult check constraints
before populating tables?

   Thanks as always.

Re: COPY and partitioning

From
Guido Barosio
Date:
Thinking about dumping the schema, rewriting that structure with your partitioning needs, and after copy the data.
 
Why not?
 
Just 2 cents, never done it.
 
to dump a schema:
pg_dump -s dbname > dbname.schema 
 
Then just restore the data, it should work I guess.
 
Best regards,
Guido

 
On 11/17/05, Colton A Smith <smith@cs.utk.edu> wrote:
Hi

  I understand v8.1 implements table partitioning.  Very exciting!

  I have a big table (30 G) that is a real headache.  I'd like to
partition it.  My plan is the following: (1) use pg_dump -t to
dump the data; (2) create a partitioning schema with check constraints;
and (3) repopulate using the COPY utility.

  My question: does the COPY utility consult check constraints
before populating tables?

  Thanks as always.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend



--
"Adopting the position that you are smarter than an automatic
optimization algorithm is generally a good way to achieve less
performance, not more" - Tom Lane.

Re: COPY and partitioning

From
Simon Riggs
Date:
On Thu, 2005-11-17 at 09:55 -0500, Colton A Smith wrote:

>    My question: does the COPY utility consult check constraints
> before populating tables?

No.

I'd dump it piece by piece using a WHERE clause exactly the same as the
constraints you will use, then reload using COPY into the partitions.

Best Regards, Simon Riggs