PostgreSQL not running on Mac

Question

The error in its entirety reads:

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

This is my second time setting up Postgresql via Homebrew on my Mac, and I have no clue what is going on. Previously, it had been working. At some point, I must've entered a command that messed things up. I'm not sure. Now, whenever I enter a SQL command from the command line, I receive the above message. I've run a command to check whether the server is running, and it apparently is not. If I attempt to start the server using

$ postgres -D /usr/local/pgsql/data

I receive the following error:

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

I've uninstalled and reinstalled Postgresql via Homebrew, but the problem persists. I'm completely at a loss as to how to get this working. Any help would be appreciated.

• sudonitin: I tried all the solutions from stackoverflow and this thread. But this one worked like a charm for me stackoverflow.com/a/71954046/8871869. Mac by default doesn't create a db with username

Answer 1

The problem can also be attributed to a crashed process that left postmaster.pid file behind.

$ brew services stop postgresql
# adjust path accordingly to your install, e.g. `/opt/homebrew/var/postgres/postmaster.pid`
$ rm /usr/local/var/postgres/postmaster.pid 
$ brew services start postgresql

2023 Update: I highly recommend running PostgreSQL in a docker container and keeping your local host clean.

• vinhboy: On MacOSX, this answer saved me. Had the same error as OP after my computer hard rebooted. The brew services command were not helpful because they made it seem like everything was working. Removing the postmaster.pid is what finally got everything working again. Thanks!
• Matthijs: This happened to me too. Hard crash of Mac OS X caused a restart, after that Postgres didn't come up. brew services start/stop/restart doesn't work, you have to manually remove the pid file.
• demisx: The recommended way would be to run Postgres in a docker container, of course. Very easy to start and everything is cleaned up when a container is shut down. I'd say running any third party app in a docker container should be de facto nowadays.
• Touch: Recently, on Mac, I had to remove rm /opt/homebrew/var/postgres/postmaster.pid. I use brew. Leaving this here in case I run into this issue again.
• qualbeen: Thanks a lot! This was my issue as well. Finally I'm able to run postgres locally on my mac again (M1 Pro, Monterey-OS). rm /opt/homebrew/var/postgresql@14/postmaster.pid

Answer 2

I was getting the same

Is the server running locally and accepting connections on Unix domain 
socket "/tmp/.s.PGSQL.5432"?

loop of Homebrew install / start / stop / restart to no avail...

Finally, brew postgresql-upgrade-database worked.

Seems I was on 9.6 instead of 10.4, and something my latest App Store restart restarted all my database servers...

• seniorpreacher: brew postgresql-upgrade-database fixed the issue for me on Catalina
• Vishal: It worked for me
• x85ms16: This worked for me as well. My problem was that the database files were created by [email protected] and my current installation was [email protected]
• Brandon Culley: This worked for me trying to do a Udemy course with Phoenix and Ecto, it updated my database from version 10 to 14. This was on a Mac Monterey
• MasterOfNone: I'm having an issue trying to upgrade a very old postgres database. It appears that an intermediate version is so old that Homebrew no longer supports it. I've already upgraded the postgres installation and really want to save this database. Is there a way to make this work? $brew postgresql-upgrade-database Warning: Use postgresql@14 instead of deprecated postgresql ==> brew install [email protected] Error: [email protected] has been disabled because it is not supported upstream! Error: No postgresql 9.4.* version installed!

Answer 3

The answer is here.

Run this command to manually start the server:

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

Answer 4

I've just resolved the same problem. It's just because I forgot to run it properly before use.

For pure installing postgresql on Mac OS, the process will be (using brew command):

brew install postgresql

then if you want to automatically run postgresql at login:

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

or else you just want to run it anytime you want:

postgres -D /usr/local/var/postgres

If your case is more complicated, let's brew uninstall postgresql and redo these steps.

Hope it helps!

• henry: Update: for Homebrew-installed postgres, brew services start postgresql is now the preferred way to start postgres at login

Answer 5

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

I kept on getting the above error and none of the above solutions worked for me. Finally the following solution solved my problem on Mac OS X

Install postgres using brew

brew install postgres

Install brew services

brew tap homebrew/services

To start postgres as a background service

brew services start postgresql

To stop postgres manually

brew services stop postgresql

We can also use brew services to restart Postgres

brew services restart postgresql

Answer 6

This happens when postgres server is not running. Steps to properly install Postgres via Homebrew on MAC :

1. brew install postgres

2. initdb /Users/<username>/db -E utf8 [This initializes postgres to use the given directory as the database directory. Normally it is not adviced to use the user directory for database storage. Edit sudoers file to add initdb and similar commands and then run initdb on /usr/local/var/postgres]

3. pg_ctl -D /Users/<username>/db -l logfile start [After getting success with step 2 it will prompt to run step 3. This command manually starts the server.]

• Aswin Kumar K P: This helped me after I tried dozens of non-working steps

Answer 7

I was looking for a long time, and this was the most clean and neat solution:

I recently upgraded Postgres from 9.2 to 9.3 using brew upgrade postgres. The process was smooth and pg_upgrade is a very handy tool.

