From: | "S(dot)Bob" <sbob(at)quadratum-braccas(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | pg_dump / pgrestore question - cannot restore a single schema |
Date: | 2020-03-23 23:47:07 |
Message-ID: | 5144169d-9913-2261-2ec0-3045679a8477@quadratum-braccas.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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)
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2020-03-24 00:40:09 | Re: pg_dump / pgrestore question - cannot restore a single schema |
Previous Message | Justin King | 2020-03-23 22:22:28 | Re: PG12 autovac issues |