Thank you all for the comments!
On 2024-10-09 15:15, Yugo Nagata wrote:
On Tue, 8 Oct 2024 21:37:38 -0700
"David G. Johnston" <david.g.johns...@gmail.com> wrote:
On Tuesday, October 8, 2024, Tom Lane <t...@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johns...@gmail.com> writes:
> > On Tuesday, October 8, 2024, Yugo Nagata <nag...@sraoss.co.jp> wrote:
> >> On Wed, 09 Oct 2024 11:10:37 +0900
> >> Shinya Kato <shinya11.k...@oss.nttdata.com> wrote:
> >>> When SQL scripts created with pg_dump/pg_dumpall/pg_restore are
> executed
> >>> in psql with AUTOCOMMIT turned off, they will not succeed in many
> cases.
>
> > Agreed. If we aren’t already outputting psql-only stuff I am a strong -1
> > for making this the first such case.
>
> I really doubt that this is the only way in which you can break a
> pg_dump script by executing it in a non-default psql environment.
> We'd likely be better advised to spend some documentation effort
> recommending that pg_dump scripts be executed under "psql --no-psqlrc".
+1
Reinforcing that our output script basically assumes a default
execution
environment seems worth mentioning even if it seems self-evident once
it’s
said.
+1
While adding to the documentation is sufficient if users use it
correctly, users often behave unexpectedly. My intention was to
implement it in a way that works without issues even if misused.
However, since the prevailing opinion seems to favor simply updating the
documentation, I will proceed with that approach.
A new patch is attached.
I am not a native English, so corrections to the texts are welcome.
--
Regards,
Shinya Kato
NTT DATA GROUP CORPORATION
From f2ac5156b8fc02b6b529d3aeef16d80f21bbe08c Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.k...@oss.nttdata.com>
Date: Thu, 10 Oct 2024 14:50:39 +0900
Subject: [PATCH v2] doc: Add --no-psqlrc in pg_dump/pg_dumpall docs
---
doc/src/sgml/backup.sgml | 16 +++++++++-------
doc/src/sgml/ref/pg_dump.sgml | 9 ++++++++-
doc/src/sgml/ref/pg_dumpall.sgml | 11 ++++++++++-
3 files changed, 27 insertions(+), 9 deletions(-)
diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
index e4e4c56cf1..21ad34f4e0 100644
--- a/doc/src/sgml/backup.sgml
+++ b/doc/src/sgml/backup.sgml
@@ -106,10 +106,10 @@ pg_dump <replaceable class="parameter">dbname</replaceable> > <replaceable cl
<para>
Text files created by <application>pg_dump</application> are intended to
- be read in by the <application>psql</application> program. The
- general command form to restore a dump is
+ be read in by the <application>psql</application> program with its default
+ settings. The general command form to restore a dump is
<synopsis>
-psql <replaceable class="parameter">dbname</replaceable> < <replaceable class="parameter">dumpfile</replaceable>
+psql --no-psqlrc <replaceable class="parameter">dbname</replaceable> < <replaceable class="parameter">dumpfile</replaceable>
</synopsis>
where <replaceable class="parameter">dumpfile</replaceable> is the
file output by the <application>pg_dump</application> command. The database <replaceable
@@ -117,7 +117,9 @@ psql <replaceable class="parameter">dbname</replaceable> < <replaceable class
command, so you must create it yourself from <literal>template0</literal>
before executing <application>psql</application> (e.g., with
<literal>createdb -T template0 <replaceable
- class="parameter">dbname</replaceable></literal>). <application>psql</application>
+ class="parameter">dbname</replaceable></literal>). To use
+ <application>psql</application> with its default settings, use the
+ <option>--no-psqlrc</option> option. <application>psql</application>
supports options similar to <application>pg_dump</application> for specifying
the database server to connect to and the user name to use. See
the <xref linkend="app-psql"/> reference page for more information.
@@ -141,7 +143,7 @@ psql <replaceable class="parameter">dbname</replaceable> < <replaceable class
behavior and have <application>psql</application> exit with an
exit status of 3 if an SQL error occurs:
<programlisting>
-psql --set ON_ERROR_STOP=on <replaceable>dbname</replaceable> < <replaceable>dumpfile</replaceable>
+psql --no-psqlrc --set ON_ERROR_STOP=on <replaceable>dbname</replaceable> < <replaceable>dumpfile</replaceable>
</programlisting>
Either way, you will only have a partially restored database.
Alternatively, you can specify that the whole dump should be
@@ -160,7 +162,7 @@ psql --set ON_ERROR_STOP=on <replaceable>dbname</replaceable> < <replaceable>
write to or read from pipes makes it possible to dump a database
directly from one server to another, for example:
<programlisting>
-pg_dump -h <replaceable>host1</replaceable> <replaceable>dbname</replaceable> | psql -h <replaceable>host2</replaceable> <replaceable>dbname</replaceable>
+pg_dump -h <replaceable>host1</replaceable> <replaceable>dbname</replaceable> | psql --no-psqlrc -h <replaceable>host2</replaceable> <replaceable>dbname</replaceable>
</programlisting>
</para>
@@ -205,7 +207,7 @@ pg_dumpall > <replaceable>dumpfile</replaceable>
</synopsis>
The resulting dump can be restored with <application>psql</application>:
<synopsis>
-psql -f <replaceable class="parameter">dumpfile</replaceable> postgres
+psql --no-psqlrc -f <replaceable class="parameter">dumpfile</replaceable> postgres
</synopsis>
(Actually, you can specify any existing database name to start from,
but if you are loading into an empty cluster then <literal>postgres</literal>
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index ffc29b04fb..b6c33c3c2f 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -1636,6 +1636,13 @@ CREATE DATABASE foo WITH TEMPLATE template0;
option will be automatically enabled by the subscriber if the subscription
had been originally created with <literal>two_phase = true</literal> option.
</para>
+
+ <para>
+ It is generally recommended to use the <option>--no-psqlrc</option> option
+ when restoring a database from a <application>pg_dump</application> script
+ to ensure a clean restore process and prevent potential conflicts with
+ existing <application>psql</application> configurations.
+ </para>
</refsect1>
<refsect1 id="pg-dump-examples" xreflabel="Examples">
@@ -1653,7 +1660,7 @@ CREATE DATABASE foo WITH TEMPLATE template0;
<literal>newdb</literal>:
<screen>
-<prompt>$</prompt> <userinput>psql -d newdb -f db.sql</userinput>
+<prompt>$</prompt> <userinput>psql --no-psqlrc -d newdb -f db.sql</userinput>
</screen>
</para>
diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index 4d7c046468..f8db63326a 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -817,6 +817,15 @@ exclude database <replaceable class="parameter">PATTERN</replaceable>
database creation will fail for databases in non-default
locations.
</para>
+
+ <para>
+ It is generally recommended to use the <option>--no-psqlrc</option> option
+ when restoring a database from a <application>pg_dumpall</application> script
+ to ensure a clean restore process and prevent potential conflicts with
+ existing <application>psql</application> configurations. Additionally,
+ because the script includes <application>psql</application> meta-commands,
+ it is incompatible with clients other than <application>psql</application>.
+ </para>
</refsect1>
@@ -833,7 +842,7 @@ exclude database <replaceable class="parameter">PATTERN</replaceable>
<para>
To restore database(s) from this file, you can use:
<screen>
-<prompt>$</prompt> <userinput>psql -f db.out postgres</userinput>
+<prompt>$</prompt> <userinput>psql --no-psqlrc -f db.out postgres</userinput>
</screen>
It is not important to which database you connect here since the
script file created by <application>pg_dumpall</application> will
--
2.43.0
