pg_dump bug fixing

On Sun, Jul 18, 2004 at 14:33:09 +0800,
Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:

* If you drop your public schema, a drop command is not issued for it in
the dump, so when you restore your public schema is back

I am not sure that is really a bug. If someone really wants less than
what is in template1, they should be dropping stuff from template1
before recreating the database.

I am not sure that is really a bug. If someone really wants less than
what is in template1, they should be dropping stuff from template1
before recreating the database.

No, because pg_dump itself dumps template1's contents...

Chris

On Sun, Jul 18, 2004 at 19:42:09 +0800,
Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:

I am not sure that is really a bug. If someone really wants less than
what is in template1, they should be dropping stuff from template1
before recreating the database.

No, because pg_dump itself dumps template1's contents...

Are you planning on extending this line of thought to other things in
template1 such as operators?

On Sun, 2004-07-18 at 07:42, Christopher Kings-Lynne wrote:

I am not sure that is really a bug. If someone really wants less than
what is in template1, they should be dropping stuff from template1
before recreating the database.

No, because pg_dump itself dumps template1's contents...

Hmm.

1. Add language handler (say plpgsql) to template1.
2. Create new database
3. Dump database
4. Restore dump, having pg_dump create a new database

Doh.. errors because language handlers are there twice.

You can play this game with tables, sequences, and all sorts of other
things that might be useful in the default template. pg_dump just skips
it (not necessarily bad unless they did an alter table afterward) but
pg_restore doesn't like this at all.

I think what we want is a clean template without all of the extras that
template1 has.

If we dump & restore the public schema and other items that we might be
interested in having, we can have pg_dump use template_clean as a
template for new databases. Template_clean contains none of the things
that pg_dump can dump/restore (like language handlers) but is NOT the
default template.

I've done this myself, removed lots of stuff from template1 after
creating a template_<companyname> that is used for creating new DBs
with. This works great when we remember to specify WITH TEMPLATE.

No, because pg_dump itself dumps template1's contents...

Are you planning on extending this line of thought to other things in
template1 such as operators?

Ah, I see where you are going with this....

I think that we should treat the public schema specially :) That's
because it is much more likely to be dropped and messed with than the
other system objects, in fact you're encouraged to drop it in the docs.

Chris

Rod Taylor <pg@rbt.ca> writes:

I think what we want is a clean template without all of the extras that
template1 has.

Sounds like a job for ... template0 !

Seriously, this thread would be more convincing if anyone in it betrayed
any knowledge that pg_dump wants you to start from template0 rather than
template1.

regards, tom lane

Sounds like a job for ... template0 !

Seriously, this thread would be more convincing if anyone in it betrayed
any knowledge that pg_dump wants you to start from template0 rather than
template1.

What if we made it so that template1 is always restored last? Won't
that be an improvement?

Chris

On Sun, 2004-07-18 at 23:55, Tom Lane wrote:

Rod Taylor <pg@rbt.ca> writes:

I think what we want is a clean template without all of the extras that
template1 has.

Sounds like a job for ... template0 !

It doesn't quite work in my case as I've removed items included in
template0 (public schema).

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

I think that we should treat the public schema specially :)

We already are to some extent, since pg_dump will dump its comment and
privileges, which it would not do for any other predefined object.
I think this is actually an implementation artifact rather than
something that was explicitly intended at the time, but since no one
has complained about it, it's probably a good thing ;-)

Also, if we're going to apply Fabien's proposed patch to alter the
ownership of the public schema, that's still another way in which the
public schema becomes less like a system-defined object ...

regards, tom lane

Rod,

I think what we want is a clean template without all of the extras that
template1 has.

We have this, it's called Template0.

Actually, KL, that would solve a lot of these duplicate object problems. What
if pg_restore used Template0 and not Template1? It wouldn't fix the "drop
public schema" issue but it would solve the others.

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Mon, 2004-07-19 at 12:36, Josh Berkus wrote:

Rod,

