Обсуждение: public schema doubt
Hello,
I did a migration from 8.2.4 to 8.2.5, I used pg_dumpall to backup all the db and then restore it into 8.2.5. In my 8.2.4 db, I don't have public schema (it was dropped when I create the db, so only myschema is there), but when I restore to 8.2.5, I found that it created a public schema for me. So why it doing this for me? I thought it should restore back excatly the same what I backup from 8.2.4?
Should I just let the public schema there? Will it cause any problem? In the postgres doc http://www.postgresql.org/docs/8.2/interactive/ddl-schemas.html (5.7.2) it said if I create a table without specify any schema name, it will put it into public schema, so I test it in my case, but it not doing it, the table is create under myschema, strange. And I check the search_path, it show "$user",public .
Could someone explain it to me?
Thanks
Louis
On Nov 16, 2007 8:56 AM, <mailtolouis2020-postgres@yahoo.com> wrote: > > > Hello, > > I did a migration from 8.2.4 to 8.2.5, Just FYI, from 8.2.4 to 8.2.5 doesn't require a dump / restore, you can update minor versions in place. > I used pg_dumpall to backup all the > db and then restore it into 8.2.5. In my 8.2.4 db, I don't have public > schema (it was dropped when I create the db, so only myschema is there), but > when I restore to 8.2.5, I found that it created a public schema for me. A freshly created database has a public schema by default, because it is created form the template1 database which has a public schema. If you want a new database to have a different schema than public, you can make that change to template1 and your new dbs will have that setup. > Should I just let the public schema there? Will it cause any problem? Generally, no. > In the > postgres doc http://www.postgresql.org/docs/8.2/interactive/ddl-schemas.html > (5.7.2) it said if I create a table without specify any schema name, it will > put it into public schema, so I test it in my case, but it not doing it, the > table is create under myschema, strange. And I check the search_path, it > show "$user",public . Then the user you're creating the table with has a different search_path set than the default one of 'public',$user. Most likely the result of an alter user command. Could also be an alter database command.
Hello Martin,
Yes, SHOW search_path show this
"$user",public .
Oh yes, I get it now, the "$user" will take priority.
Another question, this public schema, in the usual practice way, do you delete it or just leave it there and create your own schema?
Regards
Louis
----- Original Message ----
From: Martin Gainty <mgainty@hotmail.com>
To: mailtolouis2020-postgres@yahoo.com
Sent: Friday, November 16, 2007 3:17:40 PM
Subject: RE: [GENERAL] public schema doubt
.hmmessage P
{
margin:0px;padding:0px;}
body.hmmessage
{
FONT-SIZE:10pt;FONT-FAMILY:Tahoma;}
Hello Louis
SHOW search_path
if public is not on search_path or does'nt come first then
SET search_path TO public,$user;
HTH/
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.
Boo! Scare away worms, viruses and so much more! Try Windows Live OneCare! Try now!
From: Martin Gainty <mgainty@hotmail.com>
To: mailtolouis2020-postgres@yahoo.com
Sent: Friday, November 16, 2007 3:17:40 PM
Subject: RE: [GENERAL] public schema doubt
SHOW search_path
if public is not on search_path or does'nt come first then
SET search_path TO public,$user;
HTH/
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.
.ExternalClass DIV {} Hello,I did a migration from 8.2.4 to 8.2.5, I used pg_dumpall to backup all the db and then restore it into 8.2.5. In my 8.2.4 db, I don't have public schema (it was dropped when I create the db, so only myschema is there), but when I restore to 8.2.5, I found that it created a public schema for me. So why it doing this for me? I thought it should restore back excatly the same what I backup from 8.2.4?Should I just let the public schema there? Will it cause any problem? In the postgres doc http://www.postgresql.org/docs/8.2/interactive/ddl-schemas.html (5.7.2) it said if I create a table without specify any schema name, it will put it into public schema, so I test it in my case, but it not doing it, the table is create under myschema, strange. And I check the search_path, it show "$user",public .Could someone explain it to me?ThanksLouis
Boo! Scare away worms, viruses and so much more! Try Windows Live OneCare! Try now!
On Nov 16, 2007 10:26 AM, <mailtolouis2020-postgres@yahoo.com> wrote: > > > Hello Martin, > > Yes, SHOW search_path show this > "$user",public . > > Oh yes, I get it now, the "$user" will take priority. > > Another question, this public schema, in the usual practice way, do you > delete it or just leave it there and create your own schema? I leave it in place. IT doesn't hurt anything really.
On Fri, Nov 16, 2007 at 11:01:17AM -0600, Scott Marlowe wrote: > > Another question, this public schema, in the usual practice way, do you > > delete it or just leave it there and create your own schema? > > I leave it in place. IT doesn't hurt anything really. Alternativly, you can revoke all permissions from it... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy