I'm having a problem restoring from a backup that may indicate a small
problem with pg_dump. I've included sample output below and a test
script. I'd appreciation confirmation that I'm reading the issue
correctly as well as any news of a fix.
SYSTEM:
postgresql 7.3.2, linux 2.4
PROBLEM:
pg_dump emits SET SESSION AUTHORIZATION before a user-owned schema is
created. During restore, the CREATE SCHEMA fails (presuming the owning
user doesn't have schema creation privileges). All subsequent
restoration to that schema fails as well, of course.
EXAMPLE OUTPUT DURING RESTORE
(you can recreate this with the script below and attached)
$ PGUSER=admin PGPASSWORD=xxx USER=rkh ./pgdtest
[...]
SET SESSION AUTHORIZATION 'rkh';
--
-- TOC entry 2 (OID 28785657)
-- Name: rkh; Type: SCHEMA; Schema: -; Owner: rkh
--
CREATE SCHEMA rkh;
psql:<stdin>:12: ERROR: t2: permission denied
SET search_path = rkh, pg_catalog;
psql:<stdin>:15: ERROR: Namespace "rkh" does not exist
--
-- TOC entry 3 (OID 28785658)
[...]
You can generate the above with the following script (same as attached):
#!/bin/sh -ex
# pgdtest -- script to demonstrate session auth problem when restoring
# non-admin schemas and tables from pg_dump-generated sql
# INVOKE LIKE THIS:
# $ PGUSER=<adminuser> PGPASSWORD=<adminpw> USER=<id> ./pgdtest
# and perhaps with PGHOST
# USER is the non-administrative login to use for testing
# /tmp/t1.pgdump will be created and contains the pgdump output
# Reece Hart <reece@in-machina.com>
# t1 will be the source db and we'll restore into t2
createdb t1;
createdb t2;
# create a user-owned schema and table in t1
psql -qa -dt1 -f- <<EOF
create schema $USER authorization $USER;
set session authorization $USER;
create table $USER.testtable (id integer);
\z
EOF
# now backup t1 directly into t2
pg_dump -X use-set-session-authorization t1 \
| tee /tmp/t1.pgdump \
| psql -Uadmin -qa -dt2 -f-
#(end of script)
--
Reece Hart, Ph.D. rkh@gene.com, http://www.gene.com/
Genentech, Inc. 650/225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93 http://www.in-machina.com/~reece/
South San Francisco, CA 94080-4990 reece@in-machina.com, GPG: 0x25EC91A0