Installing PostgreSQL

Problem

You want to install a PostgreSQL database server to be accessed by your Rails applications.

Solution

Windows

If you're a Windows user, download the latest version from , and unpack the ZIP archive. Inside, you'll find a directory containing the PostgreSQL Windows installer (the filename extension is .msi). Launch the installation wizard by double-clicking on this file.

The installation options allow you to include several database tools and interfaces. Make sure that the psql tool (the command-line user interface) is included; if you prefer a GUI administration tool, also include pgAdmin III.

Linux

To install PostgreSQL on a Debian GNU/Linux system, point your sources.list file to the Debian archive locations you'd like to use. Then run apt-get update to resynchronize the package index files from the repository sources.

$ cat /etc/apt/sources.list
deb http://archive.progeny.com/debian/ etch main deb-src http://archive.progeny.com/debian/ etch main deb http://security.debian.org/ etch/updates main deb-src http://security.debian.org/ etch/updates main

$ sudo apt-get update

Install the PostgreSQL Debian GNU/Linux package (postgresql-8.1 as of this writing) and development package. These packages include dependent packages for the PostgreSQL client and common libraries as well as header files necessary for compilation of the Ruby PostgreSQL driver.

$ sudo apt-get install postgresql-8.1 postgresql-dev

Now, su to the postgres user, and connect to the server with the client program psql:

$ sudo su postgres
$ psql
Welcome to psql 8.1.0, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
 \h for help with SQL commands
 \? for help with psql commands
 \g or terminate with semicolon to execute query
 \q to quit postgres=# \l
 List of databases
 Name | Owner | Encoding 
-----------+----------+-----------
 postgres | postgres | SQL_ASCII
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
(3 rows)
postgres=# 

Mac OS X

The simplest way to install PostgreSQL on the Mac is to use MacPorts. If you don't already have MacPorts, you can get it from . But first, make sure you've installed Apple's XCode Tools, X11, and X11SDK, which are located on your Mac OS X installation disk. Once you have MacPorts, simply install PostgreSQL with the following command:

$ sudo port install postgresql8

Discussion

PostgreSQL is a popular open source object-relational database that's been in active development for more than 15 years. It is an extremely capable alternative to MySQL and commercially available databases such as Oracle. A notable feature of PostgreSQL is its support of user-defined functions and triggers. User-defined functions can be written in a number of scripting languages, including PL/Ruby.

To use PostgreSQL with Rails you'll need to install the Postgres driver:

$ gem install postgres

Next, you'll need to specify postgresql in your database.yml file:

development:
 adapter: postgresql
 database: products_dev
 host: localhost
 username: some_user
 password: some_password

See Also