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

On 19/9/19 10:49 π.μ., Ekaterina Amez wrote:
>
> 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
> *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**@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 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@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
>
>
> 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

Can you load plpgsql in your 8.4 cluster? Can you run plpgsql in the same system? I'd say ensure this first. If you can't, then drop it and try to upgrade. Then in your 10.10 you re-create plpgsql as 
an extension.

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


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt