Hi,
Commit 45762084 [1] forced standard_conforming_strings to always be ON in 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. Discussion: https://postgr.es/m/[email protected] [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=45762084545ec14dbbe66ace1d69d7e89f8978ac Thanks, Baji Shaik AWS RDS
v1-0001-Doc-standard_conforming_strings-dump-restore-incompatibility.patch
Description: Binary data
