Pictorial: Configuring VirtualAppliance.net's PostgreSQL Server

By David Medinets, http://www.codebits.com
This article shows how to install, configure, and use a PostgreSQL virtual appliance with plenty of pictures to walk you through the process. You'll download the VMWare Server, download the Virtual Appliance PostgreSQL appliance, configure the appliance, and create a database table using phpPgAdmin.

I've been hearing about virtual appliances for quite some time now and their use has always held a particular fascination for me. However, it wasn't recently that I had the time to start using them in a serious way. As a software developer, my first thoughts were how the appliances could help me become more productive or give me an ability that I hadn't had before.

Database software seems particularly invasive to desktop systems. They have daemons running all the time, eating memory and CPU cycles. While it's certainly possible to manually start and stop them, there are no standard methods for doing so, and sometimes more than one service needs to be managed. This cognitive overhead is annoying especially if you want to deal with more than a few databases (say PostgreSQL, MySQL and Derby) on a regular basis as you might if you wanted to test an integration service offering.

I envision using virtual appliances to reduce the complexity invovled in switching databases. The virtual server management software provides a standardized way to start and stop databases. This article focuses on the PostgreSQL virtual appliance, but there are others.

Getting VMWare Server

We'll use VMWare Server to handle our virtual appliances in this article. There are other technologies like QEMU and XEN but VMWare is free, professionally maintained and easy to work with. Download the VMWare server from http://www.vmware.com/download/server/.

Figure 1: Download VMWare server from this page

After installing and starting VMWare Server, you'll see a window like this:

Figure 2: VMWare Server Console

Using the Host->Settings menu option to display the Host Settings dialog box:

Figure 3: VMWare Host Settings Dialog Box

Set the default location for virtual machines to where you'd like. Make sure that you have plenty of hard disk space available - at least 6GB.

Getting the Database Virtual Appliances

When dealing with a database service (as opposed to a service), management is much easy. The server is either off or it's on. There are no individual services to consider and all databases are started the same way -- hit the power button. With this thought in mind, I started looking for database appliances. I found four that I wanted to explore:

I'd like to note how different the Virtual Appliance appliances are. The MySql and PostgreSQL appliances were 24 and 15 megabytes to download. They are specialized appliances - designed to perform just one function. The Derby appliance is a 1.2Gb download while the Firebird appliance is over .5Gb. I vastly prefer the specialized appliances. If I need to create a virtual network of four or five appliances to test clustering or failover, I want each appliances to be as slim as possible. So far, I've been impressed by the competence and professionalism of the people running VirtualAppliances.net. Given that background, I decided to use the PostgreSQL appliance for this pictorial.

Creating the Appliance Inventory

Open the VMWare Server Console, then select File->Open to see the Open Virtual Machine dialog box.

Figure 4: Open Virtual Machine Dialog Box

Now select the Browse open and navigate to the location of the database appliances. Select the VMX file and click the Open button.

Figure 5: Browse for a Virtual Machine Dialog Box

Your VMWare Server Console should look something like this:

Figure 6: VMWare Server Console with PostgreSQL Database Appliance

Add the rest of the database appliances to your console's inventory.

Configuring the Database Appliance

Click on Virtual Appliances PostgreSQL appliance in the inventory pane to see the PostgreSQL tab. Then click on the 'Start this virtual machine' link.

When the appliance completes the boot cycle, you should see a screen like this:

Figure 7: PostgreSQL Appliance After Booting

It's important to notice that IP address of the appliance and the URL of the appliance's admin console; especially note that the appliance using HTTPS for secure connections.

Configuring the Appliance Console

Using a web browser connect to the admin console using the URL displayed as the last bootup message.In figure 7, it's https://192.168.1.103:8000/. The communications between your web browser and the appliance is encrypted using an SSL certificate. However, since Virtual Appliances created and issued the certificate your web browser doesn't recognize it as being valid. We're only using a localhost connection so this isn't an issue for us. Select the 'Accept this certificate temporarily' option and click Ok.

