Thread: pg_upgrade + Extensions

pg_upgrade + Extensions

From

"David E. Wheeler"

Date:

10 July 2015, 20:28:49

Hackers,

My co-workers tell me that pg_upgrade told them to drop the colnames and hostname extensions before upgrading from 9.3
to9.4. Fortunately, Postgres had  not recorded any dependencies on functions from these extensions (not sure why not,
sincewe do user them, but for the moment grateful), so it wasn’t a big deal to drop them and then add them back after
finishingthe upgrade. But frankly I don’t understand why this was necessary. It’s true that they’re C extensions with
sharedlibraries, but there are separate .so files for the 9.3 and 9.4 installs. 

Would there be a way to convince pg_upgrade that extensions don’t need to be dropped before upgrading?

Thanks,

David

Re: pg_upgrade + Extensions

From

Tom Lane

Date:

10 July 2015, 20:59:49

"David E. Wheeler" <david@justatheory.com> writes:
> My co-workers tell me that pg_upgrade told them to drop the colnames and
> hostname extensions before upgrading from 9.3 to 9.4.

Really?  I see nothing in the source code that would print any such
advice.

There *is* a check on whether .so libraries used by the source
installation exist in the destination one.  But the preferred way to
deal with that type of complaint is to install the needed libraries
(in the destination's lib/ folder).  You shouldn't have to drop anything
as long as you have a copy of the extension that works for the new PG
version.
        regards, tom lane

Re: pg_upgrade + Extensions

From

Smitha Pamujula

Date:

10 July 2015, 21:32:17

Tom,

I just tested and yes that worked. Once we have the new library for the hostname, pg_upgrade is not complaining about the hostname extension.

Another thing we found is this. We needed to drop json_build extension before the upgrade. However the upgrade fails with the following error and the way to resolve it is to install json_build94 library. Any ideas why this might be?

/usr/pgsql-9.4/bin/pg_upgrade --check --link

...

....

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@pdxdvrptsrd04 ~]$ cat loadable_libraries.txt

Could not load library "json_build"

ERROR: could not access file "json_build": No such file or directory

Thanks

Smitha

On Fri, Jul 10, 2015 at 10:59 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David E. Wheeler" <david@justatheory.com> writes:
> My co-workers tell me that pg_upgrade told them to drop the colnames and
> hostname extensions before upgrading from 9.3 to 9.4.

Really? I see nothing in the source code that would print any such
advice.

There *is* a check on whether .so libraries used by the source
installation exist in the destination one. But the preferred way to
deal with that type of complaint is to install the needed libraries
(in the destination's lib/ folder). You shouldn't have to drop anything
as long as you have a copy of the extension that works for the new PG
version.

regards, tom lane

Smitha Pamujula

Database Administrator // The Watch Woman

Direct: 503.943.6764

Mobile: 503.290.6214 // Twitter: iovation
www.iovation.com

Re: pg_upgrade + Extensions

From

"David E. Wheeler"

Date:

11 July 2015, 00:06:10

On Jul 10, 2015, at 11:32 AM, Smitha Pamujula <smitha.pamujula@iovation.com> wrote:

> I just tested and yes that worked. Once we have the new library for the hostname, pg_upgrade is not complaining about
thehostname extension.  

Great, thank you Smitha -- and Tom for the pointer.

> 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@pdxdvrptsrd04 ~]$ cat loadable_libraries.txt
> Could not load library "json_build"
> ERROR:  could not access file "json_build": No such file or directory

So you drop the json_build extension before upgrading, but pg_upgrade still complains that it’s missing? That seems
odd.

Best,

David

Re: pg_upgrade + Extensions

From

Andrew Dunstan

Date:

11 July 2015, 02:26:45

On Fri, Jul 10, 2015 at 5:05 PM, David E. Wheeler <david@justatheory.com> wrote:

On Jul 10, 2015, at 11:32 AM, Smitha Pamujula <smitha.pamujula@iovation.com> wrote:

> 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@pdxdvrptsrd04 ~]$ cat loadable_libraries.txt
> Could not load library "json_build"
> ERROR: could not access file "json_build": No such file or directory

So you drop the json_build extension before upgrading, but pg_upgrade still complains that it’s missing? That seems odd.

Are you sure the extension was uninstalled from every database in the cluster? This seems likely to occur when you forgot to uninstall it from some database (e.g. template1)

cheers

andrew

Re: pg_upgrade + Extensions

From

Smitha Pamujula

Date:

