Installing the postgresql package
You would think that installing PostgreSQL on Ubuntu (actually as I write I’m using Mint 14, but it is still Ubuntu based, so bear with me on that one) is as simple as opening a terminal and typing:
$ sudo apt-get install postgresql
While the above command will install the latest PostgreSQL database server on your machine, you still need to configure some parts for it to become fully functional.
Disclaimer: The steps described below are intended to be used on a local development machine, production servers should be configured with a more tight security setup in mind. Please refer to the PostgreSQL Server Administration Documentation for further information.
Allowing TCP/IP connections
There’s not much use for a database server if nobody can connect to it, is there? And most clients will want to connect via TCP/IP, which is disabled by default. So let’s enable it.
Edit the /etc/postgresql/9.1/main/postgresl.conf file (please replace the 9.1 with the appropriate version). I really like vim, but it is not installed by default on most distros. If you’re not comfortable with it, use gedit (or pluma if you also use Mint wink wink) with gksudo.
$ gksudo gedit /etc/postgresql/9.1/main/postgresl.conf
Locate the line that starts with #listen_addresses – it should look like:
#listen_addresses = 'localhost'
And just remove the leading ‘#’ char, so that it will look like:
listen_addresses = 'localhost'
The file has a lot of comments that help understand what goes on there, but what we’re doing is that this server will accept TCP/IP connections originated on ‘localhost’, that is, the same machine running the PostgreSQL server.
Save the file and leave the editor.
Establishing user permissions
Now that we can connect to the PostgreSQL server, we need a user to do so. Since we’re on a development machine and want to keep it simple, we’re gonna use the already present postgres (yes, I wrote it right, the user does not have the ql suffix) user.
First, let’s connect to the template database (we’re impersonating the postgres user and using psql to connect to the template1 database):
$ sudo -u postgres psql template1
A prompt like this should show up:
psql (9.1.9) Type "help" for help. template1=#
Now we’re gonna create a passwor for the postgres user:
template1=# \password postgres
Type and confirm the password and quit the psql program (\q at prompt).
Now we’re gonna edit the /etc/postgresql/9.1/main/pg_hba.conf file and add the following line:
local all postgres md5
Just go ahead and add it at the bottom of the file, save it and quit the editor.
Now, just one more step:
Restarting the Server
To apply the settings, you should restart the server by typing:
$ sudo service postgresql restart
You should see something like:
* Restarting PostgreSQL 9.1 database server [ OK ]
And you’re good to go!
Testing the connection
Just type (we’re using the psql program with the postgres user. If the -U postgres is ommited, the psql will try to use the current user as the database user, and therefore will fail) :
$ psql -U postgres
You should be prompted for a password – that is the one you’ve typed and confirmed for the postgres user.
If the following error is displayed:
psql: FATAL: Peer authentication failed for user "postgres"
Just add the -W switch to the command:
$ psql -U postgres -W
and you will be prompted for a password. After you type it, you will be presented with the psql prompt, where you can issue commands and queries to your PostgreSQL database.