Re: [HACKERS] pg_upgrade + Extensions
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 <br...@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 <br...@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: [HACKERS] pg_upgrade + Extensions
pg_upgrade skipping the modules makes the most sense to me as well. On Mon, Aug 31, 2015 at 4:32 PM, Bruce Momjian <br...@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 <br...@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 <br...@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: [HACKERS] pg_upgrade + Extensions
On Mon, Jul 13, 2015 at 11:56 AM, Andrew Dunstan and...@dunslane.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@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 typesok Checking for contrib/isn with bigint-passing mismatch ok Checking for invalid line user columnsok 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 [postgres@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@pdxqarptsrd04 pg_94_upgrade]$ rpm -qa|grep
Re: [HACKERS] pg_upgrade + Extensions
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 and...@dunslane.net wrote: On Fri, Jul 10, 2015 at 5:05 PM, David E. Wheeler da...@justatheory.com wrote: On Jul 10, 2015, at 11:32 AM, Smitha Pamujula smitha.pamuj...@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: [HACKERS] pg_upgrade + Extensions
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 t...@sss.pgh.pa.us wrote: David E. Wheeler da...@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
