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?

closed as unclear what you're asking by Philᵀᴹ, kevinsky, John Eisbrener, Colin 't Hart, hot2use May 25 at 7:37

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the 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.

  • 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 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 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 at 13:57
  • In our case, the oid_field on the remote database is of type oid. – Valsaraj Viswanathan May 28 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 at 6:06

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