Clarence's Wicked Mind

本來想買藤田惠美既碟, 但係搵唔到... 結果忍唔住手買左呢隻...

(Note that I wrote this article a long time ago. Now Postgresql has its own native Windows distribution.)

This is a quick installation guide of PostgreSQL on Windows. PostgreSQL and MySQL are two of the database systems that I used for program development at home. I usually run the database servers on either FreeBSD or Linux. Recently, a project idea came up and I decided to try PostgreSQL on Windows.

This guide only concentrates on the installation process. This is not a tutorial on PostgreSQL/JDBC/Cygwin......

What is PostgreSQL?
"PostgreSQL is the most advanced open source database server." -- PostgreSQL: Introduction and Concepts by Bruce Momjian.

What is Cygwin?
Cygwin is a Linux-like environment for Windows.

Overview

• Install Cygwin (together with PostgreSQL)
• Install cygipc
• Init and start the PostgreSQL server
• Create users and databases
• Using PostgreSQL with JDBC

Install Cygwin and PostgreSQL
We can choose to install the PostgreSQL together with Cygwin.

1. Goto the Cygwin homepage
2. Click on the Install or update now! link and download the setup.exe file
3. Execute the executable. Select the "Install from Internet" option
   
   
4. Select the location to install
   
   
5. The setup program needs to download packages before installing. Specify a directory to store these packages.
   
   
6. Select the connection method. Then pick a mirror site to download the packages.
   
   
7. Select the packages that you wants. Remember to select the PostgreSQL package under the Database category.
   
   
8. Proceed with the download and install process.

If the setup program doesn't install the packages after download, you can execute the setup.exe again. This time, select "Install from local directory". Then select the director where the packages are downloaded. Proceed to install the packages.

Install cygipc
PostgreSQL depends on an utility package called cygipc. It provides IPC (shared memory, semaphores etc) functions.

Note that newer versions of Cygwin already include the cygipc package. Also the command is changed to ipc-daemon2

1. Download the package here.
2. Start a cygwin shell window
3. Extract files from the package.

   $ cd /
   $ bunzip2 -c path_of_package/cygipc-1.14-1.tar.bz2 | tar xvf -
   

   Check the availability of the ipc daemon installed. Then execute the daemon in background.

   $ which ipc-daemon
   /usr/local/bin/ipc-daemon
   
   $ /usr/local/bin/ipc-daemon&
   

Init and start the PostgreSQL server
Before we can start the PostgreSQL server, we need to initialize a database storage area on disk.

1. If the ipc daemon is not running, start it now
   

   $ which ipc-daemon
   /usr/local/bin/ipc-daemon
   
   $ /usr/local/bin/ipc-daemon&
   

2. Create the directory /usr/local/pgsql/data for storing PostgreSQL data.
3. Initialize the storage area
   

   $ /usr/bin/initdb -D /usr/local/pgsql/data
   

4. Now we can start the server. Note that we need to use the -i option so that the server will listen to TCP connections. Otherwise, it just listens on Unix sockets.
   

   $ /usr/bin/postmaster -i -D /usr/local/pgsql/data 
   

   
   (Note: You can also choose to run it as a background job. But leaving it on foreground can let us view the log)

Create users and databases
We first create a new user clarence and grant it the right to create databases. Then using this user account, we create a database with Unicode encoding.

Here are the explanations of the commands

• psql -l: List existing databases
• psql template1: Login to the template1 database (as Administrator)
• CREATE USER clarence CREATEDB;: Create a new user clarence with permission to create databases
• \q: Quit psql command line
• psql template1 clarence: Login to the template1 database (as clarence)
• CREATE DATABASE test WITH ENCODING='UNICODE';: Create a new database test with Unicode encoding.

Using PostgreSQL with JDBC
There is no special trick for using PostgreSQL with JDBC.

• Download the PostgreSQL JDBC driver from http://jdbc.postgresql.org/
• Include the jar in the classpath
• The JDBC URL for PostgreSQL can be
  • jdbc:postgresql:database
  • jdbc:postgresql://host/database
  • jdbc:postgresql://host:port/database
• Access the database as usual

  ......
  import java.sql.*;
  import org.postgresql.*;
  
  ......
  Class.forName(
  "org.postgresql.Driver");
  
  ......
  con = DriverManager.getConnection(
  "jdbc:postgresql://127.0.0.1/test",
  "clarence", "");
              
  PreparedStatement ps = 
  con.prepareStatement("select * from version()");
  ResultSet rs = ps.executeQuery();
  ......
  

