pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPg_upgrade error could not load library from 7.14/8.4 to 10.10 version
13+ messages / 3 participants
[nested] [flat]
* Pg_upgrade error could not load library from 7.14/8.4 to 10.10 version
@ 2019-09-19 07:49 Ekaterina Amez <ekaterina.amez@zunibal.com>
0 siblings, 1 reply; 13+ messages in thread
From: Ekaterina Amez @ 2019-09-19 07:49 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
*postgre*s 1920 1 6 08:46 ? 00:00:00 /usr/lib/*postgre*sql/8.4/bin/*postgre*s -D /var/lib/*postgre*sql/8.4/main -c config_file=/etc/*postgre*sql/8.4/main/*postgre*sql.conf
*postgre*s 1921 1 0 08:46 ? 00:00:00 /usr/lib/*postgre*sql/10/bin/*postgre*s -D /var/lib/*postgre*sql/10/main -c config_file=/etc/*postgre*sql/10/main/*postgre*sql.conf
*postgre*s 1923 1921 0 08:46 ? 00:00:00*postgre*s: 10/main: checkpointer process
*postgre*s 1924 1921 0 08:46 ? 00:00:00*postgre*s: 10/main: writer process
*postgre*s 1925 1921 0 08:46 ? 00:00:00*postgre*s: 10/main: wal writer process
*postgre*s 1926 1921 0 08:46 ? 00:00:00*postgre*s: 10/main: autovacuum launcher process
*postgre*s 1927 1921 0 08:46 ? 00:00:00*postgre*s: 10/main: stats collector process
*postgre*s 1928 1921 0 08:46 ? 00:00:00*postgre*s: 10/main: bgworker: logical replication launcher
*postgre*s 1931 1920 0 08:46 ? 00:00:00*postgre*s: writer process
*postgre*s 1932 1920 0 08:46 ? 00:00:00*postgre*s: wal writer process
*postgre*s 1933 1920 0 08:46 ? 00:00:00*postgre*s: autovacuum launcher process
*postgre*s 1934 1920 0 08:46 ? 00:00:00*postgre*s: 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 conexiones
listo
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á apagado
listo
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 connections
done
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 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
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10 version
@ 2019-09-19 08:07 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Ekaterina Amez <ekaterina.amez@zunibal.com>
0 siblings, 1 reply; 13+ messages in thread
From: Achilleas Mantzios @ 2019-09-19 08:07 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
> *postgre*s 1920 1 6 08:46 ? 00:00:00 /usr/lib/*postgre*sql/8.4/bin/*postgre*s -D /var/lib/*postgre*sql/8.4/main -c config_file=/etc/*postgre*sql/8.4/main/*postgre*sql.conf
> *postgre*s 1921 1 0 08:46 ? 00:00:00 /usr/lib/*postgre*sql/10/bin/*postgre*s -D /var/lib/*postgre*sql/10/main -c config_file=/etc/*postgre*sql/10/main/*postgre*sql.conf
> *postgre*s 1923 1921 0 08:46 ? 00:00:00*postgre*s: 10/main: checkpointer process
> *postgre*s 1924 1921 0 08:46 ? 00:00:00*postgre*s: 10/main: writer process
> *postgre*s 1925 1921 0 08:46 ? 00:00:00*postgre*s: 10/main: wal writer process
> *postgre*s 1926 1921 0 08:46 ? 00:00:00*postgre*s: 10/main: autovacuum launcher process
> *postgre*s 1927 1921 0 08:46 ? 00:00:00*postgre*s: 10/main: stats collector process
> *postgre*s 1928 1921 0 08:46 ? 00:00:00*postgre*s: 10/main: bgworker: logical replication launcher
> *postgre*s 1931 1920 0 08:46 ? 00:00:00*postgre*s: writer process
> *postgre*s 1932 1920 0 08:46 ? 00:00:00*postgre*s: wal writer process
> *postgre*s 1933 1920 0 08:46 ? 00:00:00*postgre*s: autovacuum launcher process
> *postgre*s 1934 1920 0 08:46 ? 00:00:00*postgre*s: 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 conexiones
> listo
> 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á apagado
> listo
> 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 connections
> done
> 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 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.
> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10 version
@ 2019-09-19 11:35 Ekaterina Amez <ekaterina.amez@zunibal.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 13+ messages in thread
From: Ekaterina Amez @ 2019-09-19 11:35 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10 version
@ 2019-09-19 13:31 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Ekaterina Amez <ekaterina.amez@zunibal.com>
1 sibling, 0 replies; 13+ messages in thread
From: Achilleas Mantzios @ 2019-09-19 13:31 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 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;
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10 version
@ 2019-09-19 13:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ekaterina Amez <ekaterina.amez@zunibal.com>
1 sibling, 2 replies; 13+ messages in thread
From: Tom Lane @ 2019-09-19 13:57 UTC (permalink / raw)
To: Ekaterina Amez <ekaterina.amez@zunibal.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10 version
@ 2019-09-19 14:07 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 13+ messages in thread
From: Achilleas Mantzios @ 2019-09-19 14:07 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 could not 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10 version
@ 2019-09-20 07:05 Ekaterina Amez <ekaterina.amez@zunibal.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 13+ messages in thread
From: Ekaterina Amez @ 2019-09-20 07:05 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 down
done
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10 version
@ 2019-09-20 07:26 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Ekaterina Amez <ekaterina.amez@zunibal.com>
0 siblings, 1 reply; 13+ messages in thread
From: Achilleas Mantzios @ 2019-09-20 07:26 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 down
> done
> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10 version
@ 2019-09-20 09:22 Ekaterina Amez <ekaterina.amez@zunibal.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 13+ messages in thread
From: Ekaterina Amez @ 2019-09-20 09:22 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10 version
@ 2019-09-20 09:41 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Ekaterina Amez <ekaterina.amez@zunibal.com>
0 siblings, 1 reply; 13+ messages in thread
From: Achilleas Mantzios @ 2019-09-20 09:41 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 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?
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.
>
>
>
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10 version
@ 2019-09-23 07:45 Ekaterina Amez <ekaterina.amez@zunibal.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 13+ messages in thread
From: Ekaterina Amez @ 2019-09-23 07:45 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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;
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10 version
@ 2019-09-23 08:18 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Ekaterina Amez <ekaterina.amez@zunibal.com>
0 siblings, 1 reply; 13+ messages in thread
From: Achilleas Mantzios @ 2019-09-23 08:18 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
>
>
>
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10 version
@ 2019-09-23 08:30 Ekaterina Amez <ekaterina.amez@zunibal.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 13+ messages in thread
From: Ekaterina Amez @ 2019-09-23 08:30 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
>>
>>
>>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2019-09-23 08:30 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-09-19 07:49 Pg_upgrade error could not load library from 7.14/8.4 to 10.10 version Ekaterina Amez <ekaterina.amez@zunibal.com>
2019-09-19 08:07 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2019-09-19 11:35 ` Ekaterina Amez <ekaterina.amez@zunibal.com>
2019-09-19 13:31 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2019-09-19 13:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-09-19 14:07 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2019-09-20 07:05 ` Ekaterina Amez <ekaterina.amez@zunibal.com>
2019-09-20 07:26 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2019-09-20 09:22 ` Ekaterina Amez <ekaterina.amez@zunibal.com>
2019-09-20 09:41 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2019-09-23 07:45 ` Ekaterina Amez <ekaterina.amez@zunibal.com>
2019-09-23 08:18 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2019-09-23 08:30 ` Ekaterina Amez <ekaterina.amez@zunibal.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox