Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Installing Postgres via Brew (OSX)

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 postgres

  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/homebrew.mxcl.postgresql.plist"
         alias pg_stop="launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist"
    
  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 (10.0)
    Type "help" for help.
    
    ibraheem=# 
    

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

@nydiarra

This comment has been minimized.

Copy link

@nydiarra nydiarra commented Aug 20, 2018

I like your post. Very useful.

@Piglacquer

This comment has been minimized.

Copy link

@Piglacquer Piglacquer commented Sep 10, 2018

Nice post, one little typo: Between steps 3 & 4 the aliases are different. Step 3 is pg_start // pg_stop, and step 4 is pg-start // pg-stop. Thanks!

@sandro-git

This comment has been minimized.

Copy link

@sandro-git sandro-git commented Sep 22, 2018

thanks it help me a lot

@leftsider

This comment has been minimized.

Copy link

@leftsider leftsider commented Oct 17, 2018

Simple and clear. Cheers!

@cyzanfar

This comment has been minimized.

Copy link

@cyzanfar cyzanfar commented Oct 18, 2018

awesome stuffffff

@Darius-Ndubi

This comment has been minimized.

Copy link

@Darius-Ndubi Darius-Ndubi commented Nov 6, 2018

Works like a charm.

@sergeev

This comment has been minimized.

Copy link

@sergeev sergeev commented Nov 21, 2018

Thx! очень помогли!

@Physsix27

This comment has been minimized.

Copy link

@Physsix27 Physsix27 commented Dec 11, 2018

Super helpful, thank you so much 👍 💯

@Sylvance

This comment has been minimized.

Copy link

@Sylvance Sylvance commented Dec 13, 2018

Works like a charm.

True.

@balanka

This comment has been minimized.

Copy link

@balanka balanka commented Dec 31, 2018

Login as default PostgreSQL user (postgres)

sudo -u postgres -i

@BesatZardosht

This comment has been minimized.

Copy link

@BesatZardosht BesatZardosht commented Jan 9, 2019

I had to change step 3 to make it work:

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

This comment has been minimized.

Copy link

@AyoAlfonso AyoAlfonso commented Jan 17, 2019

I think you meant to write 'pg_start' you wrote 'pg-start' instead.
Thanks for this guide Ibraheem.

@rajeshksv

This comment has been minimized.

Copy link

@rajeshksv rajeshksv commented Jan 27, 2019

7th step - Read carefully. Don't reinstall readline until and unless needed.

I did it and it broke my whole system. Bash, Awk commands stopped working. Had to do "brew upgrade" to fix the issue.

@meniltiac

This comment has been minimized.

Copy link

@meniltiac meniltiac commented Feb 5, 2019

Thanks so much for putting this together! It looks like the brew command has been updated, though. It's now brew install postgres and not brew install postgresql.

@ibraheem4

This comment has been minimized.

Copy link
Owner Author

@ibraheem4 ibraheem4 commented Feb 16, 2019

Nice post, one little typo: Between steps 3 & 4 the aliases are different. Step 3 is pg_start // pg_stop, and step 4 is pg-start // pg-stop. Thanks!

Thanks for catching this @Piglacquer, updated :)

@ibraheem4

This comment has been minimized.

Copy link
Owner Author

@ibraheem4 ibraheem4 commented Feb 16, 2019

7th step - Read carefully. Don't reinstall readline until and unless needed.

I did it and it broke my whole system. Bash, Awk commands stopped working. Had to do "brew upgrade" to fix the issue.

Thanks for the comment, I've bolded if needed so hopefully others don't have this issue.

@ibraheem4

This comment has been minimized.

Copy link
Owner Author

@ibraheem4 ibraheem4 commented Feb 16, 2019

Thanks so much for putting this together! It looks like the brew command has been updated, though. It's now brew install postgres and not brew install postgresql.

Thanks for the comment, I've updated to postgres instead of postgresql since it's the latest / greatest naming :)

@ganga549

This comment has been minimized.

