Solving Postgres problems in Mountain Lion

Maricris Nonato -

Problem #1:

psql (9.1.5, server 9.2.2)
WARNING: psql version 9.2, server version 9.2.
     Some psql features might not work.
Type "help" for help.

Solution:
brew upgrade postgresql

This upgrades your postgresql server (doesn't yet address your problem). If you still want to go on, I suggest now is the great time to do a backup.

While you do that, let's watch some kitties fly!

Ready? Let's move on.

cd /usr/local/var
mkdir postgres9.2

# Initialize a the new postgres 9.2 database
initdb /usr/local/var/postgres9.2

# Tell postgres we are upgrading the existing postgres database to the new postgres9.2 location
pg_upgrade -d /usr/local/var/postgres/ -D /usr/local/var/postgres9.2 -b /usr/local/Cellar/postgresql/9.2.4/bin/ -B /usr/local/Cellar/postgresql/9.2.4/bin/ -v

# Swap the folders
mv postgres9.2 postgres

If you have your Postgres run at startup, do this as an extra step:

# Restart postgres
launchctl unload -w homebrew.mxcl.postgresql.plist
launchctl load -w homebrew.mxcl.postgresql.plist

Problem #2:
If you encounter this while you tried to do an initdb (as in Solution #1),

FATAL:  could not create shared memory segment: Cannot allocate memory
DETAIL:  Failed system call was shmget(key=1, size=1646592, 03600).</code></pre>

Solution:

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

This temporarily makes adjustment in your kernel as you need it. If you want to make it permanent, then

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

Problem #3:
If, after you do all these, and you find that you circumvent back to Problem #1, then check your paths.

which psql #and take note of the path

Solution:
Since you're running on homebrew, you need to add the cellar path to your PATH environment variable.

vim ~/.bash_profile

Add this

/usr/local/Cellar/postgresql/9.2.4/bin

Just before the last :$PATH.  My PATH looks something like this:

export PATH="$HOME/.rbenv/bin:/usr/local/Cellar/postgresql/9.2.4/bin:$PATH"

To immediately reload the variable, just copy that line and run on your terminal.

Whew.  Now I can go back to work.  You should be able to see your psql matching your server version.

References: