Обсуждение: pg_dumpall

On Wed, 19 Feb 2003, Donald Fraser wrote:

> PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.96
>
> I issue the following command:
> CREATE DATABASE "CISX" WITH OWNER = postgres TEMPLATE = template1 ENCODING = 'SQL_ASCII';
>
> When I do a backup from the database using pg_dumpall (with no command line options) it creates the following:
> CREATE DATABASE "CISX" WITH OWNER = postgres TEMPLATE = template0 ENCODING = 'SQL_ASCII';
>
> I don't know whether this is a bug or not as I can't find any hard documentation to say otherwise?

I'd guess not, because the objects you copied from template1 when making
the database are part of the dumpall output.  If you made it from
template1 after the backup you'd get anything that was in the new
template1 plus all of the old objects.

"Donald Fraser" <demolish@cwgsy.net> writes:
> When I do a backup from the database using pg_dumpall (with no command line=
>  options) it creates the following:
> CREATE DATABASE "CISX" WITH OWNER =3D postgres TEMPLATE =3D template0 ENCOD=
> ING =3D 'SQL_ASCII';

> I don't know whether this is a bug or not

It is not; the use of template0 is intentional.  (Otherwise, whatever
local additions may exist in template1 would get duplicated.)

            regards, tom lane

> On Wed, 19 Feb 2003, Donald Fraser wrote:
>
> > PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.96
> >
> > I issue the following command:
> > CREATE DATABASE "CISX" WITH OWNER = postgres TEMPLATE = template1
ENCODING = 'SQL_ASCII';
> >
> > When I do a backup from the database using pg_dumpall (with no command
line options) it creates the following:
> > CREATE DATABASE "CISX" WITH OWNER = postgres TEMPLATE = template0
ENCODING = 'SQL_ASCII';
> >
> > I don't know whether this is a bug or not as I can't find any hard
documentation to say otherwise?
>
> I'd guess not, because the objects you copied from template1 when making
> the database are part of the dumpall output.  If you made it from
> template1 after the backup you'd get anything that was in the new
> template1 plus all of the old objects.
>
>

Ok I understand now... I haven't been using template1 with non-pg_catalog
objects.
My question now would be, is there an easy way to place your own objects
into pg_catalog for template0? (In advance of why... it is much easier to
apply any upgrades / patches to my own customisations when they are not
backed up into the standard backup - otherwise you have to manually edit the
backup file which is much harder to do than apply a patch to the template.)

Regards Donald

"Donald Fraser" <demolish@cwgsy.net> writes:
> My question now would be, is there an easy way to place your own objects
> into pg_catalog for template0?

Changing template0 is exactly what you are NOT supposed to do, as it
breaks what pg_dump is trying to accomplish.

However, you could probably just edit the dump file to replace the
references to template0 with template1.  When you reload the dump,
you will get lots of errors as the customized objects coming in from
the dump collide with the ones copied from template1 --- but that's
what you want.

            regards, tom lane