Java Hair

I recently downloaded a VM of Ubuntu 8.10 Server.  Though I have Ubuntu 8.10 installed on my desktop at the office, I needed to be able to do some work with PostgreSQL from home and I did not want the overhead of a VPN connection or to install PostgreSQL on my already overburdened laptop.  I figured a server edition of Ubuntu would solve my problems, and sure enough, it did.

Most of what I wanted came from this post on Hokusposkus:http://hocuspokus.net/2007/11/05/install-postgresql-on-ubuntu-710/.

I am perfectly comfortable using the Synaptic Package Manger when a desktop is available, but the server does not have an X-server installed and so I needed to do it all via command line.

The first thing I needed to do was to update the available packages in apt-get:

sudo apt-get update

Next I installed PostgreSQL server version 8.3:

sudo apt-get install postgresql-8.3 postgresql-contrib

Next su to postgres user:

sudo su postgres

Install the support functions for pgAdmin3:

plsql < /usr/share/postgresql/8.3/contrib/adminpack.sql

Start psql

psql

Change the postgres account’s password:

ALTER USER postgres WITH PASSWORD '[password]';

Create a new user for the database:

CREATE USER [username] WITH PASSWORD '[password]';

Create a new database and grant acess to the new user:

 CREATE DATABASE [dbname] OWNER [username];

So this is great, but it still will not allow me to connect from a remote machine. So we need to do two things.

First edit the postgresql.conf file to allow remote connections:

sudo vi /etc/postgresql/8.3/main/postgresql.conf

Uncomment the line #listen_address = ‘localhost’ and change ‘localhost’ to ‘*’.
Also change the password_encryption to off

This will allow you to connect to the service, but you won’t be authenticated. For that you need to modify the pg_hba.conf file like so:

sudo vi /etc/postgresql/8.3/main/pg_hba.conf

add a line like so:

host all all 192.168.1.0/24 md5

to allow all users from the 192.168.1 subdomain access to the database.

Finally, restart the server:

sudo /etc/init.d/postgresql-8.3 restart

and voila! you can now connect from an external machine, like the machine you are hosting the VM from. If you are using a VM be sure to put in the ip address for the VM’s ethernet connection and not the regular ipaddress of the machine hosting the VM.

You can now download pgAdmin3 for whatever operating system is hosting your postgresql VM and access the database installed there. You can find pgAdmin3 here: http://www.pgadmin.org/.

Related posts:

1. Installing JDeveloper 11g on Ubuntu 8.10 I recently got a little annoyed at Vista and upgraded...
2. Favorite Useful(and Mostly Free) Software for Ubuntu and Windows Everytime I install an OS on a new machine there...
3. ADF 11g as a Platform for Client/Server Conversions I have been chosen as a speaker at Oracle’s ODTUG...
4. Webinar: Client/Server Oracle Forms Modernization with Oracle ADF 11g You might thinnk I could have come up with a...
5. Why VirtualBox rocks! First, let me tell you why VirtualBox doesn’t rock, it’s...

You must be logged in to post a comment.