Subject: Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10
 version
To: pgsql-admin@lists.postgresql.org
References: <e95cf94c-b7f1-1963-6647-6335785870d9@zunibal.com>
 <4519fdad-d747-cd39-f6a2-33e7db1f3324@matrix.gatewaynet.com>
From: Ekaterina Amez <ekaterina.amez@zunibal.com>
Message-ID: <a82dd9e3-d388-24d1-95bb-cdb1ac7ee758@zunibal.com>
Date: Thu, 19 Sep 2019 13:35:11 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.8.0
MIME-Version: 1.0
In-Reply-To: <4519fdad-d747-cd39-f6a2-33e7db1f3324@matrix.gatewaynet.com>
Content-Type: multipart/alternative;
 boundary="------------DFFB1BB9CB544654A1FF4D59"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------DFFB1BB9CB544654A1FF4D59
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit


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

--------------DFFB1BB9CB544654A1FF4D59
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <p><br>
    </p>
    <div class="moz-cite-prefix">El 19/9/19 a las 10:07, Achilleas
      Mantzios escribió:<br>
    </div>
    <blockquote type="cite"
      cite="mid:4519fdad-d747-cd39-f6a2-33e7db1f3324@matrix.gatewaynet.com">
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
      <div class="moz-cite-prefix">On 19/9/19 10:49 π.μ., Ekaterina Amez
        wrote:<br>
      </div>
      <blockquote type="cite"
        cite="mid:e95cf94c-b7f1-1963-6647-6335785870d9@zunibal.com">
        <meta http-equiv="content-type" content="text/html;
          charset=UTF-8">
        <p>Hi all,</p>
        <p>I've been looking in the archives and googled this problem
          but not found really a solution nor an explanation so finally
          posting here.</p>
        <p>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: <br>
        </p>
        <pre>
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 &gt;&gt; "pg_upgrade_server.log" 2&gt;&amp;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
</pre>
      </blockquote>
      <br>
      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.<br>
    </blockquote>
    <p><br>
    </p>
    <p>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):</p>
    <p>CREATE OR REPLACE FUNCTION fn_now()<br>
      RETURNS CHAR(14) AS '<br>
      DECLARE<br>
          _resultado CHAR(14);<br>
      BEGIN<br>
          SELECT to_char(NOW(), ''YYYYMMDDHH24MISS'') INTO _resultado;<br>
          RETURN _resultado;<br>
      END;<br>
      ' LANGUAGE plpgsql;<br>
    </p>
    <p>I've created it in PG 10.10 and called from a select: no problem.</p>
    <p>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.". <br>
    </p>
    <p>With these results, finally I've:</p>
    <p>    1- modified template1 in PG 8.4 server to include plpgsql
      language, so it can be inherited in new databases created from
      this template. <br>
    </p>
    <p>    2- Dropped and recreated my database (let's call it newdb).</p>
    <p>    3- Modified backup file to remove the part that creates the
      function plpgsql_call_handler().</p>
    <p>    4- Restored modified backup.</p>
    <pre><font color="#8AE234"><b>ekaterina@mymachine</b></font>:<font color="#729FCF"><b>~/temp</b></font>$ /usr/lib/postgresql/8.4/bin/psql -U postgres
psql (8.4.22)

postgres=# \c template1

template1=# CREATE LANGUAGE plpgsql;
CREATE LANGUAGE
</pre>
    <pre>template1=# create database newdb with owner=root template=template1;
CREATE DATABASE
</pre>
    <pre>newdb=# \q
<font color="#8AE234"><b>ekaterina@mymachine</b></font>:<font color="#729FCF"><b>~/temp</b></font>$ /usr/lib/postgresql/8.4/bin/psql -U root -d newdb &lt; backup_from_7_14.sql &gt; errors.log

</pre>
    <p>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.</p>
    <p> 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.</p>
    <p><br>
    </p>
    <p>Thanks Achilleas for your tips.<br>
    </p>
    <p><br>
    </p>
    <p><br>
    </p>
    <blockquote type="cite"
      cite="mid:4519fdad-d747-cd39-f6a2-33e7db1f3324@matrix.gatewaynet.com">
      --
      <pre class="moz-signature" cols="200">Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt</pre>
    </blockquote>
  </body>
</html>

--------------DFFB1BB9CB544654A1FF4D59--