13 July 2015, 20:12:25

Yes. I have checked that the extension didn't exist in any of the databases. I used \dx to see if there was json_build was listed and i didnt see any. Is that sufficient to check its existence. I am about to do another testing in a few minutes on a different machine. I will capture before/after shots

Thanks

On Fri, Jul 10, 2015 at 4:26 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

On Fri, Jul 10, 2015 at 5:05 PM, David E. Wheeler <david@justatheory.com> wrote:
On Jul 10, 2015, at 11:32 AM, Smitha Pamujula <smitha.pamujula@iovation.com> wrote:

> 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@pdxdvrptsrd04 ~]$ cat loadable_libraries.txt
> Could not load library "json_build"
> ERROR: could not access file "json_build": No such file or directory

So you drop the json_build extension before upgrading, but pg_upgrade still complains that it’s missing? That seems odd.

Are you sure the extension was uninstalled from every database in the cluster? This seems likely to occur when you forgot to uninstall it from some database (e.g. template1)

cheers

andrew

Smitha Pamujula

Database Administrator // The Watch Woman

Direct: 503.943.6764

Mobile: 503.290.6214 // Twitter: iovation
www.iovation.com

Re: pg_upgrade + Extensions

From

Andrew Dunstan

Date:

13 July 2015, 21:56:50

On 07/13/2015 01:12 PM, Smitha Pamujula wrote:
> Yes. I have checked that the extension didn't exist in any of the 
> databases. I used \dx to see if there was json_build was listed and i 
> didnt see any. Is that sufficient to check its existence. I am about 
> to do another testing in a few minutes on a different machine. I will 
> capture before/after shots
>
>

Please don't top-post on the PostgreSQL lists - see 
<http://idallen.com/topposting.html>

In theory it should be enough if it was installed in the standard way. 
But a more thorough procedure would be to run this command:
   select count(*)   from pg_proc   where prosrc ~ 'json_build';

Here's a one-liner that will check every database for you:
   for db in `psql  -t -c 'select datname from pg_database where   datallowconn'` ; do C=`psql -t -c "select count(*)
frompg_proc   where prosrc ~ 'json_build'" $db`; echo $db $C; done
 

cheers

andrew

Re: pg_upgrade + Extensions

From

Smitha Pamujula

Date:

14 July 2015, 19:49:07

<div dir="ltr"><br /><div class="gmail_extra"><br /><div class="gmail_quote">On Mon, Jul 13, 2015 at 11:56 AM, Andrew
Dunstan<span dir="ltr"><<a href="mailto:andrew@dunslane.net" target="_blank">andrew@dunslane.net</a>></span>
wrote:<br/><blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span
class=""><br/> On 07/13/2015 01:12 PM, Smitha Pamujula wrote:<br /><blockquote class="gmail_quote" style="margin:0px
0px0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"> Yes. I
havechecked that the extension didn't exist in any of the databases. I used \dx to see if there was json_build was
listedand i didnt see any. Is that sufficient to check its existence. I am about to do another testing in a few minutes
ona different machine. I will capture before/after shots<br /><br /><br /></blockquote><br /></span> Please don't
top-poston the PostgreSQL lists - see <<a href="http://idallen.com/topposting.html" rel="noreferrer"
target="_blank">http://idallen.com/topposting.html</a>><br/><br /> In theory it should be enough if it was installed
inthe standard way. But a more thorough procedure would be to run this command:<br /><br />    select count(*)<br />  
 frompg_proc<br />    where prosrc ~ 'json_build';<br /><br /> Here's a one-liner that will check every database for
you:<br/><br />    for db in `psql  -t -c 'select datname from pg_database where<br />    datallowconn'` ; do C=`psql
-t-c "select count(*) from pg_proc<br />    where prosrc ~ 'json_build'" $db`; echo $db $C; done<br /><br />
cheers<spanclass=""><font color="#888888"><br /><br /> andrew<br /></font></span></blockquote></div><br /><br /><font
color="#0000ff">Ki have tested it on our db. Sorry for the long mail, most of it is just output from the commands. My
commentsare in blue.</font></div><div class="gmail_extra"><font color="#0000ff"><br /></font></div><div
class="gmail_extra"><fontcolor="#0000ff">Pre-upgrade:<br /></font><br /></div><div class="gmail_extra"><pre
style="color:rgb(0,0,0);background-image:initial;background-repeat:initial">psql<span
style="color:rgb(128,128,48)">-</span>l                                 List of databases  Name    <span
style="color:rgb(128,128,48)">|</span> Owner   <span style="color:rgb(128,128,48)">|</span> Encoding <span
style="color:rgb(128,128,48)">|</span>  Collate   <span style="color:rgb(128,128,48)">|</span>    Ctype    <span
style="color:rgb(128,128,48)">|</span>   Access privileges 
<span style="color:rgb(128,128,48)">-</span><span style="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">+</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">+</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">+</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">+</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">+</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span>postgres  <span
style="color:rgb(128,128,48)">|</span>postgres <span style="color:rgb(128,128,48)">|</span> UTF8     <span
style="color:rgb(128,128,48)">|</span>en_US<span style="color:rgb(128,128,48)">.</span>UTF<span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(0,140,0)">8</span> <span
style="color:rgb(128,128,48)">|</span>en_US<span style="color:rgb(128,128,48)">.</span>UTF<span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(0,140,0)">8</span> <span
style="color:rgb(128,128,48)">|</span>reporting<span style="color:rgb(128,128,48)">|</span> sqitch   <span
style="color:rgb(128,128,48)">|</span>UTF8     <span style="color:rgb(128,128,48)">|</span> en_US<span
style="color:rgb(128,128,48)">.</span>UTF<spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(0,140,0)">8</span><span style="color:rgb(128,128,48)">|</span> en_US<span
style="color:rgb(128,128,48)">.</span>UTF<spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(0,140,0)">8</span><span style="color:rgb(128,128,48)">|</span> <span
style="color:rgb(128,128,48)">=</span>Tc<spanstyle="color:rgb(128,128,48)">/</span>sqitch              <span
style="color:rgb(128,128,48)">+</span>         <span style="color:rgb(128,128,48)">|</span>          <span
style="color:rgb(128,128,48)">|</span>         <span style="color:rgb(128,128,48)">|</span>             <span
style="color:rgb(128,128,48)">|</span>            <span style="color:rgb(128,128,48)">|</span> sqitch<span
style="color:rgb(128,128,48)">=</span>CTc<spanstyle="color:rgb(128,128,48)">/</span>sqitch       <span
style="color:rgb(128,128,48)">+</span>         <span style="color:rgb(128,128,48)">|</span>          <span
style="color:rgb(128,128,48)">|</span>         <span style="color:rgb(128,128,48)">|</span>             <span
style="color:rgb(128,128,48)">|</span>            <span style="color:rgb(128,128,48)">|</span> owner_gulper<span
style="color:rgb(128,128,48)">=</span>C<spanstyle="color:rgb(128,128,48)">/</span>sqitch   <span
style="color:rgb(128,128,48)">+</span>         <span style="color:rgb(128,128,48)">|</span>          <span
style="color:rgb(128,128,48)">|</span>         <span style="color:rgb(128,128,48)">|</span>             <span
style="color:rgb(128,128,48)">|</span>            <span style="color:rgb(128,128,48)">|</span> owner_reporting<span
style="color:rgb(128,128,48)">=</span>C<spanstyle="color:rgb(128,128,48)">/</span>sqitchtemplate0 <span
style="color:rgb(128,128,48)">|</span>postgres <span style="color:rgb(128,128,48)">|</span> UTF8     <span
style="color:rgb(128,128,48)">|</span>en_US<span style="color:rgb(128,128,48)">.</span>UTF<span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(0,140,0)">8</span> <span
style="color:rgb(128,128,48)">|</span>en_US<span style="color:rgb(128,128,48)">.</span>UTF<span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(0,140,0)">8</span> <span
style="color:rgb(128,128,48)">|</span><span style="color:rgb(128,128,48)">=</span>c<span
style="color:rgb(128,128,48)">/</span>postgres            <span style="color:rgb(128,128,48)">+</span>          <span
style="color:rgb(128,128,48)">|</span>         <span style="color:rgb(128,128,48)">|</span>          <span
style="color:rgb(128,128,48)">|</span>            <span style="color:rgb(128,128,48)">|</span>             <span
style="color:rgb(128,128,48)">|</span>postgres<span style="color:rgb(128,128,48)">=</span>CTc<span
style="color:rgb(128,128,48)">/</span>postgrestemplate1<span style="color:rgb(128,128,48)">|</span> postgres <span
style="color:rgb(128,128,48)">|</span>UTF8     <span style="color:rgb(128,128,48)">|</span> en_US<span
style="color:rgb(128,128,48)">.</span>UTF<spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(0,140,0)">8</span><span style="color:rgb(128,128,48)">|</span> en_US<span
style="color:rgb(128,128,48)">.</span>UTF<spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(0,140,0)">8</span><span style="color:rgb(128,128,48)">|</span> <span
style="color:rgb(128,128,48)">=</span>c<spanstyle="color:rgb(128,128,48)">/</span>postgres             <span
style="color:rgb(128,128,48)">+</span>         <span style="color:rgb(128,128,48)">|</span>          <span
style="color:rgb(128,128,48)">|</span>         <span style="color:rgb(128,128,48)">|</span>             <span
style="color:rgb(128,128,48)">|</span>            <span style="color:rgb(128,128,48)">|</span> postgres<span
style="color:rgb(128,128,48)">=</span>CTc<spanstyle="color:rgb(128,128,48)">/</span>postgres</pre><pre
style="color:rgb(0,0,0);background-image:initial;background-repeat:initial"><br/></pre><pre
style="background-image:initial;background-repeat:initial"><fontcolor="#0000ff" face="arial, helvetica,
sans-serif">Removedthe json_build extension from 9.3 database. Verified here:</font><font color="#000000"> 
</font></pre><pre style="color:rgb(0,0,0);background-image:initial;background-repeat:initial"><pre
style="background-image:initial;background-repeat:initial">fordb in `psql  <span
style="color:rgb(128,128,48)">-</span>t<span style="color:rgb(128,128,48)">-</span>c 'select datname from pg_database
where
<span style="color:rgb(128,128,48)">></span>    datallowconn'` <span style="color:rgb(128,128,48)">;</span> do
C<spanstyle="color:rgb(128,128,48)">=</span>`psql <span style="color:rgb(128,128,48)">-</span>t <span
style="color:rgb(128,128,48)">-</span>c"select count<span style="color:rgb(105,105,105)">(*) from pg_proc</span> 
<span style="color:rgb(105,105,105)">>    where prosrc ~ 'json_build'" $db`; echo $db $C; done</span>
<span style="color:rgb(105,105,105)">template1 0</span>
<span style="color:rgb(105,105,105)">postgres 0</span>
<span style="color:rgb(105,105,105)">reporting 0</span></pre></pre><pre
style="background-image:initial;background-repeat:initial"><fontcolor="#0000ff" face="arial, helvetica,
sans-serif">ThenI installed the pg 9.4 and started the empty instance.</font></pre><pre
style="color:rgb(0,0,0);background-image:initial;background-repeat:initial"><pre
style="background-image:initial;background-repeat:initial">psql<span style="color:rgb(128,128,48)">-</span>d postgres 
psql <span style="color:rgb(128,128,48)">(</span><span style="color:rgb(0,128,0)">9.3</span><span
style="color:rgb(128,128,48)">.</span><spanstyle="color:rgb(0,140,0)">5</span><span
style="color:rgb(128,128,48)">,</span>server <span style="color:rgb(0,128,0)">9.4</span><span
style="color:rgb(128,128,48)">.</span><spanstyle="color:rgb(0,140,0)">4</span><span
style="color:rgb(128,128,48)">)</span>
<span style="color:rgb(227,74,220)">WARNING:</span> psql major version <span style="color:rgb(0,128,0)">9.3</span><span
style="color:rgb(128,128,48)">,</span>server major version <span style="color:rgb(0,128,0)">9.4</span><span
style="color:rgb(128,128,48)">.</span>       Some psql features might not work<span
style="color:rgb(128,128,48)">.</span>
Type <span style="color:rgb(128,0,0)">"</span><span style="color:rgb(0,0,230)">help</span><span
style="color:rgb(128,0,0)">"</span><span style="color:rgb(128,0,0);font-weight:bold">for</span> help<span
style="color:rgb(128,128,48)">.</span>

