PostgreSQL 'permissions error'

Too many times now I've searched high and low to find the cause of my PostgreSQL 'permissions' error. Certainly it didn't make sense...I had created a superuser and a database with that superuser; PostgreSQL was started;

dropdb dbname

would drop the database and

createdb dbname

would create the database. What was wrong?

In each case I had forgotten something so basic most guides seem to leave it out:

initdb

If you're running Ubuntu and installed PostgreSQL through apt-get, you'll need to initialize your database with the commands below as the user you want to own the data. Generally I make a login with the same name as a database superuser to simplify things.

• Note the folder must also be owned by the user who will own the data.


• Note multiple databases can be stored in this datafolder.

/usr/lib/postgresql/8.4/bin/initdb -D /path/to/datafolder

Ubuntu Karmic 9.10 and ruby-pg

PostgreSQL, Ubuntu, and Rails seem exactly like a winning combination for the type of work I do.

As of this writing (March 12, 2010), the best driver to connect Rails and PostgreSQL is easily ruby-pg. My development environment is Snow Leopard, and my troubles installing ruby-pg on OS X were alleviated by this very helpful article at icoretech.

My server environment of choice is Ubuntu. Getting things up and running had me stumbling a bit again with the ruby-pg gem driver. Turns out the solution is simple -- just install the postgresql-server-dev-8.4 package and the gem installs fine.

The following steps will have PostgreSQL 8.4 and the ruby-pg driver up and running on your Ubuntu Karmic system (assuming you've already installed Ruby)

sudo apt-get install postgresql
sudo apt-get install postgresql-server-dev-8.4
sudo gem install pg