What is the reason to not include database settings (like search_path)
to database dump created with "pg_dump -C"?
For me, I've created tmp patch for pg_dump to make my system work
(patch for CVS version is included).

---------- Forwarded message ----------
From: Nikolay Samokhvalov <samokhvalov@gmail.com>
Date: Oct 9, 2006 12:45 PM
Subject: pg_dump VS alter database ... set search_path ...
To: PostgreSQL-general <pgsql-general@postgresql.org>


Hi,

What is the best practice for following case:
In my database I have a set (~10) of schemas; my database periodically
is being backed up and restored at another machine. I have set up
search_path via "ALTER DATABASE ... SET search_path TO ..." to make
all needed schemas visible to any user who has appropriate rights. The
problem is that I cannot use pg_dumpall and pg_dump DOES NOT dump this
ALTER command, even being executed with "-C" option.

Using additional restoration script with list of schemas seems not the
best solution, because a set of schemas can be changed and I have not
only one database.

Search in mail archives gives me understanding that this issue is
among not resolved ones (there are pros and cons for including such
ALTER in pg_dump-ing).

Is there any common practice for this [probably very frequent] issue?

--
Best regards,
Nikolay


--
Best regards,
Nikolay

Search Discussions

  • Tom Lane at Oct 9, 2006 at 2:36 pm

    "Nikolay Samokhvalov" <samokhvalov@gmail.com> writes:
    What is the reason to not include database settings (like search_path)
    to database dump created with "pg_dump -C"?
    Duplication of code and functionality with pg_dumpall. I'd want to see
    some thought about how to resolve that, not just a quick copy-some-code-
    from-pg_dumpall-into-pg_dump. You also need to explain why this issue
    should be treated differently from users and groups ... a dump won't
    restore correctly without that supporting context either.

    I have no objection to rethinking the division of labor between the two
    programs, but let's end up with something that's cleaner not uglier.

    regards, tom lane
  • Nikolay Samokhvalov at Oct 9, 2006 at 6:29 pm

    On 10/9/06, Tom Lane wrote:
    Duplication of code and functionality with pg_dumpall.
    Well, then "-C" option of pg_dump can be considered as duplication of
    pg_dumpall's functionality too, right?
    I'd want to see
    some thought about how to resolve that, not just a quick copy-some-code-
    from-pg_dumpall-into-pg_dump. You also need to explain why this issue
    should be treated differently from users and groups ... a dump won't
    restore correctly without that supporting context either.

    I have no objection to rethinking the division of labor between the two
    programs, but let's end up with something that's cleaner not uglier.
    "-C" option is useful in cases like mine. Example: in a PG cluster of
    100 databases there is one database containing 10 schemes; this
    database is being dumped every night and restored on 3 separate
    machines, where some operations are then being executed). pg_dumpall
    is not a solution in this case. Moreover, playing with "ALTER USER ...
    SET search_path TO ..." may not the best solution too - there may be
    different users sets on different hosts, and, what is more important,
    if I (developing my app) add new schema to that database, I should run
    <number of hosts> * <number of roles> ALTERs, this is not good.

    When I write "ALTER DATABASE ... SET ..." I expect that corresponding
    *database's* property will be modified. When I choose "-C" option of
    pg_dump I expect that "CREATE DATABASE" with all its properties (in
    ALTER stmts) will be printed. I think it's not a question of "division
    of labor between the two programs".

    As for "users and groups" - I do not understand why you are mentioning
    it. I'm talking about "-C" option, and complain that it doesn't allow
    me to dump/restore the database with its properties. I suppose,
    users/roles shouldn't be involved in this discussion.

    Maybe my understanding is wrong - I'll be glad to hear why.

    --
    Best regards,
    Nikolay
  • Nikolay Samokhvalov at Oct 11, 2006 at 6:59 am

    On 10/9/06, Nikolay Samokhvalov wrote:
    Maybe my understanding is wrong - I'll be glad to hear why.
    Maybe at least to create special switcher for database settings? (It
    would remain backward compatibility...)

    --
    Best regards,
    Nikolay
  • Ivan Zolotukhin at Oct 13, 2006 at 5:37 am
    Tom,

    Can you please suggest a good practice how to propagate such DB
    settings into dumps?

    I also suffer from this: my DB currently have 5 schemas and
    application strongly depends on the search_path. I cannot dump whole
    cluster, I need only 1 specific database. At this moment I use ugly
    solution and store search_path setting as per-user settings in my
    secondary databases.

    Solution of Nikolay, being improved for backward compatibility
    (additional switch for pg_dump to include alter database statements
    with these settings into sql dump generated) would fit me perfectly.

    But unfortunately you're not constructive in your critics here and do
    not propose a way to solve the problem, only saying that this (very
    useful and awaited option!) is ugly. With approach like this the
    community will wait for the solution for ages.

    :-(


    On 10/9/06, Tom Lane wrote:
    "Nikolay Samokhvalov" <samokhvalov@gmail.com> writes:
    What is the reason to not include database settings (like search_path)
    to database dump created with "pg_dump -C"?
    Duplication of code and functionality with pg_dumpall. I'd want to see
    some thought about how to resolve that, not just a quick copy-some-code-
    from-pg_dumpall-into-pg_dump. You also need to explain why this issue
    should be treated differently from users and groups ... a dump won't
    restore correctly without that supporting context either.

    I have no objection to rethinking the division of labor between the two
    programs, but let's end up with something that's cleaner not uglier.

    regards, tom lane

    ---------------------------(end of broadcast)---------------------------
    TIP 1: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to majordomo@postgresql.org so that your
    message can get through to the mailing list cleanly

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedOct 9, '06 at 10:24a
activeOct 13, '06 at 5:37a
posts5
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase