This has been fixed in the current CVS snapshot and will be in the next 7.4 beta. Thanks.
--------------------------------------------------------------------------- Ben Grimm wrote: > I haven't tried the 7.4 beta, so it may be fixed there - but in > 7.3.4, pg_dumpall doesn't generate the commands to create schemas > in the right order. This bug may have been reported before, but > I saw no response to it in the lists. > > Try this in a fresh database after an initdb: > > template1=# create user test nocreatedb nocreateuser; > CREATE USER > template1=# create database testdb; > CREATE DATABASE > template1=# \c testdb > You are now connected to database testdb. > testdb=# create schema authorization test; > CREATE SCHEMA > testdb=# set session authorization test; > SET > testdb=# set search_path="test"; > SET > testdb=# create table abc (); > CREATE TABLE > template1=# \q > testdb=# \q > > $ pg_dumpall -U postgres > -- > -- PostgreSQL database cluster dump > -- > > \connect "template1" > > -- > -- Users > -- > > DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE > datname = 'template0'); > > CREATE USER test WITH SYSID 100 NOCREATEDB NOCREATEUSER; > > > -- > -- Groups > -- > > DELETE FROM pg_group; > > > > -- > -- Database creation > -- > > CREATE DATABASE testdb WITH OWNER = postgres TEMPLATE = template0 ENCODING = > 'SQL_ASCII'; > > > \connect template1 > -- > -- PostgreSQL database dump > -- > > -- > -- TOC entry 2 (OID 1) > -- Name: DATABASE template1; Type: COMMENT; Schema: -; Owner: > -- > > COMMENT ON DATABASE template1 IS 'Default template database'; > > > \connect testdb > -- > -- PostgreSQL database dump > -- > > SET SESSION AUTHORIZATION 'test'; > > -- > -- TOC entry 2 (OID 16977) > -- Name: test; Type: SCHEMA; Schema: -; Owner: test > -- > > ************************************************************************* > This will fail because user 'test' has not been granted > create on the database (which pg_dump also fails to output, > but that's a separate bug) It should create the schema as > the superuser, then switch to the use to create tables within > that schema. > ************************************************************************* > > CREATE SCHEMA test; > > > SET search_path = test, pg_catalog; > > -- > -- TOC entry 3 (OID 16978) > -- Name: abc; Type: TABLE; Schema: test; Owner: test > -- > > CREATE TABLE abc ( > ); > > > -- > -- Data for TOC entry 4 (OID 16978) > -- Name: abc; Type: TABLE DATA; Schema: test; Owner: test > -- > > COPY abc FROM stdin; > \. > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > -- Bruce Momjian | http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
