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 :
- Tuning Your PostgreSQL Server from Postgres wiki
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.
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
explain.depesz.com is a tool to help format the output from an 'explain analyze' of a query
postgresguide.com is another great resource to keep handy
My pinboad links tagged 'postgres' are the source material for this blog
ServersForHackers.com provides another similar crash-course guide