PostgreSQL on Windows (April 2004)

(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