"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

On 10/9/06, Tom Lane <tgl@sss.pgh.pa.us> 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

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

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 <tgl@sss.pgh.pa.us> 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
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend