From: Achilleas Mantzios <achill@matrix.gatewaynet.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: Fri, 20 Sep 2019 10:26:54 +0300
Message-ID: <a590a557-8e53-6ccf-311c-aa912075d027@matrix.gatewaynet.com> (raw)
In-Reply-To: <b99f1989-ade9-6936-d811-1747541c4ce0@zunibal.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>
On 20/9/19 10:05 π.μ., Ekaterina Amez wrote:
>
>
> El 19/9/19 a las 15:57, Tom Lane escribió:
>> Ekaterina Amez<ekaterina.amez@zunibal.com> writes:
>>> El 19/9/19 a las 10:07, Achilleas Mantzios escribió:
>>>> On 19/9/19 10:49 π.μ., Ekaterina Amez wrote:
>>>>> 2019-09-19 08:53:50.345 CEST [2283] postgres@template1 ERROR: could not load library "/usr/lib/postgresql/8.4/lib/plpgsql.so": /usr/lib/postgresql/8.4/lib/plpgsql.so: undefined symbol: SPI_push
>> This error is clearly due to trying to load the 8.4 version of plpgsql.so
>> into the v10 server.
>
> Before posting here, my investigation lead me to think this was the problem as every post I found with similar errors were all related to newer PG trying to use old libs. But didn't know what/how to
> search for this (nor how to resolve). After your comment I suppose it can be seen in the last block of pg_upgrade_server.log:
>
> 2019-09-19 08:53:50.345 CEST [2283] postgres@template1 ERROR: could not load library "/usr/lib/postgresql/8.4/lib/plpgsql.so": /usr/lib/postgresql/8.4/lib/plpgsql.so: undefined symbol: SPI_push
> 2019-09-19 08:53:50.345 CEST [2283] postgres@template1 STATEMENT: LOAD '/usr/lib/postgresql/8.4/lib/plpgsql.so'
> command: "/usr/lib/postgresql/10/bin/pg_ctl" -w -D "/var/lib/postgresql/10/main" -o "-c config_file=/etc/postgresql/10/main/postgresql.conf" -m fast stop >> "pg_upgrade_server.log" 2>&1
> 2019-09-19 08:53:50.354 CEST [2272] LOG: received fast shutdown request
> waiting for server to shut down....2019-09-19 08:53:50.358 CEST [2272] LOG: aborting any active transactions
> 2019-09-19 08:53:50.363 CEST [2272] LOG: worker process: logical replication launcher (PID 2278) exited with exit code 1
> 2019-09-19 08:53:50.364 CEST [2274] LOG: shutting down
> 2019-09-19 08:53:50.377 CEST [2272] LOG: database system is shut down
> done
> server stopped
>
> It's running command
>
> "/usr/lib/postgresql/10/bin/pg_ctl"
>
> When complains with
>
> could not load library "/usr/lib/postgresql/8.4/lib/plpgsql.so"
>
> Right?
>
>> What seems like the likely cause is that the
>> pg_proc.probin entries for the plpgsql support functions were literally
>> "/usr/lib/postgresql/8.4/lib/plpgsql.so", rather than something that
>> would adapt to the new version. The preferred way to declare such
>> functions, for a long time, has been with probin = "$libdir/plpgsql",
>> relying on the server to subsitute an appropriate path for $libdir.
>
> I've looked in pg_proc catalog and plpgsql_call_handler is declared with probin = "$libdir/plpgsql" (only in PG10). Is the substitution of $libdir what's wrong? Don't know how to look for this (I've
> been working with Linux/Postgres for 3 months only, so still limited ability/knowledge)
>
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?
>
>>> With these results, finally I've:
>>> 1- modified template1 in PG 8.4 server to include plpgsql language,
>>> so it can be inherited in new databases created from this template.
>>> 2- Dropped and recreated my database (let's call it newdb).
>> This probably fixed it by ensuring that the plpgsql support functions
>> were declared with the standard value of probin.
>>
>>> The conclusion is that I can avoid the use of plpgsql.so library. My
>>> question now would be related about the creation of the language in
>>> version 8.4: is it better to create it at template level or at database
>>> level?
>> I would not sweat too much about this, since you don't intend to keep
>> using 8.4 (or at least I hope not).
> That's what I'm trying: convince people to upgrade our servers.
>> Since 9.0, plpgsql is automatically
>> installed at initdb time and there's no need for an additional copy.
> Yeah, I know that, but since I can't assure that we are going to upgrade (at least inmediatly) to PG > 9.0 I'd like to know if there's anything that I should take in consideration when creating
> language in 8.4, in order to create it at template level or at database level only.
>> regards, tom lane
>
> Thanks,
>
> Ekaterina
>
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: achill@matrix.gatewaynet.com
Subject: Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10 version
In-Reply-To: <a590a557-8e53-6ccf-311c-aa912075d027@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>
* 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