[PATCH] Doc: document standard_conforming_strings dump/restore incompatibility

Started by Baji Shaik13 days ago1 messageshackers

baji.pgdev@gmail.com

13 days ago

Hi,

PostgreSQL 19. The release notes mention this change, but neither the

pg_dump/pg_restore reference pages nor the migration section note the

implications for restoring older dump files.

Dump files produced by older pg_dump versions from servers that had

standard_conforming_strings = off contain "SET standard_conforming_strings

= off" in the output. When restored into v19:

1/ Default (COPY format): pg_restore continues past the SET error and

data restores correctly, but exits with non-zero status. With

--exit-on-error, the restore aborts entirely.

2/ --inserts format: string literals containing backslashes may not be

restored correctly, since the escaping conventions differ between

standard_conforming_strings = off (source) and on (target).

The workaround is to restore into a pre-v19 server first, then produce

a fresh dump using v19 pg_dump (which forces scs=on in the source

session).

I verified this by dumping from PG18 with standard_conforming_strings =

off and restoring into PG19devel. The COPY case works because COPY has

its own escape rules independent of standard_conforming_strings. The

--inserts case results in double backslashes in the restored data.

The attached patch adds notes to:

- pg_dump reference page (Notes section)

- pg_restore reference page (Notes section)

- release-19 migration section

The patch applies cleanly on current HEAD and compiles without errors.

Thanks,
Baji Shaik
AWS RDS