I’ve been using a MacBook for about 2 years now and I honestly can’t count the number of times I’ve encountered this weird Postgres bug:
psql: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
I’ve solved this quite a number of times but I usually never remember how I resolve it the next time I encounter it. I end up restarting my PC a number of times and having to stack overflow it over and over again.
Today, that changes, I’m going to write about it so I can always come back and check it whenever I run into this problem again.
This issue can be caused by a number of things but usually, it’s when you run
brew update and you upgrade your Postgres version that this problem comes up. When this upgrade occurs, your Postgres config file is usually not compatible with the new one. To confirm what the problem is I advise you use this command:
postgres -D /usr/local/var/postgres
This command outputs the logs and you should see the cause of the issue. Here’s what the logs look like on my PC at the time of writing:
2019-02-14 19:15:41.691 WAT  FATAL: database files are incompatible with server 2019-02-14 19:15:41.691 WAT  DETAIL: The data directory was initialized by PostgreSQL version 10, which is not compatible with this version 11.1.
Usually, I don’t have production data or any important database on my PC, I instead have a back up for large dataset I use for testing. So I resolve to reset my PostgreSQL database because I don’t really mind losing the data.
Albeit if you would like to do this quickly with a three-liner and you’re fine with using the default Postgres user, here’s a quick solution— proposed to me by Dawuda Ebenezer.
postgres -D /usr/local/var/postgres rm -rf /usr/local/var/postgres && initdb /usr/local/var/postgres -E utf8
# Then start the server: $ pg_ctl -D /usr/local/var/postgres -l logfile start (the minor difference I think)
Let’s start by stopping the Postgres service with the command
brew services stop postgresql
Once this is done, you’d need to delete the data directory for PostgreSQL — L**et me remind you that this is an irreversible action **— if you aren’t sure, use the snippet below to back up the directory
mv `/usr/local/var/postgres/ /usr/local/var/postgres.backup`
You can do this with the snippet below to delete the Postgres data directory:
rm -rf /usr/local/var/postgres/
Once this is done, you can create a brand new database with the command
After which you can start the PostgreSQL service with HomeBrew
brew services start postgresql
The action taken above clears all databases saved on the PC. So we need to get started by creating a user, this can be done by entering the PostgreSQL repl with the command:
Now, you should have a new repl instance similar to the screenshot below:
We can then proceed to create a new user with the command
CREATE ROLE username WITH LOGIN PASSWORD 'quoted password';
Remember to add the semi-colon at the end of the command, Postgres can act weird at times when the semi-colon isn’t there.
With the user created, you should be able to log into Postgres now with your credentials.
At this time, the only action we can perform is to log in, because no role has been assigned to the created user. Let’s proceed to assign the user a role, we do that with the command:
ALTER ROLE username CREATEDB
What we’ve done is simply give the created user the ability to create a database. There are other types of roles and you can assign a user multiple roles. The other role types include:
- BYPASS RLS
Once you’re done assigning roles, you can go on with your day-to-day database activity.
I use Postico for managing my Postgres databases — I personally love the simple and intuitive layout.
Now that the database user was created successfully, I can simply log into PostgreSQL using Postico and manage my database(s). A screenshot of what that looks like is shown below
My solution to this problem is resetting my Postgres database because this mainly occurs in my dev environment which is my PC, albeit, if you would love to retain your data Lawrence Wachira has shared some info on how to go about it. Check the gist and screenshot below
|After automatically updating Postgres to 10.0 via Homebrew, the pg_ctl start command didn't work.|
|The error was "The data directory was initialized by PostgreSQL version 9.6, which is not compatible with this version 10.0."|
|Database files have to be updated before starting the server, here are the steps that had to be followed:|
|# need to have both 9.6.x and latest 10.0 installed, and keep 10.0 as default|
|brew unlink postgresql|
|brew install email@example.com|
|brew unlink firstname.lastname@example.org|
|brew link postgresql|
|# move 9.6.x db files to another directory|
|mv /usr/local/var/postgres /usr/local/var/postgres96|
|# init new database using 10.0|
|initdb /usr/local/var/postgres -E utf8|
|# make timezone and timezonesets directories available for 9.6.x installation|
|cp -r /usr/local/share/postgresql/timezone /usr/local/share/postgresql96|
|cp -r /usr/local/share/postgresql/timezonesets /usr/local/share/postgresql96|
|# finally the actual upgrade|
|# -b is the old binary dir, -B is the new binary dir|
|# -d is the old data dir, -D is the new data dir|
|pg_upgrade -b /usr/local/Cellaremail@example.com/9.6.5/bin -B /usr/local/Cellar/postgresql/10.0/bin -d /usr/local/var/postgres96 -D /usr/local/var/postgres|
|# start 10.0 to check that upgrade works|
|pg_ctl start -D /usr/local/var/postgres|
|# cleanup if upgrade was successful|
|brew uninstall firstname.lastname@example.org|
|rm -rf /usr/local/var/postgres96|
|rm -rf /usr/local/share/postgresql96|
I hope you enjoyed reading this article.
Alternatively, you can check this out: