SEO

#1 (**permalink**) 04-10-2008, 05:30 AM

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

#2 (**permalink**) 04-10-2008, 05:30 AM

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.

#3 (**permalink**) 04-10-2008, 05:30 AM

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

COPY and partitioning