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
