Partition existing PostgreSQL table

0
votes

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.

1
postgresqlpartitioning
Offtopic, Not a programming question. This is more db administration - try the DBA site. - Marc B
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

1 Answers

1
votes

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.