Re: pg_dump/all doesn't output schemas correctly (v7.3.4) - Mailing list pgsql-bugs

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
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073