Ubuntu, PostgreSQL, pgAdmin

I've been interested to play with PostgreSQL for a while and it looks like now is the right time to have fun and go mad at it since my new work requires it.

My machine/initial setups is as follows:

1. Ubuntu Server 12.04 64bit running on VirtualBox (love free stuff!)
2. PostgreSQL 9.1 as part of Ubuntu Server installation
3. pgAdmin installed in OSX

I've encountered a few hiccups when trying to connect the pgAdmin to the PostgreSQL server and here are the steps to avoid that in the future.

0. Configuration Files

The locations for the main configuration files specifically for Ubuntu Server is at:

/etc/postgresql/<version>/main 

For example, because at the time of this writing I installed the PostgreSQL 91, the configuration files are located at:

/etc/postgresql/9.1/main

The content of the configuration directory should contain the following files:

enathaniel@ubuntu:/etc/postgresql/9.1/main$ ls -la
total 52
drwxr-xr-x 2 postgres postgres  4096 Jul 29 00:34 .
drwxr-xr-x 3 postgres postgres  4096 Jul 28 23:50 ..
-rw-r--r-- 1 postgres postgres   316 Jul 28 23:50 environment
-rw-r--r-- 1 postgres postgres   143 Jul 28 23:50 pg_ctl.conf
-rw-r----- 1 postgres postgres  4693 Jul 29 00:28 pg_hba.conf
-rw-r----- 1 postgres postgres  1636 Jul 28 23:50 pg_ident.conf
-rw-r--r-- 1 postgres postgres 19250 Jul 29 00:24 postgresql.conf
-rw-r--r-- 1 postgres postgres   378 Jul 28 23:50 start.conf

1. Modify the PostgreSQL configuration file: postgresql.conf

Find the listen_address entry, uncomment if necessary, and modify its value to asterisk (*), which means to bind the PostgreSQL server service (daemon) to any IP Addresses of the server machine:

 listen_address = '*'

The default PostgreSQL installation does not bind itself to any particular IP Address of the server. One can also bind the PostgreSQL server service (daemon) to a specific set of IP Addresses if the server has more than one by using comma between values:

 listen_address = 'localhost, 192.168.0.114' # my server address is 192.168.0.114

2. Modify the PostgreSQL Client Authentication Configuration file: pg_hba.conf

Insert a line to pg_hba.conf file to grant client access to the database.

host    all             all             192.168.0.0/24          md5

In short, the above lines will do the following: grant client, via network connection TCP/IP with or without SSL (host), access to all (first) database for all (second) users from the IP addresses of 192.168.0.0 to 192.168.0.255 and use md5 password encryption.

3. Change password of the postgres user

Access PostgreSQL via command-line "psql" and update the password

enathaniel@ubuntu:/etc/postgresql/9.1/main$ sudo -u postgres psql postgres
psql (9.1.3)
Type "help" for help.

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

The psql command must be run as postgres user (hence the sudo -u postgres) because that is the only user that has an account in the default installation of PostgreSQL.

4. Install adminpack extension

pgAdmin typically would give warning when it detects that the PostgreSQL instance does not have adminpack installed. To install the adminpack extension, the following psql command would do the trick

enathaniel@ubuntu:/etc/postgresql/9.1/main$ sudo -u postgres psql

psql (9.1.3)
Type "help" for help.

postgres=# create extension adminpack
postgres-# \q

5. Connect to PostgreSQL instance from pgAdmin
The following screenshot should give some idea how to connect to the PostgreSQL instance.

Voila, we're done!

PS: Yes, there's no food, fruits, or traveling story in this post.