logo image

Setting up PostgreSQL in macOS


PostgreSQL is one of the fast growing object-relational database management system (ORDBMS). This tutorial helps you install PostgreSQL on your macOS and helps you configure a user & database for your first application using PostgreSQL.

1. Install Homebrew (If you don't have already)

Installing PostgreSQL using Homebrew is the easiest & less hassle way of setting up on macOS. So first get Homebrew if you don't have it already. You can install it by running following command

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

2. Install PostgreSQL using Homebrew

Once you have homebrew in your mac, installing any other software is easy. You can install PostgreSQL by running following command

brew install postgresql

3. Start PostgreSQL service

  • Start & Stop On demand

    If you want to run PostgreSQL on demand then you can start the service with the following command
brew services start postgresql

And you can stop the service with the following command

brew services stop postgresql
  • Run Always (Start on System startup)

    If you want to run PostgreSQL on startup of your macOS then you need to run the following command
pg_ctl -D /usr/local/var/postgres start && brew services start postgresql

4. Check the successful installation

Now you if you run the postgres -V command then you should see the PostgreSQL if you can see that, your installation is successful.

postgres -V

This command will output the following

postgres (PostgreSQL) 9.6.5

5. Creating User & Database using 'psql' utility

Now lets use psql utility which is shipped by PostgreSQL to create our User & Databse 1. Login to psql utility using the following command

psql postgres

Now you should see output like below

MacBook-Pro:~ root$ psql postgres
psql (9.6.5)
Type "help" for help.

Now you are in psql command line, you can start executing SQL queries here.

  1. Create User Now lets create User with name 'hello_world_user' by running following commands
CREATE ROLE hello_world_user WITH LOGIN PASSWORD 'password';
ALTER ROLE hello_world_user CREATEDB; 
  1. Switch to the User we just created Now let's switch to the User we just created, to do that first quit from the current session. To quit from the session run following command

Now login to psql with the user 'hello_world_user', run the following command to do that.

psql postgres -U hello_world_user;
  1. Create Database Now lets create the database with name 'hello_world_db' and grant all privileges to the user 'hello_world_user'. Run the following commands to so that
CREATE DATABASE hello_world_db;
GRANT ALL PRIVILEGES ON DATABASE hello_world_db TO hello_world_user;
  1. Connect to the database to run SQL queries Now we can login to the data base we just created using the following command
\connect hello_world_user

6. GUI Options to connect to your database

Though you can run the SQL queries using plql utility, it will become extremely hard to use that once you have a good amount of data and tables in your database. That's where GUI tools come to help you. postico (https://eggerapps.at/postico/) is one among them. It's a very good looking GUI option for PostgreSQL. Following screen shots shows you how to connect to the database using postico and how to create tables and play with the data

Connecting to postgresql database using postico

Connecting to the database we created

Creating new table in postgresql database using postico Creating new table

Add columns to a table in postgresql database using postico Adding columns by altering table

See the table records in postgresql database using postico Doing CRUD operations on data