0

We have configured postgres_fdw to move data from a DB to remote DB. It works fine but the large object field's data in source db not getting copied to remote DB.

This is the statement used to copy:

INSERT INTO archive_remote.target_db(id, oid_field)
SELECT a.id, a.oid_field
FROM appl.source_db a
ON CONFLICT DO NOTHING;

Both databases are Postgres 9.6.

What is the right way to fix this?

| improve this question | | | | |
3

I assume that the oid in question is the Oid of a large object, and you are wondering why the large object isn't copied when the oid field is copied.

Large objects cannot be copied via postgres_fdw. You'd have to use the large object API (or pg_dump) to move them from one database to the other. If the Oids are already taken on the second database, you'll have to use different ones and update the table accordingly.

Large objects are cumbersome to handle.

| improve this answer | | | | |
  • If used lo_get as shown below? INSERT INTO archive_remote.target_db(id, oid_field) SELECT a.id, lo_get(a.oid_field) FROM appl.source_db a ON CONFLICT DO NOTHING; – Valsaraj Viswanathan May 27 '19 at 8:51
  • Actually as per the query I am executing above query from local which reads lo value in local and inserts into similar table on the remote DB. Without lo_get, only the large object id is saved. But now we are using lo_get() which will persist the actual large object value, right? – Valsaraj Viswanathan May 27 '19 at 13:10
  • I see, sorry for the confusion. Yes, that will work, as long as oid_field on the remote database is of type bytea. – Laurenz Albe May 27 '19 at 13:57
  • In our case, the oid_field on the remote database is of type oid. – Valsaraj Viswanathan May 28 '19 at 5:41
  • Then it won't work, because you cannot insert a bytea into an oid. The large object API is not accessible via foreign data wrapper. You'll have to think of a different way of transferring your large objects, e.g. using client code. – Laurenz Albe May 28 '19 at 6:06

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