Installation

OSX Installation

brew install postgres
brew install postgis

To have launchd start postgresql at login:
ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents

To load postgresql immediately:
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

postgres -D /usr/local/var/postgres // -D defines database directory

postgresapp.com installation

Run psql:
/Applications/Postgres.app/Contents/Versions/9.3/bin/psql -h localhost

Debian Installation

Installing non-shipped versions.

Configuration files: /etc/postgresql/[version]/[cluster]/
Binaries: /usr/lib/postgresql/[version]
Data files: /var/lib/postgresql/[version]/[cluster]
Log files: Installing PostgreSQL creates log directory /var/log/postgresql/. Starting the database engine creates log file with name postgresql-[version]-[cluster].log.

 Load Postgres utility pg_ctl

PATH=$PATH:/usr/lib/postgresql/9.4/bin
export PATH
Allow host connections: /etc/postgresql/9.4/main/pg_hba.conf
pg_ctl restart -D /var/lib/postgresql/9.4/main

Usage

Run from Postgres bin:
createdb [name of db] // create db
createuser [username] // create user
dropdb [name of db] // drop db
pg_dump -h [host] -p [port] -U [user] -d [db] -f [location/file.sql] // backup specific database
pg_dumpall -h [host] -U [user] -l [database] -f [path/to/db.dump] // backup all databases and server globals
pg_restore --dbname=mydb --jobs=4 --verbose mydb.backup
pg_default // stores for all user data
pg_global // stores all system data

psql

psql [db] // access database using psql command line tool - postgres database if default
\? // view help options
\h [command] // view help for particular command
\du // show roles
\d // show relations
\dt [shema.*] // show tables in current db
\l // list all dbs
\dx // list database extensions such as pg_crypto (for salted hash passwords) \dn // list schemas

psql -[-U username] [-d database_name] [-h 127.0.0.1] -W // login with a user, provided has login rights