How to migrate a PostgreSQL table to partition table referenced by foreign keys

Question

How would you migrate a table, referenced by a foreign key, to a partition table in PostgreSQL?

If I'm reading the docs correctly, version 13 now supports partition tables referenced by foreign keys, whereas in version 11, it explicitly mentioned foreign key references were not supported.

However, given previous solutions on how to migrate to a partition table, it's unclear how foreign key references would be updated.

For example, say I have two tables, Library and Book, where Book has a foreign key column called library_id pointing to a record in Library.

I haven't tested this, but what would be the caveat behind this strategy:

1. Rename table `Library` to `Library_old`.
2. Create partition table called `Library`.
3. Create child tables or use rule to automatically create child tables as needed.
4. Insert all data from `Library_old` into `Library`.
5. Rename column `Book.library_id` (currently pointing to `Library_old`) to `Book.library_id_old`.
5. Create a new column `Book.library_id` pointing to `Library`.
6. Iterate over each `library_id_old` value and update to `library_id`.
7. Delete column `Book.library_id_old`.
8. Delete `Library_old`.

Would this work, or am I missing anything? Is there a better migration plan?

Answer 1

I had a similar task and the way I solved it was the following (with your example):

1. drop foreign key of Book table
2. rename table Library to Library_old
3. create new primary key which should include the column used in partitioning
4. add foreign key to Book table to reference the new, composite primary key
5. create new partitioned table Library with LIKE Library_old INCLUDING ALL
6. create partitions for new Library table (create table Library_partition1 of Library for values from .. to ..
7. drop table Library_old

This of course is a fairly simplified version of my implementation, without mentioning the possible index/sequence duplicates etc.

As you can see my case I used range partitioning with the help of a datetime column.

Hope it helps, cheers!