Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10 version

Dear Ekaterina,

On 23/9/19 10:45 π.μ., Ekaterina Amez wrote:
> Hi Achilleas,
>
> El 20/9/19 a las 11:41, Achilleas Mantzios escribió:
>> On 20/9/19 12:22 μ.μ., Ekaterina Amez wrote:
>>> Hi Achilleas,
>>>
>>> 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?
>>
>> Just drop the 2nd function (in the public schema) :
>> drop function public.plpgsql_call_handler ( ) ;
>> and see pg_upgrade goes from there.
>>
>>> 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.
>>
>
> I've tested your suggestion and it went OK: restored db in 8.4, run "drop language" sentence, stopped services and run pg_upgrade
I wrote "drop function" not language.
> --check which ended up with "Clusters are compatible". GREAT!! But...
>
> In one of my tests I forgot to change the path to plpgsql.so library, and when restoring db it gave me an error saying it could not
Drop the wrong extra function, and then you don't have to change the path. The correct function (in pg_catalog) will be correct.
> access to the path given (of course, because that path only exists in the server, in my machine is different). After restoring with this error I found that:
>
> newdb=# select fn_now();
>      fn_now
> ----------------
>  20190923085521
> (1 fila)
>
> newdb=# select proname,probin,pronamespace from pg_proc where proname='plpgsql_call_handler';
>        proname        |     probin      | pronamespace
> ----------------------+-----------------+--------------
>  plpgsql_call_handler | $libdir/plpgsql |           11
> (1 fila)
>
> Which means that despite the error, language has been created. So in the end, not creating plpgsql_call_handler function and running "drop language" both leads me to the same result. I understood
> this piece of code [1] like: "create this language called plpgsql that will be interpreted by this function called plpgsql_call_handler wich code is in the library plpgsql.so". But with these
> results don't know the meaning of this code, as one of the solutions is drop language after creating, and the other one is create language without it's handler. I would appreciate an explanation
> about this, why is this happening or what am I misunderstanding.
>
> [1] this piece of code:
> CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
>     LANGUAGE c
>     AS '/usr/lib/pgsql/plpgsql.so', 'plpgsql_call_handler';
>
> CREATE PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
You're kinda messing up FUNCTION with LANGUAGE. Read the docs, and if you find any questions then go ahead and ask again.
Please try to just DROP the extra public.plpgsql_call_handler (not the language) and try again.
>
>
>

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt