Re: pg_upgrade + Extensions

On Mon, Jul 13, 2015 at 11:56 AM, Andrew Dunstan <andrew(at)dunslane(dot)net>
wrote:

>
> 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(*) from pg_proc
> where prosrc ~ 'json_build'" $db`; echo $db $C; done
>
> cheers
>
> andrew
>

K i have tested it on our db. Sorry for the long mail, most of it is just
output from the commands. My comments are in blue.

Pre-upgrade:

psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype |
Access privileges-----------+----------+----------+-------------+-------------+--------------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
reporting | sqitch | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=Tc/sqitch +
| | | | |
sqitch=CTc/sqitch +
| | | | |
owner_gulper=C/sqitch +
| | | | |
owner_reporting=C/sqitch
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres +
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres +
| | | | |
postgres=CTc/postgres

Removed the json_build extension from 9.3 database. Verified here:

for db in `psql -t -c 'select datname from pg_database where>
datallowconn'` ; do C=`psql -t -c "select count(*) from pg_proc>
where prosrc ~ 'json_build'" $db`; echo $db $C; donetemplate1
0postgres 0reporting 0

Then I installed the pg 9.4 and started the empty instance.

psql -d postgres
psql (9.3.5, server 9.4.4)WARNING: psql major version 9.3, server
major version 9.4.
Some psql features might not work.
Type "help" for help.

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype |
Access privileges-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres +
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres +
| | | | |
postgres=CTc/postgres

Now I ran the same extension check on the 94.

for db in `psql -t -c 'select datname from pg_database where>
datallowconn'` ; do C=`psql -t -c "select count(*) from pg_proc>
where prosrc ~ 'json_build'" $db`; echo $db $C; donetemplate1
4postgres 4

I see that its got the new procs as part of the 94. Now if i do the
check link its giving me this error.

[postgres(at)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(at)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

[postgres(at)pdxqarptsrd04 pg_94_upgrade]$ rpm -qa|grep json_build
json_build93-1.0.0-1iov.x86_64

This error will go away only if I install the new json_build94.

[postgres(at)pdxqarptsrd04 pg_94_upgrade]$ rpm -qa|grep json_build
json_build94-1.0.0-1iov.x86_64
json_build93-1.0.0-1iov.x86_64

[postgres(at)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*

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.

Thanks

Smitha