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