Postgres install incomplete; needs to update shared memory settings

[joeybaker]

Issue

Postgres, installed via brew, fails to start with the message:

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

steps to repeat

1. install brew install postgres
2. start postgres pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
3. try to use postgres psql

Postgres CLI should open, but instead fails with the error message above.

The problem

The postgres-for-mac installer readme states the following:

PostgreSQL One Click Installer README

Shared Memory

PostgreSQL uses shared memory extensively for caching and inter-process
communication. Unfortunately, the default configuration of Mac OS X does
not allow suitable amounts of shared memory to be created to run the
database server.

Before running the installation, please ensure that your system is
configured to allow the use of larger amounts of shared memory. Note that
this does not 'reserve' any memory so it is safe to configure much higher
values than you might initially need. You can do this by editting the
file /etc/sysctl.conf - e.g.

% sudo vi /etc/sysctl.conf

On a MacBook Pro with 2GB of RAM, the author's sysctl.conf contains:

kern.sysv.shmmax=1610612736
kern.sysv.shmall=393216
kern.sysv.shmmin=1
kern.sysv.shmmni=32
kern.sysv.shmseg=8
kern.maxprocperuid=512
kern.maxproc=2048

Note that (kern.sysv.shmall * 4096) should be greater than or equal to
kern.sysv.shmmax. kern.sysv.shmmax must also be a multiple of 4096.

Once you have edited (or created) the file, reboot before continuing with
the installation. If you wish to check the settings currently being used by
the kernel, you can use the sysctl utility:

% sysctl -a

The database server can now be installed.

For more information on PostgreSQL's use of shared memory, please see:

http://www.postgresql.org/docs/current/static/kernel-resources.html#SYSVIPC

Support

For help with this installer, please visit the forum at:

http://forums.enterprisedb.com/forums/show/9.page

For help with the packages within the installer, please visit the packages
website.

The brew formula doesn't do this step, and postgres will just silently fail to start. It would be super-awesome-coolio if brew was smart enough to run the same script that the postgres installer runs to setup the shared memory settings.

Less cool, but still super useful, would be adding this step to the documentation.

[adamv]

Even more cool: a pull request

[jacknagel]

If it requires root to change these settings, we can't do it in the formula.

[jacknagel]

Also, if this is required, why is this the first we are hearing of it? We've packaged Postgres for years.

[joeybaker]

Why it hasn't been reported before I don't know, but the documentation online, while fragmented, is pretty specific. I've personally used brew to install postgres on 4 different macs running lion and mountain lion and alway run into this issue. Further, the official installer takes this step.

[jacknagel]

We're not going to start editing /etc/sysctl.conf in a formula.

[joeybaker]

Well… okay, I guess it is a pretty major step to take. I was creeped out by it too, but it is required to make postgres work (as far as I can tell – I'd love to be proven wrong).

Instead of updating the formula, can I suggest just adding to the brew info docs that this step must/likely needs to be be taken?

[mxcl]

Add it to the caveats by all means. But I'm as surprised as Jack that nobody has brought this up before.

[mistydemeo]

This might be system-specific. I have postgres installed on my home machine and definitely didn't have to do this. My work machine, on the other hand, has a specific complaint about

FATAL: could not create shared memory segment: Cannot allocate memory

when I attempted to initdb.

From the caveats we already print:

Some machines may require provisioning of shared memory:
http://www.postgresql.org/docs/current/static/kernel-resources.html#SYSVIPC

[dserodio]

Maybe instead of rising the kernel settings, could the PostgreSQL requirements be lowered?

[trevor]

i had a similar error, but checking /usr/local/var/postgres/server.log i saw it was repetitively looking for a postgresql.conf file that it couldn't find.

[Sharpie]

Postgres, installed via brew, fails to start with the message:

psql: could not connect to server: No such file or directory
   Is the server running locally and accepting
   connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

Looks to me like you aren't using the psql client that was installed with Homebrew because /usr/local/bin isn't before /usr/bin in your path.

l bet this has nothing to do with shared memory and that if you run /usr/local/bin/psql everything will work like a charm.

[Sharpie]

Some background for the previous comment:

Lion and newer ship with a psql client in /usr/bin, but not a server. The error message reported is the exact message that results if someone installs Postgres via Homebrew, boots the server and then tries to connect using the psql client shipped as part of OS X instead of using the one that was installed along with the server via Homebrew.

[Sharpie]

And for a practical example, see the following thread on the Homebrew mailing list:

http://librelist.com/browser//homebrew/2012/7/30/postgresql-error-on-os-10-8-cannot-connect-to-server/#08476fec2c289d7b44fed52777c5b223

[trevor]

• uninstall / install postgres
• copy plist to LaunchAgents
• verify no other postgres in LaunchAgents folder
• restart

% which psql
/usr/local/bin/psql

% psql -V
psql (PostgreSQL) 9.1.5
contains support for command-line editing

% psql
psql: could not connect to server: No such file or directory
  Is the server running locally and accepting
  connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

% rm /usr/local/var/postgres/server.log ; sleep 14 ; cat /usr/local/var/postgres/server.log
postgres cannot access the server configuration file "/usr/local/var/postgres/postgresql.conf": No such file or directory

% ls /usr/local/var/postgres
server.log

%

[mistydemeo]

Note that the original issue with shared memory will apparently be resolved in 9.3: http://rhaas.blogspot.ca/2012/06/absurd-shared-memory-limits.html

[adamv]

Closing without taking action.

Will review caveat updates for 9.2.x, or wait until this is mooted in 9.3.

The caveats for Postgresql are already huge; would be nice to strip them down, point to a URL somewhere, or install our own post-install.txt and tell the user how to find it.

[slyrus]

FWIW, I'm seeing this on a new MBP with 10.8.2 trying to run 9.2.3. Sure would be nice to add this to caveats (or, better yet, use 9.3).

[slyrus]

Umm... 9.2.1 of course, not 9.2.3.

[arta]

This just happened to me.
Unsuspectingly, I updated an outdated postgresql using homebrew. It installed 9.2.1.

I couldn't make it run at all (not understanding what exactly I was doing I ran the 'start', 'stop', 'launch at startup' command lines but postgresql wouldn't start), so I uninstalled the 9.2.1 version.

Now, I cannot run my original brew install (9.1.2, 9.1.3).
Can you, please, advise how can I run my old postgres installation?
I'm stranded.

$ which psql
/usr/bin/psql

Which shouldn't be, I installed postgres via homebrew on clean Mac (OS X 10.7.5 now).
I have no clue how to get out of this.
Thanks.

[mrchess]

fwiw same deal on my new mba 11". installed via brew, postgres (PostgreSQL) 9.2.4 on a 10.8.4 system.

will update with soln. once i figure it out.

[mrchess]

fix for me was to, in addition to modifying the memory settings, adding this to my bash_profile

export PGHOST=localhost

[adarsh]

export PGHOST=localhost solved this issue for me. Added it to my ~/.zshrc. Life is good now.

[azouariUTD]

I upgraded to Yosemite. I did the steps above and it worked! Thanks!

[bobmacneal]

@azouariUTD Just upgraded to Yosemite too. Exactly which steps did you try to get it working?

[mbannert]

@azouariUTD, yup which steps? Mine does not work anymore after a Yosemite upgrade. I am also using lunchy as manager, which still seems able to start the process but psql can't find it anymore.

[azouariUTD]

@mbannert and @bobmacneal
Do the following:
brew update
brew prune
brew install postgres (or upgrade)
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

(In my case since this was a testing environment I went ahead and re-installed PostgreSQL from scratch) Don't forget to run brew prune if you uninstall and re-install.