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 public.pg_ts_dict.dict_init public.pg_ts_dict.dict_lexize public.pg_ts_parser.prs_start public.pg_ts_parser.prs_nexttoken public.pg_ts_parser.prs_end public.pg_ts_parser.prs_headline public.pg_ts_parser.prs_lextype
Also, in the console, every 10 seconds (!!) there was one of these:
8/17/16 7:58:43.376 AM com.apple.xpc.launchd: (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/ \ -v
That time it worked. As did steps 5 and 6 from the mattbrictson.com 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.