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.
Best Answer
Testing with a 8.1 instance, it doesn't look that there is a problem. See these 3 different examples:
Create data
1. Dump of 8.1 data with 8.1 pg_dump
this produces (after removing the irrelevant stuff):
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.2. Dump of 8.1 data with 8.1 pg_dump with --inserts option
So COPY is not used. The dump now contains, for the interesting part:
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.13.Dump of 8.1 data with 9.1 pg_dump with --inserts option
Now the dump happens to contain this, somewhere at the start
and then in the data section:
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.