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;
\.
