pg_dump / pgrestore question - cannot restore a single schema - Mailing list pgsql-admin

All

I have a question about pg_restore:

I created a database named pgbench

I ran pgbench and created the pgbench tables, then I created 2 new schemas and ran several alter table statements to move the tables into the new schemas. Then I ran another pgbench run into the public schema. The table layout looked like this:

Here are my schemas:

pgbench=# \dn
    List of schemas
    Name    |  Owner  
------------+----------
 bench_sch  | postgres
 mytest_sch | postgres
 public     | postgres
(3 rows)

And the tables:

pgbench=# select schemaname, tablename from pg_tables where schemaname in ('public', 'bench_sch', 'mytest_sch');
 schemaname |    tablename    
------------+------------------
 bench_sch  | pgbench_tellers
 mytest_sch | pgbench_accounts
 mytest_sch | pgbench_branches
 mytest_sch | pgbench_history
 public     | pgbench_accounts
 public     | pgbench_branches
 public     | pgbench_history
 public     | pgbench_tellers
(8 rows)

Then I created a database dump with pg_dump:

$ pg_dump -Fc pgbench > pgbench.Fc.dmp

I can restore the entire db like this and it works as expected:

$ dropdb pgbench

$ createdb pgbench

$ pg_restore -Fc -d pgbench pgbench.Fc.dmp

$ psql pgbench
psql (12.2)
Type "help" for help.

pgbench=# select schemaname, tablename from pg_tables where schemaname in ('public', 'bench_sch', 'mytest_sch');
 schemaname |    tablename    
------------+------------------
 bench_sch  | pgbench_tellers
 mytest_sch | pgbench_accounts
 mytest_sch | pgbench_branches
 mytest_sch | pgbench_history
 public     | pgbench_accounts
 public     | pgbench_branches
 public     | pgbench_history
 public     | pgbench_tellers
(8 rows)

However if I try to restore only one of the schemas it does not work, seems like it does not create the schema first:

$ dropdb pgbench

$ createdb pgbench

$ pg_restore -Fc -d pgbench -n mytest_sch pgbench.Fc.dmp

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 205; 1259 16971 TABLE pgbench_accounts postgres
pg_restore: error: could not execute query: ERROR:  schema "mytest_sch" does not exist
LINE 1: CREATE TABLE mytest_sch.pgbench_accounts (
                     ^
Command was: CREATE TABLE mytest_sch.pgbench_accounts (
    aid integer NOT NULL,
    bid integer,
    abalance integer,
    filler character(84)
)
WITH (fillfactor='100');


pg_restore: error: could not execute query: ERROR:  schema "mytest_sch" does not exist
Command was: ALTER TABLE mytest_sch.pgbench_accounts OWNER TO postgres;

pg_restore: from TOC entry 206; 1259 16974 TABLE pgbench_branches postgres
pg_restore: error: could not execute query: ERROR:  schema "mytest_sch" does not exist
LINE 1: CREATE TABLE mytest_sch.pgbench_branches (
                     ^
Command was: CREATE TABLE mytest_sch.pgbench_branches (
    bid integer NOT NULL,
    bbalance integer,
    filler character(88)
)
WITH (fillfactor='100');


pg_restore: error: could not execute query: ERROR:  schema "mytest_sch" does not exist
Command was: ALTER TABLE mytest_sch.pgbench_branches OWNER TO postgres;

pg_restore: from TOC entry 207; 1259 16977 TABLE pgbench_history postgres
pg_restore: error: could not execute query: ERROR:  schema "mytest_sch" does not exist
LINE 1: CREATE TABLE mytest_sch.pgbench_history (
                     ^
Command was: CREATE TABLE mytest_sch.pgbench_history (
    tid integer,
    bid integer,
    aid integer,
    delta integer,
    mtime timestamp without time zone,
    filler character(22)
);


pg_restore: error: could not execute query: ERROR:  schema "mytest_sch" does not exist
Command was: ALTER TABLE mytest_sch.pgbench_history OWNER TO postgres;

pg_restore: from TOC entry 3943; 0 16971 TABLE DATA pgbench_accounts postgres
pg_restore: error: could not execute query: ERROR:  schema "mytest_sch" does not exist
Command was: COPY mytest_sch.pgbench_accounts (aid, bid, abalance, filler) FROM stdin;
pg_restore: from TOC entry 3944; 0 16974 TABLE DATA pgbench_branches postgres
pg_restore: error: could not execute query: ERROR:  schema "mytest_sch" does not exist
Command was: COPY mytest_sch.pgbench_branches (bid, bbalance, filler) FROM stdin;
pg_restore: from TOC entry 3945; 0 16977 TABLE DATA pgbench_history postgres
pg_restore: error: could not execute query: ERROR:  schema "mytest_sch" does not exist
Command was: COPY mytest_sch.pgbench_history (tid, bid, aid, delta, mtime, filler) FROM stdin;
pg_restore: from TOC entry 3807; 2606 16995 CONSTRAINT pgbench_accounts pgbench_accounts_pkey postgres
pg_restore: error: could not execute query: ERROR:  schema "mytest_sch" does not exist
Command was: ALTER TABLE ONLY mytest_sch.pgbench_accounts
    ADD CONSTRAINT pgbench_accounts_pkey PRIMARY KEY (aid);


pg_restore: from TOC entry 3809; 2606 16997 CONSTRAINT pgbench_branches pgbench_branches_pkey postgres
pg_restore: error: could not execute query: ERROR:  schema "mytest_sch" does not exist
Command was: ALTER TABLE ONLY mytest_sch.pgbench_branches
    ADD CONSTRAINT pgbench_branches_pkey PRIMARY KEY (bid);


pg_restore: warning: errors ignored on restore: 11

Thanks in advance for any help...

-- 
`When you say "I wrote a program that crashed Windows", people just stare at you blankly and say "Hey, I got those with the system, *for free*".' (By Linus Torvalds)