Re: pg_dump/all doesn't output schemas correctly (v7.3.4)

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Ben Grimm <ben(at)zaeon(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: pg_dump/all doesn't output schemas correctly (v7.3.4)
Date: 2003-09-26 17:52:18
Message-ID: 200309261752.h8QHqIe18765@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers


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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tomas Szepe 2003-09-26 18:15:40 Re: Postgresql 'eats' all mi data partition
Previous Message Bruce Momjian 2003-09-26 16:34:08 Re: pg 7.4beta1 doc bug: vacuum not updated

Browse pgsql-hackers by date

  From Date Subject
Next Message Patrick Welche 2003-09-26 17:56:35 Re: 2-phase commit
Previous Message Marc G. Fournier 2003-09-26 17:49:30 Re: 2-phase commit