Re: pg_dump bug fixing

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.