Assign foreign keys only after populating database

Question

I am porting a MongoDB database over to a PostgreSQL (relational) database. I would like to perform this task concurrently, and I have developed code with parallelism in mind, but dependencies between tables (foreign keys) are not allowing it. This happens when a row (containing a foreign key) is added, but the table it points to hasn't had that record (primary key) added, throwing the following error:

insert or update on table "observernodeoccurrence" violates foreign key constraint "observernodeoccurrence_canonical_id_fkey"
DETAIL:  Key (canonical_id)=(ba956ef88bcc366fa7d1d5fb696fe98d) is not present in table "canonical".

This is logical, since the record being added, which contains a FK, is pointing to a record from another table which hasn't been added yet.

Possible solution: I thought about a solution consisting of declaring the foreign keys as regular TEXT attributes, containing no dependencies to other tables. As soon as the port was completed, those attributes would be converted back to foreign keys. This way, every foreign key would have a record in the destiny table.

Is there a way to achieve this in Postgresql? If not, is there any other thing I can try to achieve parallelism in populating the database?

Answer 1

Option 1: Use COPY from a valid dump of the tables in order to avoid any foreign key conflict (Copy is the fastest way i've found to load bulk data)

Option 2: Create the schema without foreign keys, load the dumps, and recreate the foreign keys after that: ALTER TABLE table2 DROP CONSTRAINT fkey; ALTER TABLE table2 ADD CONSTRAINT fkey; FOREIGN KEY (column_name) REFERENCES table1 (column_name);

Option 3: If you are in a production environment you can try a replication solution, check out how to create a stream replica from mongo to postgres.

Option 4: If you are just trying to migrate some subset or data and keep it sync you can use a connector.

For sure people here can help you with other approaches.