Goal: I want a postgres environment wherein I can create some tables and insert some values into those tables and run some queries. This allows me to practice writing SQL queries to understand some concepts.
The first step is to install homebrew - package manager for mac.
Run command: /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
The first part of the output shows what will be installed under /usr/local/
(base) shravan-~$ /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
==> This script will install:
/usr/local/bin/brew
/usr/local/share/doc/homebrew
/usr/local/share/man/man1/brew.1
/usr/local/share/zsh/site-functions/_brew
/usr/local/etc/bash_completion.d/brew
/usr/local/Homebrew
==> The following existing directories will be made group writable:
/usr/local/bin
/usr/local/lib
==> The following existing directories will have their owner set to shravan:
/usr/local/bin
/usr/local/lib
==> The following existing directories will have their group set to admin:
/usr/local/bin
/usr/local/lib
==> The following new directories will be created:
/usr/local/etc
/usr/local/include
/usr/local/sbin
/usr/local/share
/usr/local/var
/usr/local/opt
/usr/local/share/zsh
/usr/local/share/zsh/site-functions
/usr/local/var/homebrew
/usr/local/var/homebrew/linked
/usr/local/Cellar
/usr/local/Caskroom
/usr/local/Homebrew
/usr/local/Frameworks
Press RETURN to continue or any other key to abortPress RETURN to continue ..
==> Installation successful!
==> Homebrew has enabled anonymous aggregate formulae and cask analytics.
Read the analytics documentation (and how to opt-out) here:
https://docs.brew.sh/Analytics
==> Homebrew is run entirely by unpaid volunteers. Please consider donating:
https://github.com/Homebrew/brew#donations
==> Next steps:
- Run `brew help` to get started
- Further documentation:
https://docs.brew.sh
Run: brew install postgres
This will install postgres, the last few messages give you some suggestions as to what we can do to start postgres.
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
I don't want postgres to be running in the background everytime I start my laptop. So, just start the postgres server once using:
pg_ctl -D /usr/local/var/postgres start
By default postgres runs on port 5432
(base) shravan-~$ pg_ctl -D /usr/local/var/postgres start
waiting for server to start....2019-10-17 11:02:16.746 EDT [25689] LOG: listening on IPv6 address "::1", port 5432
2019-10-17 11:02:16.746 EDT [25689] LOG: listening on IPv4 address "127.0.0.1", port 5432
2019-10-17 11:02:16.747 EDT [25689] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-10-17 11:02:16.756 EDT [25690] LOG: database system was shut down at 2019-10-17 10:55:32 EDT
2019-10-17 11:02:16.760 EDT [25689] LOG: database system is ready to accept connections
done
server started
Check what users exist by default:
When we did the installation, postgres automatically creates the user postgres and shravan. The way to confirm this is by using psql utility. psql is a utility installed with Postgres that lets you carry out administrative functions without needing to know their actual SQL commands.
Run psql postgres
(base) shravan-~$ psql postgres
psql (11.5)
Type "help" for help.
postgres=#
The above output prompt is the psql command line. We can now enter a command to see what users are installed:
Run \du
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
shravan | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#
Ah!! so, the default user postgres is not visible here, but you can see that Role name: shravan is created by default. Postgres deals with roles and not users. So, your role is shravan and it has the following attributes Superuser, Create role, Create DB, Replication, Bypass RLS.
Postgres doesn’t actually directly manage users or groups, it directly manages what it calls roles.
Ok, so now we understand roles and users. The next thing is to understand how to connect to a database. But before that, can we list the existing databases and who owns them?
There is a default database that gets created when you first install postgres. The name of this database is postgres
That's why you typed psql postgres when you launched the psql command line utility, because you need to specify which database you are connecting to and as which user.
So, you want to connect to the default database postgres as shravan, then run this:
psql postgres -U shravan
(base) shravan-~$ psql postgres -U shravan
psql (11.5)
Type "help" for help.
postgres=#
Since you are a superuser, you are getting the root prompt #.
List existing databases: Using \l we can list all the databases. As you can see, we have a database with name postgres whose owner is shravan.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+---------+----------+---------+-------+---------------------
postgres | shravan | UTF8 | C | C |
template0 | shravan | UTF8 | C | C | =c/shravan +
| | | | | shravan=CTc/shravan
template1 | shravan | UTF8 | C | C | =c/shravan +
| | | | | shravan=CTc/shravan
(3 rows)
postgres=#
Remember our goal is to be able to create some tables and run some queries on it. We don't want to do this on the default database postgres. For this reason, we need to create a new database.
Create a database called interviews:
- First connect to the postgres database as shravan:
psql postgres -U shravan - Second run
create database interviewsand list the databases to see the output
postgres=# create database interviews;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+---------+----------+---------+-------+---------------------
interviews | shravan | UTF8 | C | C |
postgres | shravan | UTF8 | C | C |
NOTE: Since shravan is a superuser, I thought I already have all the permissions to create tables and such. Turns out, that I need to run the GRANT command explicitly. See the difference now in the Access privileges column.
postgres=# GRANT ALL PRIVILEGES ON DATABASE interviews TO shravan;
GRANT
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+---------+----------+---------+-------+---------------------
interviews | shravan | UTF8 | C | C | =Tc/shravan +
| | | | | shravan=CTc/shravan
postgres | shravan | UTF8 | C | C |
Alight, we are all set now to connect to this database from our jupyter notebook!!