PostgreSQL cheatsheet. Basic stuff, but I always forget it.

postgresql.md

Create a new database cluster.

pg_ctl init -D /usr/local/var/postgres

Start, stop and restart the database.

pg_ctl -D /usr/local/var/postgres -l logfile start
pg_ctl -D /usr/local/var/postgres stop
pg_ctl -D /usr/local/var/postgres restart

Interactive console.

psql -U username [-d databasename]

List all databases.

\list
\l

Create a user, a database and grant privileges.

CREATE USER username [WITH PASSWORD 'hurrdurr'];
CREATE DATABASE dbname;
GRANT ALL PRIVILEGES ON DATABASE dbname TO name;

New database owner.

ALTER DATABASE dbname OWNER TO username;

Give permissions to create databases.

ALTER USER name CREATEDB;

Quit.

\q

Import dump. Directly from Heroku devcenter.

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U username -d dbname latest.dump