dyrelund.com

I’ve been asked alot and I’ve wondered myself - so here it is:

First thing you wanna do is getting all the needed files.

This installation uses the follwing files:

• Cygwin [setup.exe] - I’ll explain later why you need this.
• postgresql-7.4.2
• pgadmin3-1_0_2

Instead of pgadmin3-1_0_2 you can use phpPgAdmin - but that requires php, Apache or similar webserver facilities running on your computer - maybe I’ll write an installation guide for that later (it can be rather tricky)…

Get the files and save them in a folder on your desktop.

Cygwin
What is Cygwin and why do I need it?
- Well, PostgreSQL wasn’t made for WIN32 even though the developer team has plans for that: PostgreSQL on Windows. So what you need is a UNIX enviroment on your Windows Machine. There are many ways to do so - one of them is using Cygwin.

Installing Cygwin:
Follow this guide: Installing Cygwin/X.

When you’re selecting packages make sure to expand the following sections:

• Database - select the ‘postgresql‘ package
• Develop - select the ‘gcc‘ and ‘make‘ packages

Note: if you select all the packages you’ll have about 375 MB downloading ahead of you, so unless you really feel desire to explore all corners of Cygwin, I suggest you stick to the default installation (with the postgresql, gcc and make packages enabled of course).
Keep the setup.exe file close by - you might wanna install additional packages as you learn more about UNIX, and maybe one day you’ll do the only right thing and buy a Mac

Install Cygwin in a root library e.g.: C:\cygwin

PostgreSQL
I’ll recommend a visit to postgreSQL’s homepage - it will come in handy later when you run into troubles.

Installing postgreSQL:
You might have noticed that the postgreSQL file you’ve downloaded is a .tar.gz file - no worries, that’s nothing more than a .zip or .rar file - a packed file.

Put the postgresql-7.4.2 file the /usr/local library: C:\cygwin\usr\local. The reason you need the file in the /usr/local library is due to the way the UNIX file system works, read more about it here: The UNIX file system

Once it is there start Cygwin and navigate to it’s location like this:

cd /usr/local

Now unpack the file using the tar command:

tar -zxvf postgresql-7.4.2.tar.gz

The file is now unpacked in a library named: postgresql-7.4.2 - you might wanna rename the directory to something less complicated - like: pgsql. It’s easily done:

mv postgresql-7.4.2 pgsql

Go to the library:

cd pgsql

Since we’ve downloaded the source code for postgres we need to configure and compile it.

First make the configuration:

./configure

Next compile the code:

./make

Install the compiled code properbly:

./make install

The next thing is to tell Windows about the new inhabiter. This is done by setting the following enviroment variables:

• PGHOME: “C:\cygwin\usr\local\pgsql”
• PGDATA: “C:\%PGHOME%\data”
• PGLIB: “%PGHOME%\lib”
• PGHOST: “localhost”

Modify the ‘PATH’ by adding the following:

• “%PGHOME%\bin;%PGHOME%\lib;c:\cygwin\bin”

Open a Cygwin shell and do the following for a basic installation:
Start ipc-daemon2 for shared memory support. To do this, enter the command:

ipc-daemon2 &

The ‘&’ makes postmaster run in the background so your shell doesn’t freeze.

This program needs to be running anytime you start the PostgreSQL server (postmaster) or initialize a database (initdb).

Use the initdb command to create a new database cluster. An example command would be:

initdb -D /usr/local/pgsql/data -W -E LATIN1

Which will create a cluster in the C:\cygwin\usr\local\pgsql\data directory, will prompt for a superuser password and will set the default database encoding to LATIN1. Notice that postgres tells you whom is the user for this server. By default the user logged on to the current Windows session is choosed. You can always modify the users in the .conf files - consult the documentation for more info.

To keep track of your database server you need to set up a log. It’s quite simple:

Make a directory for the logfile to be in:

mkdir /usr/local/pgsql/log

Start up the postmaster. Use a command similar to the following:

postmaster -D /usr/local/pgsql/data > /usr/local/pgsql/log/logfile 2>&1 &

This will start the postmaster, and if successful you will see some initial log entries in your logfile, and an entry “LOG: database system is ready”.

If at anytime your system isn’t working probably always remember to consult the logfile!

You are now running a PostgreSQL server on your Windows machine.

However! It’s not guaranteed that you’ll be able to work with postgres just yet. As default the postgresql.conf file -situated here: C:\cygwin\usr\local\data - doesn’t allow for TCP/IP connections, which you need to enable.

#tcpip_socket = false

and change it to:

tcpip_socket = true

In pg_hba.conf (situated same place as postgresql.conf) find this line:

local all all trust

and add the following beneath it to:

host all all 127.0.0.1 255.255.255.255 trust

After the changes restart the postmaster:

pg_ctl reload

That’s it, you’re set :0)

Open another Cygwin shell and type:

createdb testDB

This will create a new database called ‘testDB’

Now you can access that database by typing:

psql testDB

pgAdmin
Normally PostgreSQL is TUI (Text User Interface) based which can be rather anoying to work with. Fortunately some people have thought of a GUI (Graphical User Interface) for the postgres server. As I mentioned earlier you can choose between pgAdmin and phpPgAdmin. We use pgAdmin in this guide.

Installing pgAdmin:
It’s quite simple really, just dobbelt-click ‘pgadmin3.msi’ and you’re home safe.

Start pgAdmin and choose ‘add server’

Type in localhost as server, testDB as database and finally the name which postgres knows as user.

pgAdmin gives you a variety of easy to use facilities.

- enjoy the power of postgres!

Comments, corrections etc. are most welcome…

—
Special thanks to Giorgio Ponza for kind corrections and comments.