My machine/initial setups is as follows:
- Ubuntu Server 12.04 64bit running on VirtualBox (love free stuff!)
- PostgreSQL 9.1 as part of Ubuntu Server installation
- 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:
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
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:
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:
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.
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 md5In 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=# \qThe 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.
