Обсуждение: pg_restore error

Hi all,

When doing a pg_restore from a pg_dump of a database, I am seeing the
following error:
pg_restore: [archiver (db)] could not execute query: ERROR:  stat failed on
file '/usr/local/pgsql-7.2/lib/plpgsql': No such file or directory

I am currently on 7.2.1 and the software location has changed (to
/usr/local/pgsql).  And, further research shows that the pg_proc shows:
 plpgsql_call_handler  |        1 |      13 | f        | t            | f
| f           |        0 | f         |          0 |             |
100 |
       0 |              0 |            100 | plpgsql_call_handler  |
/usr/local/pgsql-7.2/lib/plpgsql

This shows /usr/local/pgsql-7.2/lib/plpgsql for the "probin" column.

How should an update of software be handled as per pg_proc?  I.E. should
all the locations recorded in pg_proc be updated to use the new location of
the pgsql software?  Is there an update script that will do this already?

Are there other parts that will need updating due to the change in the
software (directory)?

Thanks,

--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...

Laurette Cisneros <laurette@nextbus.com> writes:
> I am currently on 7.2.1 and the software location has changed (to
> /usr/local/pgsql).  And, further research shows that the pg_proc shows:
> This shows /usr/local/pgsql-7.2/lib/plpgsql for the "probin" column.

> How should an update of software be handled as per pg_proc?

The preferred contents of probin are now like
    $libdir/plpgsql
(the dollar sign is literal text here).  As of 7.2 the server will
substitute a suitable directory name for "$libdir".  You probably
have a database that was reloaded from an old dump without taking
advantage of this feature --- before 7.2 you could only use an
absolute path in probin.  Now, absolute paths are deprecated for exactly
the reason that they make version updates hard.

Note the lack of a ".so" in that probin entry, too.  This is also
a recommended new practice in 7.2 --- no reason to put
platform-dependent assumptions about shared library extensions into
your dumps.

            regards, tom lane

Ah.  Makes sense.  Since this is but our "restore" system, I will re-do the
server from scratch to ensure the right probin entries.

Thanks for the help and info.

L.
On Tue, 3 Sep 2002, Tom Lane wrote:

> Laurette Cisneros <laurette@nextbus.com> writes:
> > I am currently on 7.2.1 and the software location has changed (to
> > /usr/local/pgsql).  And, further research shows that the pg_proc shows:
> > This shows /usr/local/pgsql-7.2/lib/plpgsql for the "probin" column.
>
> > How should an update of software be handled as per pg_proc?
>
> The preferred contents of probin are now like
>     $libdir/plpgsql
> (the dollar sign is literal text here).  As of 7.2 the server will
> substitute a suitable directory name for "$libdir".  You probably
> have a database that was reloaded from an old dump without taking
> advantage of this feature --- before 7.2 you could only use an
> absolute path in probin.  Now, absolute paths are deprecated for exactly
> the reason that they make version updates hard.
>
> Note the lack of a ".so" in that probin entry, too.  This is also
> a recommended new practice in 7.2 --- no reason to put
> platform-dependent assumptions about shared library extensions into
> your dumps.
>
>             regards, tom lane
>

--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...

A followup question on this...

What about user create functions (i.e. create function...as '<path>')
can the path have $libdir/ on it as well?

Thanks,

L.
On Tue, 3 Sep 2002, Tom Lane wrote:

> Laurette Cisneros <laurette@nextbus.com> writes:
> > I am currently on 7.2.1 and the software location has changed (to
> > /usr/local/pgsql).  And, further research shows that the pg_proc shows:
> > This shows /usr/local/pgsql-7.2/lib/plpgsql for the "probin" column.
>
> > How should an update of software be handled as per pg_proc?
>
> The preferred contents of probin are now like
>     $libdir/plpgsql
> (the dollar sign is literal text here).  As of 7.2 the server will
> substitute a suitable directory name for "$libdir".  You probably
> have a database that was reloaded from an old dump without taking
> advantage of this feature --- before 7.2 you could only use an
> absolute path in probin.  Now, absolute paths are deprecated for exactly
> the reason that they make version updates hard.
>
> Note the lack of a ".so" in that probin entry, too.  This is also
> a recommended new practice in 7.2 --- no reason to put
> platform-dependent assumptions about shared library extensions into
> your dumps.
>
>             regards, tom lane
>

--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...

Laurette Cisneros <laurette@nextbus.com> writes:
> What about user create functions (i.e. create function...as '<path>')
> can the path have $libdir/ on it as well?

Sure, if you put them where $libdir points to...

            regards, tom lane