PostgreSQL under Cygwin

PostgreSQL should be already installed on your Laboratory PCs. The server itself lives in the /usr/bin directory and is called postgres. But normally we run it through a wrapper called postmaster, which also lives in /usr/bin. In the NTFS notation, this may be something like C:\cygwin\usr\bin. There is also a SQL client there, called psql.

The way to run PostgreSQL is as follows: first you have to start the server, and then you talk to it using the client. The server and the client may run on the same machine, or they may run on different machines. Both configurations are supported. The same, of course, applies to Linux and therefore to the AVIDD cluster.

But before you can run the PostgreSQL server, you have to configure the IPC daemon, this is the only part that must be performed by the system administrator. All else you can do yourself, simply as a normal Cygwin (or Windows) user.

If you administer your own PC, then the procedure for configuring and running the IPC daemon is as follows. First you have to become the administrator in order to install the service. Because the service is a Cygwin program, it must be configured using the  cygrunsrv utility. This utility lives in the Cygwin /usr/bin too.

Issue the command:

# cygrunsrv --install ipc-daemon --path /usr/bin/ipc-daemon2 \
            --desc "IPC daemon for PostgreSQL"

Then start the IPC daemon  with

# cygrunsrv --start ipc-daemon

You should be able to see the daemon if you run

# ps -ef

Alternatively, you can go to

-> My Computer
   -> Control Panel
      -> Administrative Tools
         -> Services

and start the service the Windows way, i.e., by right-clicking on it and then selecting Start - this, at least, is how it works on my PC under Windows 2000.

One way or another, you should have the daemon running. Now once the daemon runs, you can switch back to your normal user account.

You have to decide where you want to keep all your PostgreSQL data. If the data base is going to be for your personal use, you should select a directory in your Cygwin home. If the data base is going to be used by your research group and served from a server, you may as well dedicate the whole drive or at least a partition to it.

In this case I am simply going to create the data base in a directory in my Cygwin home:

gustav@WOODLANDS:../gustav 19:39:57 !539 $ pwd
/home/gustav
gustav@WOODLANDS:../gustav 19:39:58 !540 $ mkdirhier pgsql/data
gustav@WOODLANDS:../gustav 19:40:03 !541 $

At this stage the directory has to be initialized for work with a PostgreSQL server. The utility that does the initialization is called initdb and  it will initialize the directory not just for one data base, but for the whole data base cluster, i.e., a collection of data bases, that are going to have their data stored in this area:

gustav@WOODLANDS:../gustav 19:47:39 !551 $ initdb -D `pwd`/pgsql/data
The files belonging to this database system will be owned by user "gustav".
This user must also own the server process.

The database cluster will be initialized with locale C.

Fixing permissions on existing directory /home/gustav/pgsql/data... ok
creating directory /home/gustav/pgsql/data/base... ok
creating directory /home/gustav/pgsql/data/global... ok
creating directory /home/gustav/pgsql/data/pg_xlog... ok
creating directory /home/gustav/pgsql/data/pg_clog... ok
creating template1 database in /home/gustav/pgsql/data/base/1... ok
creating configuration files... ok
initializing pg_shadow... ok
enabling unlimited row size for system tables... ok
initializing pg_depend... ok
creating system views... ok
loading pg_description... ok
creating conversions... ok
setting privileges on built-in objects... ok
vacuuming database template1... ok
copying template1 to template0... ok

Success. You can now start the database server using:

    /usr/bin/postmaster -D /home/gustav/pgsql/data
or
    /usr/bin/pg_ctl -D /home/gustav/pgsql/data -l logfile start

gustav@WOODLANDS:../gustav 19:48:44 !552 $

Let us have a look at what some of the files created by initdb look like.

gustav@WOODLANDS:../gustav 19:50:28 !561 $ cd pgsql/data
gustav@WOODLANDS:../data 19:50:31 !562 $ ls -FC
PG_VERSION  global/   pg_hba.conf    pg_xlog/
base/       pg_clog/  pg_ident.conf  postgresql.conf
gustav@WOODLANDS:../data 19:50:33 !563 $

There are three configuration files here and then some directories in which PostgreSQL is going to store data and logs.

The file that defines from which hosts you can connect to the data base server and as who, is pg_hba.conf. The  header of the file explains in detail how to construct the entries. An example entry may look as follows:

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD
host    biblio      adams       129.79.207.223    255.255.255.0     md5

which specifies that a user adams is allowed to connect to the data base biblio from a host of address 129.79.207.223, and that the user must be authenticated using the md5 method. By default all users from the local machine are allowed connections to PostgreSQL servers serving all data bases in the cluster. But $\ldots$also by default PostgreSQL will ignore this file altogether and allow data base access to the data base owner only. To change this you will have to run postmaster with the -i option. But before you do this, you should edit this file very carefully indeed.

File pg_ident.conf is  used for the user identification based on the ident protocol. This protocol is very popular, but not very secure. In order to use this protocol you would have to type ident in the METHOD field in the pg_hba.conf file.

Finally  there is the postgresql.conf file, which is the PostgreSQL configuration file. It has a lot of parameter definitions, including the port number the server is going to listen on, whether SSL should be used for security, what is the maximum number of connections allowed, what is the maximum number of tables per data base, what language should be used in the data base messages (e.g., Lithuanian), and so on. There is usually little need to change much on this file, unless your data base is going to be really big and accessed very frequently.

Now you can start the  postmaster directing it to the newly created area. The postmaster will refuse to run if the directories it's been pointed to have not been prepared with initdb. The program does not return and cannot be talked to from the keyboard, although it logs all it does on standard output. So the right way to run it is to start it in the background and have logging redirected to a file. Here is how I do it:

gustav@WOODLANDS:../gustav 20:10:47 !586 $ postmaster \
     -D `pwd`/pgsql/data > pgsql/data/Nov-16-2003.log 2>&1 &
[1] 2300
gustav@WOODLANDS:../gustav 20:10:32 !587 $ jobs
[1]+  Running postmaster -D `pwd`/pgsql/data >pgsql/logs/Nov-16-2003.log 2>&1 &
gustav@WOODLANDS:../gustav 20:12:06 !588 $

Well, it runs! We can go to the logs directory and see the logging:

gustav@WOODLANDS:../gustav 20:12:06 !589 $ cd pgsql/logs
gustav@WOODLANDS:../logs 20:12:42 !590 $ ls
Nov-16-2003.log
gustav@WOODLANDS:../logs 20:12:42 !591 $ cat Nov-16-2003.log 
LOG:  database system was shut down at 2003-11-16 19:48:43 USEST
LOG:  checkpoint record is at 0/83B238
LOG:  redo record is at 0/83B238; undo record is at 0/0; shutdown TRUE
LOG:  next transaction id: 480; next oid: 16976
LOG:  database system is ready
gustav@WOODLANDS:../logs 20:12:44 !592 $

and once we're there we can look at the log continuously with tail -f. If you are going to do this, you will need another window, in which to run a client application that talks to the server.

But before we are going to connect to the server let us create an empty data base first. This is done  with the command createdb. The command is going to connect to the first postmaster it finds on the default port. If you run more than one server, you will have to provide createdb with more information. But the default will do just fine for us. So let us create a data base called neighbors:

gustav@WOODLANDS:../gustav 20:25:27 !595 $ createdb neighbors
CREATE DATABASE
gustav@WOODLANDS:../gustav 20:25:42 !596 $

Well, this worked.

Now we can connect to the data base and do various things with it.

If you want to completely delete the whole data base, issue the command (with caution!):

gustav@WOODLANDS:../gustav 20:37:10 !615 $ dropdb neighbors
DROP DATABASE
gustav@WOODLANDS:../gustav 20:37:18 !616 $

To stop the postmaster you can simply type ^C, i.e., control-C, if the postmaster runs in the foreground, or you can send it a kill, if it runs in the background:

gustav@WOODLANDS:../gustav 20:37:18 !616 $ jobs
[1]+  Running                 postmaster -D `pwd`/pgsql/data >pgsql/logs/Nov-16-2003.log 2>&1 &
gustav@WOODLANDS:../gustav 20:38:20 !617 $ kill %1
gustav@WOODLANDS:../gustav 20:39:29 !618 $ jobs
[1]+  Done                    postmaster -D `pwd`/pgsql/data >pgsql/logs/Nov-16-2003.log 2>&1
gustav@WOODLANDS:../gustav 20:39:37 !619 $

There are many methods to connect to a PostgreSQL data base (the postmaster must be running, of course). First and foremost PostgreSQL provides a C-language interface. There is a direct interface and an embedded SQL interface. There is also a Tcl interface, a Java interface, and a Python interface. And $\ldots$ last but not least, a SQL (it is often pronounced sea-quell, hence ``a'' rather than ``an'' in front of it) interface and a SQL client  called psql.

How can you find more about PostgreSQL? Abundant documentation  is provided with it. You should find it in the /usr/doc/postgresql-7.3.4 (the number may be different) on your Cygwin or Linux system. There is an html subdirectory there and if you point your browser at the index.html file in it, you'll get to view the manuals. There should be at least six of them:

1.

PostgreSQL 7.3.4 Tutorial

2.

PostgreSQL 7.3.4 User's Guide

3.

PostgreSQL 7.3.4 Administrator's Guide

4.

PostgreSQL 7.3.4 Programmer's Guide

5.

PostgreSQL 7.3.4 Reference Manual

6.

PostgreSQL 7.3.4 Developer's Guide

7.

Index

In the next section I am going to show you how to talk to PostgreSQL using the SQL client.