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

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