Setting up a PostgreSQL locally in Ubuntu 14.04+ (Rails friendly!)

posted in: HOWTO | 0

Fresh Ruby on Rails installations will default with support for SQLite3, which is a very fast database solution right out of the box. As you expand your app, though, you will inevitably encounter problems (most often, for me, it’s concurrency issues while running tests). PostgreSQL is a powerful open-source tool to back your databases — it also happens to be what Heroku uses, so using it locally ensures solid parity if you deploy there!

PostgreSQL can be a little frustrating to set up, as there is slightly more configuration necessary than the “barely more than nothing” required for SQLite3.

Step 1: Install the necessary native Ubuntu packages

There are two PostgreSQL core packages, and then the GUI admin (it just makes things easier, don’t hate). The first is the PostgreSQL database package, itself; the second is the development library necessary for the “pg” gem (more on that later), and the third is the GUI tool.

aaron@yersinia:~$ sudo apt-get install postgresql libpq-dev pgadmin3

It’ll install a bunch of companion packages, just roll with it.

If you are so inclined, search for “pgadmin” in Dash and drag it to your launchbar.

Step 2: Setup your main PostgreSQL user and the GUI tool

There are a few different methods for doing this, but I’m just cargo-culting the method I’ve used a few times that I know works. You may have another way you want to do it. By default, a postgres user is created, but with no registered password. We need to set a password for that user first so that we can connect to the main database node in PGAdmin3.

aaron@yersinia:~$ sudo -i -u postgres
[sudo] password for aaron:
postgres@yersinia:~$ psql
psql (9.4rc1)
Type "help" for help.

postgres=# ALTER USER Postgres WITH PASSWORD 'Myanacondadontwantnone.';
postgres=# q

Open up PgAdmin3 (that GUI tool) by clicking on the “WHO THE HELL UNPLUGGED THE DAMN WEBCAM AGAIN” icon:

It will open a dialog box as seen here. Make yours look like mine.

application screenshot
For “name” you can really call it anything you like. I prefer meaningful names like “localhost”, but there’s really nothing stopping you from using something more daring, like “local_host” or even “THE MOST LOCAL OF HOSTS”.

Call it “Miss Jackson” if you’re nasty.

application screenshot

For “host” you do need to use something valid here: “localhost”, or “” should both work. You might be able to use your hostname as defined in /etc/hosts, but I haven’t tested that. I strongly advise just using “localhost” here.

For “Maintenance DB”, use “postgres” unless you have some really important reason not to, or if you enjoy failure. Same with “Username”.

The “Password” field should be whatever you ALTERed in the psql query earlier — in that example, I used “Myanacondadontwantnone.”, which is totally the password I use for all my internet banking and important accounts (just kidding, I don’t use caps).

Leave the rest of the options as-is, and click “OK”. It should just work, and you should be able to expand the “Server Groups” cluster to see a “localhost” (“Miss Jackson”, if you’re nasty) node.

Step 3: Prep the DB for your Rails app

I like to do this in the console because I find it faster than the time it would take me to learn how to do it in the GUI. Back to the terminal. Let’s pretend that your app is called “FigNewton” — some dumb social media aggregator or whatever. Super important. You’ve got startup VC? Great. Really. I’m impressed. Awesome. Crush some code dude. #fistbump Great, can I get back to this?

So your dumb app is named “FigNewton” and we’re going to create two databases, one for Dev and one for Test, because Rails.

aaron@yersinia:~$ sudo -i -u postgres
Password: Myanacondadontwantnone.
postgres@yersinia:~$ psql
psql (9.4rc1)
Type "help" for help.

postgres=# CREATE DATABASE fignewton_dev;
postgres=# CREATE DATABASE fignewton_test;
postgres=# CREATE USER fignewtondev WITH PASSWORD 'SomeL337Passw0rd';
postgres=# GRANT ALL PRIVILEGES ON DATABASE "fignewton_dev" TO fignewtondev;
postgres=# CREATE USER fignewtontest WITH PASSWORD 'SomeOtherL337Password';
postgres=# GRANT ALL PRIVILEGES ON DATABASE "fignewton_test" TO fignewtontest;
postgres=# q

You should probably not use these passwords, and instead use your own.

If you refresh PGAdmin3’s localhost node, you should see those two databases created. It’s possible that all of this could be done by a rake db:create if you have your database.yml set correctly, but what fun would that be?

Step 4: Convert or configure your Rails app

We have to make two changes to the app: change the gem and the database config.

aaron@yersinia:~/apps/fignewton/$ vi Gemfile

Remove the line:

gem 'sqlite3'

And replace it with:

gem 'pg'
aaron@yersinia:~/apps/fignewton/$ bundle install
# should see lots of green and successful stuff #
aaron@yersinia:~/apps/fignewton/$ vi config/database.yml

And make it look something like this:

default: &default
adapter: postgresql
pool: 5
timeout: 5000
host: localhost

<<: *default
database: fignewton_dev
username: fignewtondev
password: SomeL337Password

<<: *default
database: fignewton_test
username: fignewtontest
password: SomeOtherL337Password

The first block defines some common traits among all the instances. It’s imported into the other blocks. The other blocks use the credentials defined earlier.

That’s it! If you’re changing horses mid-stream with your database, you’ll need to re-run all your migrations before doing anything fun.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.