Обсуждение: Need help importing dump with bytea into db
Hi,
I have the strangest problem. Scenario: Server running postgresql 8.4.10, 32-bit Ubuntu, local machine running postgresql 9.1.4, 64-bit OSX Snow Leopard. I want to create a local copy of the database running on the server for development purposes, as the server database is ‘live’.
So, on my Mac I use pg_dump to create a dump from the server database. This database contains one table that has a bytea column which stores pictures (png,jpg). Examining the dump, I see this data is ‘escaped’ as octects for all ‘non-readable’ characters. Now, when I use psql (or pg_restore for that matter) to import the dump into my local database, the bytea data is not ‘unescaped’ properly. It appears as if the characters for the string representation of the actual bytes are returned.
To clarify, suppose the server contained the bytea ‘AB BC 10’hex, then my query from the local db will return ‘4142 4243 3130’hex, so basically what I must do is convert the bytes to their ASCII values, group them by 2 and convert that string back to the actual byte value (41=A, 42=B, so together I again have ‘AB’).
I noticed that if I try and restore the dump into a new database on the server this issue does not occur. I tried dumping as plain text, custom format, I tried using inserts instead of copy, I tried psql and pg_restore but it all failed.
What am I missing or doing wrong? Any help will be greatly appreciated!
Met vriendelijke groeten,
Ir. Philippe Dirkse
Project Manager Alten PTS Development Center
Tel: +31-(0)40-2563080
Mob: +31-(0)6-23401846
Philippe Dirkse <philippe.dirkse@alten.nl> writes:
> I have the strangest problem. Scenario: Server running postgresql 8.4.10, 32-bit Ubuntu, local machine running
postgresql9.1.4, 64-bit OSX Snow Leopard. I want to create a local copy of the database running on the server for
developmentpurposes, as the server database is 'live'.
> So, on my Mac I use pg_dump to create a dump from the server database. This database contains one table that has a
byteacolumn which stores pictures (png,jpg). Examining the dump, I see this data is 'escaped' as octects for all
'non-readable'characters. Now, when I use psql (or pg_restore for that matter) to import the dump into my local
database,the bytea data is not 'unescaped' properly. It appears as if the characters for the string representation of
theactual bytes are returned.
My first guess is that there's nothing actually wrong with the data, but
you're getting confused by the fact that 9.1 by default outputs bytea
data in hex rather than the traditional "escaped" format. See the
bytea_output configuration parameter.
regards, tom lane