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: Mon, 23 Sep 2019 10:30:27 +0200
Message-ID: <ef96252d-7d3d-283f-ddb3-9912770ffb9c@zunibal.com> (raw)
In-Reply-To: <575b4ec4-020e-e750-d622-dfa5ad2cc078@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>
<b357dd47-0350-8cc8-0d0d-d4dce4446ca1@zunibal.com>
<13f3ecf4-248e-eb80-3906-fd2defe8a443@matrix.gatewaynet.com>
<d813f6ce-ab05-dcc1-83a5-08ab85d3bf9c@zunibal.com>
<575b4ec4-020e-e750-d622-dfa5ad2cc078@matrix.gatewaynet.com>
My apologies, I think I still don't have enough coffee in my body.
I've made my tests right, with drop function, but used a script and
named it drop language. This made me mess up things in my head and my
last email.
No more questions about this. Thanks for your patience.
El 23/9/19 a las 10:18, Achilleas Mantzios escribió:
> 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.
>>
>>
>>
>
>
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: <ef96252d-7d3d-283f-ddb3-9912770ffb9c@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