Re: pg_restore -n sch1 : schema "sch1" does not exist

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