postgres<span style="color:rgb(128,128,48)">=</span><span style="color:rgb(128,128,48)">#</span> \l
           List of databases  Name    <span style="color:rgb(128,128,48)">|</span>  Owner   <span
style="color:rgb(128,128,48)">|</span>Encoding <span style="color:rgb(128,128,48)">|</span>   Collate   <span
style="color:rgb(128,128,48)">|</span>   Ctype    <span style="color:rgb(128,128,48)">|</span>   Access privileges 
<span style="color:rgb(128,128,48)">-</span><span style="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">+</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">+</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">+</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">+</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">+</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(128,128,48)">-</span>postgres <span style="color:rgb(128,128,48)">|</span> postgres <span
style="color:rgb(128,128,48)">|</span>UTF8     <span style="color:rgb(128,128,48)">|</span> en_US<span
style="color:rgb(128,128,48)">.</span>UTF<spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(0,140,0)">8</span><span style="color:rgb(128,128,48)">|</span> en_US<span
style="color:rgb(128,128,48)">.</span>UTF<spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(0,140,0)">8</span><span style="color:rgb(128,128,48)">|</span>template0 <span
style="color:rgb(128,128,48)">|</span>postgres <span style="color:rgb(128,128,48)">|</span> UTF8     <span
style="color:rgb(128,128,48)">|</span>en_US<span style="color:rgb(128,128,48)">.</span>UTF<span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(0,140,0)">8</span> <span
style="color:rgb(128,128,48)">|</span>en_US<span style="color:rgb(128,128,48)">.</span>UTF<span
style="color:rgb(128,128,48)">-</span><spanstyle="color:rgb(0,140,0)">8</span> <span
style="color:rgb(128,128,48)">|</span><span style="color:rgb(128,128,48)">=</span>c<span
style="color:rgb(128,128,48)">/</span>postgres         <span style="color:rgb(128,128,48)">+</span>          <span
style="color:rgb(128,128,48)">|</span>         <span style="color:rgb(128,128,48)">|</span>          <span
style="color:rgb(128,128,48)">|</span>            <span style="color:rgb(128,128,48)">|</span>             <span
style="color:rgb(128,128,48)">|</span>postgres<span style="color:rgb(128,128,48)">=</span>CTc<span
style="color:rgb(128,128,48)">/</span>postgrestemplate1<span style="color:rgb(128,128,48)">|</span> postgres <span
style="color:rgb(128,128,48)">|</span>UTF8     <span style="color:rgb(128,128,48)">|</span> en_US<span
style="color:rgb(128,128,48)">.</span>UTF<spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(0,140,0)">8</span><span style="color:rgb(128,128,48)">|</span> en_US<span
style="color:rgb(128,128,48)">.</span>UTF<spanstyle="color:rgb(128,128,48)">-</span><span
style="color:rgb(0,140,0)">8</span><span style="color:rgb(128,128,48)">|</span> <span
style="color:rgb(128,128,48)">=</span>c<spanstyle="color:rgb(128,128,48)">/</span>postgres          <span
style="color:rgb(128,128,48)">+</span>         <span style="color:rgb(128,128,48)">|</span>          <span
style="color:rgb(128,128,48)">|</span>         <span style="color:rgb(128,128,48)">|</span>             <span
style="color:rgb(128,128,48)">|</span>            <span style="color:rgb(128,128,48)">|</span> postgres<span
style="color:rgb(128,128,48)">=</span>CTc<spanstyle="color:rgb(128,128,48)">/</span>postgres</pre></pre><pre
style="color:rgb(0,0,0);background-image:initial;background-repeat:initial"><br/></pre><pre
style="background-image:initial;background-repeat:initial"><fontcolor="#0000ff" face="arial, helvetica, sans-serif">Now
Iran the same extension check on the 94.</font></pre><pre
style="background-image:initial;background-repeat:initial"><pre
style="color:rgb(0,0,0);background-image:initial;background-repeat:initial">fordb in `psql  <span
style="color:rgb(128,128,48)">-</span>t<span style="color:rgb(128,128,48)">-</span>c 'select datname from pg_database
where
<span style="color:rgb(128,128,48)">></span>    datallowconn'` <span style="color:rgb(128,128,48)">;</span> do
C<spanstyle="color:rgb(128,128,48)">=</span>`psql <span style="color:rgb(128,128,48)">-</span>t <span
style="color:rgb(128,128,48)">-</span>c"select count<span style="color:rgb(105,105,105)">(*) from pg_proc</span> 
<span style="color:rgb(105,105,105)">>    where prosrc ~ 'json_build'" $db`; echo $db $C; done</span>
<span style="color:rgb(105,105,105)">template1 4</span>
<span style="color:rgb(105,105,105)">postgres 4</span></pre><pre
style="background-image:initial;background-repeat:initial"><fontcolor="#0000ff" face="arial, helvetica, sans-serif">I
seethat its got the new procs as part of the 94. Now if i do the check link its giving me this error.</font></pre><pre
style="background-image:initial;background-repeat:initial"><fontcolor="#000000">[postgres@pdxqarptsrd04 pg_94_upgrade]$
/usr/pgsql-9.4/bin/pg_upgrade--check --link 
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is a superuser                       ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for invalid "line" user columns                    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@pdxqarptsrd04 pg_94_upgrade]$ cat loadable_libraries.txt
Could not load library "$libdir/json_build"
ERROR:  could not access file "$libdir/json_build": No such file or directory</font></pre><pre
style="background-image:initial;background-repeat:initial"><br/></pre><pre
style="background-image:initial;background-repeat:initial">[postgres@pdxqarptsrd04pg_94_upgrade]$ rpm -qa|grep
json_build
json_build93-1.0.0-1iov.x86_64<br /></pre><pre style="background-image:initial;background-repeat:initial"><font
color="#0000ff"face="arial, helvetica, sans-serif">This error will go away only if I install the new
json_build94.</font></pre><prestyle="background-image:initial;background-repeat:initial"><pre
style="color:rgb(0,0,0);background-image:initial;background-repeat:initial"><span
style="color:rgb(166,87,0)">[</span><spanstyle="color:rgb(95,80,53)">postgres@pdxqarptsrd04 pg_94_upgrade</span><span
style="color:rgb(166,87,0)">]</span>$rpm <span style="color:rgb(121,121,151)">-</span><span
style="color:rgb(0,121,151)">qa|grep</span>json_build 
json_build94<span style="color:rgb(128,128,48)">-</span><span style="color:rgb(0,128,0)">1.0</span><span
style="color:rgb(128,128,48)">.</span><spanstyle="color:rgb(0,140,0)">0</span><span
style="color:rgb(128,128,48)">-</span>1iov<spanstyle="color:rgb(128,128,48)">.</span>x86_64 
json_build93<span style="color:rgb(128,128,48)">-</span><span style="color:rgb(0,128,0)">1.0</span><span
style="color:rgb(128,128,48)">.</span><spanstyle="color:rgb(0,140,0)">0</span><span
style="color:rgb(128,128,48)">-</span>1iov<spanstyle="color:rgb(128,128,48)">.</span>x86_64</pre><pre
style="background-image:initial;background-repeat:initial"><fontcolor="#000000">[postgres@pdxqarptsrd04 pg_94_upgrade]$
/usr/pgsql-9.4/bin/pg_upgrade--check --link 
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is a superuser                       ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for invalid "line" user columns                    ok
Checking for presence of required libraries                 ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok

