Re: pg_restore cannot restore index - Mailing list pgsql-sql
From | Jie Liang |
---|---|
Subject | Re: pg_restore cannot restore index |
Date | |
Msg-id | 7C760DAA511DC74B99E7D22189F786F101BF2118@MAIL01.stbernard.com Whole thread Raw |
In response to | pg_restore cannot restore index (Jie Liang <jie@stbernard.com>) |
Responses |
Re: pg_restore cannot restore index
(Bruce Momjian <pgman@candle.pha.pa.us>)
|
List | pgsql-sql |
Thanks! But I did not make long form works also, is it: pg_restore --index="\"indexname\"" --dbname=mydb mydumpfile ??????????? msg: pg_restore:[archiver] could open input file: No such file or directory could you give out a example of long form???? Thanks again. Jie Liang -----Original Message----- From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] Sent: Tuesday, July 09, 2002 7:59 PM To: Jie Liang Cc: 'Jan Wieck'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org' Subject: Re: pg_restore cannot restore index Yep, documentation is wrong. Documentation patch attached and applied. Also, in 7.3 you will not need the weird quoting for objects. --------------------------------------------------------------------------- Jie Liang wrote: > Another possible bug: > pg_restore -i "\"indexname\"" -d mydb mydumpfile > msg: > pg_restore: connecting to database for restore > pg_restore: creating FUNCTION "plpgsql_call_handler" () > pg_restore: [archiver (db)] could not execute query: ERROR: function > plpgsql_call_handler already exists with same argument types > pg_restore: *** aborted because of error > > I read the pg_restore.c source code, I found: > #ifdef HAVE_GETOPT_LONG > struct option cmdopts[] = { > {"clean", 0, NULL, 'c'}, > {"create", 0, NULL, 'C'}, > {"data-only", 0, NULL, 'a'}, > {"dbname", 1, NULL, 'd'}, > {"file", 1, NULL, 'f'}, > {"format", 1, NULL, 'F'}, > {"function", 1, NULL, 'P'}, > {"host", 1, NULL, 'h'}, > {"ignore-version", 0, NULL, 'i'}, > {"index", 1, NULL, 'I'}, > So, -i may be mapped wrong, however, -I is illegal option. > > Thanks! > > > Jie Liang > > > > -----Original Message----- > From: Jie Liang [mailto:jie@stbernard.com] > Sent: Wednesday, July 03, 2002 12:03 PM > To: 'Jan Wieck'; Jie Liang > Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org' > Subject: Re: [SQL] pg_restore cannot restore function > > > > OK, we figured it out. > The problem is the documentation confused me!!! > In man page of pg_restore: > -P function-name > --function=function name > Specify a procedure or function to be restored. > > User will assume that syntax of restoring a function is same as > restoring a table, but it's not true, it's slightly different. > To restore a table: > pg_restore -Rxt mytable -d mydb2 dbf > works, but to restore a function: > pg_restore -P myfunction -d mydb2 dbf > won't work, and you need to use: > pg_restore -P "\"myfunction\" (args and type)" -d mydb2 dbf > to make it work!!!!! > > > I believe that the man page of pg_restore should be improved. > > > Thanks. > > > > Jie Liang > > > > -----Original Message----- > From: Jan Wieck [mailto:JanWieck@Yahoo.com] > Sent: Monday, July 01, 2002 11:14 AM > To: Jie Liang > Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org' > Subject: Re: [SQL] pg_restore cannot restore function > > > Jie Liang wrote: > > > > Oops,my OS is FreeBSD4.3 PostgreSQL7.2 > > I cannot see such an error message in the pg_restore sources at all. Are > you sure to use the right versions together? > > > Jan > > > > > Thanks > > > > Jie Liang > > > > -----Original Message----- > > From: Jie Liang > > Sent: Friday, June 28, 2002 1:46 PM > > To: 'Jan Wieck' > > Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org' > > Subject: RE: [SQL] pg_restore cannot restore function > > > > No any error msg in the logfile, I didn't see any create function > statement > > in my logfile which I enabled the query log. > > This function is written in PL/pgSQL which is enabled in target db, > > If I pg_dump the schema into a plain text file, I can see its defination > > there, I can easily copy & paste (restore) it into mydb2. > > however, I failed to restore it by using flag -P with compressed file. > > I also tried to use > > su postgres -c "/usr/local/pgsql/bin/pg_restore --function=myfunction > > --dbname=mydb2 dbf" > > error msg > > pg_restore: [archiver] could not open input file: No such file or > directory > > > > weird??? > > > > I use > > pg_restore -Rxt mytable -d mydb2 dbf > > have no such a problem, it works. > > > > Is any syntax error?? > > I am confused by documentation now! > > Is it a bug???? > > > > Thanks > > > > Jie Liang > > > > -----Original Message----- > > From: Jan Wieck [mailto:JanWieck@Yahoo.com] > > Sent: Friday, June 28, 2002 12:39 PM > > To: Jie Liang > > Cc: 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org' > > Subject: Re: [SQL] pg_restore cannot restore function > > > > Jie Liang wrote: > > > > > > I use > > > pg_dump -Fc mydb > dbf > > > then I create another db by: > > > createdb mydb2 > > > I use > > > pg_restore -P myfunction -d mydb2 dbf > > > > > > cannot restore myfunction into mydb2 > > > > > > why?????? > > > > Good question. Is there any error message in the postmaster log? > > > > If the function is written in a procedural language, is that language > > enabled in the target database? If the function is written in the SQL > > language, do all underlying objects like tables and views exist? If it's > > a C language function, does the shared object containing the function > > exist at the expected location? > > > > Jan > > > > -- > > > > #======================================================================# > > # It's easier to get forgiveness for being wrong than for being right. # > > # Let's break this rule - forgive me. # > > #================================================== JanWieck@Yahoo.com # > > -- > > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #================================================== JanWieck@Yahoo.com # > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026