Re: Does anything dump per-database config settings? (was Re: ALTER DATABASE vs pg_dump) - Mailing list pgsql-hackers

We never came up with any idea of how pg_dump could dump ALTER DATABASE
... SET commands, so I have added a mention in the documentation, and
backpatched to 8.3.X;  attached.

---------------------------------------------------------------------------

Richard Huxton wrote:
> Robert Treat wrote:
> > On Tuesday 01 July 2008 03:45:44 Richard Huxton wrote:
> >> The only time we need to restore per-database settings is if the
> >> database has been dropped. If you're not having the dump/restore
> >> re-create the database then presumably you've taken charge of the
> >> per-database settings.
> >
> > I'm not sure I agree with that entirely. For example, one common usage
> > scenario when upgrading between major versions is to create the database,
> > load contrib modules (whose C functions or similar may have changed), and
> > then load the dump into the database. In those case you still might want the
> > database settings to be dumped, even though you are creating the database
> > manually. (Now, one might argue that you could still dump with --create and
> > ignore the error of the database creation command, but that probably isn't
> > ideal).
>
> Well, with -Fc I'd expect it to be dumped all the time and pg_restore
> would selectively restore it. That should mean it has its own line in
> the pg_restore --list output which would let you just comment out the
> database-creation but leave the ALTER...SET in.
>
> --
>    Richard Huxton
>    Archonet Ltd
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/ref/pg_dump.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v
retrieving revision 1.103
diff -c -c -r1.103 pg_dump.sgml
*** doc/src/sgml/ref/pg_dump.sgml    20 Jul 2008 18:43:30 -0000    1.103
--- doc/src/sgml/ref/pg_dump.sgml    21 Aug 2008 22:11:32 -0000
***************
*** 754,775 ****
    </para>

    <para>
!    <application>pg_dump</application> has a few limitations:
!
!    <itemizedlist>
!     <listitem>
!      <para>
!       When a data-only dump is chosen and the option
!       <option>--disable-triggers</> is used,
!       <application>pg_dump</application> emits commands to disable
!       triggers on user tables before inserting the data and commands
!       to re-enable them after the data has been inserted.  If the
!       restore is stopped in the middle, the system catalogs might be
!       left in the wrong state.
!      </para>
!     </listitem>
!
!    </itemizedlist>
    </para>

    <para>
--- 754,766 ----
    </para>

    <para>
!    <application>pg_dump</application> has a limitation; when a
!    data-only dump is chosen and the option <option>--disable-triggers</>
!    is used, <application>pg_dump</application> emits commands
!    to disable triggers on user tables before inserting the data
!    and commands to re-enable them after the data has been
!    inserted.  If the restore is stopped in the middle, the system
!    catalogs might be left in the wrong state.
    </para>

    <para>
***************
*** 782,792 ****
    </para>

    <para>
!    The dump file produced by <application>pg_dump</application> does
!    not contain the statistics used by the optimizer to make query
!    planning decisions.  Therefore, it is wise to run
!    <command>ANALYZE</command> after restoring from a dump file to
!    ensure good performance.
    </para>

    <para>
--- 773,786 ----
    </para>

    <para>
!    The dump file produced by <application>pg_dump</application>
!    does not contain the statistics used by the optimizer to make
!    query planning decisions.  Therefore, it is wise to run
!    <command>ANALYZE</command> after restoring from a dump file
!    to ensure good performance.  The dump file also does not
!    contain any <command>ALTER DATABASE ... SET</> commands;
!    these settings are dumped by <xref linkend="app-pg-dumpall">,
!    along with database users and other installation-wide settings.
    </para>

    <para>