Обсуждение: pg_restore -n sch1 : schema "sch1" does not exist
Hi,all:
I create a schema (sch1) in the database of db1, and I also create a table sch1.t1, then I use the comand "pg_dump -d db1 -Fd -f dir1" to back up the database of db1; at lase I drop the schema of sch1; I want to restore the schema sch1, but when I execute the command "pg_restore -d db1 -Fd dir1 -n sch1", get error, just like below.
I just want to test the parameter of "-n" for pg_restore, how can do to use the pg_restore correctly?
[wln@localhost test]$ pg_restore -d db1 -Fd dir1 -n sch1
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 171; 1259 24669 TABLE t1 wln
pg_restore: [archiver (db)] could not execute query: ERROR: permission denied to create "pg_catalog.t1"
DETAIL: System catalog modifications are currently disallowed.
Command was: CREATE TABLE t1 (
id integer
);
pg_restore: [archiver (db)] could not execute query: ERROR: schema "sch1" does not exist
Command was: ALTER TABLE sch1.t1 OWNER TO wln;
pg_restore: [archiver (db)] Error from TOC entry 2777; 0 24669 TABLE DATA t1 wln
pg_restore: [archiver (db)] could not execute query: ERROR: relation "t1" does not exist
Command was: COPY t1 (id) FROM stdin;
WARNING: errors ignored on restore: 3
[wln@localhost test]$ pg_restore -d db1 -Fd dir1 -n sch1 -l
;
; Archive created at Fri Sep 26 10:26:43 2014
; dbname: db1
; TOC Entries: 11
; Compression: -1
; Dump Version: 1.12-0
; Format: DIRECTORY
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 9.3beta2
; Dumped by pg_dump version: 9.3beta2
;
;
; Selected TOC Entries:
;
171; 1259 24669 TABLE sch1 t1 wln
2777; 0 24669 TABLE DATA sch1 t1 wln
Thanks,
wanglin
lin <jluwln@163.com> writes: > I create a schema (sch1) in the database of db1, and I also create a table sch1.t1, then I use the comand "pg_dump-d db1 -Fd -f dir1" to back up the database of db1; at lase I drop the schema of sch1; I want to restore the schemasch1, but when I execute the command "pg_restore -d db1 -Fd dir1 -n sch1", get error, just like below. > I just want to test the parameter of "-n" for pg_restore, how can do to use the pg_restore correctly? Well, you could adopt an actually supported release: > ; Dumped from database version: 9.3beta2 > ; Dumped by pg_dump version: 9.3beta2 I'm not just being snarky: the messages you're getting here remind me of a bug we fixed some time ago. But even aside from this specific complaint, the list of known data-loss bugs in 9.3beta should terrify you. Why in the world are you still using it? regards, tom lane
Are you sure it is a bug and you have fixed it? I test it with postgresql-9.4rcl, the result is the same with postgresql-9.3 beta2.
At 2014-12-09 23:54:01, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: >lin <jluwln@163.com> writes: >> I create a schema (sch1) in the database of db1, and I also create a table sch1.t1, then I use the comand "pg_dump -d db1 -Fd -f dir1" to back up the database of db1; at lase I drop the schema of sch1; I want to restore the schema sch1, but when I execute the command "pg_restore -d db1 -Fd dir1 -n sch1", get error, just like below. >> I just want to test the parameter of "-n" for pg_restore, how can do to use the pg_restore correctly? > >Well, you could adopt an actually supported release: > >> ; Dumped from database version: 9.3beta2 >> ; Dumped by pg_dump version: 9.3beta2 > >I'm not just being snarky: the messages you're getting here remind me of a >bug we fixed some time ago. But even aside from this specific complaint, >the list of known data-loss bugs in 9.3beta should terrify you. Why in >the world are you still using it? > > regards, tom lane > > >-- >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general
On 12/09/2014 07:29 AM, lin wrote: > Hi,all: > I create a schema (sch1) in the database of db1, and I also > create a table sch1.t1, then I use the comand "pg_dump -d db1 -Fd -f > dir1" to back up the database of db1; at lase I drop the schema of > sch1; I want to restore the schema sch1, but when I execute the > command "pg_restore -d db1 -Fd dir1 -n sch1", get error, just like below. > I just want to test the parameter of "-n" for pg_restore, how > can do to use the pg_restore correctly? Hmm, seems -n is not the same in pg_dump and pg_restore. If you do a dump with the -n switch you get the SCHEMA creation in the restore. If you use the -n on the restore you get the contained objects but not the SCHEMA creation statement. Might try generating a TOC from your dump and then pulling the entry for schema sch1 and put it at the top of the TOC you generated below. > > [wln@localhost test]$ pg_restore -d db1 -Fd dir1 -n sch1 > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 171; 1259 24669 TABLE > t1 wln > pg_restore: [archiver (db)] could not execute query: ERROR: permission > denied to create "pg_catalog.t1" > DETAIL: System catalog modifications are currently disallowed. > Command was: CREATE TABLE t1 ( > id integer > ); > > > > pg_restore: [archiver (db)] could not execute query: ERROR: schema > "sch1" does not exist > Command was: ALTER TABLE sch1.t1 OWNER TO wln; > > > pg_restore: [archiver (db)] Error from TOC entry 2777; 0 24669 TABLE > DATA t1 wln > pg_restore: [archiver (db)] could not execute query: ERROR: relation > "t1" does not exist > Command was: COPY t1 (id) FROM stdin; > > WARNING: errors ignored on restore: 3 > > > [wln@localhost test]$ pg_restore -d db1 -Fd dir1 -n sch1 -l > ; > ; Archive created at Fri Sep 26 10:26:43 2014 > ; dbname: db1 > ; TOC Entries: 11 > ; Compression: -1 > ; Dump Version: 1.12-0 > ; Format: DIRECTORY > ; Integer: 4 bytes > ; Offset: 8 bytes > ; Dumped from database version: 9.3beta2 > ; Dumped by pg_dump version: 9.3beta2 > ; > ; > ; Selected TOC Entries: > ; > 171; 1259 24669 TABLE sch1 t1 wln > 2777; 0 24669 TABLE DATA sch1 t1 wln > > > Thanks, > wanglin > > > > > > -- Adrian Klaver adrian.klaver@aklaver.com