Homebrew postgres broken

Question

I installed Postgresql 9.4.0 installed on my Mac (10.10.1/Yosemite) using homebrew. It does not work.

I have created the softlink to /usr/local/opt/postgresql/homebrew.mxcl.postgresql.plist in ~/Library/LaunchAgents.

If I try to manually load postgres I get the message that the "Operation is in progress"

> launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
/usr/local/Cellar/postgresql/9.4.0/homebrew.mxcl.postgresql.plist: Operation already in progress

However postgres does not appear to be running.

> ps auxw | grep post
billmcn           670   0.0  0.0  2424272    452 s000  R+   10:12PM   0:00.01 grep post

and I cannot connect with the command line client.

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

To my knowledge I have tried all the fixes suggested on other Stackoverflow threads discussing this problem. Specifically:

I have uninstalled and reinstalled postgres and the accompanying Ruby gem. There is no postgres 8.0 version on my machine.
I have verified that the psql client program is the 9.4.0 version installed by Homebrew and not a Mac system binary.
I have verified that the /usr/local/var/postgres/postmaster.pid does not exist.
I have rebooted the machine.

I did have Homebrew postgres working on this machine earlier. I think what broke it is upgrading from version 8 to version 9 but I'm not sure.

I don't have any databases I need to preserve. I'm willing to start clean with postgres; I just need to get it to work now. Any ideas?

The issue appears to have been permissions on the /usr/local/var/postgres directory. Here is what my var directory looked like when things weren't working.

ll /usr/local/var/
drwxr-xr-x  3 billmcn  admin  102 Dec 20 12:44 cache
drwxr--r--  2 root     admin   68 Dec 29 21:37 postgres

(whoami = "billmcn")

I deleted /usr/local/var/postgres, uninstalled and reinstalled postgres, and now it looks like this.

ll /usr/local/var/
drwxr-xr-x   3 billmcn  admin  102 Dec 20 12:44 cache
drwx------  23 billmcn  admin  782 Dec 30 10:51 postgres

Not sure how it got into this state because I don't remember futzing with the permissions on this directory, but no matter. It works now.

score 132 · Accepted Answer · answered Dec 30 '14 at 16:14

132

I had the same problem installing postgres using homebrew on a freshly installed Yosemite.

First off my brew config looks like this:

HOMEBREW_VERSION: 0.9.5
ORIGIN: https://github.com/Homebrew/homebrew
HEAD: 9f6926265f8e4be7cc80dfe9042f2cd3c1e8dc9e
Last commit: 64 minutes ago
HOMEBREW_PREFIX: /usr/local
HOMEBREW_CELLAR: /usr/local/Cellar
CPU: quad-core 64-bit sandybridge
OS X: 10.10.1-x86_64
Xcode: 6.1.1
Clang: 6.0 build 600
X11: N/A
System Ruby: 2.0.0-481
Perl: /usr/bin/perl
Python: /usr/bin/python
Ruby: ~/.rvm/rubies/ruby-2.1.1/bin/ruby

First thing i noticed was that I had no write permission to /usr/local/var/postgres. This was easily changed issuing sudo chown -R `whoami` /usr/local/var/postgres then I reinstalled postgresql and did

cat /usr/local/var/postgres/server.log

which revealed:

postgres cannot access the server configuration file "/usr/local/var/postgres/postgresql.conf": No such file or directory

So I removed the directory /usr/local/var/postgres and issued the command to initialize the database.

initdb -D /usr/local/var/postgres/

This seemed to have done the trick and postgres is running fine.

answered Dec 30 '14 at 16:14

pixeltom

1,799
1
15
19

I followed these steps but upon executing the initdb command I get: "could not access directory "/usr/local/var/postgres": Permission denied." Again, this is after the steps you list, including deleting that directory directly before running initd. – Dave Munger Feb 14 '15 at 04:42
The system normally don't lie on these things. If you do a "ls -l /usr/local/var | grep postgres" is your user set as owner of the directory ? – pixeltom Feb 16 '15 at 07:52
I just gave up and switched to macports before I saw your comment. And, it's working just fine now. – Dave Munger Feb 17 '15 at 08:34
2

OMG! Thank you very much. I'm struggling with this since 2 hours. I thought that Homebrew should have simplified that installation. – fro_oo Apr 01 '15 at 17:36
1