However, trouble struck once I tried to run any specs that needed to connect to Postgres. Even though Postgres was definitely running, suddenly I was getting:

could not connect to server: No such file or directory (PG::ConnectionBad) Is the server running locally and accepting connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"? The problem was that the new version of Postgres listens on /tmp/.s.PGSQL.5432 instead. I could’ve messed around with the config and made Postgres use the domain socket it was previously, or told Rails explictly how to connect, but both of those approaches seemed like work I shouldn’t have to do. At no point had I told Rails to connect to postgres on that path, Rails had assumed it, and now its assumptions were wrong.

The fix is simple, if a little suprising. When you install the ‘pg’ gem, it detects which version of Postgres is installed and sets the domain socket path appropriately. The solution is as simple as reinstalling the gem. $ gem uninstall pg $ cd my-rails-app/ $ bundle install

http://daniel.fone.net.nz/blog/2014/12/01/fixing-connection-errors-after-upgrading-postgres/

• Paul White: Please edit your answer so it provides value if the link stops working. You can find guidance on how to do this correctly in How to reference material written by others. Thank you.
• Subtletree: This was exactly my problem. Cheers!

Answer 8

It worked for me. Change your postgresql directory according to version in your system.

Common path- rm /usr/local/var/postgres/postmaster.pid

but for [email protected] in my system path is rm /usr/local/var/[email protected]/postmaster.pid

restart [email protected] brew services restart [email protected]

Answer 9

I just uncomment in /etc/postgresql/9.5/main/postgresql.conf

unix_socket_permissions = 0777

and restart postgres. And for me it works.

Answer 10

recently I went thru a similar problem. there's just another problem and solution. I was running 2 version of postgres (9.3 and 9.6) although the server was set to run on 2 different port but some how the psql command on bash try to connect to default port 5432. Make sure to check if your server is running and check your port settings, then run psql -p <port> postgres. The solution is changing port.

Answer 11

For me this also happened after a reboot and none of the above solutions worked for me. After checking the server log like this:

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

I noticed:

 2019年11月06日 11:04:31.797 CET [85029] FATAL: data directory "/usr/local/var/postgres" has invalid permissions
2019年11月06日 11:04:31.797 CET [85029] DETAIL: Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).

So I changed the permissions like this:

sudo chmod 700 /usr/local/var/postgres

and everything worked again and life was good.

• Doug: Same, using postgresql@12 on osx 10.15. Last thing needed was a brew services restart postgresql

Answer 12

1. Check if postgres is already running using the following command:

   pgrep -u postgres -fa -- -D

usually you'll get one or more lines depending on whether you are running one or multiple instances of the server. If postgres isn't running you'll get no lines.

2. Now remove (rm) the postmaster.pid file from the data directory which is usually

   ~/Library/Application\ Support/Postgres/var-9.6

3. start the postgres server

If the data directory already contains the postmaster.pid file then it means postgres is running. When the server crashes or is killed the database process needs to be stopped before the postmaster.pid is removed otherwise the data directory could get corrupted.

Answer 13

update it by using command

 brew postgresql-upgrade-database

if you have following error Command 'brew' not found, but can be installed with: sudo apt install linuxbrew-wrapper

then install it by using command

 sudo apt install linuxbrew-wrapper

Answer 14

I got the exact same error message, where psql could not connect to the server, and brew install postgresql gave the error message Failed to install plist file.

The problem turned out to be that some software install had changed the ownership of /usr/local/var to root.

Solution: $ sudo chown /usr/local/var `whoami`

I'm surprised that brew doctor did not detect this.

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

Answer 15

High level summary of the issue:

This issue might occur for one of the reasons below:

a. Maybe the port is not the default port used (which is 5432)- How to confirm this?

ps -ef | grep UID && ps -ef | grep postgres

Got the pid of 5833 from the output below:

UID PID PPID C STIME TTY TIME CMD
501 5833 1 0 12:07PM ?? 0:00.13 /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres

If you DO NOT get the output like above, most likely the postgres server is not running. You can confirm postgres server status with pg_ctl or other commands to confirm. There are other articles on how to check the status

Find the log location with the pid (5833 is the pid of postgres from the above command, replace the pid with your pid from the command output you get):

$ lsof -p 5833 | grep log
postgres 5833 <username> 1u REG 1,4 1610136 14411021 /usr/local/var/log/postgres.log

Open the log file and confirm the port number from the log line like below:

[5833] LOG: listening on IPv4 address "127.0.0.1", port 5488

[5833] being the process id in this case

So, you can grep that as well like:

egrep "[5833]*listening*" /usr/local/var/log/postgres.log

Once you get the port, which was 5488 in my case, connect to postgres server with an option "-p" to connect (replace with the port found from the log):

psql -p 5488 <databasename> #add host and other options as needed

b. The process is not running at all or there is some other issue, which can again be seen in the log (which can be found with the steps above). If the postgres server is not started, please try restarting the postgres instance with the command as appropriate to the operating system you are using.

For Mac OS the command to restart was brew services restart postgresql

Hope this helps methodically troubleshoot this issue.