Hi,
At my work, I have to dump a database and restore it on another database under
a specific schema. My first idea was to create the new schema on the old
database, move all the database objects on this new schema, dump the old db
and restore on the new one. But I have to move quite a lot of objets. So, I
took another way to do it : rename the public schema, dump and restore.
Pretty simple and straightforward.
After the "ALTER SCHEMA public RENAME TO foobar", pgAdmin can't see it
anymore. I made a patch to fix this, send a mail to the pgAdmin hackers and a
thread began on the right way to handle this. I was pretty sure I was right
but I'm not so sure anymore.
Apparently, I can rename all schemas, even system schemas !
metier=# alter schema pg_catalog rename to foobar;
ALTER SCHEMA
Doing so is a bit dumb because everything is now broken on this database. I
can't use psql, pg_dump, ... But I think this command should protect the user
from doing weird stuff. For example, DROP SCHEMA doesn't allow me to drop
pg_* schemas and that seems right to me.
So here it is. I think we should disallow user from renaming system schemas
and I think we should made clear if public is a system or public schema.
Regards.
--
Guillaume.
<!-- http://abs.traduc.org/ http://lfs.traduc.org/ http://traduc.postgresqlfr.org/ -->
