Re: Does anything dump per-database config settings? (was Re: [HACKERS] ALTER DATABASE vs pg_dump)
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Does anything dump per-database config settings? (was Re: [HACKERS] ALTER DATABASE vs pg_dump)
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 [EMAIL PROTECTED]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 - 1.103 --- doc/src/sgml/ref/pg_dump.sgml 21 Aug 2008 22:11:32 - *** *** 754,775 /para para !applicationpg_dump/application has a few limitations: ! !itemizedlist ! listitem ! para ! When a data-only dump is chosen and the option ! option--disable-triggers/ is used, ! applicationpg_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 !applicationpg_dump/application has a limitation; when a !data-only dump is chosen and the option option--disable-triggers/ !is used, applicationpg_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 applicationpg_dump/application does !not contain the statistics used by the optimizer to make query !planning decisions. Therefore, it is wise to run !commandANALYZE/command after restoring from a dump file to !ensure good performance. /para para --- 773,786 /para para !The dump file produced by applicationpg_dump/application !does not contain the statistics used by the optimizer to make !query planning decisions. Therefore, it is wise to run !commandANALYZE/command after restoring from a dump file !to ensure good performance. The dump file also does not !contain any commandALTER DATABASE ... SET/ commands; !these settings are dumped by xref linkend=app-pg-dumpall, !along with database users and other installation-wide settings. /para para -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Does anything dump per-database config settings? (was Re: [HACKERS] ALTER DATABASE vs pg_dump)
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 commandALTER 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Does anything dump per-database config settings? (was Re: [HACKERS] ALTER DATABASE vs pg_dump)
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 commandALTER 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 [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Does anything dump per-database config settings? (was Re: [HACKERS] ALTER DATABASE vs pg_dump)
Bruce Momjian [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Does anything dump per-database config settings? (was Re: [HACKERS] ALTER DATABASE vs pg_dump)
Tom Lane wrote: Richard Huxton [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Does anything dump per-database config settings? (was Re: [HACKERS] ALTER DATABASE vs pg_dump)
On Tuesday 01 July 2008 03:45:44 Richard Huxton wrote:
Tom Lane wrote:
Richard Huxton [EMAIL PROTECTED] 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Does anything dump per-database config settings? (was Re: [HACKERS] ALTER DATABASE vs pg_dump)
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
Re: Does anything dump per-database config settings? (was Re: [HACKERS] ALTER DATABASE vs pg_dump)
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Does anything dump per-database config settings? (was Re: [HACKERS] ALTER DATABASE vs pg_dump)
Richard Huxton [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Does anything dump per-database config settings? (was Re: [HACKERS] ALTER DATABASE vs pg_dump)
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Does anything dump per-database config settings? (was Re: [HACKERS] ALTER DATABASE vs pg_dump)
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Does anything dump per-database config settings? (was Re: [HACKERS] ALTER DATABASE vs pg_dump)
Alvaro Herrera [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Does anything dump per-database config settings? (was Re: [HACKERS] ALTER DATABASE vs pg_dump)
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
