v1-0001-Doc-standard_conforming_strings-dump-restore-incompatibility.patch

From 36293bb2b2449256ef5a4ba874cc786bcf1b699a Mon Sep 17 00:00:00 2001
From: Baji Shaik <baji.pgdev@gmail.com>
Date: Mon, 11 May 2026 07:59:32 -0500
Subject: [PATCH] Doc: document standard_conforming_strings dump/restore
 incompatibility

Commit 45762084 forced standard_conforming_strings to always be ON
starting in PostgreSQL 19. This means dump files produced by older
pg_dump versions from servers that had standard_conforming_strings
set to off will produce errors when restored into v19 or later.

While COPY-format dumps restore data correctly despite the SET error,
dumps produced with --inserts may have incorrectly escaped string
literals, resulting in incorrect data being restored.

Add notes to the pg_dump and pg_restore reference pages, and to the
release-19 migration section, documenting this incompatibility and
the workaround (restore into a pre-v19 server first, then re-dump).

Discussion: https://postgr.es/m/3279216.1767072538@sss.pgh.pa.us
---
 doc/src/sgml/ref/pg_dump.sgml    | 16 ++++++++++++++++
 doc/src/sgml/ref/pg_restore.sgml | 17 +++++++++++++++++
 doc/src/sgml/release-19.sgml     |  1 +
 3 files changed, 34 insertions(+)

diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index ae1bc14..3d2581a 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -1732,6 +1732,22 @@ CREATE DATABASE foo WITH TEMPLATE template0;
    reserved-word lists in different <productname>PostgreSQL</productname> versions.
   </para>
 
+  <para>
+   Beginning in <productname>PostgreSQL</productname> 19,
+   <varname>standard_conforming_strings</varname> is always
+   <literal>on</literal> and cannot be set to <literal>off</literal>.
+   When dumping from older servers that have
+   <varname>standard_conforming_strings</varname> set to
+   <literal>off</literal>, a version 19 or later
+   <application>pg_dump</application> will force the setting to
+   <literal>on</literal> during the dump session, ensuring that the
+   output is compatible with version 19 and later.  However, dump files
+   previously produced by older versions of <application>pg_dump</application>
+   while <varname>standard_conforming_strings</varname> was
+   <literal>off</literal> may not restore cleanly into version 19 or later;
+   see the <xref linkend="app-pgrestore"/> documentation for details.
+  </para>
+
   <para>
    When dumping logical replication subscriptions,
    <application>pg_dump</application> will generate <command>CREATE
diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index 5e77ddd..d419866 100644
--- a/doc/src/sgml/ref/pg_restore.sgml
+++ b/doc/src/sgml/ref/pg_restore.sgml
@@ -1200,6 +1200,23 @@ CREATE DATABASE foo WITH TEMPLATE template0;
 </programlisting>
   </para>
 
+  <para>
+   Dump files produced by <application>pg_dump</application> from servers
+   that had <varname>standard_conforming_strings</varname> set to
+   <literal>off</literal> contain
+   <literal>SET standard_conforming_strings = off</literal>, which will
+   produce an error when restored into <productname>PostgreSQL</productname>
+   19 or later.  By default, <application>pg_restore</application> will
+   continue past this error and restore data correctly when using
+   <command>COPY</command> format, but will exit with a non-zero status.
+   If <option>--exit-on-error</option> is specified, the restore will abort.
+   Dumps produced with <option>--inserts</option> may have incorrectly
+   escaped string literals, resulting in incorrect data being restored.
+   To safely restore such dump files, first restore into a server running
+   a version prior to 19, then produce a fresh dump using a version 19 or
+   later <application>pg_dump</application>.
+  </para>
+
   <para>
    The limitations of <application>pg_restore</application> are detailed below.
 
diff --git a/doc/src/sgml/release-19.sgml b/doc/src/sgml/release-19.sgml
index f265ac0..c769672 100644
--- a/doc/src/sgml/release-19.sgml
+++ b/doc/src/sgml/release-19.sgml
@@ -110,6 +110,7 @@ Force standard_conforming_strings to always be "on" in the database server (Tom
 
 <para>
 Server variable escape_string_warning has been removed as unnecessary.  Client applications still support "standard_conforming_strings = off" for compatibility with old servers.
+Dump files previously produced with standard_conforming_strings set to off may not restore correctly into version 19; in particular, dumps made with <option>--inserts</option> may have incorrectly escaped string literals.  To restore such files, first load them into a pre-version-19 server and then produce a fresh dump.
 </para>
 </listitem>
 
-- 
2.50.1 (Apple Git-155)