Postgres Cheatsheet for Linux and macOS: Login on psql and create a new user role. Also dump and restore.

postgres.sh

# Install postgres first (e.g. OS X):
brew install postgres

# After installation... if user not created:
# This is a wrapper around CREATE ROLE, you might not have it.
# BTW, a role is the same as a user.
# A role is an entity that can own database objects and have database privileges;
# a role can be considered a "user", a "group", or both depending on how it is used.
createuser --superuser postgres  # -s works as well

# Login as the 'postgres` superuser
sudo -u postgres psql  # -u stands for user
# or (prefer this one)
psql -U postgres
# Connect to a database named 'postgres`
psql --dbname postgres

## PSQL Commands
# protip: type \ (backslash) and press tab to see the available commands.

\c \connect <dbname>
\l \list  # DBs
\dt       # tables
\du       # users


# Create a role (you may have to make it the same as the OS's username)
CREATE ROLE simone login createdb;

# Create a superuser and immediately assign a password
CREATE ROLE simone PASSWORD 'xyzzy' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

# Change password for an existing user
ALTER USER davide WITH PASSWORD 'hu8jmn3';

# Dump database (will prompt for password)
pg_dump DBNAME dump.sql

# Dump database parameterized
pg_dump --dbname= --username= --host= --port= --file=dump.sql

# Create a new database
createdb DBNAME

# Drop an existing database
dropdb DBNAME

# Restore database
psql --username=postgres DBNAME -1 -f dump.sql
psql DBNAME < dump.sql ## This may be less reliable

# Dump heroku db and restore locally
See: https://devcenter.heroku.com/articles/heroku-postgres-import-export