Monday May 05, 2008

Creating PostgreSQL OpenSolaris LiveCD

OpenSolaris OS 2008.05 is now available for download.  Its quite a departure from traditional Solaris. Why? because the CD that one will be downloading is probably just a chapter in the full book. To understand the full picture one will have to really look at the bigger picture on how the whole deployment model is now changing. The full scope is not my topic of discussion but I probably want to focus on probably couple of pieces today in order to demonstrate on how to create a PostgreSQL OpenSolaris LiveCD.

In order to create a custom LiveCD, its probably best to start with OpenSolaris OS 2008.05 installation though it is not necessary as it can be done on Solaris Express installations also but needs pkg(5) to be installed. But the two things really required to create a custom LiveCD, you need one kit to download and a repository to access. The kit to download is OpenSolaris's Distribution Constructor project. Its quite easy to clone the scripts in the project using mercurial "hg" command. Installing the SUNWmercurial package is as easy as typing "pkg install SUNWmercurial" (provided networking is working and internet connection is available).  The good thing is if your "pkg" command does successfully install SUNWmercurial it means the second requirement of accessing a repository is already fulfilled.  Coming back to Distribution Constructor the project can be cloned as follows

hg clone ssh://anon at hg dot opensolaris dot org/hg/caiman/distro_constructor

This creates a distro_constructor directory in the current working directory which includes all the scripts and templates to create a distribution.  The best way to proceed is to copy test_data directory  and modify the settings in it after reading through the README file on the project source repository.

You will soon realize that pkgs.txt is what one really needs to modify to select what packages to be added as part of one's custom LiveCD. What I realized later is that its easier to add packages than actually remove packages. Since while pkgs.txt  doesnt handle dependencies (in the sense person reading the file cannot figure it out), the actual kit does resolve dependencies. (Though I wouldn't try random packages only since there are some utilities required and hence hard to get it right in first few tries if one is trying to do a minimized LiveCD.) Right now GNOME et all is required since the installer is dependent on it and hence its hard to cut down the size of the LiveCD to a size less than 600MB if one wants an installer from the LiveCD to the hard disk to work. However the good news is there is some space still available to fit PostgreSQL in and still burn a CD that will work. Anyway coming back to the pkgs.txt of the test_data which needs to be edited its surprising to see such a small list. It basically containts slim_install, SUNWslim-utils and entire. There is hardly any stuff that can be removed in it.  However the modification I did was to add PostgreSQL 8.2 packages (SUNWpostgr-82\*)  along with pgAdminIII (SUNWpgadmin3) packages which are available on and as mentioned in README file executed the script file build_dist.bash /pathto/test1.conf. If the setup is right and access to is fast then maybe in couple of hours (or three) you will get your own OpenSolaris 2008.05 LiveCD including PostgreSQL and PgAdminIII with a size of about 640MB.


OpenSolaris 2008.05 and Open Source Databases

Lets start at the point where you have just installed OpenSolaris OS 2008.05 and have logged in using your primary userid on the system.

First thing to do is install the packages for PostgreSQL and MySQL on OpenSolaris OS 2008.05. Right click on the desktop and select "Open Terminal" to start a terminal session. Use "su" to assume the root userid. (The primary user already has root role however some programs still explicitly check for userid of root and hence needed to avoid unexpected surprises.)

Verify pkg is able to communicate with the IPS repository.

