Re: pg_restore cannot restore index - Mailing list pgsql-sql
From | Bruce Momjian |
---|---|
Subject | Re: pg_restore cannot restore index |
Date | |
Msg-id | 200207100258.g6A2wn618602@candle.pha.pa.us Whole thread Raw |
In response to | pg_restore cannot restore index (Jie Liang <jie@stbernard.com>) |
List | pgsql-sql |
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, Pennsylvania 19026 Index: pg_restore.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/ref/pg_restore.sgml,v retrieving revision 1.26 diff -c -r1.26 pg_restore.sgml *** pg_restore.sgml 4 Jul 2002 03:04:54 -0000 1.26 --- pg_restore.sgml 10 Jul 2002 02:57:12 -0000 *************** *** 28,34 **** <arg> -d <replaceable class="parameter">dbname</replaceable> </arg> <arg> -f <replaceable class="parameter">output-file</replaceable> </arg> <arg> -F <replaceable class="parameter">format</replaceable> </arg> ! <arg> -i <replaceable class="parameter">index</replaceable> </arg> <arg> -l </arg> <arg> -L <replaceable class="parameter">contents-file</replaceable> </arg> <group> <arg> -N </arg> <arg> -o </arg> <arg> -r </arg> </group> --- 28,35 ---- <arg> -d <replaceable class="parameter">dbname</replaceable> </arg> <arg> -f <replaceable class="parameter">output-file</replaceable> </arg> <arg> -F <replaceable class="parameter">format</replaceable> </arg> ! <arg> -i </arg> ! <arg> -I <replaceable class="parameter">index</replaceable> </arg> <arg> -l </arg> <arg> -L <replaceable class="parameter">contents-file</replaceable> </arg> <group> <arg> -N </arg> <arg> -o </arg> <arg> -r </arg> </group> *************** *** 210,220 **** </varlistentry> <varlistentry> ! <term><option>-i <replaceable class="parameter">index</replaceable></option></term> ! <term><option>--index=<replaceable class="parameter">index</replaceable></option></term> <listitem> <para> ! Restore definition for named <replaceable class="parameter">index</replaceable> only. </para> </listitem> </varlistentry> --- 211,221 ---- </varlistentry> <varlistentry> ! <term><option>-i </term> ! <term><option>--ignore-version</term> <listitem> <para> ! Ignore database version checks. </para> </listitem> </varlistentry>