Re: pg_dump design problem (bug??)

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(at)meme(dot)com> wrote:
>
> On 05/09/2006 03:47:20 PM, Tom Lane wrote:
> > "Karl O. Pinc" <kop(at)meme(dot)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(at)meme(dot)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