From: Ekaterina Amez <ekaterina.amez@zunibal.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10 version
Date: Fri, 20 Sep 2019 11:22:25 +0200
Message-ID: <b357dd47-0350-8cc8-0d0d-d4dce4446ca1@zunibal.com> (raw)
In-Reply-To: <a590a557-8e53-6ccf-311c-aa912075d027@matrix.gatewaynet.com>
References: <e95cf94c-b7f1-1963-6647-6335785870d9@zunibal.com>
<4519fdad-d747-cd39-f6a2-33e7db1f3324@matrix.gatewaynet.com>
<a82dd9e3-d388-24d1-95bb-cdb1ac7ee758@zunibal.com>
<22046.1568901429@sss.pgh.pa.us>
<b99f1989-ade9-6936-d811-1747541c4ce0@zunibal.com>
<a590a557-8e53-6ccf-311c-aa912075d027@matrix.gatewaynet.com>
Hi Achilleas,
El 20/9/19 a las 9:26, Achilleas Mantzios escribió:
> Just tested in my 11 and 10 :
> dynacom=# select proname,probin,pronamespace from pg_proc where
> proname='plpgsql_call_handler';
> proname | probin | pronamespace
> ----------------------+---------------------------------+--------------
> plpgsql_call_handler | $libdir/plpgsql | 11
> plpgsql_call_handler | /usr/local/pgsql/lib/plpgsql.so | 2200
> (2 rows)
>
> with 2200 being the public namespace/schema.
>
> I my case, in every postgresql installation since 2001, always libdir
> was /usr/local/pgsql/lib/ , so maybe this problem could not be
> manifested. Did you check to see if you have the plpgsql_call_handler
> defined in two schemas as well?
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
I've restored template1 in v8.4, and created again the db where I will
be restoring v7.14 backup
newdb=# select proname,probin,pronamespace from pg_proc where
proname='plpgsql_call_handler';
proname | probin | pronamespace
---------+--------+--------------
(0 filas)
And in v10.10 (which is still an empty installation)
template1=# select proname,probin,pronamespace from pg_proc where
proname='plpgsql_call_handler';
proname | probin | pronamespace
----------------------+-----------------+--------------
plpgsql_call_handler | $libdir/plpgsql | 11
(1 row)
After restoring backup in v8.4:
newdb=# select proname,probin,pronamespace from pg_proc where
proname='plpgsql_call_handler';
proname | probin |
pronamespace
----------------------+----------------------------------------+--------------
plpgsql_call_handler | $libdir/plpgsql | 11
plpgsql_call_handler | /usr/lib/postgresql/8.4/lib/plpgsql.so
| 2200
(2 filas)
As you said, 2200 is public namespace/schema and 11 is pg_catalog.
At the beginning of the backup file I can find these sentences:
CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
LANGUAGE c
AS '/usr/lib/postgresql/8.4/lib/plpgsql.so',
'plpgsql_call_handler'; <-- I've changed this line to use the right path
to plpgsql.so library
CREATE PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
I guess these are the ones causing all of this. **What should be the
best way to handle this situation?** Remove these lines and create the
language explicitly when creating database? Or replace them with a
create language sentence? Maybe something else? My final goal is migrate
from 7.14 server to 8.4 server and after that (if I have an OK from the
boss) upgrade 8.4 to the latest version that I can use. Server uses
CentOS, and probably I won't be able to upgrade to v10 but I hope at
least 9.5/9.6 will be available.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ekaterina.amez@zunibal.com, pgsql-admin@lists.postgresql.org
Subject: Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10 version
In-Reply-To: <b357dd47-0350-8cc8-0d0d-d4dce4446ca1@zunibal.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox