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

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

From
Ekaterina Amez
Date:

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

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

From
Ekaterina Amez
Date:


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 LANGUAGE
template1=# create database newdb with owner=root template=template1;

using root is a bad practice. Also postgres is a (less) bad practice.

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.

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

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

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

From
Tom Lane
Date:
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




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

From
Ekaterina Amez
Date:


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_push
This 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)



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). 
That's what I'm trying: convince people to upgrade our servers.
 Since 9.0, plpgsql is automatically
installed at initdb time and there's no need for an additional copy.
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.
		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:


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_push
This 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)

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?


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). 
That's what I'm trying: convince people to upgrade our servers.
 Since 9.0, plpgsql is automatically
installed at initdb time and there's no need for an additional copy.
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.
			regards, tom lane

Thanks,

Ekaterina



-- 
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
Ekaterina Amez
Date:
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




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

From
Ekaterina Amez
Date:
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




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

From
Ekaterina Amez
Date:
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.
>>
>>
>>
>
>