I wish I could +5 this. – Geoffrey Burdett Jul 17 '15 at 17:55
initdb: could not change permissions of directory "/usr/local/var/postgres": Operation not permitted – Herb Meehan Aug 24 '15 at 04:22
sudo chown -R `whoami` /usr/local/var/postgres, then initdb. – Suge Dec 16 '15 at 03:49
Strangely enough, these steps work. I had done small variation of these steps (i.e., sudo chown and initdb; without reinstalling postgresql in between) and that hadn't worked. Very strange. – Asad Iqbal Sep 08 '16 at 06:43
Second time I'm running into this issue after upgrading the OS - it would be really great, if brew postgres could handle this automatically – Andreas Feb 19 '18 at 06:15
```sudo chown -R `whoami` /usr/local/var/postgres``` did the trick for me, thanks! – Kaka Ruto Apr 06 '18 at 12:18
I had the same problem on High Sierra. The solution worked – stevec Jul 31 '18 at 14:59
awesome, thanks... I have difficulty when running in the new postgres version 13 from 12 this helps me – Nald Dev Feb 26 '21 at 02:09
This worked for me. For some reason I was completely missing the `/usr/local/var` directory (after having postgres working fine for months). Recreated the var folder with `sudo mkdir /usr/local/var` ran the sudo chown command on it, then reran the `initdb` fn mentioned above. Then had to recreate the initial user ([details here](https://stackoverflow.com/questions/15301826/psql-fatal-role-postgres-does-not-exist)) and now I'm good – orpheus Apr 11 '22 at 15:55

James · Answer 2 · 2015-08-06T19:06:47.520

64

I had this same problem. The primary issue here is that the initdb step of installation will create the directory with root ownership instead of as the user on a Mac. To solve this issue:

Create the data directory before running initdb and set permissions of 0700

rm -rf /usr/local/var/postgres  # in case this is not your first try
mkdir /usr/local/var/postgres
chmod 0700 /usr/local/var/postgres

Then run initdb and it will respect the permissions of the data directory.

initdb -D /usr/local/var/postgres

For grins and giggles, create a test db named after your user:

createdb `whoami`

Login to test:

psql

edited Aug 06 '15 at 19:06

answered Mar 18 '15 at 19:08

James

755
5
4

I go to `rm -rf /usr/local/var/postgres` and or `sudo rm -rf /usr/local/var/postgres` and both seemingly delete the postgres directory, then i go to manually add my own new directory and I get `mkdir: /usr/local/var/postgres: File exists` !? `psql` still says `no such file or directory`. Context on my situation:http://stackoverflow.com/questions/31088325/how-can-i-migrate-postgres-9-3-5-to-9-4-4-after-deleting-the-9-3-5-oldbindir – jbk Jun 27 '15 at 14:05
I had to follow this same process on my M1 Mac but with the brew path instead which for me was: `/opt/homebrew/var/postgres` – rii Jun 21 '22 at 16:03

score 14 · Answer 3 · answered Apr 21 '16 at 13:29

After trying to install postgresql with Homebrew, I got this:

Warning: postgresql-9.5.2 already installed, it's just not linked

So I tried:

brew link postgresql

And got this error:

Linking /usr/local/Cellar/postgresql/9.5.2... 
Error: Could not symlink share/man/man3/SPI_connect.3
/usr/local/share/man/man3 is not writable.

It seemed to be a write permission matter, so I did:

sudo chown -R `whoami` /usr/local/share/man/

It did the trick because, then I was able to do (without error):

brew link postgresql

score 13 · Answer 4 · answered Mar 02 '19 at 12:16

13

In case anyone upgraded from a previous version, dont forget to:

brew postgresql-upgrade-database

That will solve the problem by upgrading your existing databases to the version you upgraded postgres to.

answered Mar 02 '19 at 12:16

Lisandro

10,543
1
24
29

this fixed it for me. – Micah Oct 14 '21 at 16:51
Wow! Been googling this for hours! This fixed mine! – Paul Tuckett May 14 '22 at 20:19

Freddo · Answer 5 · 2016-01-18T11:41:19.703

Please note that their is a thread on Homebrew's github dealing with this issue: https://github.com/Homebrew/homebrew/issues/35240

I have had a similar issue. James answer helped me solve it. But I then ran into the issue jbk is mentioning (after having deleted /usr/local/var/postgres, it kept on being recreated).

The issue is that if you have created the symlink:

ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents

and launched the process:

launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

you should first unload it:

launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

before running James's commands.

rm -rf /usr/local/var/postgres  # in case this is not your first try
mkdir /usr/local/var/postgres
chmod 0700 /usr/local/var/postgres

In addition, if, like me, you have an admin user managing homebrew and a regular user who will be using pgsl for development purpose, James command should be run as super user:

sudo -s

and ownership over the postgres directory should be given to your dev user:

chown my-dev-user /usr/local/var/postgres

The following command, run as the dev user, should then properly populate the directory:

createdb `whoami`

Running:

psql -l

should show you the tables and user permissions in postgre after such manipulations.

Hope this helps.

This was amazing! Thank you Cedric. I had the same issue with permissions. — thesayhey, Oct 16 '15 at 17:07

score 5 · Answer 6 · answered Oct 10 '21 at 18:07

5

I had to delete the .pid file after seeing this in the logs

/usr/local/var/log/postgres.log

2021-10-10 19:05:27.468 BST [41868] FATAL:  lock file "postmaster.pid" already exists
2021-10-10 19:05:27.468 BST [41868] HINT:  Is another postmaster (PID 820) running in data directory "/usr/local/var/postgres"?

rm /usr/local/var/postgres/postmaster.pid

I installed it using brew

answered Oct 10 '21 at 18:07

Gianfranco P.

10,049
6
51
68

I have no idea why this problem happens in the first place, but this solved my problem as well. – Burak Kaymakci Jan 01 '22 at 20:55

CodingYourLife · Answer 7 · 2022-05-14T03:20:08.117

4

based on @James answer this is what I did on my M1 Monterey machine. For me the directory differed.

DANGER: In the comments it has been pointed out that my script deletes the database.

In terminal:

#to fix postgresql of existing installation
cd /opt/homebrew/var
rm -rf postgres
mkdir postgres
chmod 0700 postgres
initdb -D postgres

#install postgres
echo "installing postgres..."
brew install postgresql
brew services restart postgresql
createuser postgres -s

I then could brew install --cask pgadmin4 and run pgadmin from Applications and connect to 127.0.0.1.

edited May 14 '22 at 03:20

answered Nov 18 '21 at 00:26

CodingYourLife

7,172
5
55
69

This worked! Thank you so much. – Damagedcase Jan 27 '22 at 05:40
1

This works, unfortunately it also deletes the entire databases – Jeremy Lynch May 14 '22 at 02:49
added a danger db drop in text – CodingYourLife May 14 '22 at 03:21

score 2 · Answer 8 · answered Nov 26 '19 at 03:37

I recently had a problem which began when I upgraded some brew updates / upgrades, mainly python versions etc. What worked for me.

brew uninstall postgres
brew install postgresql@9.5
echo 'export PATH="/usr/local/opt/postgresql@9.5/bin:$PATH"' > ~/.zshrc
# you may need > ~/.bashrc if you use bash

I needed pg_dump, pg_restore etc so to get that working I did

brew install libpq

Start the service

brew services start postgresql@9.5

From here I would have expected everything to work but still all rails db commands were giving error that server was not running. This final bit was the missing piece of the puzzle which finally solved it for me.

gem uninstall pg
gem install pg -v 0.20.0 # which was set in Gemfile
# could also just probably do bundle install instead.

score 2 · Answer 9 · answered Oct 06 '22 at 01:05

2

I'd this issue after shutting down the computer due power outage.

# This initialize your database with the current data and settings
initdb -D postgres

# This will start database service
pg_ctl -D postgres -l logfile start

answered Oct 06 '22 at 01:05

monteirobrena

2,562
1
33
45

score 1 · Answer 10 · answered Jun 12 '19 at 12:51

For posterity, I had this issue and wanted to note what worked for me.

I am running postgres 11.2 on High Sierra. I had recently upgraded from postgres 10 with brew postgresql-upgrade-database.

I kept getting the error psql: could not connect to server: No such file or directory, and my server.log indicated is another postmaster (PID 5894) running in data directory "/usr/local/var/postgres"?

I tried several solutions including restarting my computer, deleting postmaster.pid, using brew services restart postgres, but to no avail. I eventually stumbled on the solution:

brew unlink postgresql && brew link postgresql

No idea why this worked, but putting it here mostly so I can reference it myself in the future! Throw stuff at the wall till it sticks!

score 0 · Answer 11 · answered Oct 02 '17 at 18:49

Check @leo_chaz_maltrait for fixing errors the error Could not symlink share/man/man3/SPI_connect.3 Another error that might show up is:

Error: Could not symlink lib/pkgconfig/libecpg.pc

sudo chown -R `whoami` /usr/local/lib/pkgconfig

brew link postgresql

vikas95prasad · Answer 12 · 2022-07-25T06:48:13.617

Please read and follow the instructions.

Check postgres logs to see what the issue is.
- tail -f /usr/local/var/log/postgres.log
- tail -f /opt/homebrew/var/log/postgres.log
- tail -f /usr/local/var/postgres/server.log
In my case it this was the error.
- 2022-07-19 21:16:12.095 IST [2138] FATAL: data directory "/usr/local/var/postgres" has invalid permissions
- [3472] FATAL: lock file "postmaster.pid" already exists
Added the required permission and issue got fixed.
- sudo chown -R vikas /usr/local/var/postgres
- rm /usr/local/var/postgres/postmaster.pid

That's it.

Homebrew postgres broken

12 Answers12

Linked