WINDOWS INSTALLATION - Mailing list pgsql-advocacy

Fellows,

In view of the fact that the Windows native release of PostgreSQL
might not be out until late 2004 and the wide request for Windows
users, I thought it necessary to offer this guide.

I must say that the UltraSQL Database is quite reliable.  But should
you require 7.3.x engine please check this piece.

I have been using the Windows port - PgSQL731a1 - for some time in
Nigeria and found it reliable but with many installation and
administration bottlenecks which are surmountable.

INSTALLATION PROBLEMS.

There are two issues that must be addressed for successful
installation.  These are:

1.    Who owns the server process? i.e., under whose account would
Postgre Service run!
The Postgre Service must run under an Administrator account and note
that certain Window flavours do not permit a service account without a
password, meaning that you must have an account with a password to run
a service.  Also, this build of Postgre does not permit running with
Local System Account.

You can create a new Administrator account for the Postgre service.
Please you must install Postgre using this account (login to Windows
with the account).

Now, two files are important – they are:
    initialise.bat and serviceinstall.bat

Halt the installation when you are sure these two files have been
copied to the bin directory (C:\program files\postgresql\bin).

Open the initialize.bat file.  The contents are:

“
@echo off

C:
chdir C:\Program Files\PostgreSQL\bin

bash --login -c 'cd /; chown -Rh Administrator.unknown
* .bash_profile; ln -s /bin/postgres.exe /bin/postmaster; /bin/initdb;
ipc-daemon --install-as-service; cygrunsrv --install "PostgreSQL
Server" --path /usr/bin/postmaster --args "-D /data -i" --dep ipc-
daemon --termsig INT --user Administrator --shutdown'

“

Locate Administrator.unknown (after cgown –Rh) and change to your
Windows login name – the Administrator user created for Postgre
service.  Change also Administrator after –user to the same name.

Open servicestart.bat.  The contents are:

“
@echo off

C:
chdir C:\Program Files\PostgreSQL\bin

bash --login -c 'ipc-daemon --install-as-service; cygrunsrv --
install "PostgreSQL Server" --path /usr/bin/postmaster --args "-
D /data -i" --dep ipc-daemon --termsig INT --user Administrator --
shutdown'

“
Change also the Administrator to your user name.

Save the two files and continue the installation.

After the installation the DOS console screen would appear. Also, the
setup would issue a finish notice asking for a reboot.  It is
advisable that you reboot the system yourself.  If the system restarts
before the completion of the initialization, the installation would be
incomplete.  You can either ignore the restart prompt or select No and
click on Finish button.  You restart the system when you are finished
with the installation.

The DOS screen would show the following texts:

The files belonging to this database system will be owned by
user “xxx”.
(xxx is your Windows user name.)

The database cluster will be initialized with Locale C.

Creating directory /data … ok
Creating directory /data/base … ok
Creating directory /data/global… ok
Creating directory /data/pg_xlog … ok
Creating directory /data/pg_clog … ok
.
.
.
Success
.
.

Enter password of user ‘.\xxx’:

Please note that the password would not echo to the screen.  Also this
should be your Windows login password.

At this junction, go to Windows Services Manager and select PostgreSQL
service. Under Logon tab your username would be checked as the service
account.  Manually retype your password in to the boxes again.

Restart Windows.  A user with your Windows login name would be created
as the Postgre default user without any password.  Everything should
be okay.


2.    Who owns the database files?, i.e., who owns the data
directory (c:\program files\postgresql\data).  Note that
postmaster.pid resides in this directory.  This file must be deleted
after server is shutdown and Postgres would not start with this file
in the data directory.  Funnily, this file is not deleted most often
after the service has shut down creating problem when Windows is
restarted.  You then must find a way of manually removing this file at
start or at shutdown!.  That is not easy!

The initialization process makes the data directory inaccessible to
all other users except the owner.  What I usually do is to

-    change the permission and grant full permission to the
Administrator group for the immediate contents of the data directory
not the subfolders.
-    change the startup type of Postgres Service to Manual
-    create a batch file with this content

@echo
del “c:\program files\postgresql\data\*.pid”
net start “postgresql server”
exit

Insert the batch file into Windows start up menu.

You can include in the batch file the code that would switch the
ownership before and after the deletion of the postmaster.pid if you
do not want to expose the data directory files such as pg_hba.conf and
postgresql.conf.

Well these are security problems we must live with until the perfect
Windows port is released.  If you must run PostgreSQL for highly open
and sensitive jobs please consider a Linux server for Windows clients.

That should do the job.  For this to always work an Administrator must
boot the Server machine.

DUMPING.
Usually the pg_dump and the likes do not work.  It complains of
cygz.dll.  I think this was an oversight during the compilation of the
program.  If you can get any valid cygz.dll from any Cygwin
distribution to the bin directory all would be well. Contact me if you
need one – topeakinniyi@shepherdhill.biz.

Ride on the elephant.  Kindle contact for further clarification.


Tope Akinniyi
Lagos, Nigeria.