pg_dump / pgrestore question - cannot restore a single schema

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

pg_dump / pgrestore question - cannot restore a single schema

S. Bob

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)
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump / pgrestore question - cannot restore a single schema

David G Johnston
On Mon, Mar 23, 2020 at 4:47 PM S.Bob <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: pg_dump / pgrestore question - cannot restore a single schema

Holger Jakobs-2

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

Am 24.03.20 um 01:40 schrieb David G. Johnston:
On Mon, Mar 23, 2020 at 4:47 PM S.Bob <[hidden email]> 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