PostgreSQL is a powerful object-relational database management system, provided under a flexible BSD-style license. PostgreSQL contains many advanced features, is very fast and standards compliant. It supports a large part of the SQL standard and is designed to be extensible by users in many aspects.
Some of the features are: ACID transactions, foreign keys, views, sequences, sub queries, triggers, user-defined types and functions, outer joins, multi version concurrency control. Graphical user interfaces and bindings for many programming languages are available as well.
Installing The Database Server
To install PostgreSQL 8.2 you may use the command line and type:
sudo apt-get install postgresql-8.2
GUI for PostgreSQL
To install pgAdmin III, a handy GUI for PostgreSQL, you may use the command line and type:
sudo apt-get install pgadmin3
Basic Server Setup
To start off, we need to change the PostgreSQL postgres user password, we will not be able to access the server otherwise. As the “postgres” Linux user, we will execute the psql command, in a terminal type:
sudo -u postgres psql template1
Then at the new prompt, type these two commands, replacing secret with the new password (up to you ;))
ALTER USER postgres WITH PASSWORD 'secret';
To create the first database, which we will call “mydatabase”, simply type :
sudo -u postgres createdb mydatabase
Managing The Server
Change Authentication Method
We need to edit file pg_hba.conf to change authentification method for accessing PostgreSQL database.
sudo vim /etc/postgresql/8.2/main/pg_hba.conf
host all all 127.0.0.1/32 password
hostname all all 192.168.1.52 password
we have to edit the following line in /etc/postgresql/8.2/main/postgresql.conf
- - Connection Settings -
listen_addresses = '192.168.1.52' # what IP address(es) to listen on;
Create a Database
To create a database with a user that have full rights on the database, use the following command:
sudo -u postgres createuser -D -A -P mynewuser
sudo -u postgres createdb -O mynewuser mydatabase
To Access a Remotehost Database
sudo psql -U username -d databasename -h hostname
psql -U dhanya -d mydatabase -h 192.168.1.52
That’s it, now all you have to do is restart the server and all should be working!
sudo /etc/init.d/postgresql-8.2 restart