From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ekaterina Amez <ekaterina.amez@zunibal.com>
cc: pgsql-admin@lists.postgresql.org
Subject: Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10
 version
In-reply-to: <a82dd9e3-d388-24d1-95bb-cdb1ac7ee758@zunibal.com>
References: <e95cf94c-b7f1-1963-6647-6335785870d9@zunibal.com>
 <4519fdad-d747-cd39-f6a2-33e7db1f3324@matrix.gatewaynet.com>
 <a82dd9e3-d388-24d1-95bb-cdb1ac7ee758@zunibal.com>
Comments: In-reply-to Ekaterina Amez <ekaterina.amez@zunibal.com>
	message dated "Thu, 19 Sep 2019 13:35:11 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <22045.1568901429.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 19 Sep 2019 09:57:09 -0400
Message-ID: <22046.1568901429@sss.pgh.pa.us>
Precedence: bulk

Ekaterina Amez <ekaterina.amez@zunibal.com> writes:
> El 19/9/19 a las 10:07, Achilleas Mantzios escribi=C3=B3:
>> On 19/9/19 10:49 =CF=80.=CE=BC., Ekaterina Amez wrote:
>>> 2019-09-19 08:53:50.345 CEST [2283] postgres@template1 ERROR:  could n=
ot load library "/usr/lib/postgresql/8.4/lib/plpgsql.so": /usr/lib/postgre=
sql/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 =3D "$libdir/plpgsql",
relying on the server to subsitute an appropriate path for $libdir.

> With these results, finally I've:
>  =C2=A0=C2=A0=C2=A0 1- modified template1 in PG 8.4 server to include pl=
pgsql language, =

> so it can be inherited in new databases created from this template.
>  =C2=A0=C2=A0=C2=A0 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