*Clusters are compatible*<br /></font></pre><pre style="background-image:initial;background-repeat:initial"><br
/></pre><prestyle="background-image:initial;background-repeat:initial"><font color="#0000ff" face="arial, helvetica,
sans-serif">Iwas under the impression that we dont need to get the json_build libraries for 94. But the upgrade wont go
forwardwithout that. Are we missing doing something here or is it necessarty to get json_build94 before
upgrade.</font></pre><prestyle="background-image:initial;background-repeat:initial"><font color="#0000ff" face="arial,
helvetica,sans-serif"><br /></font></pre><pre style="background-image:initial;background-repeat:initial"><font
color="#0000ff"face="arial, helvetica, sans-serif">Thanks</font></pre><pre
style="background-image:initial;background-repeat:initial"><fontcolor="#0000ff" face="arial, helvetica,
sans-serif">Smitha</font></pre></pre></pre></div><divclass="gmail_extra"></div></div>

Re: pg_upgrade + Extensions

From

Bruce Momjian

Date:

01 September 2015, 01:18:32

On Tue, Jul 14, 2015 at 09:48:59AM -0700, Smitha Pamujula wrote:
> This error will go away only if I install the new json_build94.
> ........................................................................

> I was under the impression that we dont need to get the json_build
> libraries for 94. But the upgrade wont go forward without that. Are we
> missing doing something here or is it necessarty to get json_build94
> before upgrade.

