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.

8 comments:

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

    ReplyDelete

  2. Is a Yorkie a good family dog?

    Yorkies, like many other Toy breeds, make good pets for people; they're especially good for senior citizens, people with medical issues, and those who may worry about the size and strength of a larger dog. ... They're loving, devoted, and very affectionate: This makes them great personal companions and good family pets.

    Do Yorkshire terriers bark a lot?
    https://www.newdaypuppies.com/teacup-yorkie-puppies-for-sale/
    Yorkshire Terriers are little dogs with huge personalities. teacup yorkies for sale With those huge personalities come a fierce territorial bark. Any time your phone rings, someone speaks or knocks on your door, or your doorbell chimes, your Yorkshire Terrier will likely bark. Outside noises aren't even required for barking for some Yorkies. yorkshire terriers for sale

    How much do Yorkshire terriers cost?
    yorkie for sale near me
    Typical Yorkie prices range from $1,500 to $3,000, but the cost can fall far below or above that range. Prices will vary based on the puppy's lineage, appearance, health, and the breeder. It's also possible to adopt an older Yorkie for significantly less through a rescue shelter. yorkie for sale

    ReplyDelete
  3. Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. A debt of gratitude is in order for sharing.data scientist course in warangal

    ReplyDelete
  4. I have bookmarked your site since this site contains significant data in it. You rock for keeping incredible stuff. I am very appreciative of this site.
    data analytics course in hyderabad

    ReplyDelete
  5. I am genuinely thankful to the holder of this web page who has shared this wonderful paragraph at at this place data analytics course in kanpur

    ReplyDelete
  6. Betting spreads and totals isn’t enough for most individuals anymore. The payouts will range primarily based on what number of} bets you embrace, and the prices of those bets. If you wager $11 to win $10 and the Colts win by 14, you’d win $10. The spread is -8, so to win your wager, you need Indy to win by 9+ factors. The over/under idea is utilized to many various kinds of bets. Bettors can then wager on whether or not the sport will go over or beneath 빅카지노 the entire.

    ReplyDelete