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>
From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Message-ID: <13f3ecf4-248e-eb80-3906-fd2defe8a443@matrix.gatewaynet.com>
Date: Fri, 20 Sep 2019 12:41:24 +0300
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:60.0) Gecko/20100101
 Thunderbird/60.3.2
MIME-Version: 1.0
In-Reply-To: <b357dd47-0350-8cc8-0d0d-d4dce4446ca1@zunibal.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

On 20/9/19 12:22 μ.μ., Ekaterina Amez wrote:
> 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? 

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


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