0

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?

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.

  • I appreciate your answer. For now, option 1 and 2 seem viable, I will give them a try. I'm still in early development, I am a researcher for a company. – GRoutar May 29 at 9:49
  • I personally recommend option 1, i have restored dumps of hundreds of Gigs in minutes @Khabz – Imanol Y. May 29 at 10:11
  • Thanks, I will try it out. COPY_FROM seems the most suitable solution. My doubt is: is the command personalizable enough to be able to extract data out of a list of jsons (mongo) into a table? – GRoutar May 29 at 10:46
  • Since I want to export a mongo db into a postgres' I wonder if it's worth converting the jsons into a readable stream and only then copy them into postgres – GRoutar May 29 at 11:15
  • Also, I created a dummy database and tried copying content from mongo into a table that references another empty table and it threw the violates foreign key constraint - not present in table. How exactly does it avoid any foreign key conflict? – GRoutar May 29 at 11:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.