Here we will see how to install PostgreSQL on Mac with homebrew.

Versions:

  • Mac OS Big Sur 11.04
  • Homebrew 3.2.3

How to install PostgreSQL on Mac:

Make sure you have to install homebrew first, if you don’t have it yet, it’s straightforward.

Update/Upgrade Homebrew:

It’s always recommended to update homebrew before installing any package.

chandra~ % brew update
Updated 1 tap (homebrew/core).
==> Updated Formulae
luv

You have 10 outdated formulae and 2 outdated casks installed.
You can upgrade them with brew upgrade
or list them with brew outdated.

The above brew update command gave me 10 outdated formulae and 2 casks. It may be different for you.

chandra ~ % brew upgrade
==> Upgrading 10 outdated packages:
pyenv 2.0.0 -> 2.0.3
vim 8.2.3050 -> 8.2.3175
terraform 0.14.8 -> 1.0.2
aws/tap/aws-sam-cli 1.24.1 -> 1.26.0
pivotal/tap/gemfire 9.10.6 -> 9.10.8
poetry 1.1.6 -> 1.1.7
pcre2 10.36 -> 10.37_1
ruby 3.0.1 -> 3.0.2
git 2.31.0 -> 2.32.0
python@3.9 3.9.5 -> 3.9.6
==> Upgrading ruby
  3.0.1 -> 3.0.2 

==> Downloading https://ghcr.io/v2/homebrew/core/ruby/manifests/3.0.2
######################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/ruby/blobs/sha256:39da97472055c844b0d23fc3c6030393d8fb8cde17098cf4eae0c590a95e6990
==> Downloading from https://pkg-containers.githubusercontent.com/ghcr1/blobs/sha256:39da97472055c844b0d23fc3c6030393d8fb8cde17098cf4eae0c590a95e6990?se=2021-07-19T17%3A30%3A00Z&sig=n6lGnCqUmAlKP5NIVpN16pTgBYxmH1GmfB%2FTA5g5SgE%3D&sp=r&spr=https&sr=b&sv=
######################################################################## 100.0%
==> Pouring ruby--3.0.2.big_sur.bottle.tar.gz
🍺  /usr/local/Cellar/ruby/3.0.2: 16,390 files, 38.5MB
Removing: /usr/local/Cellar/ruby/3.0.1... (16,358 files, 38.4MB)
Removing: /Users/chandra/Library/Caches/Homebrew/ruby--3.0.1... (11.0MB)
==> Upgrading vim
  8.2.3050 -> 8.2.3175 

==> Downloading https://ghcr.io/v2/homebrew/core/python/3.9/manifests/3.9.6
######################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/python/3.9/blobs/sha256:1397f8fbb9c5858aa02b0177787cb74b8eb0dfede69fc37b64e2787d76baf2fe
==> Downloading from https://pkg-containers.githubusercontent.com/ghcr1/blobs/sha256:1397f8fbb9c5858aa02b0177787cb74b8eb0dfede69fc37b64e2787d76baf2fe?se=2021-07-19T17%3A30%3A00Z&sig=fm3Mzi19ThnhN50Kyp8n48rdZwByOKsg03RLZgsy2aE%3D&sp=r&spr=https&sr=b&sv=20
######################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/vim/manifests/8.2.3175
######################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/vim/blobs/sha256:c146d3ba4da3dfe873b670e0c5f396a7ecdffbe0befce600855561f04a1f8665
==> Downloading from https://pkg-containers.githubusercontent.com/ghcr1/blobs/sha256:c146d3ba4da3dfe873b670e0c5f396a7ecdffbe0befce600855561f04a1f8665?se=2021-07-19T17%3A30%3A00Z&sig=QAiCQ%2BbpgVXdlq%2BTec7yeCOezEc47qv0KUHUBEe9w%2Fg%3D&sp=r&spr=https&sr=b
######################################################################## 100.0%
==> Installing dependencies for vim: python@3.9
==> Installing vim dependency: python@3.9
==> Pouring python@3.9--3.9.6.big_sur.bottle.tar.gz
.....
.....

So far, the prerequisites were done.

Install PostgreSQL:

Now let’s initiate our actual process with brew install postgresql