Copy link

@ganga549 ganga549 commented Feb 20, 2019

Hi,
I am getting error at Step 5 - '-bash: createdb: command not found'

@leodotng

This comment has been minimized.

Copy link

@leodotng leodotng commented Feb 24, 2019

There's a gazillion ways to install this is the only guide that I don't run into errors first time around. Thank you for crystal clear steps!

@wardch

This comment has been minimized.

Copy link

@wardch wardch commented Mar 7, 2019

Thanks a million for this gist, so straighforward. Got me rolling with a new db when I was having issues with set up

@arshpreetsingh

This comment has been minimized.

Copy link

@arshpreetsingh arshpreetsingh commented Mar 14, 2019

Thanks! You rocks! saved my life ;)

@GUIEEN

This comment has been minimized.

Copy link

@GUIEEN GUIEEN commented Mar 24, 2019

Thank you for your nice summary. But I'm curious that is there a reason you don't use brew services start/stop postgresql instead of making alias in shell ? I think controlling services installed by brew with brew services makes much more easier and simple.

@mercer08

This comment has been minimized.

Copy link

@mercer08 mercer08 commented Apr 19, 2019

truly useful,thanks

@sinanaltaii

This comment has been minimized.

Copy link

@sinanaltaii sinanaltaii commented May 19, 2019

Thank you! Very useful and helped me

@kaushik-evani-pe

This comment has been minimized.

Copy link

@kaushik-evani-pe kaushik-evani-pe commented Jun 17, 2019

Instead of creating symlinks, why don't you use brew service start postgresql@9.6?

@dirkjot

This comment has been minimized.

Copy link

@dirkjot dirkjot commented Jul 3, 2019

This is no longer necessary!

$ brew info postgresql
[..]
To have launchd start postgresql now and restart at login:
  brew services start postgresql
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgres start


@jsjoeio

This comment has been minimized.

Copy link

@jsjoeio jsjoeio commented Jul 3, 2019

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

I had to do this to make mine work as well.

@lukewest

This comment has been minimized.

Copy link

@lukewest lukewest commented Jul 12, 2019

I also wanted to have a status capability, so I added the following to my ~/.bash_profile

alias pg_status="pg_ctl status -D /usr/local/var/postgres/"
echo "start/stop postgres pg_start and pg_stop, status= pg_status"
echo ""
@ndukwec

This comment has been minimized.

Copy link

@ndukwec ndukwec commented Jul 18, 2019

Helpful, thanks!

@DonkeyKong1

This comment has been minimized.

Copy link

@DonkeyKong1 DonkeyKong1 commented Sep 9, 2019

Thanks for this excellent post!

@Alexandre1202

This comment has been minimized.

Copy link

@Alexandre1202 Alexandre1202 commented Sep 21, 2019

Useful! Thank you.

@dafnalia

This comment has been minimized.

Copy link

@dafnalia dafnalia commented Sep 24, 2019

Thank you so much for this!

@jwilmott

This comment has been minimized.

Copy link

@jwilmott jwilmott commented Oct 12, 2019

thank you!

@vinayakmkmishra1792

This comment has been minimized.

Copy link

@vinayakmkmishra1792 vinayakmkmishra1792 commented Nov 6, 2019

Thank You for the setup. I am noob to psql and trying to connect it via JDBC. What will be the values of :
1)Connection string
2)Username
3)Password

@prashanth180

This comment has been minimized.

Copy link

@prashanth180 prashanth180 commented Nov 23, 2019

Thank you!

@amjimenez

This comment has been minimized.

Copy link

@amjimenez amjimenez commented Nov 24, 2019

Awesome. 😄

@booleguo

This comment has been minimized.

Copy link

@booleguo booleguo commented Nov 26, 2019

Thanks!

@danvalencia

This comment has been minimized.

Copy link

@danvalencia danvalencia commented Dec 5, 2019

Thanks for this 🙏

@albagon

This comment has been minimized.

Copy link

