1

I have a huge table (~500M rows), which I did not partition at the time of loading the data. If I create the partitions now, do I need to manually move the data from the master table to the child tables? Are there any better options.

Improve this question
2
  • Offtopic, Not a programming question. This is more db administration - try the DBA site. Commented Sep 2, 2016 at 21:21
  • 1
    Short answer: yes. You might want to look for an extension to help, like pg_partman, but Postgres itself doesn't even strictly know what a partition is. Commented Sep 2, 2016 at 21:24

1 Answer 1

Reset to default
1

It is an old question, but what I have done and planned is to:

  1. rename the big table
  2. create a copy of the original table with include all (to get tablestructure, indexes, ect from the original table),
  3. partition the new table (I cant alter the table now by list (dont know why). c) Create partition tables by the list (example client_id) for each distinct value in the original table
  4. migrate the old table (with values) in the new partitioned table.
  5. Check data is in the new partitioned tables. If ok then
  6. delete old table.
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

How long did it take for step d? What process did you follow for the migration?
It is a while back now. But as I remember it, I started around 19.00 or 20.00 in the evening, and then checked every 10 minutes first, then every 30 minutes. It was still updating when I stopped monitoring around 22.00 or 23.00. Then next day it was finished. So somwhere between 3 and 12 hours. I used a insert into...
how did u delete the old table. when u created the copy of the old table, the sequences, inxdexes etc are copied but he owner is still the old table right?. when i tried to drop the old table it failed saying there are other tables depending on the sequences from the old table. how did u resolve this?
It looks like you have dependencies on the old table. You have to go through those. Also I have noticed, what when you enter the table, there is a session open, so first task is to close all sessions, so the table is free.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.