chandra ~ % brew install postgresql 
==> Downloading https://ghcr.io/v2/homebrew/core/icu4c/manifests/69.1
######################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/icu4c/blobs/sha256:d46b8ec5c3db629e7848e9fd31e5ec99ed952d9c81c8936a2511fae803d831fd
==> Downloading from https://pkg-containers.githubusercontent.com/ghcr1/blobs/sha256:d46b8ec5c3db629e7848e9fd31e5ec99ed952d9c81c8936a2511fae803d831fd?se=2021-07-19T17%3A25%3A00Z&sig=xHAdE8MjfV6UUK5HbaXtrl%2FkReEBcnTYJaeWH5U%2FeHE%3D&sp=r&spr=https&sr=b&s
######################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/krb5/manifests/1.19.1
######################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/krb5/blobs/sha256:d544c1111503eb27b253e190998b948889ea224b1ebecbceb6a4dd912317eb53
==> Downloading from https://pkg-containers.githubusercontent.com/ghcr1/blobs/sha256:d544c1111503eb27b253e190998b948889ea224b1ebecbceb6a4dd912317eb53?se=2021-07-19T17%3A25%3A00Z&sig=sdQrYAuIhcUTLUmngoAOlTTQ0WV3PdV4uGHQmdDNcI0%3D&sp=r&spr=https&sr=b&sv=20
######################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/postgresql/manifests/13.3
######################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/postgresql/blobs/sha256:eaf28965ead970ecfb327b121ec6a07f0a4e39865797a1a0383605a17e5911e3
==> Downloading from https://pkg-containers.githubusercontent.com/ghcr1/blobs/sha256:eaf28965ead970ecfb327b121ec6a07f0a4e39865797a1a0383605a17e5911e3?se=2021-07-19T17%3A25%3A00Z&sig=jZPXwrBY21IOMMZvx%2BHqiohONqM2g8dtdcuEzFsssRs%3D&sp=r&spr=https&sr=b&sv=
######################################################################## 100.0%
==> Installing dependencies for postgresql: icu4c and krb5
==> Installing postgresql dependency: icu4c
==> Pouring icu4c--69.1.big_sur.bottle.tar.gz
🍺  /usr/local/Cellar/icu4c/69.1: 259 files, 72.8MB
==> Installing postgresql dependency: krb5
==> Pouring krb5--1.19.1.big_sur.bottle.tar.gz
🍺  /usr/local/Cellar/krb5/1.19.1: 162 files, 5.2MB
==> Installing postgresql
==> Pouring postgresql--13.3.big_sur.bottle.tar.gz
==> /usr/local/Cellar/postgresql/13.3/bin/initdb --locale=C -E UTF-8 /usr/local/var/postgres
==> Caveats
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:
  initdb --locale=C -E UTF-8 /usr/local/var/postgres
For more details, read:
  https://www.postgresql.org/docs/13/app-initdb.html

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
==> Summary
🍺  /usr/local/Cellar/postgresql/13.3: 3,225 files, 42.7MB
==> Caveats
==> postgresql
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:
  initdb --locale=C -E UTF-8 /usr/local/var/postgres
For more details, read:
  https://www.postgresql.org/docs/13/app-initdb.html

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

Homebrew installs PostgreSQL as a service in the Mac, so it is our task to start or stop the service.

Start/Stop PostgreSQL:

brew services start postgresqlcommand helps us to start the service, similarly brew services stop postgresqlused to stop the service.

chandra~ % brew services start postgresql
==> Tapping homebrew/services
Cloning into '/usr/local/Homebrew/Library/Taps/homebrew/homebrew-services'...
remote: Enumerating objects: 1327, done.
remote: Counting objects: 100% (206/206), done.
remote: Compressing objects: 100% (146/146), done.
remote: Total 1327 (delta 79), reused 173 (delta 57), pack-reused 1121
Receiving objects: 100% (1327/1327), 391.28 KiB | 682.00 KiB/s, done.
Resolving deltas: 100% (555/555), done.
Tapped 1 command (28 files, 473.4KB).
==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)

Configure PostgreSQL server:

So far we have installed the PostgreSQL DB server on Mac successfully, but to get access to the DB we need user credentials, so let’s create a user and password for it.

The psql postgrescommand allows you to get into the PostgreSQL terminal.

chandra ~ % psql postgres 
psql (13.3)
Type "help" for help.

postgres=#

As per the above output we get into the PostgreSQL REPL.

Create user:

Create a user with a login password goes like below.

postgres=# CREATE ROLE otp WITH LOGIN PASSWORD ‘123456’;
CREATE ROLE

The above command creates a user without having any roles associated with it, now let’s assign create DB role.

ALTER ROLE otp CREATEDB;
ALTER ROLE

Login to PostgreSQL:

Now we have a user and password to login into the PostgreSQL DB. Let’s log in with the user.

chandra ~ % psql postgres -U otp
psql (13.3)
Type "help" for help.

postgres=>

We successfully get into the PostgreSQL console now.  Let’s check the privileges bypassing the \ducommand.

postgres=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 chandra  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 otp       | Create DB                                                  | {}

You can find the two users in the output, the first one is superuser, which was created while installing PostgreSQL itself, since it is a superuser it has almost all privileges. Next to that, we have our otpuser which we created in the previous step, it has Create DB role only.

Done!

FAQs:

1.How to change PostgreSQL password?

We can change the password using ALTER command like the following.

ALTER USER user_name WITH PASSWORD 'new_password';

2.Where can I found PostgreSQL.conf file?

The following command gives you the result.

~ % sudo find / -name "postgresql.conf"       
/usr/local/var/postgres/postgresql.conf

In my case the postgresql.conf file found at /usr/local/var/postgres/

References:

Happy Learning 🙂