Installing PostgreSQL on Cygwin

This is an UNOFFICIAL tutorial. If something goes wrong while you follow this tutorial, don't attempt to email the Cygwin or PostgresSQL mailing lists telling them that you followed instructions here. They have great documentation and tutorials of their own. I provided this document as is and without any warranty that it will work.

A great part of this article was taken from the PostgreSQL and cygserver documentation that come with cygwin. The PostgreSQL documentation that comes with cygwin was written by Jason Tishler and it was through his documentation that this article was made possible. If you installed cygwin with PostgreSQL, you could find the documents on:

/usr/share/doc/Cygwin/postgresql-x.y.z.README 
/usr/share/doc/Cygwin/cygserver.README

Many thanks to Ed Mateo for providing invaluable in correcting the typos in this tutorial and verifying the accuracy of this document on Windows XP since I've only had Windows 2000 to work on. Acknowlegements to Dr. Igor Peshansky for suggesting the inclusion for Win9x/ME support on a post on the cygwin mailing list.

This document is covered by the GNU General Public License.

1. Make sure that you've downloaded the binaries for PostgreSQL for Cygwin. You can do this by going to the Cygwin website http://cygwin.com and running the Cygwin installation program setup.exe.

IMPORTANT: This tutorial will teach you how to install PostgreSQL on Cygwin from scratch. It won't teach you how to re-configure a wrongly configured PostgreSQL on Cygwin. Hmmm, actually, installation from scratch is not a good term since I will assume that you've got the PostgreSQL binaries for cygwin installed already. Thus, it's really not a tutorial on installing PostgreSQL from scratch but configuring and initializing it from scratch.

Before proceeding, we will have to have some assumptions. First of all, We'll assume that PostgreSQL was installed on your system when you installed cygwin. Second of all, let's assume that we would install your database on the /var/postgresql/data directory. (You can change this if you want.) This directory is not created by cygwin so, unless you had reasons to do it, I assume that you don't have the /var/postgresql/data directory on your system. And, if you're on Windows NT/2000/XP systems, I will also assume that you don't have the user postgres on your system, unless of course you had reasons to have it on your system.

