Nash
Nash

Reputation: 1073

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

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.

Upvotes: 10

Views: 20951

Answers (3)

emi
emi

Reputation: 3070

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.

Upvotes: 6

janfoeh
janfoeh

Reputation: 10328

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'\'.

Upvotes: 9

zessx
zessx

Reputation: 68790

Unfortunately, PostgreSQL 9 backups are not backward compatible, and there are no options to enable this compatibility. I've been stuck on this problem for hours.

You'll need to figure out which statements are not compatible, and find their equivalent (if there's one) in PostgreSQL 8.

Another option, the better one, would be to upgrade your v8 to the latest v9.

Upvotes: 3

Related Questions