Install and configure PostgreSQL in Ubuntu 10.04.* LTS

The following process works for Ubuntu 10.04. It should work or at least help a lot for most debian-based systems.

We start by firing up a root shell and installing the PostgresSQL server.

ares:~$sudo bash
[sudo] password for f: 
ares:~#whoami
root
ares:~#apt-get install postgresql


It needs a y to install and start the PostgreSQL server and bind it on port 5432 on localhost

Let 's make sure that it started
ares:~#netstat -putan|grep postgres
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      22705/postgres  
tcp6       0      0 ::1:5432                :::*                    LISTEN      22705/postgres  
udp6       0      0 ::1:48884               ::1:48884               ESTABLISHED 22705/postgres


Cool, now we need to login to our PostgreSQL server to reset the postgres user password using the psql --the PostgreSQL interactive terminal.
ares:~#sudo -u postgres psql postgres
could not change directory to "/root"
psql (8.4.8)
Type "help" for help.

postgres=#


To set the password for the user postgres we use the following command
postgres=# \password postgres
Enter new password: 
Enter it again: 
postgres=#


We can list the databases in our PostgreSQL server using the command \l
postgres=# \l
                                    List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
(3 rows)




We can list the users using the command \du
postgres=# \du
            List of roles
 Role name | Attributes  | Member of 
-----------+-------------+-----------
 postgres  | Superuser   | {}
           : Create role   
           : Create DB   




The key sequence Ctrl,d or the command \q exits psql
postgres=# \q
ares:~#


OK, let's add a user and a database to our freshly installed PostgreSQL server.

Let's add a user named pguser0.
ares:~#sudo -u postgres createuser -P pguser0
could not change directory to "/root"
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
ares:~#


Let's add a database named pgdb0 that belongs to the user pguser0.
ares:~#sudo -u postgres createdb -O pguser0 pgdb0


Let's list all the users and all the databases in our PostgreSQL server.
ares:~#sudo -u postgres psql postgres
could not change directory to "/root"
psql (8.4.8)
Type "help" for help.

postgres=# \du
            List of roles
 Role name | Attributes  | Member of 
-----------+-------------+-----------
 pguser0   |             | {}
 postgres  | Superuser   | {}
           : Create role   
           : Create DB     

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 pgdb0     | pguser0  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
(4 rows)

postgres=# \q
ares:~#


looks good, now lets try to connect to pgdb0 as pguser0.
ares:~#psql pgdb0 -U pguser0 -h localhost
Password for user pguser0: 
psql (8.4.8)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

pgdb0=>
pgdb0=> \q
ares:~#


it works! , let's configure our PostgreSQL server to listen in 192.168.2.250 by adding it to listen_addresses in
/etc/postgresql/8.4/main/postgresql.conf like
ares:~#grep listen_addresses /etc/postgresql/8.4/main/postgresql.conf
listen_addresses = '192.168.2.250,localhost'            # what IP address(es) to listen on;


Now, we need to restart PostgreSQL
ares:~#/etc/init.d/postgresql-8.4 restart
 * Restarting PostgreSQL 8.4 database server
   ...done.


and make sure it listens where we told him to listen
ares:~#netstat -punta|grep postgre
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      23271/postgres  
tcp        0      0 192.168.2.250:5432      0.0.0.0:*               LISTEN      23271/postgres  
tcp6       0      0 ::1:5432                :::*                    LISTEN      23271/postgres  
udp6       0      0 ::1:50521               ::1:50521               ESTABLISHED 23271/postgres  


OK, the PostgreSQL server listens on 192.168.2.250.

Now, let's allow access to our PostgreSQL server from the network 192.168.2.0/24.

For that, we will have to edit /etc/postgresql/8.4/main/pg_hba.conf
We add the line "host all all 192.168.2.0/24 md5" in the
"# TYPE DATABASE USER CIDR-ADDRESS METHOD" section.


The last 11 lines of /etc/postgresql/8.4/main/pg_hba.conf look like
ares:~#tail -11 /etc/postgresql/8.4/main/pg_hba.conf

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
#allow access from 192.168.2.0/24 
host    all         all         192.168.2.0/24        md5
# IPv6 local connections:
host    all         all         ::1/128               md5



OK, let's restart the PostgreSQL server.
ares:~#/etc/init.d/postgresql-8.4 restart
 * Restarting PostgreSQL 8.4 database server
   ...done.


And, attempt to connect to the pgdb0 database from another host in 192.168.2.0/24.
g0:~$psql pgdb0 -U pguser0 -h 192.168.2.250
Password for user pguser0: 
psql (8.4.8)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

pgdb0=>


It works!