Postgres

Version

postgres -V

OSX

# Install:
brew install postgresql
# Start
brew services start postgresql
# Logs
less /usr/local/var/log/postgres.log
# Postmaster.pid
cat /usr/local/var/postgres/postmaster.pid

# Cleanup after crash
brew services stop postgresql
rm /usr/local/var/postgres/postmaster.pid
brew services restart postgresql

Special commands

Command	Description
\timing	Turn on query timing
\x	Turn on transposed results table (for large schemas)
\q	Exit psql
\d+	List tables
\d+ table_name	Describe table
\l+	List databases
\c db_name	Connect to database
\?	List psql commands

Useful diagnostics

See more here: https://gist.github.com/anvk/475c22cbca1edc5ce94546c871460fdd

List nonzero table/index sizes:

select relname, pg_size_pretty(pg_total_relation_size(relname::regclass)) as full_size, pg_size_pretty(pg_relation_size(relname::regclass)) as table_size, pg_size_pretty(pg_total_relation_size(relname::regclass) - pg_relation_size(relname::regclass)) as index_size from pg_stat_user_tables order by pg_total_relation_size(relname::regclass) desc limit 100;

List database sizes:

select datname, pg_size_pretty(pg_database_size(datname)) from pg_database order by pg_database_size(datname);

List open connections:

SELECT * FROM pg_stat_activity WHERE datname = 'dbname';