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.'; ALTER ROLE postgres=# q postgres@yersinia:~$
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.
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.
For “host” you do need to use something valid here: “localhost”, or “127.0.0.1” 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; CREATE DATABASE postgres=# CREATE DATABASE fignewton_test; CREATE DATABASE postgres=# CREATE USER fignewtondev WITH PASSWORD 'SomeL337Passw0rd'; CREATE ROLE postgres=# GRANT ALL PRIVILEGES ON DATABASE "fignewton_dev" TO fignewtondev; GRANT postgres=# CREATE USER fignewtontest WITH PASSWORD 'SomeOtherL337Password'; CREATE ROLE postgres=# GRANT ALL PRIVILEGES ON DATABASE "fignewton_test" TO fignewtontest; GRANT postgres=# q postgres@yersinia:~$
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:
And replace it with:
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 development: <<: *default database: fignewton_dev username: fignewtondev password: SomeL337Password test: <<: *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.