Postgres Cheatsheet

PostgreSQL is my preference in database. It does everything you expect from a relational database. It has an extension for working with GIS data. It can do JSON structures. It's open source. It's easy enough to get up and running on any reasonable platform, and has all the power for whatever you're building. I've worked with Postgres for everything from a simple website to a cross-platform mobile music streaming app.

Here's the bullet points from that experience ...


install and configure

Postgres is pretty easy to get up and running with on Ubuntu

# install postgres and libraries (12.04, 14.04)
apt-get -y install postgresql postgresql-client libpq-dev

You can also use the builds from the postgres team

# install postgres 9.3 and postgis 2.1
echo "deb http://apt.postgresql.org/pub/repos/apt trusty-pgdg main" >> /etc/apt/sources.list
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | apt-key add -
apt-get -y update
apt-get -y install postgresql-9.3-postgis-2.1 postgresql-contrib

You'll often need to install driver/extension/library for your language of choice

# for php, use apt-get
apt-get -y install php5-pgsql

# for python, use pip
pip install psycopg2

If you're on a Mac, check out postgres.app

Once postgres is installed, it's configured via /etc/postgresql/9.3/main/postgresql.conf

There are many options to configure, but these are some that deserve some attention beyond the defaults :

# ip to listen on, use * for all
listen_address = 'ip address'

# max number of connections to allow, default of 100 is fairly high
max_connections = #connections

# set this to 1/4 of total ram, more than 1/3 degrades performance
shared_buffers = #memory

# set to the amount of memory allocated to shared_buffers plus OS cache available
# typically this is > 50% of the total system ram
effective_cache_size = #memory

# read up on this one and understand it's consequences before increasing it
work_mem = #memory

If you increase shared_buffers, you may encounter an error on linux systems. The solution, to increase SHMMAX maximum size, is documented here.

There's some great additional resources out there for performance tuning :

- Performance Tuning PostgreSQL

- Tuning Your PostgreSQL Server from Postgres wiki

- Understanding Postgres Performance


users and databases

Administration is handled via the 'postgres' linux user account, or other superuser :

# switch to the postgres user
sudo su postgres

# then you can use the psql cli tool as an admin
psql

# specify a database for typical use
psql -d a_database

Postgres provides a few different mechanisms for user authentication

You can configure authentication in /etc/postgresql/9.3/main/pg_hba.conf

David Pashley has a more detailed explanation

You can create new users and databases via SQL :

# create user and database for geocodr app (as postgres user)
psql -c "CREATE USER username WITH PASSWORD 'password' SUPERUSER LOGIN;"
psql -c "CREATE DATABASE datebase WITH OWNER username;"

Postgres also provides some specific use command line tools :

# create user (w/ password prompt)
createuser --password username

# drop user
dropuser username

# create a database (w/ specified owner)
createdb -o owner database

# drop a database
dropdb database

# you can use --help with all of these for full usage

psql command line client

psql is the command line client for Postgres

Working in a terminal is very efficient

# launch psql
psql -h hostname -P port -U username -d database

# basic navigation
\l  : list databases available
\dt  :  show tables in a database
\d (table)  :  show column level info on a table
\du  :  show roles on the database
\di  :  list indexes
\?  :  help with a ton more of these

# making queries
\x (on/off)  :  toggle to display in 'record' mode

# working with sql in external files
\i (filepath)  :  run sql from external file
\o (filepath)  :  output query results to external file

You can use input redirection all sorts of ways with psql directly from the terminal :

# execute SQL from a file
psql -d somedatabase < /path/to/query.sql

# output query results to a file
psql -d somedatabase -t -A -F"," -c 'select column_a, column_b from a_table' > /path/to/query/results.txt

# or open the query results directly in vim
psql -d somedatabase -c 'select column_a, column_b from a_table' | vim -

Craig Kerstiens has an excellent guide for using vim w/ psql


some more tips

PGAdmin is the official Postgres GUI. It's available for Mac, Windows, and Linux.

Postico is a paid option, and pSequel is an open source option. Both are mac only.

As an admin, you can get info about what queries/connections the server is working on :

# display current transactions
SELECT * FROM pg_stat_activity;

# to stop a query ...
SELECT pg_cancel_backend(procpid);

# or kill a database connection ...
SELECT pg_terminate_backend(procpid);

# get the 'procpid' from the pg_stat_activity query

PG_Monz is a great set of templates for monitoring with Zabbix

explain.depesz.com is a tool to help format the output from an 'explain analyze' of a query


additional resources

There's two books : PostGIS in Action and Postgres High Performance

postgresguide.com is another great resource to keep handy

The Postgres Wiki and StackOverflow are great resources

My pinboad links tagged 'postgres' are the source material for this blog

ServersForHackers.com provides another similar crash-course guide

published on 2014-12-21
updated on 2015-06-30