Setting up PostgreSQL on Ubuntu

I'm continuing this linux tools / installation series because in recent days I've found it to be very helpful to look at whenever I get tripped up, and also because it's an area that is a little harder to just google and be sure the result will work. I guess you can't be 100% sure this will work either, but hopefully, at the very least, you'll know a little more about the tools you're trying to use.

Anyway, this tutorial assumes you've got a terminal prompt on a Ubuntu linux machine. I'm using 12.04, but these commands should work going back to 10.04 at least. Also, we're going to be working with Postgresql 9.1, and the package isn't available--at least not for 10.04--in apt. We're going to grab it from Martin Pitt's Personal Package Archives (ppa). Anyway, let's begin.

To get access to Martin's ppa, we have to get python-software-properties:

sudo apt-get install python-software-properties

Now we can add his repository

sudo apt-add-repository ppa:pitti/postgresql

And update our package manager

sudo apt-get update

Great. Now we can grab postgresql 9.1:

sudo apt-get install postgresql-9.1

Your probably also going to need libpq-dev, which will help your programs (e.g. a rails app) talk to postgresql.

sudo apt-get install libpq-dev

Awesome. Now we should have a working installation of postgresql 9.1. Also, postgresql will automatically start on install, so for now we need not worry about starting it. Postgresql ships with a user 'postgres' and a database 'postgres', so we can log in to the interactive shell by running the psql command as the postgres user. To log in to the interactive shell, run:

sudo -u postgres psql postgres

From there you can type SQL to accomplish mostly whatever you need, but there are super convenient ways to add databases and users from the command line too.

To explore those, let's ^D (control + d) out of the shell, so we're back at the bash prompt. To add a database, we can use some of the nifty postgresql command line utilities, createdb and createuser, that come bundled with postgresql. man createdb and man createuser should tell you all you need to know, but let's go through the motions as well in a typical example.

Say I want to create a database called 'my_cool_db' that is owned by the user 'zack'. I want zack to be able to use and communicate with my_cool_db, but I don't want him to be able to create databases nor do I want to let him create roles himself. I also don't want him to be a superuser.

### Note: Everywhere I use 'zack', you should substitute the user on your computer that you are using

First we need to define a role 'zack' (important note worth reiterating: pick a user that already exits on your system).

sudo -u postgres createuser zack -DSR

(note: the options -D -S and -R restrict the privileges we just discussed, -d, -s, and -r grant them, and the terminal will prompt you to decide if you omit them.)

Great. Now we can say sudo -u zack psql postgres and we'll be logged in as long as the user 'zack' exists on our system. Now we want to create a database for our new user to use, so let's use createdb.

sudo -u postgres createdb my_cool_db -O zack

Awesome! We've got a new database for our user to use. Now we can login with

sudo -u zack psql my_cool_db

Or just..

psql my_cool_db

(if you're already logged in as the owner of my_cool_db).

Conclusion

I'm going to stop there and maybe add more later. I'm not a PostgreSQL expert at all, so there may very well be other, better ways to do what I've done here and much more. Feel free to mess around and try out what you like. A great way to experiment is by using vagrant, which allows you to set up virtual machines on your own computer, and ssh into them. I'll do a post on setting up and working with vagrant sometime soon.