Thread: pg_restore cannot restore function
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?????? Jie Liang
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 #
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 #
Oops,my OS is FreeBSD4.3 PostgreSQL7.2 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 #
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 #
I am sure. I assume that pg_restore -t mytable -d mydb mydumpfile is same as pg_restore --table=mytable --dbname=mydb mydumpfile but it is not! the 2nd one will get: pg_restore: [archiver] could not open input file: No such file or directory 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 #
In the case that you moved your backup to another system where possibly the shared library (.so) where the function exists is on a different location then thats the problem, in which case you only need to recreate the function (with the same isstrict,iscachable attributes). -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr
This is not the case, because those db on a same server, it's I dump data from one db and try restore one of it function into another db. Thanks for your response anyway. Jie Liang -----Original Message----- From: Achilleus Mantzios [mailto:achill@matrix.gatewaynet.com] Sent: Wednesday, July 03, 2002 12:52 AM To: Jie Liang Cc: 'Jan Wieck'; 'Bruce Momjian'; 'admin@postgresql.org'; 'pgsql-sql@postgresql.org' Subject: Re: [SQL] pg_restore cannot restore function In the case that you moved your backup to another system where possibly the shared library (.so) where the function exists is on a different location then thats the problem, in which case you only need to recreate the function (with the same isstrict,iscachable attributes). -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
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 #