I have a Ubuntu 12.04 Server with PostgreSQL 9.1 installed.
I am trying to setup Partitioning on a existing Postgres DB(say 'xyz') which has very huge tables and some have foreign keys set on them.
This is what i did until now
- I followed http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html to create Partition triggers.
- Tested the Triggers, Each data i inserted got placed in the right partition.
- Took take a
pg_dumpof all the tables individually from DB 'xyz'.
--column-insertswas enabled for each dump.
- Created a new DB 'xyz_new'.
- Imported Partition Triggers and Funtions i created into it.
- Started importing tables with ones which were free of foreign keys.
- Then i imported the ones with foreign keys, in a way that the tables referred to by the foreign keys are already imported.
But while doing the last step i get a error like
ERROR: insert or update on table "aaaaa" violates foreign key constraint "bbbbb_id_refs_id_5b63fd27"
DETAIL: Key (bbbbb_id)=(6817) is not present in table "bbbbb".
As a Result, no import happened on the foreign key based tables.
If i do a Select query on bbbbb for id 6817, the record does shows up.
Then after some googling i found this, http://www.postgresql.org/docs/9.1/static/ddl-inherit.html
As per the Caveats mentioned in the Wiki, the Inheritance doesnt work with the foreign key.
(That was a very bad news for me :( i had spent lots of time to create the triggers.)
Now, I would like to know if there is a workaround to manage Partitions on Postgres with foreign keys?
Is there any other way where i can handle huge tables with foreign keys?