Thread: pg_dumpall
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?
There seems to be some kind of indication that this is the case for pg_dump but it's not clear.
Can someone clarify this, am I missing something obvious here?
If this is the case will there be a possible fix for this in the future?
Regards Donald Fraser.
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