As I was moving my Drupal 6.x websites from an old server to a new one, I decided to make use of the newest PostgreSQL database system because over time I've notice quite a few things that got fixed and thus it is a lot better! (It is not as exciting as moving from 7.4 to 8.0 way back then, but still!)

Doing so, my Drupal site started showing WSOD (White Screens Of Death). Looking at the errors in my apache2 logs, I noticed that the error was in link with the cache. Looking closer, I could see that the cache (one of the only table doing so) makes use of the %b flag which means blob. It transforms the data into a blob which makes for smaller foot print in the database.

In version 8.4 of PostgreSQL, the flag was not enough. If the string included a backslash (\) character, then it was necessary to add an E character (Escape) to tell PostgreSQL that the string included escape characters:

E'\x61333a...'

This E'...' syntax was mandatory in my 8.x version, and porting everything over, I ran in two problems: the blob could not be read properly (unserialize() would fail on the data) and the use of the bytea_output = 'escape' would fail too:

In the first case, the write was proper, exactly what you'd expect.

In the second case, the read was proper, but the write would (probably) double escape everything!

In the end, the solution was to remove the E'...' and keep the bytea_output = 'escape'. That last one is actually mentioned on Drupal for version 7.x here:

https://www.drupal.org/node/926636#comment-3625380

The command to set the bytea_output value is:

SET bytea_output = 'escape';

By default the bytea_output is set to 'hex'. Also it is possible to define that parameter in the PostgreSQL configuration file:

/etc/postgresql/9.1/main/postgresql.conf

The default is hex as we can see in the default configuration file:

#bytea_output = 'hex'     # 'hex', 'escape'

Note that your cache is probably going to just work after you added this value. Personally, I changed the database.pgsql.inc file and added the line along the SET of the client_encoding. This makes more sense (I think) than changing the PostgreSQL defaults which may break other software:

[File: includes/database.pgsql.inc, Line: (about) 95]
  pg_query($connection, "SET client_encoding=\"UTF8\"");
  pg_query($connection, "SET bytea_output='escape'");

The E'...' was a fix that I added on my own. At this point that "fix" is completely removed. If you never had such a thing, then you probably don't need to worry about that part at all.

Still need to check if other tables make use of the bytea type...