PostgreSQL 8.3 and PostGIS 1.3.5 on Ubuntu 9.10 (Karmic Koala)


Install PostgreSQL

sudo apt-get install postgresql-8.3

Install PostGis

sudo apt-get install postgis

Install the graphical administration tool pgadmin (optional)

sudo apt-get install pgadmin3

Basic setup

The default user "postgres" has been without a password. The following steps will reset the password:
sudo su postgres -c psql
=# ALTER USER postgres with PASSWORD 'new password'
=# \q

Also change the password of the Unix user "postgres"
sudo passwd -d postgres
sudo su postgres -c passwd

To allow local users access to the server without the need of having the same unix user name, the following changes to the configuration file need to be made:
sudo gedit /etc/postgresql/8.3/main/pg_hba.conf
# local all all ident sameuser
local all all md5
sudo /etc/init.d/postgresql-8.3 restart

Basic Usage

Create a user with a password
sudo -u postgres createuser -P user_name

Create a database with owner O. If -O is not specified the user creating the DB is the default owner
sudo -u postgres createdb -O user_name db_name

When creating the database as a "regular" user with the user as the owner, the following is sufficient
createdb db_name

Delete a database
dropdb -U user_name db_name

Delete a user
sudo -u postgres dropuser user_name

Log in as a user to a database. If the -U option is not specified, the user name is assumed to be the same as the current unix user.
psql -U user_name -d db_name