PostgreSQL Upgrade 9.4.4 to 9.5.3

August 17, 2016

At work, we have an old python/django/postgres app that is still in active use, and a very similar new one. For reasons beyond my control, the production db server for the new app is 9.5, for the old app it is 9.4. On my local OS X box, I was running 9.4.4.

This worked fine until the new app was released to production, some real data was created via actual usage, and we wanted to pull a copy of the production data to load locally:

pg_dump: aborting because of server version mismatch

So I upgraded to 9.5.3 using brew. It warned me that I would need to run pg_upgrade to upgrade data stored in PostgreSQL data files, thus avoiding a dump/reload for each db.

I ran pg_upgrade according to these instructions, which were conveniently similar to what I was doing. Unfortunately, it failed saying:

Your installation contains one of the reg* data types in user tables. These data types reference system OIDs that are not preserved by pg_upgrade, so this cluster cannot currently be upgraded. You can remove the problem tables and restart the upgrade. A list of the problem columns is in the file: tables_using_reg.txt Failure, exiting

And for each of my older dbs, these lines were listed in tables_using_reg.txt:

Database: xxxxxx-yyy

Also, in the console, every 10 seconds (!!) there was one of these:

8/17/16 7:58:43.376 AM[1]: (homebrew.mxcl.postgresql) Service only ran for 0 seconds. Pushing respawn out by 10 seconds.

So, I could not run the db server because I needed to run pg_upgrade, but I could not run pg_upgrade because I needed to delete these old tables that cannot be upgraded.

Fortunately I had not “cleaned up” the old 9.4.4 version of postgres, so I went to /usr/local/Cellar/postgresql/9.4.4/bin and ran:

./pg_ctl start -l /usr/local/var/postgres/server.log -D /usr/local/var/postgres

To my relief, it started up just like it should and I could list all of my databases. I deleted (./dropdb) most of them and ran ./psql on the few I wanted to keep. After some self-inflicted confusion over pg_catalog.pg_ts_dict vs. public.pg_ts_dict, I managed to drop both of the above offending tables, starting with public prefix, not pg_catalog.

# drop table public.pg_ts_dict;
# drop table public.pg_ts_parser;

I stopped the old server:

./pg_ctl stop -D /usr/local/var/postgres

and ran pg_upgrade again:

pg_upgrade \
-d /usr/local/var/postgres \
-D /usr/local/var/postgres9.5 \
-b /usr/local/Cellar/postgresql/9.4.4/bin \
-B /usr/local/Cellar/postgresql/9.5.3/bin/ \

That time it worked. As did steps 5 and 6 from the article. In all, I followed his steps 1,2, 3, 5, 6.

It is not great to be testing locally with a different version of the db than on production. Unless/until the old app db server is upgraded, it might make sense for some of us to stick to the 9.4 version locally, and also on our test servers. The copy of the new app fetched with pg_dump 9.5 can be loaded into a 9.4 copy of postgres just fine.


Leave a Reply