Thread: pg_dump design problem (bug??)
Postgresql 8.1.3 Hi, I'm wondering if there's a problem with pg_dump --create, or if I'm just missing something. It does not seem to restore things like: ALTER DATABASE foo SET "DateStyle" TO European; Shouldn't the database that is re-created be like the database that is being dumped? For our purposes we do a pg_dumpall --globals-only and then pg_dumps of each of our databases. We like this because we can restore blobs this way, get a nice compressed and flexable --format=c, and are able to restore individual databases. But there is clearly a problem because we lose the database meta-information like datestyle, timezones and all that other per-db SET stuff. It seems the only way to get that is with a pg_dumpall, and then it's not per-database. What should we do to work around this problem? Should pg_dump/pg_restore have arguments like: --dbmeta (the default when --create is used) Dumps the database's "SET" options. --no-dbmeta (the default when --create is not used) Does not dump the database's "SET" options. --dbowner (the default when --create is used) Dumps the database's owner. --no-dbowner (the default when --create is not used) Does not dump the database's owner. Hummm.... for complete control consider the following: Or maybe pg_dump/pg_restore should augment/replace --data-only --schema-only --create with: --content=ctype[, ...] where ctype=db|dbowner|meta|schema|schemaowner|table|tableowner|data db create the database dbowner set the database owner as in the dumped db meta set the database SETs as in the dumped db schema create the schema (not data definitions/table structure) as in the dumped db schemaowner set the schema owner as in the dumped db table create the table(s) as in the dumped db tableowner set the table owners as in the dumped db data load the data as in the dumped db I'd also want to add functions, triggers, views and the other sorts of things that go into databases to the above list, but that's enough for now. Thanks for listening. Karl <kop@meme.com> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
"Karl O. Pinc" <kop@meme.com> writes: > I'm wondering if there's a problem with pg_dump --create, > or if I'm just missing something. > It does not seem to restore things like: > ALTER DATABASE foo SET "DateStyle" TO European; > Shouldn't the database that is re-created be like > the database that is being dumped? The major reason why pg_dump doesn't touch that stuff is that it wants to be agnostic about the name of the database you are restoring into. I don't see any particular problem with leaving it to pg_dumpall, in any case. pg_dump is already assuming that you've correctly set up cluster-wide state; for example it doesn't create users for you. regards, tom lane
On 05/09/2006 03:47:20 PM, Tom Lane wrote: > "Karl O. Pinc" <kop@meme.com> writes: > > I'm wondering if there's a problem with pg_dump --create, > > or if I'm just missing something. > > It does not seem to restore things like: > > ALTER DATABASE foo SET "DateStyle" TO European; > > Shouldn't the database that is re-created be like > > the database that is being dumped? > > I don't see any particular problem with leaving it to pg_dumpall, in > any case. pg_dump is already assuming that you've correctly set up > cluster-wide state; for example it doesn't create users for you. Thing is, I don't see the ALTER DATABASE x SET ... to be part of a cluster-wide structure, I see it as belonging to a database. (I do see your point as far as database owners go.) The convenient way to backup and restore a single database is to use pg_dump. I could do a pg_dumpall --schema-only and then remove everything not having to do with the specific db I'm interested in when I want to, say, copy a database from one machine to another, but it's a hassle. The SETs make a big difference. I was looking in the wrong schema because I didn't restore my database's my search_path properly. Karl <kop@meme.com> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein
So, what about it? I periodically encounter with the same problem. People (e.g. me :-) but not only) expect that when they use pg_dump to backup some database (either schema only or both schema and data), all database properties will be dumped and, then, restored. People think that this thing seems to be gotcha. Anyway, if we can assign variable's value to database, it makes this value to be the property of database and, therefore, should be dumped... I saw several bad (from my point of view) solutions to this issue. For example, developer stops to use it as property of database (i.e. he claims that in his project using 'adlter database set ... to ...' is Bad Thing) and start to: - use additional initialization commands for every database session in the project (e.g. additional lines such as of pg_query('SET search_path TO ...'); in some file like core.php if he uses PHP) - adds corresponding lines to hist .bashrc/.bash_profile... This issue dissapoints me for quite long period of time already... As long as other pg_dump's gotcha concerning dumping of SERIAL (but this is another issue). On 5/10/06, Karl O. Pinc <kop@meme.com> wrote: > > On 05/09/2006 03:47:20 PM, Tom Lane wrote: > > "Karl O. Pinc" <kop@meme.com> writes: > > > I'm wondering if there's a problem with pg_dump --create, > > > or if I'm just missing something. > > > It does not seem to restore things like: > > > ALTER DATABASE foo SET "DateStyle" TO European; > > > Shouldn't the database that is re-created be like > > > the database that is being dumped? > > > > I don't see any particular problem with leaving it to pg_dumpall, in > > any case. pg_dump is already assuming that you've correctly set up > > cluster-wide state; for example it doesn't create users for you. > > Thing is, I don't see the ALTER DATABASE x SET ... to be part of > a cluster-wide structure, I see it as belonging to a database. > (I do see your point as far as database owners go.) > The convenient way to backup and restore a single database > is to use pg_dump. I could do a pg_dumpall --schema-only and > then remove everything not having to do with the specific db > I'm interested in when I want to, say, copy a database from > one machine to another, but it's a hassle. > > The SETs make a big difference. I was looking in the wrong > schema because I didn't restore my database's my search_path > properly. > > Karl <kop@meme.com> > Free Software: "You don't pay back, you pay forward." > -- Robert A. Heinlein > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- Best regards, Nikolay
On 06/27/2006 09:29:36 AM, Nikolay Samokhvalov wrote: > So, what about it? > > I periodically encounter with the same problem. People (e.g. me :-) > but not only) expect that when they use pg_dump to backup some > database (either schema only or both schema and data), all database > properties will be dumped and, then, restored. > > People think that this thing seems to be gotcha. Anyway, if we can > assign variable's value to database, it makes this value to be the > property of database and, therefore, should be dumped... There are obvious acceptable work-arounds, but none (AFIK) that don't involve having to manually look through a bunch of pg_dumpall output if you want to restore just one database. There are only 2 real choices, either pg_dumpall takes an option to specify just one db be dumped, or pg_dump takes a flag that allows "alter database" into the output and pg_restore takes a flag that ignores such "alter database" information. I'd prefer pg_dump/pg_restore, it has the advantage of producing a single file per db. (Humm, it'd probably be best if the pg_restore flag only worked on -F c style data.) The real question is whether the pg developers would object to such a feature, whatever the design is, or whether it's just that nobody's gotten around to writing it. Karl <kop@meme.com> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein
On Tuesday 27 June 2006 11:10, Karl O. Pinc wrote: > On 06/27/2006 09:29:36 AM, Nikolay Samokhvalov wrote: > > So, what about it? > > > > I periodically encounter with the same problem. People (e.g. me :-) > > but not only) expect that when they use pg_dump to backup some > > database (either schema only or both schema and data), all database > > properties will be dumped and, then, restored. > > > > People think that this thing seems to be gotcha. Anyway, if we can > > assign variable's value to database, it makes this value to be the > > property of database and, therefore, should be dumped... > > There are obvious acceptable work-arounds, but none (AFIK) that don't > involve having to manually look through a bunch of pg_dumpall output > if you want to restore just one database. There are only 2 real > choices, either pg_dumpall takes an option to specify just one > db be dumped, or pg_dump takes a flag that allows "alter database" > into the output and pg_restore takes a flag that ignores > such "alter database" information. I'd prefer > pg_dump/pg_restore, it has the advantage > of producing a single file per db. (Humm, it'd probably > be best if the pg_restore flag only worked on > -F c style data.) > I think I would prefer the former... pg_dumpall --database foo that dumped all globals along with a specific database. > The real question is whether the pg developers would > object to such a feature, whatever the design is, > or whether it's just that nobody's > gotten around to writing it. > Probably more of no one getting around to it, but you need to come up with a solution that doesnt break backwards compatability; ie you need to be able to still make "dbname" agnostic dumps with pg_dump. -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL