Migration of Postgres 9.2 to 9.3 with Homebrew and json_enhancements

30 April 2014   0 comments   PostgreSQL

Mind That Age!

This blog post is 4 years old! Most likely, its content is outdated. Especially if it's technical.

If you run Homebrew on your OSX and you use that to install postgres you will have noted there's a new formula for Postgres 9.3(.4). Yay! (actually this was done many months ago but I'm slow to keep my brews upgraded)

When you run the upgrade you'll notice that psql no longer works because the server can't start.

Bummer! But there's hope: This excellent blog post

That's all you need. ...unless you have some uncompatible library extension installed. E.g. json_enhancements

The problem is that you can't install json_enhancements into a Postgres 9.3 server (json_enhancements is a backport from 9.3 to desperate people still on 9.2). And you can't do the upgrade because the new server has one less installed library. You'll get this after a failing pg_upgrade:

peterbe@mbp:~$ cat loadable_libraries.txt
Could not load library "$libdir/json_enhancements"
ERROR:  could not access file "$libdir/json_enhancements": No such file or directory

I left some more details about this on the pgsql-admin mailing list.

The trick that worked for me was to start the old 9.2 server like this:

/usr/local/Cellar/postgresql/9.2.2/bin/postgres -D /usr/local/var/postgres -p 9000

And now I can open psql against the old data[base] and drop the extension:

$ psql -p 9000 mydatabase
psql (9.3.4, server 9.2.2)
mydatabase=# drop extension json_enhancements;

After I had done that I was able to successfully complete the pg_upgrade.

I hope this helps some other poor sucker stuck in the same chicken and egg situation.

UPDATE

A much better blog post to guide you is Ketia's Blog. For example, here's a post about upgrading from 9.4 to 9.5.

Comments

Your email will never ever be published


Related posts

Previous:
Do you like angular-classy? 29 April 2014
Next:
HTML Tree on Hacker News 18 May 2014
Related by Keyword:
Msgpack vs JSON (with gzip) 19 December 2017
Fastest Redis configuration for Django 11 May 2017
Cope with JSONDecodeError in requests.get().json() in Python 2 and 3 16 November 2016
json-schema-reducer 02 August 2016
Ctags in Atom on OSX 26 February 2016
Related by Text:
jQuery and Highslide JS 08 January 2008
I'm back! Peterbe.com has been renewed 05 June 2005
Anti-McCain propaganda videos 12 August 2008
Ever wondered how much $87 Billion is? 04 November 2003
Guake, not Yakuake or Yeahconsole 23 January 2010