CODEWITHSUNDEEP
postgresqlforeign-keysdatabase-restore
raav6
raav6

Reputation: 131

Export just foreign keys in postgresql

I have a large database with 700+ tables and a size of approx. 23GB and I'm trying to export this database to a test environment, the thing is when I do the restore it doesn't include any of the foreign key constraint whatsoever, at the moment I can't figure out why exactly but it seems that the data itself is right and all tables, PK's, functions and views are restored as well, so the only thing left are the foreign keys, the postgres log doesn't show errors.

I've been googling for several days about this and no luck. And I'm pretty sure this isn't the correct approach but is all I've got right now so I'll give it a shot just for testing purposes, you will never know.

So plain and simple, is there any way that I can export only foreign keys to a script and then restore them from this script?

Thanks in advance.

@Craig: Sure Craig here are the details:

Export Command: I'm using Adepmpiere Adempiere Wiki which inlude its own DBExport and DBRestore script.
DBExport script has the following command: 

pg_dump -h 127.0.0.1 -p 5432 --no-owner -U adempiere adempiere > /home/adempiere/adempiere.sql

DBRestore has this lines: 

psql -h 127.0.0.1 -p 5432 -d adempiere -U adempiere -f /home/adempiere/adempiere.sql

I was able to restore this script on a virtual machine that I have on my laptop and it restored everything FK´s included, but no luck on test machine.

Upvotes: 1

Views: 2334

Answers (1)

raav6
raav6

Reputation: 131

Well, here´s the solution I came with

I made a Backup of the structure only with the following command pg_dump -h 127.0.0.1 -p 5432 --no-owner -U -s adempiere adempiere > /home/adempiere/adempiere_bkp.sql

The parameter -s is the key here

To restore just execute the following comand on the test machine

psql -h 127.0.0.1 -p -d adempiere -U adempiere -f /{ PATH TO FILE adempiere_bkp.sql }

And voila everything is complete again!.

Hope it helps someone else. Thanks for the help.

Upvotes: 3

Related Questions