PostgreSQL - Error: "invalid input syntax for type bytea"

Question

I am very new to PostgreSQL so I apologize if the question is elementary.

During PostgreSQL database restore, from sql file, I am getting an error "invalid input syntax for type bytea" and I believe the data is not copied to the table, i.e. the table is empty.

This is the error message:

    2015-02-20 08:56:14 EST ERROR:  invalid input syntax for type bytea
    2015-02-20 08:56:14 EST CONTEXT:  COPY ir_ui_menu, line 166, column web_icon_data: "\x6956424f5277304b47676f414141414e5355684555674141414751414141426b43414d41414142485047566d4141414143..."
2015-02-20 08:56:14 EST STATEMENT:  COPY ir_ui_menu (id, parent_id, name, icon, create_uid, create_date, write_date, write_uid, web_icon_data, web_icon, sequence, web_icon_hover, web_icon_hover_data) FROM stdin;

The database backup dump is created like this:

pg_dump -U user_name database_name -f backup_file.sql

The database restore is done like this:

psql -U user_name -d destination_db -f backup_file.sql

Source database (to get backup from) is PostgreSQL version 9.1.15 on one server and destination (to restore to) database is PostgreSQL 8.3.4, on another server.

Is there any way to resolve this issue? Thanks in advance for your help.

Answer 1

Restoring a dump from a newer version of Postgres onto an older is quite often problematic, and there is no automated way that I am aware of. Making this work will most likely require editing the dump file manually.

Specifically, Postgres 9.0 changed the handling of escape strings used with bytea: previous versions treated \ in regular string literals such as '\' as escape characters, whereas newer versions use the escape string syntax E'\'.

Answer 2

If you have access to your 9.X server configuration, you can change bytea_output variable to 'escape' on postgresql.conf:

bytea_output = 'escape'                 # hex, escape

Then restart Postgres 9.X server and dump the database as you do it normally. Finally, restore it on the 8.X server.

You can also change client connection variable just for the database dump action, but it may be outside scope.

Hope it helps.