Skip to content

Instantly share code, notes, and snippets.

@ibraheem4
Forked from sgnl/postgres-brew.md
Last active March 20, 2024 11:33
Show Gist options
  • Save ibraheem4/ce5ccd3e4d7a65589ce84f2a3b7c23a3 to your computer and use it in GitHub Desktop.
Save ibraheem4/ce5ccd3e4d7a65589ce84f2a3b7c23a3 to your computer and use it in GitHub Desktop.
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 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

@marciprescott
Copy link

marciprescott commented May 30, 2022

Thanks @joy2323

@lucas-larsson
Copy link

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.

@sofiree-zz
Copy link

removing

Thanks, this helped.

@pnoken
Copy link

pnoken commented Aug 19, 2022

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

@gnorman
Copy link

gnorman commented Sep 3, 2022

Thanks pnoken.

@Sillians
Copy link

Sillians commented Oct 6, 2022

Quite helpful, thanks

@hariramsr
Copy link

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

Thank you

@aperez-kueski
Copy link

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

@carakessler
Copy link

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

@jsantanders
Copy link

Thanks @carakessler!

@codeTIT4N
Copy link

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

Thanks it worked!

@0x-2a
Copy link

0x-2a commented Sep 26, 2023

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

@lloyddugmore
Copy link

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/

@intellect4all
Copy link

postgresql://127.0.0.1:5432/localdb

This works for me. Mac m1

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment