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