@albagon albagon commented Jan 23, 2020

Very useful. Thank you!!

@urbantumbleweed

This comment has been minimized.

Copy link

@urbantumbleweed urbantumbleweed commented Jan 23, 2020

Thank you

@rajdeep-arora

This comment has been minimized.

Copy link

@rajdeep-arora rajdeep-arora commented Feb 11, 2020

Thank you

@raghav4

This comment has been minimized.

Copy link

@raghav4 raghav4 commented Feb 12, 2020

I had to change step 3 to make it work:

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

@BesatZardosht Thank you! 😄

@danielcn

This comment has been minimized.

Copy link

@danielcn danielcn commented Feb 20, 2020

@raghav4 and @BesatZardosht thank you. But I think it's is not the best approach, since we have other services mapped in this folder.
Clarifying this, when I ran pg_start I got the follow message:

/Users/user/Library/LaunchAgents/org.virtualbox.vboxwebsrv.plist: Service is disabled

I have Virtual box installed in my machine and this service was disabled. I thought the service hadn't started, and I ran pg_start again (maybe my bad). From this time I got the following message:

/Users/user/Library/LaunchAgents/com.postgresapp.Postgres2LoginHelper.plist: service already loaded
/Users/user/Library/LaunchAgents/com.google.keystone.agent.plist: service already loaded
/Users/user/Library/LaunchAgents/org.virtualbox.vboxwebsrv.plist: Service is disabled

With that I was sure that it is not the best approach. In our case (postgres), I recommend to list all the services that you have using grep to check what is/are the service(s) associated with postgres:

ls -la /Users/user/Library/LaunchAgents/ | grep postgres*
/Users/user/Library/LaunchAgents/com.postgresapp.Postgres2LoginHelper.plist

After that you can create the symbolic links for pg_start and pg_start referring the *.plist file directly.

Using this, we don't need to create a symbolic link as presented before:
ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents (-sfv: create a symbolic link overriding if exist generically)

Thank you again. But it's was only to clarify that using your approach you will eventually start or stop other services you don't want.

I had to change step 3 to make it work:

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

@BesatZardosht Thank you! 😄

@raghav4

This comment has been minimized.

Copy link

@raghav4 raghav4 commented Feb 20, 2020

@danielcn yes, I agree. Even my mongo server got disconnected this way.

ls -la /Users/user/Library/LaunchAgents/ | grep postgres*

Ok, so is it going to stop postrgres only?

@danielcn

This comment has been minimized.

Copy link

@danielcn danielcn commented Feb 21, 2020

@danielcn yes, I agree. Even my mongo server got disconnected this way.

ls -la /Users/user/Library/LaunchAgents/ | grep postgres*

Ok, so is it going to stop postrgres only?

@raghav4 I update my comment. Please, check there and let me know if I helped you.
Thanks again!!!

@raghav4

This comment has been minimized.

Copy link

@raghav4 raghav4 commented Feb 21, 2020

@danielcn got this error, can you help me out to fix it? getting no matches found even if I've Postgres installed on my machine.

❯ ls -la /Users/user/Library/LaunchAgents/ | grep postgres*
zsh: no matches found: postgres*
ls: /Users/user/Library/LaunchAgents/: No such file or directory

@HighSoftWare96

This comment has been minimized.

Copy link

@HighSoftWare96 HighSoftWare96 commented Feb 25, 2020

Very helpful!

@danielcn

This comment has been minimized.

Copy link

@danielcn danielcn commented Feb 26, 2020

@danielcn got this error, can you help me out to fix it? getting no matches found even if I've Postgres installed on my machine.

❯ ls -la /Users/user/Library/LaunchAgents/ | grep postgres*
zsh: no matches found: postgres*
ls: /Users/user/Library/LaunchAgents/: No such file or directory

Hi @raghav4,

That isn't an error. That means you don't have a file containing the string "postgres" as part of the results (see the grep documentation).
I suggested you use my approach if you already have a *.plist file inside ~/Library/LaunchAgents. If not, you should create a symbolic link before:

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

