Installing Postgres via Brew (OSX)

postgres-brew.md

Installing Postgres via Brew

Pre-Reqs

Brew Package Manager

In your command-line run the following commands:

1. brew doctor
2. brew update

Installing

1. In your command-line run the command: brew install postgresql@14

2. Run the command: ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents

3. Create two new aliases to start and stop your postgres server. They could look something like this:

        alias pg_start="launchctl load ~/Library/LaunchAgents"
        alias pg_stop="launchctl unload ~/Library/LaunchAgents"
   

4. Run the alias you just created: pg_start. Use this comment to start your database service.

   • alternatively, pg_stop stops your database service.

5. Run the command: createdb `whoami`

6. Connect to your postgres with the command: psql

7. brew reinstall readline - ONLY IF NEEDED

8. createuser -s postgres - fixes role "postgres" does not exist

9. Test with psql command

   $ psql
   psql (14.5 (Homebrew))
   Type "help" for help.
   
   ibraheem=# exit
   

Details

What is this ln command I ran in my Terminal?

from the man ln command

The ln utility creates a new directory entry (linked file) which has the same modes as the original file. It is useful for maintaining multiple copies of a file in many places at once without using up storage for the copies''; instead, a link points'' to the original copy. There are two types of links; hard links and symbolic links. How a link ``points'' to a file is one of the differences between a hard and symbolic link.

What is launchctl?

from the man launchctl command

launchctl interfaces with launchd to manage and inspect daemons, angents and XPC services.

Commands

Create database

createdb <database_name>

createdb mydjangoproject_development

List databases

psql -U postgres -l

Show tables in database

psql -U postgres -d <database_name>

psql -U postgres -d mydjangoproject_development

Drop database

dropdb <database_name>

dropdb mydjangoproject_development

Restart database

dropdb <database_name> && createdb <database_name>

dropdb mydjangoproject_development && createdb mydjangoproject_development

I am facing the same issue in MacOS BigSur

Load failed: 5: Input/output error

Does anyone find the solution?

@Mahfuz2811 Check out this response here: https://gist.github.com/ibraheem4/ce5ccd3e4d7a65589ce84f2a3b7c23a3#gistcomment-3443897

I am facing the same issue in MacOS BigSur

Load failed: 5: Input/output error

Does anyone find the solution?

Stucked with this issue too.

Hello everyone i try to create a user it returns no response but the is not created....what am i missing here

I am facing the same issue in MacOS BigSur

Load failed: 5: Input/output error

Does anyone find the solution?

I'm also stuck here when running either the alias OR brew services start postgresql, and running brew services cleanup in the response by @C-Compton didn't seem to work either. Is there a way to revert the launchutl/ln commands that I ran because of this gist?

When trying to run the unload commands from @remilia-grimm I get Unload failed: 5: Input/output error

I am facing the same issue in MacOS BigSur
Load failed: 5: Input/output error
Does anyone find the solution?

I'm also stuck here when running either the alias OR brew services start postgresql, and running brew services cleanup in the response by @C-Compton didn't seem to work either. Is there a way to revert the launchutl/ln commands that I ran because of this gist?

When trying to run the unload commands from @remilia-grimm I get Unload failed: 5: Input/output error

I also had the Unload failed: 5: Input/output error I was able to solve it and run it via brew services start from the following steps:

This was my link command:
ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents

Which produced the following output:
/Users/yourusername/Library/LaunchAgents -> /usr/local/opt/postgresql/homebrew.mxcl.postgresql.plist

As per this stack post you can remove by removing the thing to the left of the arrow:

sudo rm /Users/yourusername/Library/LaunchAgents

I was then able to run
brew services start postgresql

That help me alot. Thanks

This helps a lot. Thank you. :-)

• This command brew services stop postgresql helped me resolve Input/output error
• Then I could successfully start using pg_ctl -D /usr/local/var/postgres start

Thank Man

For M1 Macs, the directory changed a bit I see. usr/local/ should be replaced with /opt/homebrew/ when running step 2

As it is said here, it is not necessary to manage services manually that are explained on 2, 3 and 4 steps. Instead of these steps you can use these ones, I think, it is simpler and more manageable;

• To start PostgreSQL as service use;
  brew services start postgresql
• To stop PostgreSQL
  brew services stop postgresql
• And if you un-install the postgresql then you can remove its links with that command;
  brew services cleanup

And also by doing this way, brew services maintains the status of the service. I mean; when you stop the service via brew services, then it is not automatically started on next system start up. But, on the other hand, if you do not use brew services, then the service (postgresql) always started up on the system start up.

Thank you @ibraheem4 and @rclik

Thanks @joy2323

This might seem like a total noob question but, wouldn't launchctl load ~/Library/LaunchAgents load everything in the directory? and unload would unload everything.

As ~/Library/LaunchAgents is a default created folder, it might contain other agents that one would not want to load/unload.

Please let me know If I am mistaken.

removing

Thanks, this helped.

replace line 2 with ln -sfv /opt/homebrew/opt/postgresql/*.plist ~/Library/LaunchAgents for macOs 12.5 or recent versions

Thanks pnoken.

Quite helpful, thanks

replace line 2 with ln -sfv /opt/homebrew/opt/postgresql/*.plist ~/Library/LaunchAgents for macOs 12.5 or recent versions

Thank you

use this if not working for ventura and PG14, ln -sfv /usr/local/opt/postgresql@14/*.plist ~/Library/LaunchAgents

for Ventura+, use ln -sfv /opt/homebrew/opt/postgresql@14/*.plist ~/Library/LaunchAgents

Thanks @carakessler!

ln -sfv /opt/homebrew/opt/postgresql@14/*.plist ~/Library/LaunchAgents

Thanks it worked!

Here's an updated version, which has the idea of "this is a local dev db, just grant everything to my user and set their password".

brew install postgresql@14

# Copy launch agents for background start/stop.
ln -sfv /opt/homebrew/opt/postgresql@14/*.plist ~/Library/LaunchAgents

# Add aliases to shell profile to always have available
open ~/.zprofile # or vi ~/.zprofile

# In ~/.zprofile, add to the bottom
# 
alias pg_start="launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql@14.plist"
alias pg_stop="launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql@14.plist"
#
# Save and quit

# Load what you just added in zprofile
source ~/.zprofile

# No turn on postgres, run pg_stop to turn off
pg_start 

createdb localdb

# open psql console
psql postgres 

# Inside the psql console, Use semicolons!
# 
#
create role dev_local with password 'dev_local' login;
alter role dev_local superuser createdb createrole replication bypassrls;
grant all privileges on database localdb to dev_local;
grant usage on schema public to dev_local;
alter database localdb owner to dev_local;
alter schema public owner to dev_local;
\list # Check dbs
\du # Check roles
\dn+ # Check owners
\q # Quit
#
#
# End psql console

# CONN URL is postgresql://127.0.0.1:5432/localdb
# user: dev_local
# pass: dev_local

I encountered the input load errors and I just found another article that seems to have worked better - https://www.moncefbelyamani.com/how-to-install-postgresql-on-a-mac-with-homebrew-and-lunchy/

postgresql://127.0.0.1:5432/localdb

This works for me. Mac m1