Friday, March 23, 2012

How to install PostgreSQL in Debian

PostgreSQL is an object-relational database management system.

Server

Installation in Debian is straight forward:
apt-get install postgresql
The installation adds a linux administrative user account postgres. You will need to set password (consider take a look how to generate a strong password here):
passwd postgres
There is also user postgres in database. The passwords for both should be different. Let change securely password for database user postgres (you will need this password to connect to database):
psql01:~# su - postgres
psql01:~$ psql 
psql (9.1.3)
Type "help" for help.

postgres=# \password 
Enter new password: 
Enter it again: 
postgres=# \q

Server Network Access

The installation configures the server to be available for local connections only. If you need this server to be accessible from other computers in your network follow these:
  • Ensure the server connection settings (file/etc/postgresql/9.1/main/postgresql.conf):
    # - Connection Settings -
    listen_addresses = '*'
    
  • Allow incoming network connections (file /etc/postgresql/9.1/main/pg_hba.conf):
    # Allow remote connections to any database, 
    # for any user from 192.168.10.0/24 network.
    host  all   all  192.168.10.0/24     md5
    
  • Restart server so your changes take place:
    /etc/init.d/postgresql restart
    
At this point you should get your PostgreSQL server installation finished.

Client

pgAdmin III is a database design and management application for use with PostgreSQL (graphical tool). Let install it:
apt-get install pgadmin3
Try connect to the server you installed with user postgres and password set for database user.