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

Hi all,

I've been looking in the archives and googled this problem but not found
really a solution nor an explanation so finally posting here.

We are finally removing our old postgres 7.14 server and moving to a
"new" one (better machine) with postgres 8.4 running on it. I've been
making some tests in my local machine to assure database can be restored
from one version to the other without problems, and been documenting all
the process. Now that I have it quite clear, and I know the points than
can cause problems during restoration process, I'm trying to use
pg_upgrade to upgrade "new" server to a Postgres version that still has
support; this is, to upgrade from 8.4 to 10.10. But I'm getting an error
with plpgsql.so library that I'm not sure how to resolve. These are the
steps that I made:

*ekaterina(at)mymachine*:*~*$ sudo service postgresql start
*ekaterina**(at)mymachine*:*~*$ ps -ef | grep postgre
*postgre*s 1920 1 6 08:46 ? 00:00:00 /usr/lib/*postgre*sql/8.4/bin/*postgre*s -D /var/lib/*postgre*sql/8.4/main -c config_file=/etc/*postgre*sql/8.4/main/*postgre*sql.conf
*postgre*s 1921 1 0 08:46 ? 00:00:00 /usr/lib/*postgre*sql/10/bin/*postgre*s -D /var/lib/*postgre*sql/10/main -c config_file=/etc/*postgre*sql/10/main/*postgre*sql.conf
*postgre*s 1923 1921 0 08:46 ? 00:00:00*postgre*s: 10/main: checkpointer process
*postgre*s 1924 1921 0 08:46 ? 00:00:00*postgre*s: 10/main: writer process
*postgre*s 1925 1921 0 08:46 ? 00:00:00*postgre*s: 10/main: wal writer process
*postgre*s 1926 1921 0 08:46 ? 00:00:00*postgre*s: 10/main: autovacuum launcher process
*postgre*s 1927 1921 0 08:46 ? 00:00:00*postgre*s: 10/main: stats collector process
*postgre*s 1928 1921 0 08:46 ? 00:00:00*postgre*s: 10/main: bgworker: logical replication launcher
*postgre*s 1931 1920 0 08:46 ? 00:00:00*postgre*s: writer process
*postgre*s 1932 1920 0 08:46 ? 00:00:00*postgre*s: wal writer process
*postgre*s 1933 1920 0 08:46 ? 00:00:00*postgre*s: autovacuum launcher process
*postgre*s 1934 1920 0 08:46 ? 00:00:00*postgre*s: stats collector process
ekateri+ 1956 1844 0 08:46 pts/0 00:00:00 grep --color=auto*postgre*

*ekaterina**(at)mymachine*:*~*$ cd /temp
*ekaterina(at)mymachine*:*~/temp*$ sudo service postgresql stop
*ekaterina(at)**mymachine*:*~/temp*$ ps -ef | grep postgres
ekateri+ 2181 1844 0 08:53 pts/0 00:00:00 grep --color=auto*postgres*
*ekaterina(at)**mymachine*:*~/temp*$ sudo su postgres

postgres(at)mymachine:/home/ekaterina/temp$ cd /tmp
postgres(at)mymachine:/tmp$ /usr/lib/postgresql/10/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/8.4/main \
--new-datadir=/var/lib/postgresql/10/main \
--old-bindir=/usr/lib/postgresql/8.4/bin \
--new-bindir=/usr/lib/postgresql/10/bin \
--old-options '-c config_file=/etc/postgresql/8.4/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/10/main/postgresql.conf' \
--check

Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for invalid "unknown" user columns ok
Checking for hash indexes ok
Checking for roles starting with "pg_" ok
Checking for incompatible "line" data type ok
Checking for large objects ok
Checking for presence of required libraries fatal

Your installation references loadable libraries that are missing from the
new installation. You can add these libraries to the new installation,
or remove the functions using them from the old installation. A list of
problem libraries is in the file:
loadable_libraries.txt

Failure, exiting

postgres(at)mymachine:/tmp$ ls -ltr
total 52
-rw------- 1 postgres postgres 358 sep 19 08:53 pg_upgrade_utility.log
-rw------- 1 postgres postgres 1563 sep 19 08:53 pg_upgrade_internal.log
-rw------- 1 postgres postgres 205 sep 19 08:53 loadable_libraries.txt
-rw------- 1 postgres postgres 4624 sep 19 08:53 pg_upgrade_server.log

postgres(at)mymachine:/tmp$ cat pg_upgrade_server.log

-----------------------------------------------------------------
pg_upgrade run on Thu Sep 19 08:53:47 2019
-----------------------------------------------------------------

command: "/usr/lib/postgresql/8.4/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/8.4/main" -o "-p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000 -c config_file=/etc/postgresql/8.4/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700" start >> "pg_upgrade_server.log" 2>&1
esperando que el servidor se inicie....2019-09-19 08:53:48.057 CEST [2219] LOG: el sistema de bases de datos fue apagado en 2019-09-19 08:53:21 CEST
2019-09-19 08:53:48.062 CEST [2218] LOG: el sistema de bases de datos está listo para aceptar conexiones
listo
servidor iniciado

command: "/usr/lib/postgresql/8.4/bin/pg_ctl" -w -D "/var/lib/postgresql/8.4/main" -o "-c config_file=/etc/postgresql/8.4/main/postgresql.conf" -m smart stop >> "pg_upgrade_server.log" 2>&1
esperando que el servidor se detenga....2019-09-19 08:53:49.084 CEST [2218] LOG: se recibió petición de apagado inteligente
2019-09-19 08:53:49.085 CEST [2220] LOG: apagando
2019-09-19 08:53:49.095 CEST [2220] LOG: el sistema de bases de datos está apagado
listo
servidor detenido

command: "/usr/lib/postgresql/10/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/10/main" -o "-p 50432 -b -c synchronous_commit=off -c fsync=off -c full_page_writes=off -c config_file=/etc/postgresql/10/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/tmp'" start >> "pg_upgrade_server.log" 2>&1
waiting for server to start....2019-09-19 08:53:50.121 CEST [2272] LOG: listening on Unix socket "/tmp/.s.PGSQL.50432"
2019-09-19 08:53:50.128 CEST [2273] LOG: database system was shut down at 2019-09-19 08:53:21 CEST
2019-09-19 08:53:50.131 CEST [2272] LOG: database system is ready to accept connections
done
server started

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

postgres(at)mymachine:/tmp$ cat loadable_libraries.txt
could not load library "/usr/lib/postgresql/8.4/lib/plpgsql.so": 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

postgres(at)mymachine:/tmp$ exit
*ekaterina(at)mymachine*:*~/tmp*$ nm -D /usr/lib/postgresql/10/lib/plpgsql.so |grep SPI_push
*ekaterina(at)mymachine*:*~/tmp*$*ekaterina(at)mymachine*:*~/tmp*$ nm -D /usr/lib/postgresql/8.4/lib/plpgsql.so |grep SPI_push
U*SPI_push*

So, the error is right: SPI_push is not found in newer version of
plpgsql.so. How can I solve this problem? I'm guessing it comes from the
restored database. Backup was made in PG 7.14 machine, with PG 7.14
version of pg_dump. In the first lines of the backup we can find:

--
-- Name: plpgsql_call_handler(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
    LANGUAGE c
    AS '<path_to_library>/plpgsql.so', 'plpgsql_call_handler';

I think I've read somewhere (can't find now) that languages C and plpsql
are included in template1 since PG 8.0, so are included by default when
you run "CREATE DATABASE" instruction. If this is right, one solution
could be to modify backup file and exclude the CREATE FUNCTION above, so
plpgsql.so won't be needed and would be excluded from checklist when
pg_upgrade. Is this a good solution? Or maybe am I missing something? Is
there a better way to get around the "could not load library" error?

Thanks for your patience,

Ekaterina