Setting up PostgreSQL on macOS

This tutorial was written in RMarkdown in RStudio (version 1.2.1206, R version 3.5.1), using mac OS High Sierra (10.13.6).

Why install PostgreSQL?

PostgreSQL is an open-source relational database management system (RDBMS). You can install PostgreSQL on Linux, Unix, Windows, or Mac. Users can write and execute stored procedures and functions from other programming languages (like R and Python). Multiple data types can also be stored (and even defined) in PostgreSQL, including JSON and other nonrelational data.

Read more about PostgreSQL here.

PostgreSQL application

Download the application from this website and follow the installation instructions.

The syntax documentation for PostgreSQL is here.

First steps

After downloading and installing the PostgreSQL application, open a new Terminal window. In the Terminal, enter the following:

$ sudo mkdir -p /etc/paths.d &&
$ echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp

You’ll be asked to enter whatever password you need to install programs on your computer. Type it in and hit enter.

Install PostgreSQL using Homebrew

You can also install PostgreSQL using homebrew. If you don’t have homebrew installed, learn how to do so here.

In a fresh terminal window, enter the following:

$ brew install postgresql
==> Installing postgresql
==> Downloading https://homebrew.bintray.com/bottles/postgresql-11.1.mojave.bottle.tar.gz
################################################################## 100.0%
==> Pouring postgresql-11.1.mojave.bottle.tar.gz
==> Caveats
To migrate existing data from a previous major version of PostgreSQL run:
brew postgresql-upgrade-database
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/11.1: 3,548 files, 40.3M

Launching PostgreSQL from Terminal

Read the documentation for a more in-depth explanation.

In a fresh Terminal session (If you are using RStudio’s Terminal pane, select Tools > Terminal > New Terminal), enter the following.

psql postgres

This will bring up the psql command line.

psql (10.5)
Type "help" for help.

postgres=#

The =# is where we will be entering our postgres commands.

Check users, roles and attributes

We can access the users, their roles and attributes in PostgreSQL with the following:

postgres=# \du

This will return a table with the following information:

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

Add a new user

The postgres=# prompt indicates I am currently a Superuser. To create a new user for martymcfly, I enter a USER and WITH PASSWORD:

=# CREATE USER martymcfly WITH PASSWORD 'strongpassword123';
-- CREATE ROLE

PostgreSQL typically returns the command that was previously executed. Because CREATE USER is another spelling of CREATE ROLE, PostgreSQL returns CREATE ROLE in the command prompt.

I will check to make sure this is true with \du again.

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

Grant attributes to new user

As I can see, martymcfly has no roles or attributes. I want this user to be able to create a new database. I can do this with the following:

=# ALTER ROLE martymcfly CREATEDB;

And I can verify this with the \du command again.

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

Creating a database

I’m going to switch over the new user I just created by exiting PostgreSQL and signing back in as martymcfly:

=# \q --exit 

Now sign in as a new user

psql postgres -U martymcfly

This brings up a new PostgreSQL prompt.

postgres=>

The =>tells me I am no longer the Superuser, but I know that I have given martymcfly the relevant permissions to create a new database.

Below are the steps needed to 1) create a new database (I’m creating a Lahman baseball database in PostgreSQL), 2) grant privileges to the user martymcfly on the new lahman2017 database, 3) list all the current databases in PostgreSQL, and 4) connect to the lahamn2017 database.

First create lahman2017

CREATE DATABASE lahman2017;
-- CREATE DATABASE

Then give martymcfly permissions

 GRANT ALL PRIVILEGES ON DATABASE lahman2017 TO martymcfly;
-- GRANT

Now list the databases

\list

This returns:

                             List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges |
----------------+----------------+----------+-------------+-------------+--------------------------
lahman2017 | martymcfly | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/martymcfly +
| | | | | martymcfly=CTc/martymcfly
martinfrigaard | martinfrigaard | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)

Finally, we connect to the new database

\connect lahman2017
psql (11.1, server 11.0)
You are now connected to database "lahman2017" as user "martymcfly".

Great! Next time we will build the schemas and tables in the lahman2017 database.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.