Thread: 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
Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10version
From
Achilleas Mantzios
Date:
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 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
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_pushSo, 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
El 19/9/19 a las 10:07, Achilleas Mantzios escribió:
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:
postgres@mymachine:/tmp$ cat pg_upgrade_server.log 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
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.
I'm not sure about what do you mean when you say to load plpgsql in my cluster. But after your mail, and after discussing it with nearby people I've used a simple function that is included in one of our databases to check the language exists or not in each server. This is the function (is really silly function, nothing but a now() formatted):
CREATE OR REPLACE FUNCTION fn_now()
RETURNS CHAR(14) AS '
DECLARE
_resultado CHAR(14);
BEGIN
SELECT to_char(NOW(), ''YYYYMMDDHH24MISS'') INTO _resultado;
RETURN _resultado;
END;
' LANGUAGE plpgsql;
I've created it in PG 10.10 and called from a select: no problem.
I've created an empty database in PG 8.4 and created this function in this new database. It gives me an error that says (more or less as I get the error in another language different than english) : "ERROR: «plpgsql» does not exist. SUGGESTION: Use CREATE LANGUAGE to install language in database.".
With these results, finally I've:
1- modified template1 in PG 8.4 server to include plpgsql language, so it can be inherited in new databases created from this template.
2- Dropped and recreated my database (let's call it newdb).
3- Modified backup file to remove the part that creates the function plpgsql_call_handler().
4- Restored modified backup.
ekaterina@mymachine:~/temp$ /usr/lib/postgresql/8.4/bin/psql -U postgres psql (8.4.22) postgres=# \c template1 template1=# CREATE LANGUAGE plpgsql; CREATE LANGUAGE
template1=# create database newdb with owner=root template=template1; CREATE DATABASE
newdb=# \q ekaterina@mymachine:~/temp$ /usr/lib/postgresql/8.4/bin/psql -U root -d newdb < backup_from_7_14.sql > errors.log
Backup is restored without errors, and functions are created correctly. I can call them without errors. And testing pg_upgrade with --check modifier now returns OK.
The conclusion is that I can avoid the use of plpgsql.so library. My question now would be related about the creation of the language in version 8.4: is it better to create it at template level or at database level? Of course creating it at template level leads to language being included in new databases without effort. And as it's plpgsql language, which is the only way we use at my place for programming database, it lets me foget about installing it in new databases. Also, since 9.0 version plpgsql language is installed by default in every database and all of this becomes unnecesary. But I'd like to know if I'm missing anything important.
Thanks Achilleas for your tips.
--Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10version
From
Achilleas Mantzios
Date:
On 19/9/19 2:35 μ.μ., Ekaterina Amez wrote:
El 19/9/19 a las 10:07, Achilleas Mantzios escribió: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:
postgres@mymachine:/tmp$ cat pg_upgrade_server.log 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
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.
I'm not sure about what do you mean when you say to load plpgsql in my cluster. But after your mail, and after discussing it with nearby people I've used a simple function that is included in one of our databases to check the language exists or not in each server. This is the function (is really silly function, nothing but a now() formatted):
CREATE OR REPLACE FUNCTION fn_now()
RETURNS CHAR(14) AS '
DECLARE
_resultado CHAR(14);
BEGIN
SELECT to_char(NOW(), ''YYYYMMDDHH24MISS'') INTO _resultado;
RETURN _resultado;
END;
' LANGUAGE plpgsql;I've created it in PG 10.10 and called from a select: no problem.
I've created an empty database in PG 8.4 and created this function in this new database. It gives me an error that says (more or less as I get the error in another language different than english) : "ERROR: «plpgsql» does not exist. SUGGESTION: Use CREATE LANGUAGE to install language in database.".
With these results, finally I've:
1- modified template1 in PG 8.4 server to include plpgsql language, so it can be inherited in new databases created from this template.
2- Dropped and recreated my database (let's call it newdb).
3- Modified backup file to remove the part that creates the function plpgsql_call_handler().
4- Restored modified backup.
ekaterina@mymachine:~/temp$ /usr/lib/postgresql/8.4/bin/psql -U postgres psql (8.4.22) postgres=# \c template1 template1=# CREATE LANGUAGE plpgsql; CREATE LANGUAGEtemplate1=# create database newdb with owner=root template=template1;
using root is a bad practice. Also postgres is a (less) bad practice.
The ERROR was a result of "undefined symbol: SPI_push", so the loader or the environment had some issue. I asked you to check exclusively in 8.4 to see if you get the same error while using / loading plpgsql in 8.4 (not 10) .CREATE DATABASEnewdb=# \q ekaterina@mymachine:~/temp$ /usr/lib/postgresql/8.4/bin/psql -U root -d newdb < backup_from_7_14.sql > errors.logBackup is restored without errors, and functions are created correctly. I can call them without errors. And testing pg_upgrade with --check modifier now returns OK.
The conclusion is that I can avoid the use of plpgsql.so library. My question now would be related about the creation of the language in version 8.4: is it better to create it at template level or at database level? Of course creating it at template level leads to language being included in new databases without effort. And as it's plpgsql language, which is the only way we use at my place for programming database, it lets me foget about installing it in new databases. Also, since 9.0 version plpgsql language is installed by default in every database and all of this becomes unnecesary. But I'd like to know if I'm missing anything important.
Can you now repeat the upgrade?
Thanks Achilleas for your tips.
--Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Ekaterina Amez <ekaterina.amez@zunibal.com> writes:
> El 19/9/19 a las 10:07, Achilleas Mantzios escribió:
>> On 19/9/19 10:49 π.μ., Ekaterina Amez wrote:
>>> 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
This error is clearly due to trying to load the 8.4 version of plpgsql.so
into the v10 server. What seems like the likely cause is that the
pg_proc.probin entries for the plpgsql support functions were literally
"/usr/lib/postgresql/8.4/lib/plpgsql.so", rather than something that
would adapt to the new version. The preferred way to declare such
functions, for a long time, has been with probin = "$libdir/plpgsql",
relying on the server to subsitute an appropriate path for $libdir.
> With these results, finally I've:
> 1- modified template1 in PG 8.4 server to include plpgsql language,
> so it can be inherited in new databases created from this template.
> 2- Dropped and recreated my database (let's call it newdb).
This probably fixed it by ensuring that the plpgsql support functions
were declared with the standard value of probin.
> The conclusion is that I can avoid the use of plpgsql.so library. My
> question now would be related about the creation of the language in
> version 8.4: is it better to create it at template level or at database
> level?
I would not sweat too much about this, since you don't intend to keep
using 8.4 (or at least I hope not). Since 9.0, plpgsql is automatically
installed at initdb time and there's no need for an additional copy.
regards, tom lane
Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10version
From
Achilleas Mantzios
Date:
On 19/9/19 4:57 μ.μ., Tom Lane wrote: > Ekaterina Amez <ekaterina.amez@zunibal.com> writes: >> El 19/9/19 a las 10:07, Achilleas Mantzios escribió: >>> On 19/9/19 10:49 π.μ., Ekaterina Amez wrote: >>>> 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 > This error is clearly due to trying to load the 8.4 version of plpgsql.so > into the v10 server. Yup, in the log, right after the "undefined symbol: SPI_push" ERROR there are entries about logical replication, those couldnot have come from the 8.4 server. Arghhh I didn't watch close enough. Thanx > >> With these results, finally I've: >> 1- modified template1 in PG 8.4 server to include plpgsql language, >> so it can be inherited in new databases created from this template. >> 2- Dropped and recreated my database (let's call it newdb). > This probably fixed it by ensuring that the plpgsql support functions > were declared with the standard value of probin. > >> The conclusion is that I can avoid the use of plpgsql.so library. My >> question now would be related about the creation of the language in >> version 8.4: is it better to create it at template level or at database >> level? > I would not sweat too much about this, since you don't intend to keep > using 8.4 (or at least I hope not). Since 9.0, plpgsql is automatically > installed at initdb time and there's no need for an additional copy. > > regards, tom lane > > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
El 19/9/19 a las 15:57, Tom Lane escribió:
Ekaterina Amez <ekaterina.amez@zunibal.com> writes:El 19/9/19 a las 10:07, Achilleas Mantzios escribió:On 19/9/19 10:49 π.μ., Ekaterina Amez wrote: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_pushThis error is clearly due to trying to load the 8.4 version of plpgsql.so into the v10 server.
Before posting here, my investigation lead me to think this was the problem as every post I found with similar errors were all related to newer PG trying to use old libs. But didn't know what/how to search for this (nor how to resolve). After your comment I suppose it can be seen in the last block of pg_upgrade_server.log:
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
It's running command
"/usr/lib/postgresql/10/bin/pg_ctl"
When complains with
could not load library "/usr/lib/postgresql/8.4/lib/plpgsql.so"
Right?
What seems like the likely cause is that the pg_proc.probin entries for the plpgsql support functions were literally "/usr/lib/postgresql/8.4/lib/plpgsql.so", rather than something that would adapt to the new version. The preferred way to declare such functions, for a long time, has been with probin = "$libdir/plpgsql", relying on the server to subsitute an appropriate path for $libdir.
I've looked in pg_proc catalog and plpgsql_call_handler is declared with probin = "$libdir/plpgsql" (only in PG10). Is the substitution of $libdir what's wrong? Don't know how to look for this (I've been working with Linux/Postgres for 3 months only, so still limited ability/knowledge)
That's what I'm trying: convince people to upgrade our servers.With these results, finally I've: 1- modified template1 in PG 8.4 server to include plpgsql language, so it can be inherited in new databases created from this template. 2- Dropped and recreated my database (let's call it newdb).This probably fixed it by ensuring that the plpgsql support functions were declared with the standard value of probin.The conclusion is that I can avoid the use of plpgsql.so library. My question now would be related about the creation of the language in version 8.4: is it better to create it at template level or at database level?I would not sweat too much about this, since you don't intend to keep using 8.4 (or at least I hope not).
Yeah, I know that, but since I can't assure that we are going to upgrade (at least inmediatly) to PG > 9.0 I'd like to know if there's anything that I should take in consideration when creating language in 8.4, in order to create it at template level or at database level only.Since 9.0, plpgsql is automatically installed at initdb time and there's no need for an additional copy.
regards, tom lane
Thanks,
Ekaterina
Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10version
From
Achilleas Mantzios
Date:
On 20/9/19 10:05 π.μ., Ekaterina Amez wrote:
Just tested in my 11 and 10 :
El 19/9/19 a las 15:57, Tom Lane escribió:Ekaterina Amez <ekaterina.amez@zunibal.com> writes:El 19/9/19 a las 10:07, Achilleas Mantzios escribió:On 19/9/19 10:49 π.μ., Ekaterina Amez wrote: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_pushThis error is clearly due to trying to load the 8.4 version of plpgsql.so into the v10 server.Before posting here, my investigation lead me to think this was the problem as every post I found with similar errors were all related to newer PG trying to use old libs. But didn't know what/how to search for this (nor how to resolve). After your comment I suppose it can be seen in the last block of pg_upgrade_server.log:
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 stoppedIt's running command
"/usr/lib/postgresql/10/bin/pg_ctl"When complains with
could not load library "/usr/lib/postgresql/8.4/lib/plpgsql.so"Right?
What seems like the likely cause is that the pg_proc.probin entries for the plpgsql support functions were literally "/usr/lib/postgresql/8.4/lib/plpgsql.so", rather than something that would adapt to the new version. The preferred way to declare such functions, for a long time, has been with probin = "$libdir/plpgsql", relying on the server to subsitute an appropriate path for $libdir.I've looked in pg_proc catalog and plpgsql_call_handler is declared with probin = "$libdir/plpgsql" (only in PG10). Is the substitution of $libdir what's wrong? Don't know how to look for this (I've been working with Linux/Postgres for 3 months only, so still limited ability/knowledge)
dynacom=# select proname,probin,pronamespace from pg_proc where proname='plpgsql_call_handler';
proname | probin | pronamespace
----------------------+---------------------------------+--------------
plpgsql_call_handler | $libdir/plpgsql | 11
plpgsql_call_handler | /usr/local/pgsql/lib/plpgsql.so | 2200
(2 rows)
with 2200 being the public namespace/schema.
I my case, in every postgresql installation since 2001, always libdir was /usr/local/pgsql/lib/ , so maybe this problem could not be manifested. Did you check to see if you have the plpgsql_call_handler defined in two schemas as well?
That's what I'm trying: convince people to upgrade our servers.With these results, finally I've: 1- modified template1 in PG 8.4 server to include plpgsql language, so it can be inherited in new databases created from this template. 2- Dropped and recreated my database (let's call it newdb).This probably fixed it by ensuring that the plpgsql support functions were declared with the standard value of probin.The conclusion is that I can avoid the use of plpgsql.so library. My question now would be related about the creation of the language in version 8.4: is it better to create it at template level or at database level?I would not sweat too much about this, since you don't intend to keep using 8.4 (or at least I hope not).Yeah, I know that, but since I can't assure that we are going to upgrade (at least inmediatly) to PG > 9.0 I'd like to know if there's anything that I should take in consideration when creating language in 8.4, in order to create it at template level or at database level only.Since 9.0, plpgsql is automatically installed at initdb time and there's no need for an additional copy.regards, tom laneThanks,
Ekaterina
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Hi Achilleas, El 20/9/19 a las 9:26, Achilleas Mantzios escribió: > Just tested in my 11 and 10 : > dynacom=# select proname,probin,pronamespace from pg_proc where > proname='plpgsql_call_handler'; > proname | probin | pronamespace > ----------------------+---------------------------------+-------------- > plpgsql_call_handler | $libdir/plpgsql | 11 > plpgsql_call_handler | /usr/local/pgsql/lib/plpgsql.so | 2200 > (2 rows) > > with 2200 being the public namespace/schema. > > I my case, in every postgresql installation since 2001, always libdir > was /usr/local/pgsql/lib/ , so maybe this problem could not be > manifested. Did you check to see if you have the plpgsql_call_handler > defined in two schemas as well? > > -- > Achilleas Mantzios > IT DEV Lead > IT DEPT > Dynacom Tankers Mgmt I've restored template1 in v8.4, and created again the db where I will be restoring v7.14 backup newdb=# select proname,probin,pronamespace from pg_proc where proname='plpgsql_call_handler'; proname | probin | pronamespace ---------+--------+-------------- (0 filas) And in v10.10 (which is still an empty installation) template1=# select proname,probin,pronamespace from pg_proc where proname='plpgsql_call_handler'; proname | probin | pronamespace ----------------------+-----------------+-------------- plpgsql_call_handler | $libdir/plpgsql | 11 (1 row) After restoring backup in v8.4: newdb=# select proname,probin,pronamespace from pg_proc where proname='plpgsql_call_handler'; proname | probin | pronamespace ----------------------+----------------------------------------+-------------- plpgsql_call_handler | $libdir/plpgsql | 11 plpgsql_call_handler | /usr/lib/postgresql/8.4/lib/plpgsql.so | 2200 (2 filas) As you said, 2200 is public namespace/schema and 11 is pg_catalog. At the beginning of the backup file I can find these sentences: CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler LANGUAGE c AS '/usr/lib/postgresql/8.4/lib/plpgsql.so', 'plpgsql_call_handler'; <-- I've changed this line to use the right path to plpgsql.so library CREATE PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; I guess these are the ones causing all of this. **What should be the best way to handle this situation?** Remove these lines and create the language explicitly when creating database? Or replace them with a create language sentence? Maybe something else? My final goal is migrate from 7.14 server to 8.4 server and after that (if I have an OK from the boss) upgrade 8.4 to the latest version that I can use. Server uses CentOS, and probably I won't be able to upgrade to v10 but I hope at least 9.5/9.6 will be available.
Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10version
From
Achilleas Mantzios
Date:
On 20/9/19 12:22 μ.μ., Ekaterina Amez wrote: > Hi Achilleas, > > El 20/9/19 a las 9:26, Achilleas Mantzios escribió: >> Just tested in my 11 and 10 : >> dynacom=# select proname,probin,pronamespace from pg_proc where proname='plpgsql_call_handler'; >> proname | probin | pronamespace >> ----------------------+---------------------------------+-------------- >> plpgsql_call_handler | $libdir/plpgsql | 11 >> plpgsql_call_handler | /usr/local/pgsql/lib/plpgsql.so | 2200 >> (2 rows) >> >> with 2200 being the public namespace/schema. >> >> I my case, in every postgresql installation since 2001, always libdir was /usr/local/pgsql/lib/ , so maybe this problemcould not be manifested. Did you check to see if you have the >> plpgsql_call_handler defined in two schemas as well? >> >> -- >> Achilleas Mantzios >> IT DEV Lead >> IT DEPT >> Dynacom Tankers Mgmt > > > I've restored template1 in v8.4, and created again the db where I will be restoring v7.14 backup > > newdb=# select proname,probin,pronamespace from pg_proc where proname='plpgsql_call_handler'; > proname | probin | pronamespace > ---------+--------+-------------- > (0 filas) > > > And in v10.10 (which is still an empty installation) > > template1=# select proname,probin,pronamespace from pg_proc where proname='plpgsql_call_handler'; > > proname | probin | pronamespace > ----------------------+-----------------+-------------- > plpgsql_call_handler | $libdir/plpgsql | 11 > (1 row) > > > After restoring backup in v8.4: > > newdb=# select proname,probin,pronamespace from pg_proc where proname='plpgsql_call_handler'; > proname | probin | pronamespace > ----------------------+----------------------------------------+-------------- > plpgsql_call_handler | $libdir/plpgsql | 11 > plpgsql_call_handler | /usr/lib/postgresql/8.4/lib/plpgsql.so | 2200 > (2 filas) > > As you said, 2200 is public namespace/schema and 11 is pg_catalog. > > > At the beginning of the backup file I can find these sentences: > > CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler > LANGUAGE c > AS '/usr/lib/postgresql/8.4/lib/plpgsql.so', 'plpgsql_call_handler'; <-- I've changed this line to use the right pathto plpgsql.so library > > CREATE PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; > > I guess these are the ones causing all of this. **What should be the best way to handle this situation?** Remove theselines and create the language explicitly when creating database? Just drop the 2nd function (in the public schema) : drop function public.plpgsql_call_handler ( ) ; and see pg_upgrade goes from there. > Or replace them with a create language sentence? Maybe something else? My final goal is migrate from 7.14 server to 8.4server and after that (if I have an OK from the boss) upgrade 8.4 to the > latest version that I can use. Server uses CentOS, and probably I won't be able to upgrade to v10 but I hope at least 9.5/9.6will be available. > > > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Hi Achilleas, El 20/9/19 a las 11:41, Achilleas Mantzios escribió: > On 20/9/19 12:22 μ.μ., Ekaterina Amez wrote: >> Hi Achilleas, >> >> After restoring backup in v8.4: >> >> newdb=# select proname,probin,pronamespace from pg_proc where >> proname='plpgsql_call_handler'; >> proname | probin | >> pronamespace >> ----------------------+----------------------------------------+-------------- >> >> plpgsql_call_handler | $libdir/plpgsql | 11 >> plpgsql_call_handler | /usr/lib/postgresql/8.4/lib/plpgsql.so >> | 2200 >> (2 filas) >> >> As you said, 2200 is public namespace/schema and 11 is pg_catalog. >> >> >> At the beginning of the backup file I can find these sentences: >> >> CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler >> LANGUAGE c >> AS '/usr/lib/postgresql/8.4/lib/plpgsql.so', >> 'plpgsql_call_handler'; <-- I've changed this line to use the right >> path to plpgsql.so library >> >> CREATE PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; >> >> I guess these are the ones causing all of this. **What should be the >> best way to handle this situation?** Remove these lines and create >> the language explicitly when creating database? > > Just drop the 2nd function (in the public schema) : > drop function public.plpgsql_call_handler ( ) ; > and see pg_upgrade goes from there. > >> Or replace them with a create language sentence? Maybe something >> else? My final goal is migrate from 7.14 server to 8.4 server and >> after that (if I have an OK from the boss) upgrade 8.4 to the latest >> version that I can use. Server uses CentOS, and probably I won't be >> able to upgrade to v10 but I hope at least 9.5/9.6 will be available. > I've tested your suggestion and it went OK: restored db in 8.4, run "drop language" sentence, stopped services and run pg_upgrade --check which ended up with "Clusters are compatible". GREAT!! But... In one of my tests I forgot to change the path to plpgsql.so library, and when restoring db it gave me an error saying it could not access to the path given (of course, because that path only exists in the server, in my machine is different). After restoring with this error I found that: newdb=# select fn_now(); fn_now ---------------- 20190923085521 (1 fila) newdb=# select proname,probin,pronamespace from pg_proc where proname='plpgsql_call_handler'; proname | probin | pronamespace ----------------------+-----------------+-------------- plpgsql_call_handler | $libdir/plpgsql | 11 (1 fila) Which means that despite the error, language has been created. So in the end, not creating plpgsql_call_handler function and running "drop language" both leads me to the same result. I understood this piece of code [1] like: "create this language called plpgsql that will be interpreted by this function called plpgsql_call_handler wich code is in the library plpgsql.so". But with these results don't know the meaning of this code, as one of the solutions is drop language after creating, and the other one is create language without it's handler. I would appreciate an explanation about this, why is this happening or what am I misunderstanding. [1] this piece of code: CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler LANGUAGE c AS '/usr/lib/pgsql/plpgsql.so', 'plpgsql_call_handler'; CREATE PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10version
From
Achilleas Mantzios
Date:
Dear Ekaterina, On 23/9/19 10:45 π.μ., Ekaterina Amez wrote: > Hi Achilleas, > > El 20/9/19 a las 11:41, Achilleas Mantzios escribió: >> On 20/9/19 12:22 μ.μ., Ekaterina Amez wrote: >>> Hi Achilleas, >>> >>> After restoring backup in v8.4: >>> >>> newdb=# select proname,probin,pronamespace from pg_proc where proname='plpgsql_call_handler'; >>> proname | probin | pronamespace >>> ----------------------+----------------------------------------+-------------- >>> plpgsql_call_handler | $libdir/plpgsql | 11 >>> plpgsql_call_handler | /usr/lib/postgresql/8.4/lib/plpgsql.so | 2200 >>> (2 filas) >>> >>> As you said, 2200 is public namespace/schema and 11 is pg_catalog. >>> >>> >>> At the beginning of the backup file I can find these sentences: >>> >>> CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler >>> LANGUAGE c >>> AS '/usr/lib/postgresql/8.4/lib/plpgsql.so', 'plpgsql_call_handler'; <-- I've changed this line to use the rightpath to plpgsql.so library >>> >>> CREATE PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; >>> >>> I guess these are the ones causing all of this. **What should be the best way to handle this situation?** Remove theselines and create the language explicitly when creating database? >> >> Just drop the 2nd function (in the public schema) : >> drop function public.plpgsql_call_handler ( ) ; >> and see pg_upgrade goes from there. >> >>> Or replace them with a create language sentence? Maybe something else? My final goal is migrate from 7.14 server to 8.4server and after that (if I have an OK from the boss) upgrade 8.4 to the >>> latest version that I can use. Server uses CentOS, and probably I won't be able to upgrade to v10 but I hope at least9.5/9.6 will be available. >> > > I've tested your suggestion and it went OK: restored db in 8.4, run "drop language" sentence, stopped services and runpg_upgrade I wrote "drop function" not language. > --check which ended up with "Clusters are compatible". GREAT!! But... > > In one of my tests I forgot to change the path to plpgsql.so library, and when restoring db it gave me an error sayingit could not Drop the wrong extra function, and then you don't have to change the path. The correct function (in pg_catalog) will be correct. > access to the path given (of course, because that path only exists in the server, in my machine is different). After restoringwith this error I found that: > > newdb=# select fn_now(); > fn_now > ---------------- > 20190923085521 > (1 fila) > > newdb=# select proname,probin,pronamespace from pg_proc where proname='plpgsql_call_handler'; > proname | probin | pronamespace > ----------------------+-----------------+-------------- > plpgsql_call_handler | $libdir/plpgsql | 11 > (1 fila) > > Which means that despite the error, language has been created. So in the end, not creating plpgsql_call_handler functionand running "drop language" both leads me to the same result. I understood > this piece of code [1] like: "create this language called plpgsql that will be interpreted by this function called plpgsql_call_handlerwich code is in the library plpgsql.so". But with these > results don't know the meaning of this code, as one of the solutions is drop language after creating, and the other oneis create language without it's handler. I would appreciate an explanation > about this, why is this happening or what am I misunderstanding. > > [1] this piece of code: > CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler > LANGUAGE c > AS '/usr/lib/pgsql/plpgsql.so', 'plpgsql_call_handler'; > > CREATE PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; You're kinda messing up FUNCTION with LANGUAGE. Read the docs, and if you find any questions then go ahead and ask again. Please try to just DROP the extra public.plpgsql_call_handler (not the language) and try again. > > > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
My apologies, I think I still don't have enough coffee in my body. I've made my tests right, with drop function, but used a script and named it drop language. This made me mess up things in my head and my last email. No more questions about this. Thanks for your patience. El 23/9/19 a las 10:18, Achilleas Mantzios escribió: > Dear Ekaterina, > > On 23/9/19 10:45 π.μ., Ekaterina Amez wrote: >> Hi Achilleas, >> >> El 20/9/19 a las 11:41, Achilleas Mantzios escribió: >>> On 20/9/19 12:22 μ.μ., Ekaterina Amez wrote: >>>> Hi Achilleas, >>>> >>>> After restoring backup in v8.4: >>>> >>>> newdb=# select proname,probin,pronamespace from pg_proc where >>>> proname='plpgsql_call_handler'; >>>> proname | probin | pronamespace >>>> ----------------------+----------------------------------------+-------------- >>>> >>>> plpgsql_call_handler | $libdir/plpgsql | 11 >>>> plpgsql_call_handler | /usr/lib/postgresql/8.4/lib/plpgsql.so >>>> | 2200 >>>> (2 filas) >>>> >>>> As you said, 2200 is public namespace/schema and 11 is pg_catalog. >>>> >>>> >>>> At the beginning of the backup file I can find these sentences: >>>> >>>> CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler >>>> LANGUAGE c >>>> AS '/usr/lib/postgresql/8.4/lib/plpgsql.so', >>>> 'plpgsql_call_handler'; <-- I've changed this line to use the right >>>> path to plpgsql.so library >>>> >>>> CREATE PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; >>>> >>>> I guess these are the ones causing all of this. **What should be >>>> the best way to handle this situation?** Remove these lines and >>>> create the language explicitly when creating database? >>> >>> Just drop the 2nd function (in the public schema) : >>> drop function public.plpgsql_call_handler ( ) ; >>> and see pg_upgrade goes from there. >>> >>>> Or replace them with a create language sentence? Maybe something >>>> else? My final goal is migrate from 7.14 server to 8.4 server and >>>> after that (if I have an OK from the boss) upgrade 8.4 to the >>>> latest version that I can use. Server uses CentOS, and probably I >>>> won't be able to upgrade to v10 but I hope at least 9.5/9.6 will be >>>> available. >>> >> >> I've tested your suggestion and it went OK: restored db in 8.4, run >> "drop language" sentence, stopped services and run pg_upgrade > I wrote "drop function" not language. >> --check which ended up with "Clusters are compatible". GREAT!! But... >> >> In one of my tests I forgot to change the path to plpgsql.so library, >> and when restoring db it gave me an error saying it could not > Drop the wrong extra function, and then you don't have to change the > path. The correct function (in pg_catalog) will be correct. >> access to the path given (of course, because that path only exists in >> the server, in my machine is different). After restoring with this >> error I found that: >> >> newdb=# select fn_now(); >> fn_now >> ---------------- >> 20190923085521 >> (1 fila) >> >> newdb=# select proname,probin,pronamespace from pg_proc where >> proname='plpgsql_call_handler'; >> proname | probin | pronamespace >> ----------------------+-----------------+-------------- >> plpgsql_call_handler | $libdir/plpgsql | 11 >> (1 fila) >> >> Which means that despite the error, language has been created. So in >> the end, not creating plpgsql_call_handler function and running "drop >> language" both leads me to the same result. I understood this piece >> of code [1] like: "create this language called plpgsql that will be >> interpreted by this function called plpgsql_call_handler wich code is >> in the library plpgsql.so". But with these results don't know the >> meaning of this code, as one of the solutions is drop language after >> creating, and the other one is create language without it's handler. >> I would appreciate an explanation about this, why is this happening >> or what am I misunderstanding. >> >> [1] this piece of code: >> CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler >> LANGUAGE c >> AS '/usr/lib/pgsql/plpgsql.so', 'plpgsql_call_handler'; >> >> CREATE PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; > You're kinda messing up FUNCTION with LANGUAGE. Read the docs, and if > you find any questions then go ahead and ask again. > Please try to just DROP the extra public.plpgsql_call_handler (not the > language) and try again. >> >> >> > >