Converting standard_conforming_strings from off to on when importing Postgres dump

I've got a Postgres 8.1 instance which has standard_conforming_strings set to off (== handles slashes as escape characters). Recent Postgres versions have this setting on by default (for good reasons).

When importing a dump from 8.1 to 9.x, then I can set this setting to off in the 9.x DB but I don't want to, I'd prefer converting the dump into the proper format. This is a ~100GB dump.

How could I do that? Can I simply turn this setting 'on' in the 8.1 version? (Will that affect exporting existing data? Will/can that corrupt or modify existing data?)

What doesn't work: simply importing data produced by 8.1 to 9.x without setting standard_conforming_strings to off in the 9.x DB; the result is corrupted data.

Testing with a 8.1 instance, it doesn't look that there is a problem. See these 3 different examples:

Create data

SHOW standard_conforming_strings ;
 standard_conforming_strings 
 -----------------------------
  off

CREATE TABLE bla(t text);
INSERT INTO bla VALUES('abc''def');
INSERT INTO bla VALUES(E'abc\\def');

1. Dump of 8.1 data with 8.1 pg_dump

$ pg81/bin/pg_dump db

this produces (after removing the irrelevant stuff):

COPY bla (t) FROM stdin;
abc'def
abc\\def
\.

Here standard_conforming_strings does not matter because the COPY format is independant from it. When this COPY is executed, it will make no difference whether it's on or off.

1. Dump of 8.1 data with 8.1 pg_dump with --inserts option

$ pg81/bin/pg_dump --inserts db

So COPY is not used.
The dump now contains, for the interesting part:

INSERT INTO bla VALUES ('abc''def');
INSERT INTO bla VALUES (E'abc\\def');

Since the E'...' syntax is used, standard_conforming_strings is again irrelevant. These strings will be interpreted correctly, whether the setting is on or off, by any version of postgres>=8.1

3.Dump of 8.1 data with 9.1 pg_dump with --inserts option

$ pg91/bin/pg_dump --inserts db

Now the dump happens to contain this, somewhere at the start

SET standard_conforming_strings = off;

and then in the data section:

INSERT INTO bla VALUES ('abc''def');
INSERT INTO bla VALUES ('abc\\def');

So again it's being taken care of automatically, albeit differently than with 8.1's pg_dump, but this dump is going to be reloadable directly in any version of postgres>=8.1, without tweaking anything.

StackExchange Database Administrators Q#132758, answer score: 3