Postgres command cheatsheet

http://blog.jasonmeridth.com/posts/postgresql-command-line-cheat-sheet/

change to postgres user and open psql prompt
$ sudo -u postgres psql postgres

Once the postgres database is running the prompt will look like this: postgres=#
(So don't type in postgres=#, only what follows in the following commands...)

list databases
postgres=# \l

list roles
postgres=# \du

create role
postgres=# CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD 'password1' CREATEDB;

create role with multiple privileges
postgres=# CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD
postgres=# 'password1' CREATEDB CREATEROLE REPLICATION SUPERUSER;

alter role
postgres=# ALTER ROLE demorole1 CREATEROLE CREATEDB REPLICATION SUPERUSER;

drop role
postgres=# DROP ROLE demorole1;

create database
postgres=# CREATE DATABASE demodb1 WITH OWNER demorole1 ENCODING 'UTF8';

grant privileges to new user
postgres=# GRANT ALL PRIVILEGES ON DATABASE demodb1 TO demorole1;

drop database
postgres=# DROP DATABASE demodb1;

connect to database
postgres=# \c <databasename>

list tables in connected database
postgres=# \dt

list columns on table
postgres=# \d <tablename>

backup database

$ pg_dump <databasename> > <outfile>