I am coming very late to this discussion, but I do have some
information.  Basically, pg_upgrade is trying to predict if the
dump/restore will fail by checking references to shared objects in the
old cluster.  It sees a reference to json_build in the old cluster and
assumes this is needed in the new cluster, when in fact it isn't.

We could have hard-coded this knowledge into 9.4 pg_upgrade if we had
known it.  The simple solution is to install json_build94 as you did,
run pg_upgrade, then just uninstall json_build94 as nothing depends on
it.  Not sure if this should be in the pg_upgrade docs or not.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

Re: pg_upgrade + Extensions

From

Smitha Pamujula

Date:

01 September 2015, 02:03:27

Thank you Bruce. So far installing it before have been working well so we will continue with that plan.

I think it would help if its noted somewhere in the document as it would have helped us save some time understanding why it was failing and why it was looking for json_build.

On Mon, Aug 31, 2015 at 3:18 PM, Bruce Momjian <bruce@momjian.us> wrote:

On Tue, Jul 14, 2015 at 09:48:59AM -0700, Smitha Pamujula wrote:
> This error will go away only if I install the new json_build94.
> ........................................................................

> I was under the impression that we dont need to get the json_build
> libraries for 94. But the upgrade wont go forward without that. Are we
> missing doing something here or is it necessarty to get json_build94
> before upgrade.

