pg_dumpall (7.3) 'public' schema bug
Hi,
Went to upgrade from postgresql (RedHat's postgresql
rh-postgresql-7.3.6-7) to Fedora core 3 postgresql
7.4.6-1 and encountered a problem. If nothing else this
is worth a note on the 7.4 upgrade doc page.
I deleted the 'public' schema from my databases
in 7.3, now in 7.4 they are back. I suppose it is remotely
possible that I really didn't delete 'public' in 7.3,
I can't recall the last time I looked, but I'm sure
I must have checked at the time I deleted public.
Ran pg_dumpall on 7.3. After feeding the ouput to psql
on 7.4 I got:
$ psql babase
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
babase=> select * from pg_namespace;
nspname | nspowner
|
nspacl
--------------------+----------+---------------------------------------------------------------------------------------------------------------
pg_toast | 1 | pg_temp_1 | 1 |
pg_catalog | 1 | {postgres=U*C*/postgres,=U/postgres}
public | 1 | {postgres=U*C*/postgres,=UC/postgres}
information_schema | 1 | {postgres=U*C*/postgres,=U/postgres}
babase | 100 |
{babase_admin=U*C*/babase_admin,"group
babase_editors=U/babase_admin","group babase_readers=U/babase_admin"}
sandbox | 100 |
{babase_admin=U*C*/babase_admin,"group
babase_editors=UC/babase_admin","group babase_readers=U/babase_admin"}
(7 rows)
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
Import Notes
Reply to msg id not found: 20041115092722.C21241@mofo.meme.comReference msg id not found: 20041115092722.C21241@mofo.meme.com
"Karl O. Pinc" <kop@meme.com> writes:
I deleted the 'public' schema from my databases
in 7.3, now in 7.4 they are back.
IMHO this is not a bug. It is not pg_dump's charter to remove
system-created objects...
regards, tom lane
On 2004.11.16 16:25 Tom Lane wrote:
"Karl O. Pinc" <kop@meme.com> writes:
I deleted the 'public' schema from my databases
in 7.3, now in 7.4 they are back.IMHO this is not a bug. It is not pg_dump's charter to remove
system-created objects...
I can live with this, but I find the implication distasteful --
that there is no way to move a database from one cluster
to another and have an exact copy. Not even when both
clusters are running the same release.
I don't care that much about the behavior, it's easy enough
to delete 'public'. I do think that a note should be
made in the administrator manual regards system upgrades
where pg_dump(all) scripts are given if this is going to be
the behavior.
Thanks for postgresql.
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
Karl,
I don't care that much about the behavior, it's easy enough
to delete 'public'. I do think that a note should be
made in the administrator manual regards system upgrades
where pg_dump(all) scripts are given if this is going to be
the behavior.
This isn't isolated to the "public" schema. In fact, anything which is in
the template database (usually template1) will be in the database you reload,
even if it wasn't in the original database. The result is that when you try
to remove built-in objects that ship with PostgreSQL, they are "replaced" on
a new migration server. pg_dump isn't capable of working around this, nor
should it be.
Search the archives of -Hackers mailing list for this issue; a few
workarounds were suggested.
--
Josh Berkus
Aglio Database Solutions
San Francisco
Import Notes
Reply to msg id not found: 20041117193934.A28503A43BA@svr1.postgresql.orgReference msg id not found: 20041117193934.A28503A43BA@svr1.postgresql.org | Resolved by subject fallback
On Wed, Nov 17, 2004 at 11:53:10 -0800,
Josh Berkus <josh@agliodbs.com> wrote:
Karl,
I don't care that much about the behavior, it's easy enough
to delete 'public'. �I do think that a note should be
made in the administrator manual regards system upgrades
where pg_dump(all) scripts are given if this is going to be
the behavior.This isn't isolated to the "public" schema. In fact, anything which is in
the template database (usually template1) will be in the database you reload,
even if it wasn't in the original database. The result is that when you try
to remove built-in objects that ship with PostgreSQL, they are "replaced" on
a new migration server. pg_dump isn't capable of working around this, nor
should it be.Search the archives of -Hackers mailing list for this issue; a few
workarounds were suggested.
I am pretty sure that the last time this was discussed, it was pointed out
that pg_dump(all) and pg_restore are relative to template0, not template1.
(Though by default template1 will be the same as template0.)