GET POSTGRES WORKING ON UBUNTU OR LINUX MINT

Posted by postfuturist on 2011-12-13 23:57:38

Warning: this post may contain profanity. I'm a programmer, I don't like dealing with sysadmin type things.

I love PostgreSQL, the baddest-ass relational database on the planet. For real.

Ugh, but it's super confusing to set up if you are used to just setting up MySQL users with passwords and then connecting to the database with said username and password you set up. Postgres has some odd defaults that make this less than straight forward.

Install PostgreSQL server on your machine:

sudo apt-get install postgresql-9.1

Create a database user, enter "somepass" or whatever you want for the password when it asks (answer no the create roles question):

sudo -u postgres createuser -D -P someuser

Create a database owned by that database user you just created:

sudo -u postgres createdb -O someuser somedb

For "fun" let's try to connect to the database with the "psql" interactive postgres shell:

psql -d somedb -U someuser -W

And the payoff:

Password for user someuser: 
psql: FATAL:  Peer authentication failed for user "someuser"

Oops. Your password didn't work, asshole.

Instead, you need to open up this file /etc/postgresql/9.1/main/pg_hba.conf in a text editor (with root privileges, of course) and change this line:

local all all peer

to this:

local all all md5

Now, restart the postgres server:

sudo service postgresql restart

Now you can connect:

psql -d somedb -U someuser -W

And this is what you get:

Password for user someuser: 
psql (9.1.1)
Type "help" for help.

somedb=>

You're welcome.