CODEWITHSUNDEEP
postgresqlblobgreenplum
XiangZzz
XiangZzz

Reputation: 92

How to dump the large object data from Postgres 9.4, and then import it to Postgres8.x?

I used pg_dump to export the data including large objects (LO) from Postgres 9.4 like this:

$ pg_dump fhir -O -b > fhir.sql

The LO statements in my generated fhir.sql are like this:

SET standard_conforming_strings = on;
SELECT pg_catalog.lowrite(0, '\x1f8b0800000000000000a5903b6ec3300c86efa2d9b1dad5a728daa2');

When I executed \i fhir.sql in my Postgres8.2, I got this error:

ERROR:  invalid input syntax for type bytea

When I SET standard_conforming_strings = off, the data was inserted, but I got a warning, and the data in my pg_largeobject table was:

14 |      0 | \0378b0800000000000000a5903b6ec3300c86efa2d9b1dad5a728daa2

The original \x1f was changed to \037, and I had a test, it is no longer my original zip file…

how can I fix this?

UPDATE:

I inserted the same original data into Greenplum(based on Postgresql8.2) with a Hibernate program, and then used pg_dump to export it, its format was like this:

SELECT pg_catalog.lowrite(0, '\\037\\213\\010\\000\\000\\000\\000\\000\\000\\000\\245\\220;n\\3030\\014')

Upvotes: 2

Views: 9659

Answers (2)

XiangZzz
XiangZzz

Reputation: 92

UPDATA

I found out an easier way to work: just use pg_dump -b -Fc to export data including LO into a custom file, later use pg_restore of the same version of pg_dump which you used to export data to import the custom file data into greenplum.

Scripts:

$ pg_dump fhir -O -a -Fc  -f fhir.dump
$ pg_restore -h mdw -d fhir -U gpadmin -a fhir.dump > errors.log 2>&1

A point fact I've missed is that lo_export exports binary data which is able to be imported into greenplum perfectly.

My solution(for my situation):

  1. export the plain data(using pg_dump,exclude LO) and LO data(using lo_export) from Postgres9.4 separately.
  2. import the plain data dump into Greenplum
  3. import the LO data with lo_import, this will generate some new oid(lo_import with oid started from Postgres8.4), meanwhile update the corresponding oid of the referenced table with these new oid.

Sample scripts:

  1. export plain data from Postgres9.4

     $ pg_dump fhir -O -a -n public -f fhir.dmp

  2. export LO from some table containing LO data, naming the exported files with the original oids

    SELECT lo_export(res_text, '/usr/local/pgsql/export/res_lo/'||res_text) FROM hfj_resource;

  3. import plain data into Greenplum

    \i fhir.dmp

  4. create a function in Greenplum to import LO and update referenced oids

    CREATE FUNCTION import_lo(tab_name text, lo_path text) RETURNS void AS $$

 DECLARE

    res record;

    new_oid oid;

 BEGIN

    FOR res in EXECUTE 'select res_text from '||$1 LOOP

        new_oid := lo_import($2||'/'||res.res_text);

        RAISE NOTICE 'res_text from % to %', res.res_text, new_oid;

        EXECUTE 'update '||$1||' set res_text='||new_oid||'where res_text='||res.res_text;

    END LOOP;

    RAISE NOTICE 'import large object into % finished .....', $1;

 END;

 $$ LANGUAGE plpgsql;

  5. import LO

    SELECT import_lo('hfj_resource', '/home/gpadmin/export/res_lo');

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246153

The problem is that the dump uses the function pg_catalog.lowrite(integer, bytea) to create the large object, and the default syntax how bytea literals are represented in PostgreSQL has changed with version 9.0.

There is the parameter bytea_output which can be set to escape to output bytea in the old format with later PostgreSQL versions. Alas, pg_dump doesn't respect that parameter when creating dumps, it always uses the “new” hex format.

The result is that a dump containing large objects from a PostgreSQL version of 9.0 or later cannot be restored into a pre-9.0 database.

You'll have to transfer these large objects in some other way, probably by writing a migration program.

You could propose (on the pgsql-hackers mailing list) an option to pg_dump that allows to set bytea_escape for the dump, but you might meet resistance, as restoring a dump from a later PostgreSQL version to an older one is not supported.

Upvotes: 1

Related Questions