Migrating from PostgreSQL 8.3 to 9.0 and beyond - Byte Array perspective

My Nerd migos,

Its bn quite sometime since my last post.

I want to share my agonies (and help u avoid the same) when migrating applications from Postgres 8.3 (or below) to 9.0 and beyond. If you had/have tables with bytea then they will/may JUST stop working.

Some guy(s) with infinite wisdom decided to change the representation of byte array from the default (escape) to hex !!! I googled my head silly until i found a solution at stackoverflow.com (forgot the thread sorry!!!)

Ok straight to the solution. Export the 8.3 db (i prefer pg_dump --inserts dbname > /path/to/dumpfile.sql) then import (just use psql or ur favorite tool to run the script created by the pg_dump command).

The next thing is to change a configuration flag in postgresql.conf. Change bytea_output to 'escape' instead of the default 'hex'. restart the server and hug yourself.

"At least the postgres guy with infinite wisdom never forgot to add this flag for backward compatibility"

Plz dont ask me what is a bytea datatype. If u dont know it means u dont have it in ur DB so relax

Hope this helps