# pkg search -r postgres
INDEX      ACTION    VALUE                     PACKAGE
basename   dir       usr/postgres              pkg:/SUNWpostgr-82-client@8.2.6-0.86
basename   dir       usr/postgres              pkg:/SUNWpostgr-82-contrib@8.2.6-0.86
basename   dir       usr/postgres              pkg:/SUNWpostgr-82-devel@8.2.6-0.86
basename   dir       usr/postgres              pkg:/SUNWpostgr-82-docs@8.2.6-0.86
basename   dir       usr/postgres              pkg:/SUNWpostgr-82-jdbc@8.2.504-0.86
basename   dir       usr/postgres              pkg:/SUNWpostgr-82-libs@8.2.6-0.86
basename   dir       usr/postgres              pkg:/SUNWpostgr-82-pl@8.2.6-0.86
basename   dir       var/postgres              pkg:/SUNWpostgr-82-server-data-root@8.2.6-0.86
basename   file      usr/postgres/8.2/bin/postgres pkg:/SUNWpostgr-82-server@8.2.6-0.86
basename   dir       usr/postgres              pkg:/SUNWpostgr-82-tcl@1.5-0.86
basename   file      usr/bin/postgres          pkg:/SUNWpostgr-server@8.1.11-0.86
basename   dir       usr/postgres              pkg:/SUNWpostgr-82-l10n-ja@0.5.11-0.86

This confirms that the repository is accessible. If this does not work confirm you have internet connection and/or try:

#  svcadm restart nwam

which currently takes some time to bring a small popup saying the interface is plumbed up and has an IP address assigned.  Anyway lets assume that the internet connects well and we are ready to install the Packages of PostgreSQL and MySQL

To install PostgreSQL binaries (currently PostgreSQL 8.2.6 32-bit  is available):

# pkg install SUNWpostgr-82-client SUNWpostgr-82-contrib SUNWpostgr-82-devel SUNWpostgr-82-docs SUNWpostgr-82-jdbc SUNWpostgr-82-libs SUNWpostgr-82-pl SUNWpostgr-82-server-data-root SUNWpostgr-82-server SUNWpostgr-82-tcl SUNWpostgr-server SUNWpostgr-82-l10n-ja
DOWNLOAD                                    PKGS       FILES     XFER (MB)
Completed                                  13/13   3159/3159   64.03/64.03

PHASE                                        ACTIONS
Install Phase                              3688/3688

Now to install PgAdminIII

 # pkg install SUNWpgadmin3
DOWNLOAD                                    PKGS       FILES     XFER (MB)
Completed                                    1/1     281/281   21.46/21.46

PHASE                                        ACTIONS
Install Phase                                378/378


Now to install MySQL packages:

# pkg install SUNWmysql-base SUNWmysql-base SUNWmysql SUNWmysql5test SUNWmysql5 SUNWmysqlt
DOWNLOAD                                    PKGS       FILES     XFER (MB)
Completed                                    5/5   2727/2727 160.65/160.65

PHASE                                        ACTIONS
Update Phase                                     2/2
Install Phase                              2971/2971

If you also want to install webmin:

# pkg install SUNWwebmin
DOWNLOAD                                    PKGS       FILES     XFER (MB)
Completed                                    1/1 19946/19946   40.35/40.35

PHASE                                        ACTIONS
Install Phase                            24298/24298

With this we are now setup with the most common packages required to use Open Source Databases PostgreSQL and MySQL on OpenSolaris 2008.05


Sunday Apr 13, 2008

Case Study - (Open Source Healthcare) using PostgreSQL on Solaris

Tolven Inc an Open Source Healthcare Solutions provider recently carried out a benchmark using PostgreSQL on Solaris 10 using ZFS on Sun Fire X4600.  The Benchmark report is now available on their website.

The size of the  single database instance using PostgreSQL 8.2.6 (32-bit) peaked at 474GB with the largest table having more than 500 million rows. More information is in the report.

Tolven's  setup guide for developers which includes information about PostgreSQL  setup is also available on their website.



Thursday Apr 10, 2008

PostgreSQL East 2008 Talk - Best Practices with PostgreSQL on Solaris

As Sun SPARC Enterprise T5140 is launched along with the Open Application Services solution which includes PostgreSQL, the talk I gave at PostgreSQL East 2008 on "Best Practices with PostgreSQL on Solaris" might be helpful for many new users.


If there are questions let me know.


Wednesday Apr 09, 2008

PostgreSQL East 2008 Talk - PostgreSQL and Benchmark

