How to run Rails with PostgreSQL on openSUSE 12.3

Ruby on Rails uses SQLite as backend database by default. Using PostgreSQL instead is not difficult, but it needs a little bit of setup and configuration to get the database up and running and connect the Rails app to it. What you need to do slightly varies with the platform you are on. Here is how you do it for a development environment on openSUSE 12.3. Running it as a production database is a different story and needs quite some more considerations to address questions of performance, scalability, backup, availability, etc. But for a development environment using PostgreSQL with Rails is quite simple.

First of all you need to install PostgreSQL:

sudo zypper install postgresql92-server postgresql92-devel

This installs the latest available version. The devel package is needed to build the Rails drivers.

Then start the database server:

sudo /usr/sbin/rcpostgresql start

If you want to automatically start the server on boot enable the service with:

sudo chkconfig -a postgresql

To finalize the setup of the database server you need to create a user, which Rails uses to access the database. You can choose whatever username you like, but the setup is most simple, if you use the username of the user under which you do your Rails development. The -d option allows the user to create databases, so the corresponding rake tasks can do their job.

sudo sudo -u postgres createuser -d USERNAME

You can check the results and later the content of the database directly in PostgreSQL by using the PostgreSQL console. You can start it with the following command. As with the createuser command, via sudo you impersonate the postgre user as root, and then are allowed to connect locally to the database server.

sudo sudo -u postgres psql

By querying the users table you can check the existing users and their rights on the PostgreSQL console:

SELECT * FROM pg_user;

To make Rails talk to PostgreSQL instead of the default SQLite backend, you just need to exchange the database driver by adding the pg gem to the Gemfile and removing the sqlite3 gem. Then run bundler to install the gems:

bundle19 install

To connect Rails to the database, you need to create the database configuration in Rails. Create a file config/database.yml with something like the following content. You need to replace USERNAME by the name of the database user you create in one of the previous steps, and APPNAME by the name of the Rails application. You are free to use whatever name you want there, but usually using something like the appname makes it easier to keep an overview.

development:

    adapter: postgresql

    database: APPNAME_development

    username: USERNAME

test:

    adapter: postgresql

    database: APPNAME_test

    username: USERNAME

production:

    adapter: postgresql

    database: APPNAME_production

    username: USERNAME

Then finally you need to create the databases with:

rake db:create

and create the database tables by running the migrations of your Rails app:

rake db:migrate

That's all. Now your Rails app runs on PostgreSQL, and you can do all your development and testing as usual.