Setting up PostgreSQL for Ruby on Rails on Linux

So every once upon a time I run into the situation, that I have a newly set up machine and have to configure my system again to use PostgreSQL and play nicely with Ruby on Rails. And I don’t want to have to google the set up every time, hence this post (and of course to help people with similar problems). Why PostgreSQL? Well many people like it and you need it for instance for deploying on heroku and your development environment should be as close to your production environment as possible.

What I do here is a way that works for me on my Linux Mint Debian Testing machines. Be aware that this is my development set up – I don’t use this in production.  I’m no PostgreSQL expert so, this just works for me and I hope that it will for other people as well :-). Suggestions/Improvements are welcome as always.

Let’s get started! So at first we have to install PostgreSQL:


sudo apt-get install postgresql

After we’ve don this we need to create a user in PostgreSQL. So we use the user account of PostgreSQL to create a user with sufficient rights. I just take my own account and grant it sufficient rights with PostgreSQL. Don’t forget to substitute my username with yours!

tobi@speedy ~ $ sudo su postgres
[sudo] password for tobi:
# the behavior of createuser seems to have changed in recent postgresql versions
# So now do the following (-d says allowed to create databases):
postgres@speedy /home/tobi $ createuser tobi -d # replace tobi with your user acc name
# In older versions it used to work like this:
postgres@speedy /home/tobi $ createuser tobi # (substitute with your username)
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n

At this point it would be good to install the pg gem – which is the adapter for the PostgreSQL database. So simply add

gem 'pg'

to your Gemfile. Also make sure to replace existing database gems – like sqlite. Now run:

bundle install

to update your dependencies. That should go smoothly, otherwise you are probably missing dependencies.

Now you still need to modify your config/database.yml. Most of all you need to change the adapter to “postgresql”. Here is the development part of my database.yml for reference:

development:
  adapter: postgresql
  database: ArbitaryDatabaseName
  pool: 5
  timeout: 5000

Make sure that the different environments (development, test and production) have got different databases (meaning the database property should be different). Otherwise they will influence each other and you don’t want that.

In order to finish the set up you need to create all the databases, which should work flawlessly by now:

rake db:create:all

When this is done you should be able to “rake db:migrate” your database as you are used to (and be sure to do so).

I hope this post helped you with setting up PostgreSQL for Ruby on Rails on Linux. If something didn’t work for you, you feel that a step is missing or you just have a useful tip – please feel free to comment!

Advertisements

