Postgres Encoding Issues

One of our community members had some trouble setting up our team project on his dev environment (cloud 9). He received some help but that wasn't enough. He brought the issue up on one of our Ruby monday hangouts and I offered to help and get his problem fixed myself.

Unfortunately, cloud9 doesn't appear to offer ssh connections so we had to use a screen sharing program.

We first tried but that didn't work out very well. Apparently it doesn't offer keyboard support or it wasn't obvious how to enable it, the performance wasn't the best either.

Then I suggested Team Viewer, since I have used it before without any major issues.

Now that I had access to his machine an intense troubleshooting session began:

The error was an encoding issue, rails wants to use unicode by default (which is the right thing to do) but the database was created with a different enconding: US-ASCII. This created an incompatibility.

Troubleshooting Time!

I started by looking for the postgres data directory, since I knew I had to delete it to recreate the database with the correct encoding.

By the way, If you are trying to solve a similar issue, be careful! Deleting this directory means you will completely reset your database, so all the data will be gone.

One way to find the data directory is to login as the postgres user.

sudo su postgres

Then if the system is setup correctly, the home directory of this user will contain the data directory.

=> /var/lib/postgresql

Now we just need to use the initdb command to create a new data directory. But we have a small problem, this command is not on our current PATH.

A quick google lookup and we find out that initdb and other postgres utilities live under this directory:


Let's cd into this directory and run initdb. If everything goes well we should be able to bring up our new database using this command:

sudo service postgresql start

More Problems!

At this point we ran into another issue: FATAL: role “ubuntu” does not exist

There are two ways to go about this, for local development using the postgres user (which is created by default) is fine. For production, you will want a dedicated user for better security.

We can setup password-less login of the postgres user via the pg_hba.conf file. We have to add this line, and make sure it's the first one (rules are processed in the order they are encountered).

local  all  postgres  trust

Restart postgres so it picks up the new configuration. Now we can do a quick test to see if our rails app can connect to the database.

I ran this command: rake db.

This will open an interactive connection to the database using the credentials defined under the config/database.yml file. If you are using spring you may have to stop it using spring stop.

Once we have confirmed that we have database access we just need to run rake db:setup and rake db:migrate to setup our application schema.

If you enjoyed this post you can check the author's blog here.