PostgreSQL CLI

Enjoy this cheat sheet at its fullest within Dash, the macOS documentation browser.

Connecting to PostgreSQL

sudo -u postgres psql postgres

Change to Postgres user and open psql prompt

psql -d my_database -U my_username -W

Connect to my_database with role my_username and prompt for password. Requires md5 or password authentication.

Working with Databases

\c <database name>

Connect to a database

pg_dump <database name> > <outfile>

Backup a database

CREATE DATABASE demodb1;

Create a database

DROP DATABASE demodb1;

Drop a database

List Information

\l

List databases

\du

List roles

Think "describe users"

\dn

List schemas in a connected DB

\dt

List tables in a connected DB

Think "describe tables"

\df

List functions in a connected DB

Think "describe functions"

\df+

List functions and show info (e.g. functions owner and source) in a connected DB

\d <tablename>

List columns on table

Roles and Privileges

CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD 'password1' CREATEDB;

Create role

ALTER ROLE demorole1 CREATEROLE CREATEDB REPLICATION SUPERUSER;

Alter role

DROP ROLE demorole1;

Drop role

GRANT ALL PRIVILEGES ON DATABASE demodb1 TO demorole1;

Grant privileges