The reason I'm saying this is because you might run into problems if you've had a wrongly configured PostgreSQL, especially if your Cygwin installation and your database repositories are on NTFS filesystems (usually if you're using WinNT/2000/XP) and if you used the same directory for your repository as the one used in the example on this tutorial (i.e. /var/postgresql/.

So, once again, I'd like to remind you that this tutorial teaches you how to install and configure PostgreSQL on cygwin from scratch and NOT how to re-configure a badly configured installation. A tutorial on re-configuring a wrongly configured installation will present us with countless possibilities and combinations which, I think, are difficult to cover in a simple tutorial. So, make sure you're configuring PostgreSQL from scratch, ok!

Following this tutorial, I assume you're installing PostgreSQL from scratch if:

If you have one or more of the above files or users, chances are, you've probably had PostgreSQL configured already. Whether you've got a correct or incorrect (read: non-working) configuration is out of the question. This tutorial assumes that you have none of the items files nor directories nor users above. So, if you want to proceed with this tutorial, I suggest that you clear your system of these items.

2. Once you're done clearing your system, we're now ready to start the PostgreSQL initialization and cofiguration process. To start off, run the following:

cygserver-config

Answer "yes" if it asks you to overwrite the Cygserver configuration file. Answer "no" if it asks you whether you want to install it as a service.

The above command (which is actually just a script on the /usr/bin directory) creates an Cygserver service configuration.

Cygserver is described in the following step.

3. Now, we have to install and run Cygserver. From you cygwin shell, run the following command:

cygrunsrv -I cygserver -p /usr/sbin/cygserver.exe 

The preceding command installs the cygserver service.

Now, let's start the cygserver service:

cygrunsrv -S cygserver 

The Cygwin install notes say that

“Cygserver is a program which is designed to run as a background service. It provides Cygwin applications with services which require security arbitration or which need to persist while no other cygwin application is running."”

In other words, this simply means that we need this service in order to run other cygwin applications without actually starting them from a shell.

4. Let's create the `postgres' user account:

Let's add the `postgres' username in our /etc/passwd file:

mkpasswd -l -u postgres >> /etc/passwd

We now have to set the password for the postgres account:

passwd postgres

You'll be prompted for a password and its corresponding confirmation. Make sure that the passwords match.

You might be wondering why we still need to have such a special postgres account. Well, this is for security reasons. We would want our daemon to run with the least privileges. In other words, we don't want to run the database daemon as Administrator since that may pose several security risks.

5. We now create the database directory or repository . To re-initialize the database (i.e. if we want to start everything from scratch), we may have to clear our old repository. To do this, enter the following command (Before doing the following command, you may want to backup the directory if you want to have access to the databases previously created on the repository):

rm -rf /var/postgresql

The previous command will delete the directory postgresql and all files and directories under it.

Let's re-create the directory so that we have a clean installation. To do this, enter the following commands:

mkdir /var/postgresql
mkdir /var/postgresql/data

The mkdir command is a mnemonic for `make directory'. Thus, the previous command creates the directories /var/postgresql/ and /var/postgresql/data/. Please note that in cygwin, the root directory is the directory where you installed cygwin. By default, it is the c:\cygwin directory. The drives are mapped in cygwin by the /cygdrive/<DRIVE_LETTER> prefix. Thus, the directory c:\winnt\system32\ in Windows/DOS format is mapped as /cygdrive/c/winnt/system32/ in cygwin. Actually, the latter format is called the POSIX format. Unix and Linux systems use this format in specifying files and directories on their file systems.

6. Once the directories have been created, we have to set the owner of the repository directory. To do this, we use the chown command, which is a mnemonic for `change owner'. What we want to do is to have user postgres own the database repository. The command is:

chown postgres /var/postgresql/data 

7. And now, for the tricky part, we actually have to be the postgres user to initialize the database. Unfortunately, the su command is not fully implemented for Windows under cygwin so we have to su (switch users) using the windows way! What we want to do is to open a shell that is owned by user postgres.

To do this, you can click `Start', `Run' and enter the following command on the textbox. Alternatively, you could open a windows command prompt (yes, a windows command prompt ant NOT your cygwin shell) to enter the following command:

Thus, if <YOUR_CYGWIN_ROOT_DIRECTORY> is c:\cygwin (this is the default), the actual command you would run would be:

You will be prompted for the password of user postgres and you simply have to enter the password you set previously.

8. Now, let's switch to the shell owned by user postgres to initialize our PostgreSQL repository. First (remember to do this on the shell owned by user postgres), enter this command:

export CYGWIN=server

The use of the preceding command, as quoted from the cygwin documentation is described as follows:

“The Cygserver services are used by Cygwin applications only if you set the environment variable CYGWIN to contain the string "server". You must do this before starting the application. Typically, you don't need any other option, so it's ok to set CYGWIN just to "server". It is not necessary to set the CYGWIN environment variable prior to starting the Cygserver process itself, but it won't hurt to do so.”

The actual database initialization is actually done by the following command (still done on the shell owned by postgres):

initdb -D /var/postgresql/data

If all went well, you should see something like this after invoking the preceding command:

Success. You can now start the database server using:

/usr/bin/postmaster -D /var/postgresql/data 
or
/usr/bin/pg_ctl -D /var/postgresql/data -l logfile

If you encounter an error inidicating that "postgres" was not found, spewing out an error such as the one below,

invalid binary "/usr/sbin/initdb"

you may have to set the permissions for initdb or postgres. To do that, from an Administrator shell, run the following or its equivalent on your system:

chmod o+rX /usr/sbin/initdb /usr/sbin/postgres

The above command gives any unprivileged user account permission to run the postgresql programs. Do this only though if you want other users to be able to run the PostgreSQL programs.

If everything has been all right, you may now exit from the shell owned by postgres. This can be done by simple typing `exit' on the shell.

9. Let's install the PostgreSQL service. (Do this on a regular cygwin shell now.) This time, we have to enter quite a long command. Please remember that this command can be entered as one line on your cygwin shell provided that you remove the backslashes (\). Alternatively, you could enter this command verbatim including the backslashes. The use of the backslash is to tell the shell that we still have more things to type on the next line.

    cygrunsrv --install postmaster --path /usr/bin/postmaster \
    --args "-D /var/postgresql/data -i" --dep cygserver \
    --user postgres --termsig INT -e "CYGWIN=server" --shutdown 

cygrunsrv --install postmaster --path /usr/bin/postmaster \
    --args "-D /var/postgresql/data -i" --dep cygserver \
    --termsig INT -e "CYGWIN=server" --shutdown 

10. Let's start the PostgreSQL service

cygrunsrv --start postmaster 

To verify that PostgreSQL is running, enter this command:

cygrunsrv -Q postmaster 

If postgresql is running well, you should see something like this:

Service postmaster exists
Type                : Own Process
Current State       : Running 
Controls Accepted   : Accept Stop, Accept Shutdown 

11. You may want to create a database user for yourself. (This step is optional.) To do this, enter the following command:

createuser -U postgres <YOUR_USER_NAME_AS_IT_APPEARS_ON_WINDOWS>

Thus, if your windows logon name is `Joe', the actual command would be:

createuser -U postgres Joe

You would be asked whether `Joe' will be allowed to create databases and other users. You may answer `yes' to all the questions.

You may also want to create your own database. Say for example, you want to create a database named `employee'. All you have to do is:

createdb employee

12. Whew! That was it! Now, every time you start your machine, you don't have to go through this entire process. You simply connect to your database with the following syntax:

psql <DATABASE_NAME>

Thus, if you want to connect to the database `employee' (yes, the term used in Database lingo is `connect to the database'), all you have to do is:

psql employee

That's it! Enjoy!

This document was created on cygwin with the command:

    xsltproc /usr/share/docbook-xsl/html/docbook.xsl \
        postgresql-cygwin.xml > postgresql-cygwin.html

The XML source of this document is here.

Carlo Flora Florendo

Electronic Mail: carlo (at) hq (period) astra (period) ph

20 March 2007