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

El 19/9/19 a las 15:57, Tom Lane escribió:
> Ekaterina Amez <ekaterina(dot)amez(at)zunibal(dot)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(at)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(at)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(at)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)

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