I am coming very late to this discussion, but I do have some
information. Basically, pg_upgrade is trying to predict if the
dump/restore will fail by checking references to shared objects in the
old cluster. It sees a reference to json_build in the old cluster and
assumes this is needed in the new cluster, when in fact it isn't.

We could have hard-coded this knowledge into 9.4 pg_upgrade if we had
known it. The simple solution is to install json_build94 as you did,
run pg_upgrade, then just uninstall json_build94 as nothing depends on
it. Not sure if this should be in the pg_upgrade docs or not.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

Smitha Pamujula

Database Administrator // The Watch Woman

Direct: 503.943.6764

Mobile: 503.290.6214 // Twitter: iovation
www.iovation.com

Re: pg_upgrade + Extensions

From

Bruce Momjian

Date:

01 September 2015, 02:20:38

On Mon, Aug 31, 2015 at 04:03:20PM -0700, Smitha Pamujula wrote:
> Thank you Bruce. So far installing it before have been working well so we will
> continue with that plan. 
> 
> I think it would help if its noted somewhere in the document as it would have
> helped us save some time understanding why it was failing and why it was
> looking for json_build. 

The problem is that this is a rare case where you had an extension that
was later included in Postgres.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

Re: pg_upgrade + Extensions

From

"David E. Wheeler"

Date:

01 September 2015, 02:21:53

On Aug 31, 2015, at 4:20 PM, Bruce Momjian <bruce@momjian.us> wrote:

>> I think it would help if its noted somewhere in the document as it would have
>> helped us save some time understanding why it was failing and why it was
>> looking for json_build.
>
> The problem is that this is a rare case where you had an extension that
> was later included in Postgres.

Maybe not so rare. Thanks to Andrew, we’ve had to do this for both 9.2-9.3 (json_object) and 9.3-9.4 (json_build).

Best,

David

Re: pg_upgrade + Extensions

From

Andrew Dunstan

Date:

01 September 2015, 02:28:16


On 08/31/2015 07:21 PM, David E. Wheeler wrote:
> On Aug 31, 2015, at 4:20 PM, Bruce Momjian <bruce@momjian.us> wrote:
>
>>> I think it would help if its noted somewhere in the document as it would have
>>> helped us save some time understanding why it was failing and why it was
>>> looking for json_build.
>> The problem is that this is a rare case where you had an extension that
>> was later included in Postgres.
> Maybe not so rare. Thanks to Andrew, we’ve had to do this for both 9.2-9.3 (json_object) and 9.3-9.4 (json_build).
>


Yeah, a lot of people don't like to wait for new stuff. :-)

cheers

andrew

Re: pg_upgrade + Extensions

From

Bruce Momjian

Date:

01 September 2015, 02:32:37

On Mon, Aug 31, 2015 at 07:28:00PM -0400, Andrew Dunstan wrote:
> 
> 
> On 08/31/2015 07:21 PM, David E. Wheeler wrote:
> >On Aug 31, 2015, at 4:20 PM, Bruce Momjian <bruce@momjian.us> wrote:
> >
> >>>I think it would help if its noted somewhere in the document as it would have
> >>>helped us save some time understanding why it was failing and why it was
> >>>looking for json_build.
> >>The problem is that this is a rare case where you had an extension that
> >>was later included in Postgres.
> >Maybe not so rare. Thanks to Andrew, we’ve had to do this for both 9.2-9.3 (json_object) and 9.3-9.4 (json_build).
> >
> 
> 
> Yeah, a lot of people don't like to wait for new stuff. :-)

It might make the most sense to mention this method in the release notes
of the extension.  However, I assume they are not using the extension in
the new server so their is no release to look at.

Still, I don't know how many people are doing this, but the right fix is
to get the names of the modules that are superceeded and tell pg_upgrade
to skip them.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

Re: pg_upgrade + Extensions

From

Smitha Pamujula

