1. Brew Postgres
2. Brew Postgresql-upgrade-database
3. Brew Postgresql Logs
4. Start Postgres Mac

Homebrew users can just run “brew install postgis” and tends to be a favorite for more advanced users since there are brew scripts for most of the popular PostgreSQL extensions, not always present in other Mac distributions. The EnterpriseDb OSX PostgreSQL combination from EnterpriseDB includes generally latest stable minor version of PostGIS. I install Postgresql on my Ubuntu with: brew install postgres now I have: psql -version psql (PostgreSQL) 9.5.0 How can I start the service automatically? On my Mac with homebrew I can do it.

Geoffroy Baumier

3 min read

I install Postgresql on my Ubuntu with: brew install postgres now I have: psql -version psql (PostgreSQL) 9.5.0 How can I start the service automatically? On my Mac with homebrew I can do it. To migrate existing data from a previous major version of PostgreSQL run: brew postgresql-upgrade-database This formula has created a default database cluster with.

After upgrading a few programs including postgres when starting a new project, I ran into an issue. I already had some postgres databases from older projects and I couldn't start postgres (and create my new database).

To install postgres with brew I used:

To see if postgres is running I can type:

Then the funny thing was when I tried to start the service:

Great! So it's running! But no..

To see what is the issue, let's look at the logs:

And there was the issue.
All I had to do to fix this was to run this command:

Then let's start it postgres:

Now I can see that I have two postgres services with the two different versions. I can create my new updated database. Also the good thing is that I didn't lose any data from previous project and I can run the service with version 12 if I ever need it.

04 Apr 2017

problems with new homebrew versioning scheme

Brew Postgres

running brew upgrade has created quite a mess for me because of a newversioning scheme: postgresql95 formula is replaced with postgresql@9.5(this is because homebrew/core now supports multiple versions). but thismigration was not smooth and resulted in many errors, to name a few:

• invalid value for parameter 'TimeZone': 'UTC'

  should be fixed by restarting service or the whole system according to SO.

• command not found: psql

  psql must have a symlink in /usr/local/bin/ (it has been added to PATHin ~/.zshenv in my case) - it’s gone now for some mysterious reason.

• psql: FATAL: database 'db_name' does not exist

  this is because running brew upgrade has created new data directory forpostgresql@9.5 (/usr/local/var/postgresql@9.5) while all my databases arestored in /usr/local/var/postgres.

solution

so this is what I did to fix problems mentioned above:

• brew untap 'homebrew/versions' (it’s deprecated now)
• brew untap 'caskroom/versions' (it’s deprecated now)
• brew uninstall postgresql postgresql95 postgresql@9.5 (remove everything)
• brew install postgresql@9.5 (install latest 9.5 version)
• brew switch postgresql@9.5 9.5.6 (switch to latest 9.5 version)
• brew prune postgresql@9.5 (remove old 9.5 versions - if any)

• brew link postgresql@9.5 --force (create symlink in /usr/local/bin)

  it’s not recommended though - it must be better to add bin directory ofspecific PostgreSQL installation to PATH explicitly in ~/.zshenv.

• cd /usr/local/var && mv postgres postgresql@9.5 (rename directory withdatabases)

  it might be necessary to remove existing postgresql@9.5 directory beforehandthat could be created when upgrading PostgreSQL (but still double check itdoesn’t contain any databases).

• rm /usr/local/Cellar/postgresql95 (remove symlink to postgresql@9.5)

  I guess it has been created for compatibility reasons.

• gem uninstall pg && bundle (reinstall pg gem)

NOTE: installing postgresql formula still installs the latest version (9.6 asof now) - all directories are named just postgresql accordingly. but itsbinaries are not symlinked into usr/bin/local directory by default (now theyall point to 9.5 installation) - if you need it runbrew link postgresql --force manually.

psql: could not connect to server: Connection refused

rails console:

solution

the problem usually appears after hard reboot. the latter doesn’t allowPostgreSQL to exit gracefully and delete its PID files - postmaster.pid inparticular. so upon reboot PostgreSQL thinks it’s still running andcorresponding service fails to start.

so just delete that PID file and start the service:

or else try to restart the service (stopping the service might remove obsoletePID file - I didn’t try this method though):

command not found: psql

solution

in my case only versioned formula of PostgreSQL (postgresql@9.5) was installedbut it didn’t create a symlink to psql in /usr/local/bin/.

to solve this problem either:

• install the latest version of PostgreSQL (postgresql)

  unversioned formula creates a symlink in /usr/local/bin/ automatically.

• create symlinks for binaries from old version of PostgreSQL manually

• add the whole bin/ from old version of PostgreSQL to PATH

  ~/.zshenv:

psql: could not connect to server: No such file or director

solution

to diagnose this and similar problems run postgres in the foreground (seebrew info postgresql output):

it turns out /usr/local/var/postgres contains data for PostgreSQL 9.6 (whenpostgresql formula was installed, 9.6 was the latest version).

=> it’s necessary to migrate existing data from a previous major version (9.6)to the latest one (10) (see brew info postgresql output):

this command will install a previous major version of PostgreSQL (if it has beenuninstalled) which is required to upgrade database.

after upgrade is complete, you can remove old major version along with its data(/usr/local/var/postgres.old):

now make sure postgresql service is started:

and try to run psql again:

Error: Invalid data directory for cluster 10 main

solution

add -h localhost option:

for this to be possible it’s necessary to have this line in/etc/postgresql/10/main/pg_hba.conf:

restart postgresql service for changes to take effect.

https://stackoverflow.com/a/26735105/3632318:

Authentication methods details: trust - anyone who can connect to the serveris authorized to access the database peer - use client’s operating system username as database user name to access it. md5 - password-base authentication

FATAL: remaining connection slots are reserved for non-replication superuser connections

this error indicates you have run out of connections.

solution

see PostgreSQL - Tuning on how toincrease the maximum number of allowed connections.

see PostgreSQL - Monitoringon how to monitor open connections.

FATAL: sorry, too many clients already

most likely this error is also caused by a low number of allowed connections -see solution for the error above.

could not access the server configuration file “/etc/postgresql/12/main/postgresql.conf”

Brew Postgresql-upgrade-database

solution

the problem was with already existing cluster.

first I reinstalled PostgreSQL manually:

but cluster from previous installation must have remained in the system so a newcluster was not created when running the last command:

Brew Postgresql Logs

=> solution is to remove old clusters manually and run installation again:

see the tip on how to remove PostgreSQL completely in PostgreSQL - Tips.

Start Postgres Mac

Please enable JavaScript to view the comments powered by Disqus.