Обсуждение: pg_dump / pgrestore question - cannot restore a single schema
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)
However if I try to restore only one of the schemas it does not work, seems like it does not create the schema first:
You could also extract the commands for creating the schema contained in the dump using the following commands:
schemaname=whatever_your_schemaname_is
pg_restore -l /tmp/dump | grep "SCHEMA - $schemaname" > /tmp/objects_to_re-create
pg_restore -L /tmp/objects_to_re-create -f - /tmp/my_custom_dump | psql
and then
pg_restore -n "$schemaname" /tmp//tmp/my_custom_dump | psql
in order to re-create the contents of the schema.
Regards,
Holger
On Mon, Mar 23, 2020 at 4:47 PM S.Bob <sbob@quadratum-braccas.com> wrote:However if I try to restore only one of the schemas it does not work, seems like it does not create the schema first:
Since you have to know the schema name anyway in order to restore that schema only just go ahead an create the schema before performing the restore. It seems that since a schema is not "in itself" it doesn't get restored. Doesn't seem like a problem.David J.
-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012