Thread: ALTER DATABASE vs pg_dump

Is it desirable that pg_dump doesn't dump config settings set via ALTER 
DATABASE?

http://archives.postgresql.org/pgsql-novice/2008-04/msg00016.php

I just got bitten by a DateStyle not being restored on my test DB (I 
usually set it client-side in the app). I could see someone without my 
steel trap of a mind letting something like this slip through. Obvious 
problem settings would be: datestyle, locale, default-text-search

Is this a deliberate behaviour of pg_dump or just an unscratched itch?

--   Richard Huxton  Archonet Ltd

Richard Huxton <dev@archonet.com> writes:
> Is it desirable that pg_dump doesn't dump config settings set via ALTER 
> DATABASE?

Well, it's intentional anyway: that's handled by pg_dumpall.  The basic
design is that anything that can be seen from "outside" a specific
database is handled on the pg_dumpall side.

> I just got bitten by a DateStyle not being restored on my test DB

You could also get bitten by not having restored users or tablespaces
that the dump depends on, so I'm not sure there's a strong argument
here for refactoring the responsibility.
        regards, tom lane

Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
>> Is it desirable that pg_dump doesn't dump config settings set via ALTER 
>> DATABASE?
> 
> Well, it's intentional anyway: that's handled by pg_dumpall.  The basic
> design is that anything that can be seen from "outside" a specific
> database is handled on the pg_dumpall side.

Well, global settings and per-user settings are clearly global. I'm not 
sure that per-database settings are "logically" global, although I'll 
accept that's how they're stored.

At present it means you can't reliably do: DROP DATABASE foo; pg_restore --create foo.dump
I'd then have to either hand edit the dumpall dump or wade through a 
bunch of errors checking that none of them were relevant.

>> I just got bitten by a DateStyle not being restored on my test DB
> 
> You could also get bitten by not having restored users or tablespaces
> that the dump depends on, so I'm not sure there's a strong argument
> here for refactoring the responsibility.

Yep, but that will give you a "no such role" error when you try to 
restore. This is a situation where you can restore without errors and 
end up with different behaviour: dd/mm/yyyy vs mm/dd/yyyy or text-search 
stop-words changing.

--   Richard Huxton  Archonet Ltd

Richard Huxton wrote:
> At present it means you can't reliably do:
>  DROP DATABASE foo;
>  pg_restore --create foo.dump
> I'd then have to either hand edit the dumpall dump or wade through a 
> bunch of errors checking that none of them were relevant.

Actually, I'm not sure pg_dumpall does them either.

tracker=> SELECT name,setting,source FROM pg_settings WHERE name = 
'DateStyle';   name    | setting  |  source
-----------+----------+---------- DateStyle | SQL, DMY | database
(1 row)

pg_dumpall -U postgres -p 5483 -g > tracker.global.schema
pg_dump -U postgres -p 5483 --schema-only > tracker.schema
grep -i datestyle tracker*schema
<nothing>

That's with 8.3.3

Am I doing something stupid here?

--   Richard Huxton  Archonet Ltd

Richard Huxton wrote:
> Richard Huxton wrote:
>> At present it means you can't reliably do:
>>  DROP DATABASE foo;
>>  pg_restore --create foo.dump
>> I'd then have to either hand edit the dumpall dump or wade through a 
>> bunch of errors checking that none of them were relevant.
> 
> Actually, I'm not sure pg_dumpall does them either.
[snip]
> Am I doing something stupid here?

OK - so to get the ALTER DATABASE commands I need to dump the schema for 
the entire cluster. Is that really desired behaviour?

--   Richard Huxton  Archonet Ltd

On Friday 27 June 2008 12:58:41 Richard Huxton wrote:
> Richard Huxton wrote:
> > Richard Huxton wrote:
> >> At present it means you can't reliably do:
> >>  DROP DATABASE foo;
> >>  pg_restore --create foo.dump
> >> I'd then have to either hand edit the dumpall dump or wade through a
> >> bunch of errors checking that none of them were relevant.
> >
> > Actually, I'm not sure pg_dumpall does them either.
>
> [snip]
>
> > Am I doing something stupid here?
>
> OK - so to get the ALTER DATABASE commands I need to dump the schema for
> the entire cluster. Is that really desired behaviour?
>

Certainly not desired by a number of people I have talked to, but I don't have 
much hope in seeing the behavoir change... perhaps someday if we get around 
to merging pg_dump and pg_dumpall....

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

Robert Treat wrote:
> On Friday 27 June 2008 12:58:41 Richard Huxton wrote:

> > > Am I doing something stupid here?
> >
> > OK - so to get the ALTER DATABASE commands I need to dump the schema for
> > the entire cluster. Is that really desired behaviour?
> 
> Certainly not desired by a number of people I have talked to, but I don't have 
> much hope in seeing the behavoir change... perhaps someday if we get around 
> to merging pg_dump and pg_dumpall....

I have never heard anyone say the current behavior is something they desired.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Robert Treat wrote:
>> Certainly not desired by a number of people I have talked to, but I don't have 
>> much hope in seeing the behavoir change... perhaps someday if we get around 
>> to merging pg_dump and pg_dumpall....

> I have never heard anyone say the current behavior is something they desired.

So put forward a worked-out proposal for some other behavior.

My first thought is that the -c and -C options create a lot of the
issues in this area.  -c in particular is evidently meant for merging a
dump into a database that already contains unrelated objects.  (In fact
you could argue that the *default* behavior is meant for this, -c just
changes the result for conflicts.)  It seems unlikely that having
pg_dump issue ALTER DATABASE SET commands is a good idea in all of these
scenarios.

