Open CheatSheet Back To List Next Tutorial

Postgres Database (Video Tutorial)

Video Timestamps
0:00 Download the Postgres Installer 3:15 Use the PG Admin Graphical User Interface. Create and delete a database 4:53 The PSQL Shell 10:30 How to uninstall Postgres

Postgres Database

Intro

Postgres and MySQL are the most popular free open-source relational database management systems.

This tutorial shows you how to download and install Postgres, the PG Admin GUI Tool, and the Postgres Shell program on Mac. This mostly applies to installing Postgres on Windows as well. 

This is the first in a series of tutorials on how to use a relational database. We will be building a simple bookstore database and it is recommended that you follow along. Feel free to use a different relational database, such as MySQL or SQLite, if you prefer.

Each tutorial has both a written version and an accompanying video tutorial. There are timestamps under the headings that align with the video version of the topic.

CheatSheet: The PSQL shell commands shown in this tutorial are available for reference in the Database CheatSheet Postgres section. As are the instructions for using the PG Admin GUI tool. You can bookmark it and refer to it as needed, or purchase the CheatSheet Desktop app and download it.
The Data Type references in the CheatSheet are covered in the next tutorial in the series. 


Install Postgres Using the EDB installer

[0:00 Video timestamp]

Go to postgresql.org/download/macosx
To install Postgres and the PGAdmin GUI at once use the Interactive installer by EDB installation option:
Instructions are at enterprisedb.com/docs/postgresql_journey/02_installing/04_macos
  • Click the "Download the installer" link. That will take you to the enterprisedb.com website.
  • Under MacOS, click the download icon for the latest Postgres version. It will download a dmg installer to your downloads folder.
  • Double-click on the .dmg installer file in your downloads folder.
  • It will open a dialog box with postgresql-MajorVersion-osx > Double-click on it.
  • A confirmation window will open > confirm by clicking "Open" > Enter your Mac password when prompted > A setup wizard window will open: Click Next.
  • Installation Directory window: Accept the default /Library/PostgreSQL/MajorVersion > Next
  • Select Components window: Leave at least the below options checked  > Next
    • PostgreSQL Server
    • pgAdmin 4
    • Command Line Tools
  • Data Directory: Accept the default: /Library/PostgreSQL/MajorVersion/data > Next
  • Password: provide a PW for the database superuser (postgres) and write it down somewhere > Next
  • Port: Accept default 5432 > Next
  • Advanced Options: Locale - leave default > Next
  • Review settings summary > If okay click Next
    • Installation Directory: /Library/PostgreSQL/MajorVersion
    • Server Installation Directory: /Library/PostgreSQL/MajorVersion
    • Data Directory: /Library/PostgreSQL/MajorVersion/data 
    • Database Port: 5432
    • Database Superuser: postgres
    • Operating System Account: postgres
    • Database Service: postgresql-MajorVersion
    • Command Line Tools Installation Directory: /Library/PostgreSQL/MajorVersion
    • pgAdmin4 Installation Directory: /Library/PostgreSQL/MajorVersion/pgAdmin 4
    • Installation Log: /tmp/install-postgresql.log


PG Admin GUI

[3:15 Video timestamp]

Open the app

  • Open Launchpad > Open PG Admin
  • On the left panel open Servers > open PostgreSQL 1x > Open Databases
  • The default database "postgres" should be listed.

Create or Drop a Database

Create a Database:
  • Right click on the "Databases icon" > Create Database
  • Enter a database name such as "practice" > Save
  • The new database should be listed under "Databases"
  • View the tables: Schemas > Public > Tables

Drop a Database:
  • Right click on the database name > Delete/Drop > Confirm Delete


PSQL Shell

[4:53 Video timestamp]

Add PSQL to your PATH

  • PATH is an environment variable that tells the shell what folders to look in for executable file names entered from the command line.
  • System paths are stored in /etc/paths or /etc/profile. These can't be edited. We will add the PSQL path to our shell.
  • Every Mac comes with a Unix shell that provides a command line interface. The shell runs in the Terminal app. Macs running macOS 10.15 and later use Zsh by default. Before that, Macs used the Bash shell by default.
    • echo $0 Get the name of the shell your Terminal app is running.
  • If using zsh:
    • Go to your home directory (your username directory) and open the .zshrc file.
      • Note: By default, Mac hides all files starting with a dot. To show them, enter Cmd+Shift+.  
      • If it's not there then create it from the terminal with command touch ~/.zshrc 
  • If using bash:
    • Go to your home directory (your username directory) and open the .bash_profile file.
      • Note: By default, Mac hides all files starting with a dot. To show them, enter Cmd+Shift+.  
      • If it's not there then create it from the terminal with command touch ~/.bash_profile 
  • In the .zshrc or .bash_profile file add export PATH="$PATH:/Library/PostgreSQL/15/bin". 
    • In Unix systems, bin folders hold the binary executable files that run programs.
    • Change the version number if you are not installing version 15.
    • If there are already paths being exported in this file, insert the path separating it from other paths with a colon: 
export PATH="/other/path:/Library/PostgreSQL/15/bin:$PATH" 
  • To access it, either close and reopen the terminal or enter command: 
    • source ~/.zshrc or source ~/.bash_profile
  • echo $PATH Confirm it is included in the path string. 
  • which psql Returns the exact path to the psql executable file.
  • psql --version Returns the psql version number.


Launch the Shell

Launch the PSQL shell from the Terminal: 
  • psql -U postgres prompts for your password. Then opens the shell to the postgres database as the postgres user. 
  • psql -d database_name -U username prompts for your password. Then opens the shell to the specified database as the postgres user. 

Quit the Shell: \q or Ctrl+D


Add yourself as a superuser

  • If the PG database is just for your own use and not on a network with multiple users, you can make yourself a user with a super user role. The advantage is the system user (your mac username) is used for the username if none is provide. So you don't have to enter -U postgres every time you open the shell.
  • In the terminal, open PSQL as the "postgres" default superuser: 
    • psql -U postgres then enter the password when prompted. This will take you into the PSQL shell.
  • Add your Mac OS username as a user, assigning yourself as a superuser:
    • CREATE USER username WITH SUPERUSER PASSWORD 'some password';
    • \du Check that you are added with superuser role.
    • ALTER USER username WITH NOSUPERUSER; Remove superuser role by adding "NO" before the role.
    • ALTER USER username PASSWORD null; 
    • DROP USER username  remove user.
  • \du shows all users and their permissions.
  • \q or Ctrl+D to quit PSQL.
  • psql -d postgres Log into PSQL. Prompts you for the password, then opens the default Postgres database with you as the user.
  • \conninfo should show you as the user and Postgres as the database.


Create or Drop a Database

Create a Database:
  • CREATE DATABASE database_name; 
  • \l List all databases
  • \c database_name Connect to another database.
  • \conninfo Return the current database and user.
  • \d List all tables in the current database

Drop a Database:
  • DROP DATABASE database_name;


Remove Postgres

[10:30 Video timestamp]

  • Double click on the uninstaller app: /library/postgresql/major-version/uninstall-postgresql.app > Select entire application. This will uninstall most of what you need removed. 
  • Delete folders/files:
    • The above does not delete databases which are stored in /library/postgresql/major-version/data. If you don't need those databases, delete the whole library/postgresql folder.
    • /applications/postgresql 
    • /users/username/.pgadmin
    • /users/username/Library/Application Support/PG Admin
    • /users/username/Library/Application Support/PG Admin 4 
  • Remove from Path:
    • Open ~/.zshrc or ~/.bash_profile then delete the path.

Open CheatSheet Back To List Next Tutorial