Date:

01 September 2015, 02:42:06

pg_upgrade skipping the modules makes the most sense to me as well.

On Mon, Aug 31, 2015 at 4:32 PM, Bruce Momjian <bruce@momjian.us> wrote:

On Mon, Aug 31, 2015 at 07:28:00PM -0400, Andrew Dunstan wrote:
>
>
> On 08/31/2015 07:21 PM, David E. Wheeler wrote:
> >On Aug 31, 2015, at 4:20 PM, Bruce Momjian <bruce@momjian.us> wrote:
> >
> >>>I think it would help if its noted somewhere in the document as it would have
> >>>helped us save some time understanding why it was failing and why it was
> >>>looking for json_build.
> >>The problem is that this is a rare case where you had an extension that
> >>was later included in Postgres.
> >Maybe not so rare. Thanks to Andrew, we’ve had to do this for both 9.2-9.3 (json_object) and 9.3-9.4 (json_build).
> >
>
>
> Yeah, a lot of people don't like to wait for new stuff. :-)

It might make the most sense to mention this method in the release notes
of the extension. However, I assume they are not using the extension in
the new server so their is no release to look at.

Still, I don't know how many people are doing this, but the right fix is
to get the names of the modules that are superceeded and tell pg_upgrade
to skip them.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

Smitha Pamujula

Database Administrator // The Watch Woman

Direct: 503.943.6764

Mobile: 503.290.6214 // Twitter: iovation
www.iovation.com

Re: pg_upgrade + Extensions

From

Andrew Dunstan

Date:

01 September 2015, 02:53:02


On 08/31/2015 07:32 PM, Bruce Momjian wrote:
> On Mon, Aug 31, 2015 at 07:28:00PM -0400, Andrew Dunstan wrote:
>>
>> On 08/31/2015 07:21 PM, David E. Wheeler wrote:
>>> On Aug 31, 2015, at 4:20 PM, Bruce Momjian <bruce@momjian.us> wrote:
>>>
>>>>> I think it would help if its noted somewhere in the document as it would have
>>>>> helped us save some time understanding why it was failing and why it was
>>>>> looking for json_build.
>>>> The problem is that this is a rare case where you had an extension that
>>>> was later included in Postgres.
>>> Maybe not so rare. Thanks to Andrew, we’ve had to do this for both 9.2-9.3 (json_object) and 9.3-9.4 (json_build).
>>>
>>
>> Yeah, a lot of people don't like to wait for new stuff. :-)
> It might make the most sense to mention this method in the release notes
> of the extension.  However, I assume they are not using the extension in
> the new server so their is no release to look at.
>
> Still, I don't know how many people are doing this, but the right fix is
> to get the names of the modules that are superceeded and tell pg_upgrade
> to skip them.
>


I don't think this knowledge should be hardcoded in pg_upgrade. I could
see some point in a switch that would tell pg_upgrade a list of
extensions to ignore.

cheers

andrew

Re: pg_upgrade + Extensions

From

Tom Lane

Date:

01 September 2015, 02:58:40

Andrew Dunstan <andrew@dunslane.net> writes:
> On 08/31/2015 07:32 PM, Bruce Momjian wrote:
>> Still, I don't know how many people are doing this, but the right fix is
>> to get the names of the modules that are superceeded and tell pg_upgrade
>> to skip them.

> I don't think this knowledge should be hardcoded in pg_upgrade. I could 
> see some point in a switch that would tell pg_upgrade a list of 
> extensions to ignore.

That would not be terribly helpful for cases where the pg_upgrade call is
embedded in some wrapper script or other.

In any case, there is plenty of precedent for hard-coding knowledge about
specific version updates into pg_upgrade.  The question here is whether
it's feasible to handle extensions that way.  I think we could reasonably
expect to know about cases where a formerly separate extension got
integrated into core, but are there other cases where pg_upgrade would
need to ignore an extension in the old database?
        regards, tom lane

Re: pg_upgrade + Extensions

From

"David E. Wheeler"

Date:

01 September 2015, 03:04:06

On Aug 31, 2015, at 4:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> In any case, there is plenty of precedent for hard-coding knowledge about
> specific version updates into pg_upgrade.  The question here is whether
> it's feasible to handle extensions that way.  I think we could reasonably
> expect to know about cases where a formerly separate extension got
> integrated into core,

+1

> but are there other cases where pg_upgrade would
> need to ignore an extension in the old database?

Not that I can think of, unless it’s already present because it was in template1 or something.

David