Using Postgresql on Linux Mint/ Ubuntu

To install postgresql in Ubuntu or Linux Mint issue the command
$sudo aptitude install postgresql

Apt will download and install the necessary packages for you to use the postgresql server on your computer. However, postgresql will not be started immediately and if you use a command like psql to connect to it, you would probably get an error like:
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

To start the server
$sudo /etc/init.d/posgresql start
However on my installation, I got an unknown command error. A bit of digging showed that there was no object called postgresql in the /etc/init.d directory. A quick ls at /etc/init.d showed that the link was actually postgresql-8.2. So if the previous command to start the server does not work, a bit of digging around would help to find the correct name.

Or of course, you could just restart the damn box. :-)

The server is now installed and started. We now have to create a user who can create a database. Or as postgresql is fond of saying, we need to define a role.
We log into the postgres profile of bash:
$su - postgres

Create a new user with database creation permissions as
$createuser -d -S -R -P graydot

You will be prompted for a password and then to reenter the password.

This command in effect tells postgresql to create a user of name ‘graydot’ with database creation permissions (-d), no super-user permissions (-D) and no permission to add roles (-R). The characters in small case for these option allot permissions and the capitals disable them. You should replace ‘graydot’ with your account user name.

We now check if the user has been created as required. Open the Sql prompt by using the command line tool ‘psql’
$psql postgres

At the prompt type
#select * from pg_shadow;

You should see something like

usename |usesysid | usecreatedb | usesuper | usecatupd |passwd | valuntil | useconfig
postgres| 10 | t | t | t | | |
jsemmanuel | 16387 | t | f | f | md5114afb9a66 | |
(2 rows)

Exit psql by typing q
Logout out of the postgres profile by typing exit once

You are now in your user profile.

We now intend to create a database. The createdb command is used to create a new database in postgresql.
$createdb test_db

A new database of the name test_db is now created with the owner set to the currently logged in user.
Open the SQL prompt with the psql command.
$psql test_db

At the sql prompt, type 'l' to see a list of databases and their owners.

These are the basics of installing postgresql in the simplest way possible, upto the step of creating a database. I will be posting more as time permits. This will go up as a tutorial on my website when I finally get around to bringing it up.

One thought on “Using Postgresql on Linux Mint/ Ubuntu

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>