Wednesday, March 16, 2016

Postgresql upgrade issues - Checking for reg* system OID user data types

So I am prepping for an upgrade of postgres from version 9.2 to 9.4. Since this is a major version upgrade, I chose to use pg_upgrade to try and minimize downtime. My first attempt in one of our dev environments went fairly well, up until I actually launched the upgrade process.

My stopwatch was ready, my commands were ready, my server was ready!

Start the stopwatch...
#/etc/init.d/postgresql-9.2  stop#sudo -u postgres /usr/pgsql-9.4/bin/pg_upgrade --old-datadir=/var/lib/pgsql/9.2/data/ --new-datadir=/var/lib/pgsql/9.4/data/ --old-bindir=/usr/pgsql-9.2/bin/ --new-bindir=/usr/pgsql-9.4/bin/


HERE WE GO!

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 types                fatal

NOOOOOO!!!!




Quick.....
#/etc/init.d/postgresql-9.2 start

Thank goodness that was only dev... So what now?? Running cat of tables_using_reg.txt, showed me that there were several tables affected:

  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

The good news is, it looks like these are remnants of the old pgsql 8.x days when tsearch2 was not part of the distribution.

My solution? Drop pg_ts_dict & pg_ts_parser. Once upgraded, I'll test everything, as long as there are not any issues, we are good to go. Worst case scenario, I can dump those two tables (5 rows total between them) and run an import post upgrade.