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
2
  • What DB type is the remote DB, if it's not Postgres? What data type is the oid_field column on the foreign DB? Is it an actual OID (postgresql.org/docs/8.1/datatype-oid.html ) in the source table? Need more information Commented May 24, 2019 at 13:29
  • added details in question. Commented May 27, 2019 at 8:54

1 Answer 1

Reset to default
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
6
  • 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; Commented May 27, 2019 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? Commented May 27, 2019 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. Commented May 27, 2019 at 13:57
  • In our case, the oid_field on the remote database is of type oid. Commented May 28, 2019 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. Commented May 28, 2019 at 6:06

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.