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 http://ftp.postgresql.org/pub/source/v9.3.1/postgresql-9.3.1.tar.bz2
######################################################################## 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:
  https://github.com/mxcl/homebrew/issues/issue/2510

To migrate existing data from a previous major version (pre-9.3) of PostgreSQL, see:
  http://www.postgresql.org/docs/9.3/static/upgrading.html

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/ \
-v

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

kern.sysv.shmall=65536
kern.sysv.shmmax=16777216

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

$ cd <RAILS_PROJECT_DIR>

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.

Cleanup

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

Notes

• Since directories have been moved and linked, do not run delete_old_cluster.sh
• 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 http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
# 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

References

http://blog.55minutes.com/2013/09/postgresql-93-brew-upgrade http://www.postgresql.org/docs/current/static/release-9-3.html http://www.postgresql.org/docs/8.4/static/kernel-resources.html http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server http://postgresapp.com http://www.pgadmin.org/download/macosx.php