2

I am trying to copy the data from one database to another database. Both the source and target tables have 2 geometry columns -- a polygon field and a point field. There are NOT NULL constraints on both of these columns.

My ogr2ogr command looks like this

ogr2ogr -f "PostgreSQL" -append -update -sql "select polygon_geom, centre_geom, id, category from sourceschema.table1" PG:"dbname=db1 host=host1 port=5432 user=user password=password" PG:"dbname=db2 host=host2 port=5432 user=user password=password" -nln targetschema.table1

However I can't get ogr to recognise the 2 geometry fields. The error i get is:

ERROR 1: ERROR:  null value in column "geom" violates not-null constraint

How can I copy this data across my 2 databases properly? Even if I export just one geometry column, the NOT NULL constraint on the other column will still prevent me from inserting data. Or do I just lift the not null constraints for the time I'm transferring data across?

EDIT: The source and target tables are not exact copies of one another - the target table has more columns than the source.

Improve this question
3
  • 1
    another alternative is to use \copy piped between the 2 DB endpoint.com/blog/2013/11/21/copying-rows-between-postgresql
    – JGH
    Commented Jun 25, 2020 at 11:43
  • @JGH thank you- unfortunately my tables are not exactly the same. I've edited the post to reflect this information. I know ogr2ogr has a -fieldmap option which is why I initially thought it would be the best choice.
    – thesunnyscientist
    Commented Jun 25, 2020 at 13:31
  • 1
    It doesn't matter, you can specify the columns on both sides \copy (select a,b,c from table1) TO ... ... \copy table2(d,e,f) FROM ... The names do not have to be the same and you don't have to export/import all columns. The default value will be used for the omitted columns
    – JGH
    Commented Jun 25, 2020 at 14:36

1 Answer 1

Reset to default
1

Dont know much about ogr2ogr, but another options is to use FDW. With that, you can link data from one database directly to another. Then you can access these data as if they are in target database, without man in the middle software. This is much cleaner, because it uses plain sql, without extra hassle of ogr2ogr. It is more complicated for first setup (defining server, make foreign tables, etc) but then it is a breeze. There are some references:

Improve this answer
2
  • I've had a read into FDW. It seems that it is most useful when wanting to routinely access a remote server. In my case, i just wish to migrate data from one database to the other so i can switch off the first server. Would FDW still be ok for this purpose?
    – thesunnyscientist
    Commented Jun 25, 2020 at 13:28
  • Yes, it will. Just remove any defined FDW objects after source database shut down. There are also another options. For example DBeaver has pretty good import/export capabilities. It can transefer data between two database connections. We use that pretty often. Also pg_dump should work for that.
    – DavidP
    Commented Jun 26, 2020 at 5:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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