[{"_1":2,"_34":-5,"_35":-5},"loaderData",{"_3":4,"_9":10},"root",{"_5":6,"_7":8},"url","http://www-origin.peterbe.com/oc-PostgreSQL/p3","method","GET","routes/home-splat",{"_11":12,"_14":15,"_29":-5,"_30":31,"_32":33},"categories",[13],"PostgreSQL","posts",[16],{"_17":18,"_19":20,"_21":22,"_23":24,"_25":26,"_11":27,"_28":-5},"title","Migration of Postgres 9.2 to 9.3 with Homebrew and json_enhancements","oid","pg-9.2-to-9.3-json_enhancement","pub_date","2014-04-30T17:08:25Z","html","

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:

\npeterbe@mbp:~$ cat loadable_libraries.txt\nCould not load library \"$libdir/json_enhancements\"\nERROR:  could not access file \"$libdir/json_enhancements\": No such file or directory\n

\n\n

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:

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

\n\n

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

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

\n\n

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",0,[13],"split","nextPage","previousPage",2,"page",3,"actionData","errors"]

Peterbe.com

Migration of Postgres 9.2 to 9.3 with Homebrew and json_enhancements

April 30, 2014