17 thoughts on “Setting up PostgreSQL for Ruby on Rails on Linux

  1. If you are going to create a postgres user, why not make it an application specific user. That is rails_app or the name of your app. That way you can limit the access from the application, and you can separate your own credentials from those used in the application. Also co-workers don’t need to either know your password, or create their own database.yml

    1. Hi, thanks for the comment!
      Well the thing with this approach is, that you don’t need to put in a user and a password if you use that user to run rails. So I use my “tobi” account for all the development work and it works without putting a password into the database.yml. If everyone sets up their database like this no one has to have a different database.yml or know about any passwords.

      See the database.yml of one of my projects for reference: https://github.com/PragTob/TracketyTrack/blob/master/config/database.yml

      Cheers,
      Tobi

      1. Hey, I’ve found Vagrant+Puppet to be a good way of setting up isolated work environments for different projects. Especially for repetitive tasks like setting up Postgres, you can abstract them into Puppet manifests easily and not deal with them every time. For an example, consider taking a look at my: https://github.com/pmeinhardt/mnml (feedback welcome!).

        1. Thanks for the comment Paul – however I can’t seem to identify the vagrant/puppet part there. My knowledge of these 2 might just be lacking though.

          Plus isn’t vagrant/puppet VM stuff? This is my personal machine, native operating system 🙂

          1. Oops, yeah sorry. Different branch: https://github.com/pmeinhardt/mnml/tree/vagrant.

            Yup, Vagrant is “VM stuff”, see http://vagrantup.com/. You should consider it, if you’re working on a multitude of different projects with different dependencies (and versions thereof). I.e. Vagrant (through VirtualBox) provides you with an encapsulated development environment.

            Puppet is a “configuration management software”, allowing you to set up and administrate machines (like in this case a VM). Helps to create automated, reproducible environments, which is a plus for getting new developers started or if you’re working on a project from a different machine. I.e. you get around the boring task of installing all the dependencies yourself.

            The combination of both may have quite a few advantages depending on the projects that you’re on to.

              1. I don’t need to be convinced Paul 🙂 I know of chef, puppet and vagrant and now that I should take a look at them but right now I spend the time on other stuff like Clojure 😀

                Haven’t found a real need for them by now, but I’m sure there is and will be one (at the latest when I have to start working on some out of date project…) – thanks for the further explanation 🙂

  2. Your instructions didn’t work for me:

    Mac OSX 10.6.8
    PostgreSQL 9.2.4 installed via enterpriseDB installer
    Install dir: /Library/PostgreSQL/9.2

    1) I couldn’t figure out a way to start the server until I discovered pgAdmin3 in the 9.2 dir. I clicked on that program, which launched a gui interface. Then I right clicked on PostgreSQL 9.2(localhost:543), and selected Connect. When prompted, I entered the database superuser password, which I specified during installation. If you haven’t figured it out, the superuser name is postgres.

    2) I had to be in the dir /Library/PostgreSQL/9.2 to sudo su to the super user postgres:

    /Library/PostgreSQL/9.2$ sudo su postgres
    Password:
    bash-3.2$

    3) The createuser command required an option to get the questions:

    bash-3.2$ createuser –interactive 7stud
    Shall the new role be a superuser? (y/n) n
    Shall the new role be allowed to create databases? (y/n) y
    Shall the new role be allowed to create more new roles? (y/n) n
    Password:

    It wasn’t entirely clear from your directions, but I used my username on my mac as the new user name for postgres. I didn’t know what password to enter there, so I entered the database superuser password.

    4) Then I exited:

    bash-3.2$ exit
    exit
    /Library/PostgreSQL/9.2$

    5) Then in pgAdmin3, with Login Roles(1) highlighted, I clicked the “Refresh the selected object” icon, and I could see the new user 7stud.

    6) I added the pg gem to my Gemfile, and did bundle install, bundle update, bundle install, and I changed my database.yml file as shown, but no matter what I put in database.yml, when I run:

    $ bundle exec db:create:all

    I always get the error:

    fe_sendauth: no password supplied

    I’ve read many posts that say you have to change your postgres pg_hba.conf file to alter the access from ‘md5’ to ‘trust’, which requires doing this:

    /Library/PostgreSQL/9.2$ sudo su postgres
    Password:
    bash-3.2$ ls
    bin pgAdmin3.app
    data pg_env.sh
    doc scripts
    include share
    installer stackbuilder.app
    lib uninstall-postgresql.app
    bash-3.2$ cd data
    bash-3.2$ ls
    PG_VERSION pg_ident.conf pg_snapshots pg_xlog
    base pg_log pg_stat_tmp postgresql.conf
    global pg_multixact pg_subtrans postmaster.opts
    pg_clog pg_notify pg_tblspc postmaster.pid
    pg_hba.conf pg_serial pg_twophase
    bash-3.2$ mvim pg_hba.conf

    But I still get that blasted error. I hate PostgreSQL.

  3. A kind soul told me how to fix my problem. After changing the METHOD’s in my pg_hba.conf from ‘md5’ to ‘trust’:

    # TYPE DATABASE USER ADDRESS METHOD

    # “local” is for Unix domain socket connections only
    local all all trust
    # IPv4 local connections:
    host all all 127.0.0.1/32 trust
    # IPv6 local connections:
    host all all ::1/128 trust
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local replication postgres md5
    #host replication postgres 127.0.0.1/32 md5
    #host replication postgres ::1/128 md5

    I attempted to reload the conf file by disconnecting from the server(right clicking it in pgAdmin3 and selecting Disconnect), and then reconnecting. But apparently, to reload the conf file you have to right click on the server and choose “Reload configuration”.

    1. Hi there,

      glad to see that it worked out for you in the end! 🙂

      However, these instructions were just made for Linux. I never tried them out with Mac OSX and I suspect that some things are quite different there 🙂

      Cheers,
      Tobi

  4. Hiya, I Do really like coding with Ruby. Have not been using it a long time
    but discovered so much from net sites around the web. Suffice to say,
    your web site has assisted me a lot, and I
    thank you so much for the crystal clear
    explanations and easy to follow material on this web page.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s