If you dump a table with -t schema.table, and in the receiving database that
schema does not
exist, pg_restore-9.3devel will restore into the pg_catalog schema:
HEAD
$ cat test.sh
#!/bin/sh
db=backupbug;
dropdb --echo $db;
createdb --echo $db;
echo "drop schema if exists s cascade;" | psql -ad $db
echo "create schema s;" | psql -ad $db
echo "create table s.t as select i from generate_series(1,10) as f(i);" | psql
-ad $db
echo '\dt+ pg_catalog.t' | psql -ad $db
pg_dump -F c -t s.t -f st.dump $db
echo "drop schema if exists s cascade;" | psql -ad $db
pg_restore -xOv -F c -d $db st.dump
echo '\dn' | psql -ad $db
echo '\dt+ s.' | psql -ad $db
echo '\dt+ pg_catalog.t' | psql -ad $db
output:
$ ./test.sh
DROP DATABASE backupbug;
CREATE DATABASE backupbug;
drop schema if exists s cascade;
DROP SCHEMA
create schema s;
CREATE SCHEMA
create table s.t as select i from generate_series(1,1000) as f(i);
SELECT 1000
\dt+ pg_catalog.t
No matching relations found.
drop schema if exists s cascade;
DROP SCHEMA
pg_restore: connecting to database for restore
pg_restore: creating TABLE t
pg_restore: processing data for table "t"
pg_restore: setting owner and privileges for TABLE t
pg_restore: setting owner and privileges for TABLE DATA t
\dn
List of schemas
Name | Owner
--------+----------
public | aardvark
(1 row)
\dt+ s.
No matching relations found.
\dt+ pg_catalog.t
List of relations
Schema | Name | Type | Owner | Size | Description
------------+------+-------+----------+-------+-------------
pg_catalog | t | table | aardvark | 64 kB |
(1 row)
#----------------------
And then adds insult to injury:
backupbug=# drop table pg_catalog.t;
ERROR: permission denied: "t" is a system catalog
Off course the workaround is obvious, but shouldn't this be prevented from
happening in the first
place? 9.2 refuses to do such a restore, which seems much better.
(and yes, I did restore a 65 GB table into the pg_catalog schema of a dev
machine; how can I
remove it? I could initdb, but it's 200+ GB; I'd rather not have to rebuild it)
Thanks,
Erik Rijkers
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
