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