This took a little while to figure out, and existing documentation wasn’t super helpful for me, so here are some notes.
First off, you create new postgres users with the createuser command, but every time I ran it I was getting an error like this:
createuser: could not connect to database postgres: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
What got me over that hump was installing postgresql-8.3 instead of 8.4, which is the default you get on Ubuntu when you install the postgres package. This is what I wanted anyway, since Heroku (my host) uses 8.3:
sudo /etc/init.d/postgresql-8.4 stop sudo apt-get remove postgresql sudo apt-get install postgresql-8.3
If you want to check if your postgresql server is running, run ps ax | grep post. You should get something like this:
4417 ? S 0:00 /usr/lib/postgresql/8.3/bin/postgres -D /var/lib/postgresql/8.3/main -c config_file=/etc/postgresql/8.3/main/postgresql.conf 4420 ? Ss 0:00 postgres: writer process 4421 ? Ss 0:00 postgres: wal writer process 4422 ? Ss 0:00 postgres: autovacuum launcher process 4423 ? Ss 0:00 postgres: stats collector process 4482 pts/4 D+ 0:00 grep post
Now you can create your user:
sudo su postgres createuser -A -d --pwprompt the_username
And then you should be able to create your databases with rake db:create.
If you are getting errors like this:
rake db:create (in /path/to/your/app) FATAL: Ident authentication failed for user "your_username" /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/postgresql_adapter.rb:968:in `initialize' /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/postgresql_adapter.rb:968:in `connect' ...
… it is because Postgres expects there will be a UNIX user that corresponds to each postgres user. When it talks about “Ident” it’s talking about the UNIX authentication system. You could just create a UNIX user with the same credentials as your postgres user, but I took a different route.
As suggested by Felix Sun, I edited my pg_hba.conf (which on Ubuntu is in /etc/postgresql/8.3/main). I didn’t do exactly what he did though, I just added another line as follows:
local all postgres ident sameuser local all all md5
As far as I understand, that just means that any user connecting locally on any database is OK as long as they authenticate with a password.
I restarted postgres (/etc/init.d/postgresql-8.3 restart) and ran my rake db:create; rake db:migrate and I was in business.