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.

9 comments:

  1. The term which offers a nearby relationship with information science is Machine learning. ExcelR Data Science Courses

    ReplyDelete
  2. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries.
    Data Science Course

    ReplyDelete
  3. This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck.

    Correlation vs Covariance

    ReplyDelete
  4. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression

    ReplyDelete
  5. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.

    Simple Linear Regression

    Correlation vs Covariance

    ReplyDelete
  6. A good blog always comes-up with new and exciting information and while reading I have feel that this blog is really have all those quality that qualify a blog to be a one.
    360digitmg

    ReplyDelete
  7. Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple Linear Regression
    data science interview questions
    KNN Algorithm

    ReplyDelete
  8. Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.

    Simple Linear Regression

    Correlation vs covariance

    KNN Algorithm

    Logistic Regression explained

    ReplyDelete
  9. Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple Linear Regression
    data science interview questions
    KNN Algorithm
    Logistic Regression explained

    ReplyDelete