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.


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

rsync to an exFAT partition in Linux

I do most of my work on Linux machines, but I have a Mac at home, I love the build quality and the fact that, after all, it is still *nix.

Yesterday I tried to copy a large file (8.8GB) to my flash drive, but as it was FAT32 formatted, it failed because of the file size cap (4GB) on such partitions. So I rsync’d the contents of the flash drive to a temp dir on my Linux machine and partitioned the flash drive as exFAT (it is native from OSX Snow Leopard and later, and if it is not already on your Linux box, try installing the exfat-fuse and exfat-utils packages on a recent Debian based Distro).

The problem is when I tried to rsync the contents of the temp directory back to the flash drive, I got a lot of errors such as:

rsync: chgrp "util/iphone/" failed: Function not implemented (38)

I googled for answers, found some, but nothing that would solve my problem, until some pages deep, after looking inside a lot of posts on a page I found the solution.

It happens that I automatically use rsync as follows:

rsync -av [SRC] [DESTINATION]

I always use the av switch, that states for:

v: increases verbosity, shows the files being synchronized
a: archive, replaces the rlptgoD switches (recurse dirs, preserve symlinks, preserve permissions, preserves modification times, preserve groups, preserve owner and preserve Device files).

The problem is that the Linux exFAT does not cope well with the switches that relate to permissions (the pgo), so the solution is to run rsync with the following switches, removing the p, g and o:

rsync -rltDv [SRC] [DESTINATION]

Of course, rembering the switch is much easier than remembering rltD, but a quick rsync –help will help you remind that.

Share Button