Figure 8: Select the 'Accept this certificate temporarily' option and click Ok.

You'll also get an error message about mismatched domain names.

Figure 9: Click Ok to Approve the Mismatched Domain Names

Use 'admin' as your username and 'admin' as your password in order to log into the admin console.

Figure 10: PostgreSQL Admin Console

Once you've logged in successfully, you'll see a message that the database is running.

Figure 11: PostgreSQL Is Running

The next step is to change the admin password. Select the 'Configuration' menu option, then the 'Configure Authenticaiton' link. Enter your new password. Now select the 'Configure PostgreSQL & PhpPgAdmin' link and click the 'Sync PostgreSQL admin password' button. This action will ensure that your console password is the same as your PostgreSQL admin password.

Using phpPgAdmin

Creating a Database

We're nearly ready to write our Hibernate program. The last configuration step is to use phpPgAdmin in order to verify the database engine is running. Click on the 'here' link immediately under the PostgreSQL server is running message. After selecting the display langauge, you'll see a screen like this:

Figure 12: The Initial phpPgAdmin Screen

Click on the PostgreSQL link in the left navigation pane, then enter 'admin' as the username and whatever password you user earlier ('admin' is the default if you haven't changed it.)

Figure 13: Looking At An Empty Database

Click the 'Create Database' link and enter 'SuperSimple' as the database name. After clicking the 'Create' button your phpPgAdmin page should look like this:

Figure 14: The SuperSimple Database Now Exists

Creating a User

While we could move onto the Hibernate program, I strongly recommend taking a few more minutes to create a application-level database user. Click on the 'Users' tab, then the 'Create User' link. Fill in the form to create a user. Use a username of 'CarSalesAdmin' with a password of 'password' as well. Leave the 'Superuser?' and 'Create DB?' checkboxes empty. Then click the 'create' button.

Without any assigned priviledges,the SuperSimple user can't do anything. Therefore, then next step is assigning some. Go back to the 'Databases' tab, then click on the 'Privledges' button on the SuperSimple database row. Then click the 'Grant' button. Select the 'CarSalesAdmin' user from the list box, and check off the 'public' group and the 'create' priviledge. Then click the 'Grant' button. The 'create' priviledge allows the CarSalesAdmin user to create database tables.

Figure 15: Granting Priviledges to the CarSalesAdmin User

Creating a Schema

Schemas are used to group database objects. For our purposes, let's create a schema called CarSales. Click on the Schemas link in the SuperSimple database in the left navigation pane. Make the schema's name 'CarSales' and the owner 'CarSalesAdmin'. Then click the 'Create' button.

Figure 16: Creating a Schema Called CarSales

Creating a Table

Logout of phpPgAdmin (click the link at the top right) and then relogin as the CarSalesAdmin user by clicking on the PostgreSQL link in the left navigation pane.

Now navigate to the CarSales schema in the left navigation pane. Then select the Tables link. This view allows you to create database tables.

Figure 17: Click the 'Create Table' Link

Let's create a three-column table to demonstrate the steps. First click the 'Create Table' link. Then fill in the form. Name the table 'Cars' and indicate that it will have three columns. Now click the 'Next' button.

Figure 18: Name the Database Table

Fill in the table's fields as shown below and click the 'Create' button.

Figure 19: The Fields for the Cars Table

Conclusion

Getting the PostgreSQL virtual appliance configuring and running wasn't difficult but it did take a leap of faith on my part. Fortunately, the folks at VirtualAppliances.net created a great nano-sized appliance so my faith was rewarded. They were also quite responsive to emailed questions.

I had fun learning about a new way of working while I prepared this pictorial. And I hope that you also embrace a sense of exploration as you review the world of virtual appliances.

Resources

Codebits.com - http://www.codebits.com/

Virtual Appliances - http://www.virtualappliances.net/

VMWare Database-related Appliances - http://www.vmware.com/vmtn/appliances/directory/cat/50

PostgreSQL - http://www.postgresql.org

phpPgAdmin - http://phppgadmin.sourceforge.net/