Tips on Upgrading Postgres Plus Keep Team on Same Version

One of my projects just ran into an issue where tests would fail one devs machine and not on another’s! Turns out the issue was that the developers were running different versions of postgres. Postgres made a change between 9.3 and 9.4 where 9.4 queries of not true returned all records with false or null, and, in 9.3., the same queries only returned records with false (null did not count as not true).

Startup code to check the DB version

Let’s alert the team that the version of postgres is not the desired version…

One of my projects is using this bit of code to make sure we’re all on the same version of Postgres. Note, the code does not error out if the version does not match. This is because the wrong version of the DB is often functional, so let’s not stop team members in their tracks!

Upgrading Postgres, with help from zsh

So you might need to update postgres. Here’s a few tips.

I’m using “postgresapp” even though I use brew for most other dependencies on my Mac.

  1. Download postgresapp:
  2. Instructions for upgrading existing DBs:

I’ve got a bunch of dbs, so I want to script this

ZSH Script to dump your dbs

dbs=(db_name db_name2 db_etc)
for db in $dbs; do
  echo "RUNNING: pg_dump $db > $db_dump"
  pg_dump $db > $db_dump

We can do better than using echo to print the line we’re running:

exe() { echo "\$ $@" ; "$@" ; }
dbs=(db_name db_name2 db_etc)
for db in $dbs; do
  exe psql --command="create database $db"
  exe psql -d $db -f $db_dump

Couple notes:

  1. Once you’ve defined a variable, like dbs, you don’t need to run that again in the same shell.
  2. zsh (and all shell scripting) is VERY whitespace sensitive.
  3. Play around with shell scripting. It’s super useful. Experiment with “echo” to print out what you might execute.
  4. Very useful to echo important command that you run. That exe function works well:

exe() { echo “$ $@” ; “$@” ; }

## Variations on the for loop

These are all the same. 
dbs=(d1 d2 d3)
for db in $dbs; do echo $db; done

for db (d1 d2 d3); do echo $db; done

for db in d1 d2 d3; do echo $db; done

For avoiding this kind of issues I suggest to give Vagrant a try. It’s always a source of pain having to worry about dependencies on every project you work with.

Other companies like Airbnb have adopted its usage

@mapreal19 I’ve heard that the performance of Docker running on Mac OSX is not great. Have you seen any performance issues with Vagrant?

I haven’t had any. Maybe I could set a simple repo with a rails app & vagrantfile so you can play with it.

I’m also a big fan of! It’s just so simple!

That being said, I think there is a larger issue in that Bundler can only handle gem dependencies. Any dependency that isn’t a ruby gem, be it databases, image minifiers, those weird libraries that nokogiri always complains about, or dependencies for headless web drivers can all cause problems.

It’s not just a team thing, either, because it can cause problems when returning to an old project after having updated one of the dependencies.

Maybe something like Vagrant is the way to go, but I’m hesitant after working so hard to customize my existing environment just the way I like it. Also, performance is a definite concern, but if that’s not actually a problem then I would be open to it.

1 Like