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 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

5 comments:

  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.

    ReplyDelete
  2. http://serverfault.com/questions/580273/osx-mavericks-installed-no-more-postgresql

    ReplyDelete
  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!

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete