Setup PostgreSQL in Ubuntu

This is a quick setup guide on how to setup PostgreSQL in Ubuntu 8.04 “Hardy Heron”. This is far from a complete PostgreSQL guide, but It should be enough to get you started.

Install PostgreSQL

Install the postgresql-8.3 package from Synaptic or apt-get:

$ sudo apt-get install postgresql-8.3

You might also want to install the pgadmin3 tool to help you manage your database:

$ sudo apt-get install pgadmin3

Reset the password of the postgres user

The default superuser, called ‘postgres’, does not have a password by default. We need to add one:

$ sudo su postgres -c psql template1
template1=# ALTER USER postgres with PASSWORD 'password';
template1=# \q

Where ‘password’ is your password. After this we need to modify the password of the postgres UNIX user:

$ sudo passwd -d postgres
$ sudo su postgres -c passwd

You will be asked for a new password, enter the same password with the one you specify in the ALTER USER statement above.

Allow local users to access the server

The default PostgreSQL installation in Ubuntu requires that a PostgreSQL user must also be a unix user. I don’t like it this since it makes it hard for us to create a new PostgreSQL user. I want a PostgreSQL user to be different than a UNIX user. To do this, we need to change the configuration in the pg_hba.conf file:

$ sudo gedit /etc/postgresql/8.3/main/pg_hba.conf

Replace out the line (comment it out by adding a ‘#’ in front of it):

local all all ident sameuser

With this line:

local all all md5

After that you will need to restart the server:

$ sudo /etc/init.d/postgresql-8.3 restart

Now you’re all set. All you need to do is to create a new user, and a database and start coding.

Some important commands to remember:

1. Creating a new PostgreSQL user

   $ sudo -u postgres createuser -d -R -P <new username>

2. Create a new database owned by <username>

   $ sudo -u postgres createdb -O <usename> <database name>

3. Executing an SQL file

   $ psql -f <filename> <database name> <username>

4. Removing a database

   $ dropdb -U <username> <database name>

5. Removing a PostgreSQL user

   $ sudo -u postgres dropuser <usernamen>