I'm also wondering why it'd be bright to treat ALTER ... SET properties
different from, say, database owner and encoding properties.
        regards, tom lane

Tom Lane wrote:
> 
> So put forward a worked-out proposal for some other behavior.

OK

> My first thought is that the -c and -C options create a lot of the
> issues in this area.  -c in particular is evidently meant for merging a
> dump into a database that already contains unrelated objects.  (In fact
> you could argue that the *default* behavior is meant for this, -c just
> changes the result for conflicts.)  It seems unlikely that having
> pg_dump issue ALTER DATABASE SET commands is a good idea in all of these
> scenarios.

Can't comment on --clean since I don't use it. I've always assumed it's 
for the case where you don't have a user with permissions to 
drop/recreate a database (e.g. web hosting).

IMHO the time a dump/restore should be issuing ALTER...SET on a database 
is when it has issued the corresponding CREATE DATABASE. If you want to 
tweak this sort of thing, just manually create the database with 
whatever options you want and don't use --create.

> I'm also wondering why it'd be bright to treat ALTER ... SET properties
> different from, say, database owner and encoding properties.

Not sure what you mean here.

--   Richard Huxton  Archonet Ltd

Richard Huxton <dev@archonet.com> writes:
> Tom Lane wrote:
>> So put forward a worked-out proposal for some other behavior.

> IMHO the time a dump/restore should be issuing ALTER...SET on a database 
> is when it has issued the corresponding CREATE DATABASE.

So pg_dump would produce this info when, and only when, you'd used
--create?  I agree that it seems sensible in that case, I'm just
wondering if that will cover all the use-cases.

This would mean duplicating some functionality between pg_dump and
pg_dumpall ... or maybe we could move all that logic over to pg_dump and
have pg_dumpall use --create when invoking pg_dump.
        regards, tom lane

Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
>> Tom Lane wrote:
>>> So put forward a worked-out proposal for some other behavior.
> 
>> IMHO the time a dump/restore should be issuing ALTER...SET on a database 
>> is when it has issued the corresponding CREATE DATABASE.
> 
> So pg_dump would produce this info when, and only when, you'd used
> --create?  I agree that it seems sensible in that case, I'm just
> wondering if that will cover all the use-cases.

Well, in the -Fc case you'd produce it always and pg_restore would only 
emit it when you --create.

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.

> This would mean duplicating some functionality between pg_dump and
> pg_dumpall ... or maybe we could move all that logic over to pg_dump and
> have pg_dumpall use --create when invoking pg_dump.

--   Richard Huxton  Archonet Ltd

On Tuesday 01 July 2008 03:45:44 Richard Huxton wrote:
> Tom Lane wrote:
> > Richard Huxton <dev@archonet.com> writes:
> >> Tom Lane wrote:
> >>> So put forward a worked-out proposal for some other behavior.
> >>
> >> IMHO the time a dump/restore should be issuing ALTER...SET on a database
> >> is when it has issued the corresponding CREATE DATABASE.
> >
> > So pg_dump would produce this info when, and only when, you'd used
> > --create?  I agree that it seems sensible in that case, I'm just
> > wondering if that will cover all the use-cases.
>
> Well, in the -Fc case you'd produce it always and pg_restore would only
> emit it when you --create.
>
> 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). 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

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

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>

On Thursday 21 August 2008 18:28:55 Bruce Momjian wrote:
> 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.
>

ok, I have no recollection of this conversation :-)

but...

> ! 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>
>

Is misleading (or maybe just wrong) because those settings are not dumped with 
the other installation wide settings. Meaning that pg_dumpall -g has no 
bearing on the alter database commands being set, you actually have to 
dumpall the entire data set to get those lines.  

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

Robert Treat wrote:
> On Thursday 21 August 2008 18:28:55 Bruce Momjian wrote:
> > 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.
> >
> 
> ok, I have no recollection of this conversation :-)
> 
> but...
> 
> > ! 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>
> >
> 
> Is misleading (or maybe just wrong) because those settings are not dumped with 
> the other installation wide settings. Meaning that pg_dumpall -g has no 
> bearing on the alter database commands being set, you actually have to 
> dumpall the entire data set to get those lines.  

Oh, that is odd, but documented:
      -g
      --globals-only             Dump  only  global objects (roles and tablespaces),             no databases.

ALTER DATABASE ... SET seems to be something that doesn't fit in
anywhere;  I am thinking pg_dump -g should dump it.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce@momjian.us> writes:
> ALTER DATABASE ... SET seems to be something that doesn't fit in
> anywhere;  I am thinking pg_dump -g should dump it.

The upthread conclusion was that pg_dump -C should do it.
I am not sure how you come to the conclusion that -g is an
appropriate place, seeing that -g will not create the database(s)
that it'd be trying to apply ALTER to.
        regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > ALTER DATABASE ... SET seems to be something that doesn't fit in
> > anywhere;  I am thinking pg_dump -g should dump it.
> 
> The upthread conclusion was that pg_dump -C should do it.
> I am not sure how you come to the conclusion that -g is an
> appropriate place, seeing that -g will not create the database(s)
> that it'd be trying to apply ALTER to.

Agreed, now that I see pg_dump -C.  I have added a TODO item:
Add:  Have pg_dump -C emit ALTER DATABASE ... SET commands afterdatabase creation 

Does the documentation need to be updated beyond my changes from
yesterday?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +