pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump / pgrestore question - cannot restore a single schema
3+ messages / 3 participants
[nested] [flat]
* pg_dump / pgrestore question - cannot restore a single schema
@ 2020-03-23 23:47 S.Bob <sbob@quadratum-braccas.com>
0 siblings, 1 reply; 3+ messages in thread
From: S.Bob @ 2020-03-23 23:47 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_dump / pgrestore question - cannot restore a single schema
@ 2020-03-24 00:40 David G. Johnston <david.g.johnston@gmail.com>
parent: S.Bob <sbob@quadratum-braccas.com>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2020-03-24 00:40 UTC (permalink / raw)
To: S.Bob <sbob@quadratum-braccas.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_dump / pgrestore question - cannot restore a single schema
@ 2020-03-24 00:55 Holger Jakobs <holger@jakobs.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Holger Jakobs @ 2020-03-24 00:55 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 <sbob@quadratum-braccas.com
> <mailto: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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-03-24 00:55 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-03-23 23:47 pg_dump / pgrestore question - cannot restore a single schema S.Bob <sbob@quadratum-braccas.com>
2020-03-24 00:40 ` David G. Johnston <david.g.johnston@gmail.com>
2020-03-24 00:55 ` Holger Jakobs <holger@jakobs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox