Обсуждение: ALTER DATABASE vs pg_dump
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
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. > 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
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
Does anything dump per-database config settings? (was Re: ALTER DATABASE vs pg_dump)
От
Richard Huxton
Дата:
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
Re: Does anything dump per-database config settings? (was Re: ALTER DATABASE vs pg_dump)
От
Richard Huxton
Дата:
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
Re: Does anything dump per-database config settings? (was Re: ALTER DATABASE vs pg_dump)
От
Robert Treat
Дата:
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
Re: Does anything dump per-database config settings? (was Re: ALTER DATABASE vs pg_dump)
От
Alvaro Herrera
Дата:
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.
Alvaro Herrera <alvherre@commandprompt.com> 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
Re: Does anything dump per-database config settings? (was Re: ALTER DATABASE vs pg_dump)
От
Richard Huxton
Дата:
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
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. 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
Re: Does anything dump per-database config settings? (was Re: ALTER DATABASE vs pg_dump)
От
Richard Huxton
Дата:
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
Re: Does anything dump per-database config settings? (was Re: ALTER DATABASE vs pg_dump)
От
Robert Treat
Дата:
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
Re: Does anything dump per-database config settings? (was Re: ALTER DATABASE vs pg_dump)
От
Richard Huxton
Дата:
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
Re: Does anything dump per-database config settings? (was Re: ALTER DATABASE vs pg_dump)
От
Bruce Momjian
Дата:
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. + 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 -0000 1.103 --- doc/src/sgml/ref/pg_dump.sgml 21 Aug 2008 22:11:32 -0000 *************** *** 754,775 **** </para> <para> ! <application>pg_dump</application> has a few limitations: ! ! <itemizedlist> ! <listitem> ! <para> ! When a data-only dump is chosen and the option ! <option>--disable-triggers</> is used, ! <application>pg_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> ! <application>pg_dump</application> has a limitation; when a ! data-only dump is chosen and the option <option>--disable-triggers</> ! is used, <application>pg_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 <application>pg_dump</application> does ! not contain the statistics used by the optimizer to make query ! planning decisions. Therefore, it is wise to run ! <command>ANALYZE</command> after restoring from a dump file to ! ensure good performance. </para> <para> --- 773,786 ---- </para> <para> ! The dump file produced by <application>pg_dump</application> ! does not contain the statistics used by the optimizer to make ! query planning decisions. Therefore, it is wise to run ! <command>ANALYZE</command> after restoring from a dump file ! to ensure good performance. 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> <para>
Re: Does anything dump per-database config settings? (was Re: ALTER DATABASE vs pg_dump)
От
Robert Treat
Дата:
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
Re: Does anything dump per-database config settings? (was Re: ALTER DATABASE vs pg_dump)
От
Bruce Momjian
Дата:
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. +
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. regards, tom lane
Re: Does anything dump per-database config settings? (was Re: ALTER DATABASE vs pg_dump)
От
Bruce Momjian
Дата:
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 afterdatabase 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. +