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.


Open EE Meter, Part 2

February 18, 2016

Turns out the code and tutorial that I worked through a couple of weeks ago were just one of three repos on github.  The other Matt (Golden) updated their code page which now looks like this:


I had only downloaded and run the tests for the first of the three, eemeter.

The second, oeem-client, I was able to set up without any problems, following the steps in the README. I ran the app and only then did it stumble, complaining of no host supplied for the datastore, which makes sense since I was not yet running any datastore.

The third, oeem-energy-datastore, started out equally simple but then failed because of a missing environment variable (DJANGO_LOGFILE, easy enough to define) and a couple of minor bugs in, which I fixed. Turns out they are not using setup anymore: just migrate, createsuperuser, and runserver.

For other random environment errors, I needed outside help, or rather, inside help from the lead developer on the project, Phil Ngo. First he said checkout develop, not master (why didn’t I think of that?) and he sent me the contents of his postactivate scripts for oeem-client and oeem-energy-datastore. Using his as examples, I was able to set up my own to work for my devbox, which is similar but not identical to his.

This all worked (even the obsolete setup, with my fixes) and I ran the datastore, which just shows a django admin list of tables. Next, following directions from Phil, I created a connection between the client and the datastore by creating a Django OAuth Toolkit application (in the datastore admin) and a Django OAUth Toolkit access token in the client.

Now the oeem-client application, which failed before due to the lack of a datastore, works!

What exactly is running?
How does eemeter relate to the client and datastore?
Can I load some fake data?



Open EE Meter

February 7, 2016


I heard about this project at work a few months ago and promptly checked out a copy and worked through the tutorial.  I was very happy to learn about it because it sounds like a great idea, and it is written in python, the language I’ve been using the most in recent years.  And I love python.

The promise of Open EE Meter is pretty compelling: a standard way to calculate energy saved, or negawatts, for individual accounts against a baseline that is somehow calculated from an aggregate set.  Since the data and the code are all open, this can be verified by any players at all: individuals, businesses, government agencies, utilities, etc.


So Monday night, my husband and I went to this meetup.  It was a lot of fun, and we learned some things, but I did not get the two practical bits that I was hoping for.

First, I want a better understanding of how it actually works.  How is the baseline calculated, and how is the set of data selected?  The very entertaining speaker (Matt Gee) did cover this but not in a way that I could understand that quickly.  Second, I want to know how to play with this with my own Green Button data.

oeem3Since the new Share my data option on my PG&E account has Open Energy Efficiency as one of the choices, I enabled that sharing, just to see what would happen, if anything.  But what I really want to do is figure out how to pull my own data and build my own meter.  Probably I need to dig a bit deeper than the tutorial.


Listening to my Smart Meter, Part 4

January 30, 2016

I logged in to my PG&E account for the first time in ages, partly to look something up and partly because I had heard they have a “Share” feature now to share your Green Button data with companies providing services related to …. Green Button data.

There were two surprises.

  1. Not only is the list of companies that I can select to Share my data with quite long, one of them is Open Energy Efficiency!  This must be the same company that is the sponsor behind the Open EE Meter project, the subject of a meetup that I am going to this coming Monday.
  2. The EnergyAware PowerTab, which I got working via shell commands and assorted phone calls to PG&E and others in the Spring of 2013, has magically appeared on my new Stream My Data Dashboard.  This definitely was not in place when I got this early product activated almost two years ago.  powertab2015 copy