Misc.

• Control client authentication by editing the file /usr/local/pgsql/data/pg_hba.conf
• To install PL/pgSQL into a database, execute the command "createlang plpgsql dbname"
• To install PostgreSQL as a service, use the cygrunsrv command. First install the IPC daemon as service:

  ipc-daemon --install-as-service
  

  Then use cygrunsrv to add PostgreSQL as a service:

  cygrunsrv --install PostgreSQL --path /usr/bin/postmaster \
  --args "-i -D /usr/local/pgsql/data" --dep ipc-daemon \
  --user username --password password --stdout stdoutlog \
  --stderr stderrlog --termsig INT --shutdown
  

• To enable the statistics collection, edit the postgresql.conf file and set STATS_START_COLLECTOR, STATS_COMMAND_STRING, STATS_BLOCK_LEVEL, and STATS_ROW_LEVEL accordingly. View the stats by selecting pg_stat_all_tables, pg_stat_user_tables, pg_statio_all_tables, pg_statio_user_tables,...
• PostgreSQL: http://www.postgresql.org/
• Cygwin: http://cygwin.com/
• GUI admin tool: http://pgadmin.postgresql.org/
• On-line book PostgreSQL: Introduction and Concepts: http://www.postgresql.org/docs/awbook.html

I created this page as my notes on how to generate SSL certificates for my home web server. This document does not meant to be a comprehensive guide on SSL certificate. Read this on your own risk...

What is OpenSSL?
"The OpenSSL Project is a collaborative effort to develop a robust, commercial-grade, full-featured, and Open Source toolkit implementing the Secure Sockets Layer (SSL v2/v3) and Transport Layer Security (TLS v1) protocols as well as a full-strength general purpose cryptography library."

Random numbers
Before we start generating certificates, we need some random numbers for key generation. This step is optional, but it increases the security of the generated keys. Also you can use any random number generation menthod you like.

dd if=/dev/urandom of=rand.txt bs=8192 count=1

The above command copies 8192 pseudo-random bytes from /dev/urandom. I did this on my FreeBSD machine. Note that you can/should generate different random numbers for each key generation presented below.

Generating a Certificate Authority (CA)
First we generate the CA private key. Then based on the key, generate a certificate request. Finally we will sign the request with our own CA key.

openssl genrsa -rand rand.txt -out ca.key 1024
openssl req -new -key ca.key -out ca.csr
openssl x509 -req -days 365 -in ca.csr \
-signkey ca.key -out ca.crt

Generating a Web Server certificate
Here is how we generate a self-signed certificate for use with web servers. The procedures are similar to generating the CA certificate.

openssl genrsa -rand rand.txt -out server.key 1024
openssl req -new -key server.key -out server.csr
openssl x509 -req -days 365 -in server.csr \
-signkey server.key -out server.crt

You can choose to sign the server certificate with the CA key (ca.key) generated in previous step.

(BTW, details on how to install the certificate on an Apache server are not presented here... maybe later when I have more time... :)

Generating a client certificate
The generation of client certificate is similar to others

openssl genrsa -rand rand.txt -out client.key 1024
openssl req -new -key client.key -out client.csr

Then use the CA key to sign the certificate request

openssl x509 -req -days 365 -CA ca.crt -CAkey ca.key \
-CAcreateserial -in client.csr -out client.crt

Finally, package the certificate and key in PKCS12 format so we can import it into browsers

openssl pkcs12 -export -clcerts -in client.crt \
-inkey client.key -out client.p12

By default, OpenSSL uses RC2 and 3DES for encrypting the certificate and private key. Note that some binary distributions of OpenSSL do not have RC2 compiled in. And some browsers don't support certificate encrypted with RC2. If so, try to use RC4 or DES etc other encryptions for the certificate. e.g.

openssl pkcs12 -export -clcerts -in client.crt \
-inkey client.key -out client.p12 -certpbe des-ecb -descert

or

openssl pkcs12 -export -clcerts -in client.crt \
-inkey client.key -out client.p12 -certpbe rc4-40 -descert

You can then copy the .p12 file to the client computer for installation. For example, on Windows 2000, just double click the file and follow the instruction to install the certificate.

