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)
