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

[Ekaterina Amez <ekaterina.amez@xxxxxxxxxxx>]

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 down
 done
server stopped


postgres@mymachine:/tmp$ cat loadable_libraries.txt
could not load library "/usr/lib/postgresql/8.4/lib/plpgsql.so": ERROR:  could not load library "/usr/lib/postgresql/8.4/lib/plpgsql.so": /usr/lib/postgresql/8.4/lib/plpgsql.so: undefined symbol: SPI_push

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

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