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>
 <a82dd9e3-d388-24d1-95bb-cdb1ac7ee758@zunibal.com>
 <22046.1568901429@sss.pgh.pa.us>
From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Message-ID: <1cbc7491-b9a3-8130-b959-2f3a2e4dd4a2@matrix.gatewaynet.com>
Date: Thu, 19 Sep 2019 17:07:22 +0300
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:60.0) Gecko/20100101
 Thunderbird/60.3.2
MIME-Version: 1.0
In-Reply-To: <22046.1568901429@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

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