(aka Imaging for Windows sucks)
It was almost noon and I was doing a routine email check before I went off for lunch. Within those 50+ spam mails, I got a case study paper in SealedMedia (spdf) format. I am (together with my team members) supposed to study that paper and hand in a report a few weeks later for one of my part-time degree courses. Since that file would expire after a month, I thought it would be a good idea to back it up into another format while I can still open it. I used an exporter to generate a tiff file from the spdf. But unfortunately the export program can't write compressed tiff file. And that 321KB spdf file became a 64MB tiff file at 200dpi with 8bpp (and a whopping 433MB if I use 300dpi with 24bpp).



No big deal. I can just use another utility to convert it into compressed tiff. Or so I thought.

Imaging for Windows
The first converter came into my mind was Imaging for Windows (also known as Wang Image Viewer in Windows 95). I never used it before but I believe it could do the job. After loaded the tiff file successfully (btw, don't use drag-and-drop with Imaging... it crashes the program...), I immediately selected the Save As command under the File menu. Hoping that I can choose to save the file in compressed format.

No luck here.



OK. Fine. Do a little exploration on the menu. It turns out that there is indeed a compression option under the Page/Properties popup. But it is in page level! That means I need to navigate all 18 pages in the file and select the compression option for each page! I mean, why on earth would someone want to use different compression methods on different pages in a single tiff file? Even so, couldn't the program designers provide an option to apply the same compression method to the whole file?



Enough complain. I still need that compressed tiff file. So I do the selection for all 18 pages (and waited for each page to compress before I can move on to next page). Since I don't want to overwrite the original tiff file, I then choose the Save As command to save the compressed tiff file in another name. The hard disk started crunching and busy writing the output file. I waited a whole minute and the writing is still going on. I fired up the Explorer to check what was going on. It turns out that Imaging writes the file in a strange and inefficient way. It seems to be writing a page out to a temp file. Append the next page to that temp file. Copy and overwrite the output file with that temp file. Then append the next page...... until the whole file complete.



OK. I can understand that there might be users out there who need different compression methods within a tiff file. But why does Imaging writes file in this #@$#%$^$@ way?!! Anyway, after several minutes of hard disk crunching, I finally got my compressed file. Done. Time to have lunch... but wait! @##%^@$!#$$!!!! Why the files size is still over 60MB??? I selected the LZW compression and I am expecting more compression than THAT!



I fired up IrfanView to check the output file. It turns out that only the last page was compressed! I switched back to the Imaging program and check. Only the last page, the page that was displayed before I hit Save As, is selected with compression. All other pages are now switched back as No Compression. @#%^#!^!!!!

At this point, I can conclude that this Imaging program sucks and I better off use either ImageMagick or tiffcp to do the job. While waiting for my poor AMD K6 200 (o/c 225) doing the compression under FreeBSD, the programmer inside me started to wonder whether the Imaging problem is simply a programming bug in the UI... So I made a copy of the original uncompressed tiff file. Loaded it into Imaging again. Repeated 18 times to select the comperssion method for each page. Then I used the Save instead of Save As command to write the output file. And bingo! The output file is under 2MB and all pages are compressed correctly



The moral here? Use the good old Unix command line programs whenever you can. Anyway, time for lunch.

The ActiveX approach
During the lunch, I kept thinking about that the slow file saving and the compression bug in Imaging. Maybe the ActiveX component of Imaging can do a better job. And maybe I should write a simple program with that ActiveX and avoid the Imaging UI hereafter. So after the lunch, I fired up the VB studio and started to explore the ActiveX component of Imaging.



And guess what I found? It seems that the "select compression per page" feature is built right into the ActiveX and not a UI design problem! Both the CompressionType and CompressionInfo properties of the Kodak Image Edit Control are read only. The only way to set compression is to use the SaveAs command!



And the biggest problem is that only the currently displayed page will be saved with the selected compression! Other pages are saved with no compression (the default). No wonder that the Save As command in Imaging has that "bug"! So I guess the only way to compress all pages is to compress and save each page separately and combine them later. That might explain the strange temp files generated by Imaging during writing files.

How can a designer expect someone to reuse such an ActiveX?

Conclusion
Imaging sucks.

BTW, there is an Easter Egg in Imaging. Select About Imaging under the Help menu. Press i on your keyboard. There will be a popup window showing a staff list.