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

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

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

ekaterina@mymachine:~/temp$ sudo su postgres

postgres@mymachine:/home/ekaterina/temp$ cd /tmp
postgres@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@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@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 conexioneslisto
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á apagadolisto
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 connectionsdone
server started


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


postgres@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@mymachine:/tmp$ exit
ekaterina@mymachine:~/tmp$ nm -D /usr/lib/postgresql/10/lib/plpgsql.so |grep SPI_push 
ekaterina@mymachine:~/tmp$ ekaterina@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