From: | Ben Grimm <ben(at)zaeon(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | pg_dump/all doesn't output schemas correctly (v7.3.4) |
Date: | 2003-09-04 12:59:37 |
Message-ID: | 20030904125937.GA6187@zaeon.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-hackers |
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;
\.
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-09-04 15:58:33 | Re: Foreign key not working in some cases when using triggers |
Previous Message | Ben Grimm | 2003-09-04 12:38:52 | pg_dump/all doesn't output database ACLs (v7.3.4) |
From | Date | Subject | |
---|---|---|---|
Next Message | Jeroen Ruigrok/asmodai | 2003-09-04 14:02:19 | Potential bug in ALTER TABLE? |
Previous Message | Andrew Dunstan | 2003-09-04 12:32:52 | Re: TCP/IP with 7.4 beta2 broken? |