Setting up PostgreSQL in macOS

1580

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

psql postgres

Now you should see output like below

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

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

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;

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

\q

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

psql postgres -U hello_world_user;

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;

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