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

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(at)mymachine:/tmp$ cat pg_upgrade_server.log
>>
>> 2019-09-19 08:53:50.345 CEST [2283] postgres(at)template1 ERROR: could not load library "/usr/lib/postgresql/8.4/lib/plpgsql.so": /usr/lib/postgresql/8.4/lib/plpgsql.so: undefined symbol: SPI_push
>> 2019-09-19 08:53:50.345 CEST [2283] postgres(at)template1 STATEMENT: LOAD '/usr/lib/postgresql/8.4/lib/plpgsql.so'
>> command: "/usr/lib/postgresql/10/bin/pg_ctl" -w -D "/var/lib/postgresql/10/main" -o "-c config_file=/etc/postgresql/10/main/postgresql.conf" -m fast stop >> "pg_upgrade_server.log" 2>&1
>> 2019-09-19 08:53:50.354 CEST [2272] LOG: received fast shutdown request
>> waiting for server to shut down....2019-09-19 08:53:50.358 CEST [2272] LOG: aborting any active transactions
>> 2019-09-19 08:53:50.363 CEST [2272] LOG: worker process: logical replication launcher (PID 2278) exited with exit code 1
>> 2019-09-19 08:53:50.364 CEST [2274] LOG: shutting down
>> 2019-09-19 08:53:50.377 CEST [2272] LOG: database system is shut down
>> done
>> server stopped
>>
>>
>> postgres(at)mymachine:/tmp$ cat loadable_libraries.txt
>> could not load library "/usr/lib/postgresql/8.4/lib/plpgsql.so": ERROR: could not load library "/usr/lib/postgresql/8.4/lib/plpgsql.so": /usr/lib/postgresql/8.4/lib/plpgsql.so: undefined symbol: SPI_push
>
> Can you load plpgsql in your 8.4 cluster? Can you run plpgsql in the
> same system? I'd say ensure this first. If you can't, then drop it and
> try to upgrade. Then in your 10.10 you re-create plpgsql as an extension.

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