I started working on my upcoming talk at PGCon 2008 and realized that I haven't put my talk from PostgreSQL Conference East 2008  - PostgreSQL and Benchmarks online yet.


 More on the upcoming presentation later.


Thursday Mar 06, 2008

Database Appliances Vs Embedded Databases

In my previous post I briefly mentioned Database Appliance with Project Indiana. Now that Sun has acquired MySQL (or as some people say MySQL has acquired Sun) and with our existing work in progress with PostgreSQL, there are quite a bit of options available of using some combination  of Storage, System, Operating System, Database  along with some end user application and present it as either Database Appliance or use it as Embedded Database. 

I thought I will just discuss the audience, symptoms, merits, cons etc regarding the two approach and how they can be useful. 

 The first question is who likes database appliances and who wants embedded database? To answer that I would put the question back: What do you in hand? A hammer or a screw-driver? Because if you have a hammer, the whole world is a nail to you and if you have a screw driver the whole world is full of screws. Similarly if you love databases (DBA) and  you want to query everything via a SQL statement then database appliances are for you. If you dont like to interact with databases (System Administrator, Application User) then you essentially want an embedded database where the OS or your end application takes care of interacting with the database. Well that is quite simple.

 First lets talk about database appliance. Think of it as your existing wireless routers. You bring it in house, plug it into your network (essentially to your cable modem) and power it on. Connect your laptop to one of the LAN ports, run DHCP, fire up a browser and go to typically htp:// and lo and behold you get a website to login and administer your router. A typical database appliance in some way will behave similarly. It has a web-based administrator to set it up quickly and allow you to connect to the database via rest of your applications on the network. In fact all the pieces are already out there: Storage, Server, Operating System, Database and even a web based management tool like webconsole (to configure ZFS) and Webmin which even supports mysql. This help reduce the knowledge of underpinning Operating System that you trust to "just work". Once setup, your applications just connect through your applications talking to client/server setup of a normal database. While the appliance takes care of snapshots, backups, clones via the web-based tool. Can a database be easier than that? That's the values that a database appliance brings to the table. Its DBA's dream to not go through a System Administrator to allocate the OS resources for the database.  It abstracts the resilient operating system underneath it and makes it easier for the DBA to quickly deploy it in actual usage.

What about Embedded Databases? In some ways it is anti-database appliance. Other way to look at it is abstraction of the database functions itself via end application Functions which interacts with the database. The application can be either Operating System or the end application. One of the analogy that can be used here is an integrated database in the operating system which is controlled via services of the operating system. For example if you use "svcadm enable postgres:version_82" on Solaris, a PostgreSQL 8.2 instance is enabled for you in the background. Which means you are ready to use it with no idea on where it is or what it is (except from the svcadm name). That's an analogy of embedded database. Many applications infact create their own menu items to backup application which includes the database underneath it and so on. If things are setup right, then you dont even have to execute any dedicated PostgreSQL commands to stop/start, backup etc and everything is done using application like svcadm, zfs snapshot, etc.

After all it is all about choice and what one desires. Options and components already available. As database becomes a commodity, I wouldnt be surprised to see small consumer devices with a hard disk presenting itself as a MySQL or PostgreSQL database for structured information and as a NAS device for unstructured information. Talk about being a commodity.


Friday May 18, 2007

I just bought a domain name from They even gave an ad-supported free hosting for it. Using the hosting tools I found that they provide a wiki application from Not exactly similar to all the other wikis that I have used till date.

Anyhow I started putting some information on it. My idea is to use it to organize all database blog entries, articles, etc in a way easily consumed by someone who is looking for that information. Check it out and also see if you can also add information on it. If it doesnt save your information, do let me know. Since this is not entirely "custom", there are many limitation on what can be done using this wiki application. But afterall it is the information that counts.

Try and let me know what you think about it.


Jignesh Shah is Principal Software Engineer in Application Integration Engineering, Oracle Corporation. AIE enables integration of ISV products including Oracle with Unified Storage Systems. You can also follow me on my blog


« April 2014