Wednesday, December 4, 2013

Upgrade from PostgreSQL 9.2.4 to 9.3.1 on OSX 10.9 and Centos 6.5

While testing a new async infrastructure, which requires more than 20 database connections, I ran out of connections and looked into upgrading PostgreSQL. The default setting for max_connections (found in /usr/local/var/postgres/postgresql.conf) is 100.

Ends up the upgrade from 9.2.4 to 9.3.1 is worthwhile for me since it greatly reduces System V shared memory requirements.

The upgrade was not time consuming, but required attention to details.

Here's what worked for me on OSX 10.9

$ brew upgrade postgresql

Keep Install Notes

==> Upgrading 1 outdated package, with result:
postgresql 9.3.1
==> Upgrading postgresql
==> Downloading
######################################################################## 100.0%
==> Patching
patching file contrib/uuid-ossp/uuid-ossp.c
==> ./configure --prefix=/usr/local/Cellar/postgresql/9.3.1 --datadir=/usr/local/Cellar/postgresql/9.3.1/share/postgresql --docdir=/usr/local/Cellar/postgresql/9.3.1/share/doc/postgresql --enable-thread-safety --with-bonjour --with-gssapi --with-krb5 --with-ldap --wi
==> make install-world
==> Caveats
initdb /usr/local/var/postgres -E utf8    # create a database cluster
postgres -D /usr/local/var/postgres       # serve that database
PGDATA=/usr/local/var/postgres postgres   # …alternatively

If builds of PostgreSQL 9 are failing and you have version 8.x installed,
you may need to remove the previous version first. See:

To migrate existing data from a previous major version (pre-9.3) of PostgreSQL, see:

When installing the postgres gem, including ARCHFLAGS is recommended:
  ARCHFLAGS="-arch x86_64" gem install pg

To install gems without sudo, see the Homebrew wiki.

To have launchd start postgresql at login:
    ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
Then to load postgresql now:
    launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
Or, if you don't want/need launchctl, you can just run:
    pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
==> Summary
🍺  /usr/local/Cellar/postgresql/9.3.1: 2919 files, 39M, built in 78 seconds

Initialize new 9.3 database

$ cd /usr/local/var
$ mkdir postgres9.3
$ initdb /usr/local/var/postgres9.3 -E utf8

Migrate Data to New 9.3 Format

$ pg_upgrade \
-d /usr/local/var/postgres \
-D /usr/local/var/postgres9.3 \
-b /usr/local/Cellar/postgresql/9.2.4/bin/ \
-B /usr/local/Cellar/postgresql/9.3.1/bin/ \

Create Link to Generic PostgreSQL directory

$ mv postgres postgres9.2.4
$ ln -s /usr/local/var/postgres9.3 /usr/local/var/postgres
$ psql postgres -c "select version()"

Increase System Memory for PostgreSQL

$ sudo sysctl -w kern.sysv.shmall=65536
$ sudo sysctl -w kern.sysv.shmmax=16777216

Create /etc/sysctl.conf file with following values


Reboot computer

Create postgres user

createuser -s -r postgres

Start postgreSQL

Note: I don't use launchd

$ pg_ctl -D /usr/local/var/postgres -l $PGDATA/server.log start

Verify PostgreSQL 9.3 Works

$ psql postgres -c "select version()"

Vacuum db

$ vacuumdb --all

Reinstall PG gem

Note: Uninstall the gem on the system command line and let Bundle Install reinstall your project's required version, which for me was version 0.17.

$ gem uninstall pg


Possible Error

If you get the following...

Building native extensions. This could take a while... ERROR: Error installing pg:

ERROR: Failed to build gem native extension.

...then run the following after uninstalling the pg gem:

$ brew install apple-gcc42
$ gcc --version

A typical OSX DevTools install won't be enough, but you can run it anyway:

xcode-select --install

Install Command Line Tools (OS X Mavericks)

If you use PostgreSQL.App, you'll need to install Command Line Tools (OS X Mavericks) for Xcode - Late October 2013

Do the following:
  • $ gem uninstall pg
  • $ xcode
  • From Xcode menu: Xcode > Open Developer Tool > More Developer Tools... > Choose "Command Line Tools (OS X Mavericks) for Xcode - Late October 2013"
  • Run the installation program
  • $ gem install pg

Update following in Gemfile

gem 'pg', '~> 0.17'

Bundle Install

$ bundle package --all

Verify PostgreSQL 9.3 works with Rails

Change directory to your favorite Rails project open the Rails Console and do some ActiveRecord queries.


$ brew cleanup postgresql
$ rm -rf /usr/local/var/postgres9.2.4


  • Since directories have been moved and linked, do not run
  • When tweaking system memory setting, keep in mind that SHMMAX must be an exact multiple of 4096
  • Special thanks to Matt Brictson, referenced below
  • This update will likely break your PostgreSQL admin tool like pgadmin
  • The pg_upgrade application only migrates the public schema.
  • Upgrade to lastest (v1.18.1) pgAdmin app, but backup your data first.
  • Do not remove the old version (9.2.4) until you are certain that all data has been migrated successfully.

Centos Install

This is still a work in progress...

$ su -
# cd softwares
# curl -O
# rpm -ivh pgdg-centos93-9.3-1.noarch.rpm
# yum install postgresql93 postgresql93-server postgresql93-contrib
# service postgresql-9.3 initdb
# chkconfig postgresql-9.3 on
# yum erase postgresql92*
# reboot



  1. What if i have no old version of Postgres in my /Cellar folder? I have the new 9.3.3 version folder in the Cellar, but for some reason no 9.2.4

    I had 9.2.4 previously, but cannot find my binaries folder for it to run the pg_upgrade command on my data.


  3. In order for the pg_upgrade command to work, it must have access to both (versions of PG) bin directories.

    So, if the older version of PG got deleted somehow, just install that old version, too.

    $ brew info postgresql # << to see what versions are installed locally

    $ brew which postgresql #<< to see which version is active

    $ brew versions postgresql #<< to make sure old version is available

    For the example in this post... Among other lines, you'll see:
    . . .
    9.2.4 git checkout e3ac215 Library/Formula/postgresql.rb
    . . .

    Install 9.2.4 and return state of PG to current version:

    $ cd $( brew --prefix ) #<< go to brew home directory
    $ git checkout e3ac215 Library/Formula/postgresql.rb
    $ brew install postgresql #<< install old version
    $ git checkout -- Library/Formula/postgresql.rb #<< reinstall latest PG version
    $ brew switch postgresql 9.3.1 #<< assuming PG 9.3.1 is the latest version

    Now, you should have 9.2.4 installed (as well as the latest 9.3.1 version) and you should be able to run the pg_upgrade command.

    Good luck!

  4. This comment has been removed by the author.

    1. This comment has been removed by the author.

  5. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in TECHNOLOGY , kindly contact us
    MaxMunus Offer World Class Virtual Instructor led training on TECHNOLOGY. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Sangita Mohanty
    Skype id: training_maxmunus
    Ph:(0) 9738075708 / 080 - 41103383