Sunday, July 29, 2012

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.

Monday, July 16, 2012

Summer Freedom Week Day 1

This week will be my last week before I go back to the grind, so we decided to have fun whenever we can. Here are our destinations on the first day of our "Summer Freedom Week!"

1. Granville Island
Granville Island entrance
We  have not been to Granville Island for a while. Our main target here was to have our lunch at "Go Fish".

Unfortunately it wasn't meant to be since they have to go fishing on Monday. So instead of feeling sad and depressed, we took a few pictures since the weather was awesome.
Burrard Bridge at far
Granville Bridge across the other
Must be nice to have a waterfront view apartments, wondering how much a unit may cost your wallet.
Waterfront Apartments
Since we were hungry, we decided to leave Granville Island. Instead of going straight to the parking lot, we went through a few stores for a quick window shopping.
Kids Market
Freaky Eyes on top of Kids Market
Car stickers
We may have buy these stickers soon.

Sometimes Vancouverites amazed us, there are plenty of them enjoying their Monday as if money grows on their backyard. We can only guess what they do for a living.
Woofles & Meowz
Woofles & Meowz? The Z comes from the 90's? Kidding aside, too bad we don't have pets.
Weird Sign
Came across one shop that we have no idea what it sells because we couldn't find the entrance door nor we can see the inside. Who knows eh..?
Yes, I'm the Boss
Boss sleeps throughout the day, leaving us to enjoy to the fullest!

2. SalaThai

We went to SalaThai for our lunch because of the proximity and also ever since one of our favorite Thai restaurants near our home has closed due to a new apartment complex is about to be built there, we haven't had Thai.
A Bowl of Soup (No Name)
Wife ordered Beef Phanaeng Curry that comes with this unknown bowl of soup (a bit sour and slightly spicy).
Beef Phanaeng Curry
While I opted for my usual: Lard Na
Lard Na
The Lard Na was a bit unusual because typically Lard Na isn't that soupy. But it tastes good, so who am I to judge?
Calamari
The calamari was a late order because my stomach was not quite there yet and the Boss demanded his lunch as well: MILK PLEASE! Lucky we're prepared.

Overall the lunch was quite nice and the price was reasonable.
Roaming Dragon Food Truck
Came across one of those "hip" food truck. The last time I tried a food from a truck, it was okay but overpriced. Some of my friends have had similar experience. Speaking of food truck, have you ever watched the Eat St. TV show airing at the Food Network?

3. Vancouver Public Library
Vancouver Public Library
VPL was our last destination before heading for home. We had a few books of our interest that we wanted to borrow.

That's it for our Summer Freedom Week day one adventure. Day two is coming up tomorrow!