Category Archives: PostgreSQL

Install PostgreSQL on Ubuntu

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.

Share Button