On Friday 20 Jun 2003 4:19 pm, _ wrote: > My understanding of "schema" that I discovered > in 7.3 (I don't think they were available before) > is that you can have two tables with the same name > if they are in different schemas. > > I have done a google search, as well as archive search > but > > pg_dump and pg_dumpall are broken if a database > contains schemas. > > First of all if there are two tables with the same > name in different schemas pg_dump only dumps out > one table. There is no way to dump other tables > and I have checked pg_dump man page
Works here - v7.3.2: SET search_path = public, pg_catalog; [begin pg_dump extract] -- -- Data for TOC entry 25 (OID 2263656) -- Name: bar; Type: TABLE DATA; Schema: public; Owner: richardh -- COPY bar (m) FROM stdin; 1.00 2345.00 99999999.00 \. SET search_path = richardh, pg_catalog; -- -- Data for TOC entry 26 (OID 2275041) -- Name: bar; Type: TABLE DATA; Schema: richardh; Owner: richardh -- COPY bar (a, b) FROM stdin; 1 aaa \. [end pg_dump extract] Did the user you pg_dumped as have visibility on your second schema? > Restoring a pg_dumpall is now a nightmare because > > I had as superuser > > # create schema test authorization httpd > > on a database not owned by database owner. > And it works merrily until the time to > dump and restore. > > pg_dumpall answers to above create authorization is > > \connect - httpd > > create schema test > > Hell breaks lose with that! Because httpd cannot > create schema on a database that it does not own. > Why couldn't pg_dumpall does > > create schema test authorization httpd Did you GRANT CREATE ON DATABASE for user httpd? That looks like what you need. > as superuser when the schema was created in that > fashion? > > I really don't think anyone is going to pay attention > to this rant since these list does not like/answer anonymous posts > but I have to post just so some poor soul might find > it in the archive and be warned. Always thought of the lists as welcoming myself, although I must admit anonymous posting is a bit odd. Especially when you could call yourself John Smith and no-one would be any the wiser. > My current versions are 7.3.2 and 7.3.3 and I have been using > posgres since 7.1 and consider myself experienced with postgres > > Schemas are the best thing since slice breads but > the baker decided to poison the bread. Nice! Let me know if this reply helps -- Richard Huxton ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend