Обсуждение: Fwd: pg_dump VS alter database ... set search_path ...
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
Вложения
"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
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
On 10/9/06, Nikolay Samokhvalov <samokhvalov@gmail.com> 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
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 >