I think what we want is a clean template without all of the extras that
template1 has.

We have this, it's called Template0.

Doesn't work for me. I remove a number of things that are included by
default in template0, but yes, it's close.

Actually, KL, that would solve a lot of these duplicate object problems. What
if pg_restore used Template0 and not Template1? It wouldn't fix the "drop
public schema" issue but it would solve the others.

Remove the public schema from template0, but leave it in template1. Have
pg_dump treat the public schema the same as all of the other ones.

Rod,

Remove the public schema from template0, but leave it in template1. Have
pg_dump treat the public schema the same as all of the other ones.

Hmmm. No good; it wipes out the primary purpose of Template0, which is to
restore a corrupted Template1.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Rod Taylor <pg@rbt.ca> writes:

Remove the public schema from template0, but leave it in template1.

Does not sound very workable. One of the functions of template0 is to
be a backup for the virgin state of template1, and you'd lose that.

regards, tom lane

On Mon, 2004-07-19 at 13:30, Josh Berkus wrote:

Rod,

Remove the public schema from template0, but leave it in template1. Have
pg_dump treat the public schema the same as all of the other ones.

Hmmm. No good; it wipes out the primary purpose of Template0, which is to
restore a corrupted Template1.

Yeah.. This is why my first suggestion was to create a new template for
this purpose -- of which most responses told me about template0.

We already are to some extent, since pg_dump will dump its comment and
privileges, which it would not do for any other predefined object.
I think this is actually an implementation artifact rather than
something that was explicitly intended at the time, but since no one
has complained about it, it's probably a good thing ;-)

Does that mean your in favour of dumping a DROP SCHEMA public; command
if they have dropped their public schema? It's definitely not worth
doing it for any other "system" object due to upwards compatibility of
the dump files...

Chris

Actually, KL, that would solve a lot of these duplicate object problems. What
if pg_restore used Template0 and not Template1? It wouldn't fix the "drop
public schema" issue but it would solve the others.

Not sure what you mean here, but CVS pg_dump dumps like this:

CREATE DATABASE phppgadmin WITH TEMPLATE = template0 OWNER = chriskl
ENCODING = 'LATIN1';
CREATE DATABASE usa WITH TEMPLATE = template0 OWNER = usadmin ENCODING =
'LATIN1';

Chris

On Mon, 2004-07-19 at 21:20, Christopher Kings-Lynne wrote:

We already are to some extent, since pg_dump will dump its comment and
privileges, which it would not do for any other predefined object.
I think this is actually an implementation artifact rather than
something that was explicitly intended at the time, but since no one
has complained about it, it's probably a good thing ;-)

Does that mean your in favour of dumping a DROP SCHEMA public; command
if they have dropped their public schema? It's definitely not worth
doing it for any other "system" object due to upwards compatibility of
the dump files...

Please don't. It would be rather surprising to have stuff disappear from
a database while doing a restore -- especially if it's a CASCADE
operation.

Creating the public schema when needed and leaving it out by default is
preferable.

In article <1090256502.414.17.camel@jester>,
Rod Taylor <pg@rbt.ca> writes:

On Mon, 2004-07-19 at 12:36, Josh Berkus wrote:

Rod,

I think what we want is a clean template without all of the extras that
template1 has.

We have this, it's called Template0.

Doesn't work for me. I remove a number of things that are included by
default in template0, but yes, it's close.

I think pg_dump should do a kind of "diff" between template1 and the
database in question and include the necessary statements in the dump
to allow pg_restore to "replay" the diff after it has created the
database from template1.

KL,

CREATE DATABASE phppgadmin WITH TEMPLATE = template0 OWNER = chriskl
ENCODING = 'LATIN1';
CREATE DATABASE usa WITH TEMPLATE = template0 OWNER = usadmin ENCODING =
'LATIN1';

Ok, so that would come under the heading of "already fixed". Great.

--
Josh Berkus
Aglio Database Solutions
San Francisco