My Profile Photo

rubycoloredglasses


I'm Jason, a web applications developer in the San Francisco Bay area.


PostgreSQL

Back to Cheat Sheets

These commands are specific to Postgres installed on a Mac using Homebrew. I recommend using Lunchy to manage the daemons running on Mac OSX machines.

See also PostgreSQL SELECT Docs

Command Line Commands

# initialize your Postgres database cluster (collection of databases managed by Postgres server)
$ initdb /usr/local/var/postgres -E utf8

# Start Postgres server manually
$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

# Stop Postgres server manually
$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log stop

# Create a user without a corresponding database
$ createuser myusername --no-createdb --no-superuser --no-createrole --pwprompt

# Create a databse with owner specified
$ createdb my_database --owner=myusername

# Drop a database
$ dropdb my_database

# Use PostgreSQL command line client to view default 'postgres' table
$ psql postgres

# Use PostgreSQL command line client to connect as a specific user, connected to specific database
$ psql -U myusername -d my_database

# Run an PostgreSQL command with user specified
$ psql -c 'CREATE DATABASE my_database WITH OWNER myusername ENCODING 'UTF8';' -d canvas_test

# Backup single database to file
$ pg_dump my_database > backup_file_path

# Restore single database from file
$ psql my_database < backup_file_path

# Backup entire database cluster
$ pg_dumpall > full_backup_file_path

# Restore entire database cluster
psql -f full_backup_file_path postgres

PSQL Client Commands

-- get list of non SQL commands
\?

-- execute query every 5 seconds
select id from tablename limit 5; \watch 5

-- list databases
\l

-- connect to database
\c my_database

-- list tables in connected database
\dt

-- list columns on table
\d table_name

-- quit psql client
\q