Subject: Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10
 version
To: pgsql-admin@lists.postgresql.org
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>
 <b357dd47-0350-8cc8-0d0d-d4dce4446ca1@zunibal.com>
 <13f3ecf4-248e-eb80-3906-fd2defe8a443@matrix.gatewaynet.com>
From: Ekaterina Amez <ekaterina.amez@zunibal.com>
Message-ID: <d813f6ce-ab05-dcc1-83a5-08ab85d3bf9c@zunibal.com>
Date: Mon, 23 Sep 2019 09:45:14 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.8.0
MIME-Version: 1.0
In-Reply-To: <13f3ecf4-248e-eb80-3906-fd2defe8a443@matrix.gatewaynet.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

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;