I have a postgres database with a complex schema with many references.

Simple example of schema:

Deployments(id, name)
Releases(deployment-id, name, id)
VMs(id, deployment-id) 
VmInfo(vm-id, info)

I would like to extract from the db all details about Deployments with a name that starts with 'FOO-*'

Is there a easy way/tool to export all data related to these records without manually crafting a query to connect all keys? I am looking to something similar to the 'ON DELETE CASCADE' to follow all references for me.

In this example I would like to see all Deployments that follow my query, with all related releases, VMs AND VmInfo.

  • Jailer claims to be able to do that: jailer.sourceforge.net – a_horse_with_no_name Feb 28 '17 at 12:02
  • Did you work out a solution that you can share with us? – Matthis Kohli Feb 14 '19 at 7:01
  • 1
    Hey @MatthisKohli, we tackled the issue from the 'other side': instead of picking out some data to migrate in a new place, we cloned the full database and deleted the rest. Deletion query was done with explicit references as CASCADE was bringing troubles as well. The risk of having extra useless data was better than missing crucial information. – Michael Mar 15 '19 at 9:48
  • @Michael for example DELETE ALL PROJECTS WHERE ID !== record of interest ? So the record of interest will be the only one left? nice ^^ – Matthis Kohli Mar 15 '19 at 11:21
  • @MatthisKohli, correct. Here you can find more context about the real use case if you are interested github.com/cloudfoundry/bosh/issues/1601 and the query gist.github.com/michaelgrifalconi/… – Michael Mar 21 '19 at 12:48

Your Answer

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

Browse other questions tagged or ask your own question.