PostgreSQL is an open source object-relational database system. It has been under active development since 1996, runs on all major operating systems, with support for foreign keys, joins, views, triggers, and stored procedures. PostgreSQL is also ACID compliant.
PostgreSQL is notably well-supported on the Heroku Platform, and available as a beta product under Amazon’s RDS product. For local development on your own server, follow the directions below.
Like MySQL (but unlike SQLite) PostgreSQL runs as a separate system process. It must be started before use, and can be connected to via the local IP address 127.0.0.1.
Postgres.app is the easiest way to install a local version of PostgreSQL on the Mac.
Just download, drag to the applications folder, and double-click.
As a perk, Postgres.app ships with the excellent PostGIS spatial extension and complete GDAL and OGR utilities.
To install PostgreSQL on Ubuntu Linux, follow Ubuntu’s installation instructions:
To install use the command line and type:
$ sudo apt-get install postgresql postgresql-contribThis will install the latest version available in your Ubuntu release and the commonly used add-ons for it.
To use PostgreSQL with any Ruby app that uses the pg gem, you will also need to install libpq-dev:
$ apt-get install libpq-dev
With PostgreSQL installed, you may want to create a new database for your project, and a user who can connect to that database. PostgreSQL provides the commands createuser and createdb for this purpose; psql is the command line client. To create a user named “janedoe” who owns the database “doedb” and connect as that user, follow the instructions below for your OS.
The new database will be available to your application. If your application requires a database URL, it will look something like postgres://janedoe:[email protected]/doedb.
With Postgres.app, these commands will be located in a directory named /Applications/Postgres.app/Contents/Versions/9.3/bin (or similar, depending on the version number).
You can connect to PostgreSQL easily using the Postgres.app elephant menu:
Then use create user and create database to generate the user:
# CREATE USER janedoe PASSWORD 'pass';
# CREATE DATABASE doedb OWNER=janedoe;
On Linux, the commands will be available globally, but the creation of the new user will need to be done by postgres, the owner the PostgreSQL installation:
$ sudo -u postgres createuser -P janedoe
$ sudo -u postgres createdb -O janedoe doedb
$ psql -h 127.0.0.1 -U janedoe doedb
To use PostgreSQL with a Ruby on Rails app on a development machine, it's recommended to create a database user (without a password) whose name matches your OS username, and to set the authentication method to trust in pg_hba.conf. Refer to this gist for more details.
Are you going to need a local version of PostgreSQL? If you are using an object-relational mapper such as SQLAlchemy, it will do its best to hide database-specific implentation details from your application. It’s likely that you may be able to run a development build of your application using SQLite, which requires no installation or separate process and comes pre-installed with an increasing number of programming languages.