FAQ

[PostgreSQL-Hackers] ALTER DATABASE vs pg_dump

Richard Huxton
Jun 27, 2008 at 1:50 pm
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
reply

Search Discussions

17 responses

  • Tom Lane at Jun 27, 2008 at 2:48 pm

    Richard Huxton 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
  • Richard Huxton at Jun 27, 2008 at 3:35 pm

    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 at Jun 27, 2008 at 4:51 pm

    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 at Jun 27, 2008 at 4:58 pm

    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
  • Robert Treat at Jun 30, 2008 at 2:47 am

    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
  • Alvaro Herrera at Jun 30, 2008 at 3:31 am

    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.
  • Tom Lane at Jun 30, 2008 at 3:44 am

    Alvaro Herrera 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
  • Richard Huxton at Jun 30, 2008 at 8:38 am

    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
  • Tom Lane at Jun 30, 2008 at 11:46 pm

    Richard Huxton 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
  • Richard Huxton at Jul 1, 2008 at 7:59 am

    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
  • Robert Treat at Jul 1, 2008 at 12:55 pm

    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
  • Richard Huxton at Jul 1, 2008 at 3:22 pm

    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
  • Bruce Momjian at Aug 21, 2008 at 10:29 pm
    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. +
  • Robert Treat at Aug 22, 2008 at 1:55 am

    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
  • Bruce Momjian at Aug 22, 2008 at 2:28 am

    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. +
  • Tom Lane at Aug 22, 2008 at 3:11 am

    Bruce Momjian 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
  • Bruce Momjian at Aug 22, 2008 at 2:31 pm

    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 after
    database 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. +

Related Discussions