Recipe 1.4. 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 http://www.postgresql.org/download, 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 http://www.macports.org. 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