Friday, April 19, 2013

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.

1 comment:

  1. Instead of using using sudo everytime, one could also switch to postres user, e.g. by "su -l postgres".

    To create the db from scratch the migrations should not be used, but

    rake db:create
    rake db:setup

    Above does not create the production DB, this is done by

    rake db:create:all

    (http://guides.rubyonrails.org/v3.1.1/migrations.html#what-are-schema-files-for)

    ReplyDelete