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

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

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