PostgreSQL installation on Unix and Windows platforms

PostgreSQL is an advanced Object-Relational database management system (DBMS) that supports almost all SQL constructs (including transactions, subselects and user-defined types and functions). You will find PostgreSQL website under the following url http://www.postgresql.org, and the documentation can be browse on-line at the this url http://www.postgresql.org/docs/. The ebxmlrr server and client were tested with PostgreSQL 7.2 and we highly recommend to install PostgreSQL if you plan to use ebxmlrr regardless either Unix or Windows system platform you use. Notice that the database encoding has to be set to UTF-8 in order to support international characters (non-ASCII).

The information presented in this document comes from various PostgreSQL documents, different websites as well as emails exchanged among ebxmlrr contributors through ebxmlrr-team and ebxmlrr-tech mailing lists. You will find following information in this document:

Installation instructions for PostgreSQL database for Unix platforms

Here are instructions to install PostgreSQL7.2.2 on Unix that were tested on the Solaris 8

  1. Download the base tar.gz from http://www.ca.postgresql.org/ftpsite/v7.2.2/
  2. gunzip the file and tar -xvf on to a separate directory.
  3. on the untar home directory execute following commands: 
      ./configure
  gmake
  su
  gmake install
  useradd postgres
  mkdir /usr/local/pgsql/data
  chown postgres /usr/local/pgsql/data
  su - postgres
  4. Initialize PostgreSQL: 
      /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
  5. If you have problem with kernel shared memory, you may try with these options to start the server: 
      /usr/local/pgsql/bin/postmaster -N 16 -B 32 \
  -D /usr/local/pgsql/data >logfile 2>&1 &
  6. Create a database (UTF-8 encoded): 
      /usr/local/pgsql/bin/createdb -E UTF-8 test
  7. To get into the intreactive mode of this database: 
      /usr/local/pgsql/bin/psql test
  8. To get out interactive mode type \q

Installation instructions for PostgreSQL database for Windows platforms

To install PostgreSQL on Windows platforms you need Cygwin Unix-on-Windows emulation environment and CygIPC that provides shared memory, semaphores, and message support for Cygwin.

Installing Cygwin involves downloading the software from http://www.cygwin.com to the target computer and initiating the install program. You MUST select postgresql and cygrunsrv on the "Select Packages" screen if you want to be able to install PostgreSQL on your machine. You may wish also to select documentation to have it installed locally.

Download the latest binary file of CygIPC from http://www.neuro.gatech.edu/users/cwilson/cygutils/cygipc/ and unpack it in your root Cygwin directory.

You are now ready to install PostgreSQL. For the most updated installation instructions please consult your PostgreSQL installation documentation that may be found in /usr/doc/Cygwin/postgresql{version_number}.README file. (On your Windows platform it will be ${cygwin_installation_directory}\usr\doc\Cygwin\postgresql{version_number}.README folder)

There are two types of Cygwin PostgreSQL installations - basic and NT services. The basic installation is good for casual use on any version of

Windows, but required on Windows 9x/Me. The NT services installation is good for a more production environment, but is only available on Windows NT/2000.

The following is the basic Cygwin PostgreSQL installation procedure:

  1. Start the cygipc ipc-daemon: 
      $ ipc-daemon &
  2. Initialize PostgreSQL: 
      $ initdb -D /usr/share/postgresql/data
  3. Start the PostgreSQL postmaster: 
      $ postmaster -D /usr/share/postgresql/data &
  4. Connect to PostgreSQL: 
      $ psql template1

The following is the NT services Cygwin PostgreSQL installation procedure:

  1. Install the cygipc ipc-daemon as a NT service: 
      # ipc-daemon --install-as-service
  2. Create the "postgres" user account: 
      # cmd /c lusrmgr.msc # [3]
  # mkpasswd -l -u postgres >>/etc/passwd
  3. Grant the "postgres" user the "Log on as a service" user right: 
      # cmd /c secpol.msc [4]
  4. Install postmaster as a NT service: 
      # cygrunsrv --install postmaster --path /usr/bin/postmaster \
  --args "-D  /usr/share/postgresql/data -i" --dep ipc-daemon \
  --termsig INT --user postgres --shutdown # [5] [6]
  5. Create the PostgreSQL data directory: 
      # mkdir /usr/share/postgresql/data
  6. Change ownership of the PostgreSQL data directory: 
      # chown postgres /usr/share/postgresql/data
  7. Initialize PostgreSQL (*when running under the "postgres" account*): 
      $ initdb -D /usr/share/postgresql/data
  8. Start the cygipc ipc-daemon: 
      # net start ipc-daemon # [7]
  9. Start postmaster: 
      # net start postmaster # [7]
  10. Connect to PostgreSQL: 
      # psql -U postgres template1 [8]

The following are the notes to the above:

[1] The "#" prompt indicates running as a user which is a member of the Local Administrators group.

[2] The "$" prompt indicates running as the "postgres" user. Log in as "postgres" or use ssh to emulate Unix's "su" command.

[3] On Windows 2000, this starts the "Local Users and Groups" applet On Windows NT 4.0, do the analogous operation.

[4] On Windows 2000, this starts the "Local Security Settings" applet. On Windows NT 4.0, do the analogous operation.

[5] Do not use rxvt for this step because the password exchange will not work properly.

[6] Clean postmaster shutdown will only work with a post Cygwin 1.3.2 snapshot from 2001-Jul-28 or later.

[7] Cygwin's bin directory (e.g., C:\Cygwin\bin) must be added to the Windows NT/2000's system PATH and the machine rebooted for the SCM to find cygwin1.dll.

[8] Actually, psql can run under any user account.

PostgreSQL FAQ

Where can I find PostgreSQL jdbc driver?
If you are searching for the jdbc driver for PostgreSQL 7.2.2, you can directly go to this link and download it from .

http://jdbc.postgresql.org/download.html
How can I create a new ebxmlrr user?
In your terminal type 
  createuser -U postgres -W ebxmlrr
Decide if you want to grant this user privileges to create new databases and new users. At the end provide a password for the new user.
How can I create a new database called registry?
Database can be also created from terminal window with the following command: 
  createdb -U ebxmlrr -E UTF-8 registry
The '-E UTF-8' parameter sets the database encoding to UTF-8, which is required in order to support international characters (non-ASCII).
What administration tools are working with PostgreSQL?
psql If you have installed PostgreSQL on your machine, psql will be already there. Just type psql in your bash or command prompt.
pgAdmin II http://pgadmin.postgresql.org/
phpPgAdmin http://phppgadmin.sourceforge.net/
pgAccess http://www.pgaccess.org/
Tora http://freshmeat.net/projects/tora/

You will find links to several other administration tools on the PostgreSQL website http://techdocs.postgresql.org/guides/GUITools

Why am I getting "Function not implemented" error when initialising PostgreSQL database under Cygwin?
You may encounter following error during database initialisation: 
  ...
  creating template1 database in /usr/local/pgsql/data/base/1
  ...
  IpcSemaphoreCreate: semget(key=1, num=17, 03600) failed: Function not implemented
Please make sure that you downloaded CygIPC from http://www.neuro.gatech.edu/users/cwilson/cygutils/cygipc/ and that you unpacked it into your Cygwin directory (during unpacking you may be asked to overwrite some files and you have to accept it). You should not use CygIPC that is distributed with Cygwin.