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.

let_there_be_light
  • 837
  • 3
  • 9
  • 15
  • Offtopic, Not a programming question. This is more db administration - try the DBA site. – Marc B Sep 02 '16 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. – IMSoP Sep 02 '16 at 21:24

1 Answers1

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.
Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
Troels
  • 342
  • 3
  • 16
  • How long did it take for step d? What process did you follow for the migration? – jeznag Apr 27 '20 at 08:37
  • 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... – Troels May 02 '20 at 08:35
  • 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? – Adithya Sama May 10 '21 at 07:43
  • 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. – Troels May 11 '21 at 08:12