I hope to help you!
Thanks!

@risha700

This comment has been minimized.

Copy link

@risha700 risha700 commented Mar 4, 2020

IF you are on a mac and using homebrew as package manger then brew services works well as central process luncher
brew install postgresql
export PGDATA='/usr/local/var/postgres' #check your package dir
brew services start postgresql
and you can check processes with

brew services list

and I think you guessed how to stop it
brew services stop postgresql

@predawnang

This comment has been minimized.

Copy link

@predawnang predawnang commented Mar 15, 2020

Postgres version 12.2
I need to type another command createdb without any argument between step 4 and 5 to make it work, otherwise it would raise an error (psql: error: could not connect to server...) when I type psql.

@coltonbh

This comment has been minimized.

Copy link

@coltonbh coltonbh commented Mar 19, 2020

If your build is still failing and you installed openssl via homebrew you may also need the following:

export LDFLAGS="-L/usr/local/opt/openssl/lib" export CPPFLAGS="-I/usr/local/opt/openssl/include"

More details here: psycopg/psycopg2#997

@peter

This comment has been minimized.

Copy link

@peter peter commented Mar 28, 2020

At step 6 (on Mac OS 10.14 Mojave) when invoking psql for the first time I am promped for a password for my OS user and my normal password doesn't work. How do you proceed from there?

@SamuelGirsang

This comment has been minimized.

Copy link

@SamuelGirsang SamuelGirsang commented May 26, 2020

thank you

@Sunhat

This comment has been minimized.

Copy link

@Sunhat Sunhat commented Jun 7, 2020

For newbies, they wouldn't know about creating aliases. This article is created by an arrogant and obnoxious person.

@codyromano

This comment has been minimized.

Copy link

@codyromano codyromano commented Jun 16, 2020

Thank you! This is very helpful

@ChariniNana

This comment has been minimized.

Copy link

@ChariniNana ChariniNana commented Jun 17, 2020

Thank you! Great post!

@stone16

This comment has been minimized.

Copy link

@stone16 stone16 commented Aug 15, 2020

Great post!

@imdoingotherthings

This comment has been minimized.

Copy link

@imdoingotherthings imdoingotherthings commented Aug 18, 2020

Very nice!

@mazlo

This comment has been minimized.

Copy link

@mazlo mazlo commented Aug 21, 2020

Small add-on to the documentation:

Import data

psql -U postgres -d dbname < psql_dump_from_before.sql

@kharam

This comment has been minimized.

Copy link

@kharam kharam commented Aug 22, 2020

awesome

@rohmatmret

This comment has been minimized.

Copy link

@rohmatmret rohmatmret commented Sep 2, 2020

hello l got a problem

dyld: Library not loaded: /usr/local/opt/openssl@1.1/lib/libssl.1.1.dylib
Referenced from: /usr/local/Cellar/postgresql/12.4/lib/libpq.5.12.dylib
Reason: image not found

@chrisscoder

This comment has been minimized.

Copy link

@chrisscoder chrisscoder commented Sep 6, 2020

🙏🏻

@rclik

This comment has been minimized.

Copy link

@rclik rclik commented Sep 6, 2020

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.

@shmuelberezin

This comment has been minimized.

Copy link

@shmuelberezin shmuelberezin commented Sep 9, 2020

Thank you @ibraheem4 and @rclik!

@NguyenHiep

This comment has been minimized.

Copy link

@NguyenHiep NguyenHiep commented Oct 3, 2020

Thanks.

@rust-7

This comment has been minimized.

Copy link

@rust-7 rust-7 commented Oct 11, 2020

Thanks;

@maherelgamil

This comment has been minimized.

Copy link

@maherelgamil maherelgamil commented Oct 13, 2020

Thanks 🙈

@gpalazzo

This comment has been minimized.

Copy link

@gpalazzo gpalazzo commented Oct 15, 2020

amazing tutorial, worked perfectly :)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.