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

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 --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 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;