Nambi's FreeSoftware

postgresql-8.1.0
------------------------------------------
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).
Admin binaries are now in /usr/sbin/
Contrib binaries in /usr/lib/postgreql/bin
cygrunsrv is not required anymore, pg_ctl has now the service functions
included. cygserver must run as service, CYGWIN must contain server.

Runtime requirements:
crypt-1.1-1
cygwin-1.5.18-1
libiconv2-1.9.2-1
libintl3-0.14.1-1
libncurses8-5.4-2
libreadline6-5.0-4
openssl-0.9.8-2
zlib-1.2.3-1
perl-5.8.7 (optional, for pgperl. only this version!)
python-2.4.x (optional, for pgpython. every 2.4 version will work)

Build requirements: (additionally)
gcc-core 3.3.x or newer (libtool-devel-1.5.10 not required)
binutils-20040725-2 or newer
make-3.80-1 or newer
fileutils-4.1 or newer
sed-4.1.2-1 or newer
m4-1.4-1

OpenSP-1.5.1-1 (for man rebuilding)
docbook-xml42 (for man rebuilding)
cpan SGMLSpm (for man rebuilding)
docbook2X (for man rebuilding)
docbook2man-sgmlspl (for man rebuilding)
Sun JDK 1.4.x (optional, for pgjava only)
Ant 1.5 (?)

Canonical homepage:
http://www.postgresql.org

Canonical download:
ftp://ftp.postgresql.org

------------------------------------

Build instructions:
unpack postgresql---src.tar.bz2
if you use setup to install this src package, it will be
unpacked under /usr/src automatically
cd /usr/src
./postgresql--.sh all

This will create:
/usr/src/postgresql--.tar.bz2
/usr/src/postgresql---src.tar.bz2

Or use './postgresql--.sh prep' to get a patched source directory

-------------------------------------------

Files included postgresql:

/etc/rc.d/init.d/postgresql
/usr/bin/clusterdb.exe
/usr/bin/createdb.exe
/usr/bin/cygpq.dll
/usr/bin/dropdb.exe
/usr/bin/ecpg.dll
/usr/bin/ecpg.exe
/usr/bin/ecpg_compat.dll
/usr/bin/pgtypes.dll
/usr/bin/pg_config.exe
/usr/bin/pg_dump.exe
/usr/bin/pg_dumpall.exe
/usr/bin/psql.exe
/usr/sbin/createlang.exe
/usr/sbin/createuser.exe
/usr/sbin/ipcclean
/usr/sbin/initdb.exe
/usr/sbin/droplang.exe
/usr/sbin/dropuser.exe
/usr/sbin/pg_controldata.exe
/usr/sbin/pg_ctl.exe
/usr/sbin/pg_resetxlog.exe
/usr/sbin/pg_restore.exe
/usr/sbin/postgres.exe
/usr/sbin/postmaster.exe
/usr/sbin/vacuumdb.exe
/usr/lib/postgresql/.dll
/usr/lib/postgresql/plperl.dll
/usr/lib/postgresql/plpgsql.dll
/usr/lib/postgresql/plpython.dll
/usr/lib/postgresql/autoinc.dll
/usr/lib/postgresql/btree_gist.dll
/usr/lib/postgresql/chkpass.dll
/usr/lib/postgresql/cube.dll
/usr/lib/postgresql/dblink.dll
/usr/lib/postgresql/dbsize.dll
/usr/lib/postgresql/fti.dll
/usr/lib/postgresql/fuzzystrmatch.dll
/usr/lib/postgresql/insert_username.dll
/usr/lib/postgresql/int_aggregate.dll
/usr/lib/postgresql/isbn_issn.dll
/usr/lib/postgresql/lo.dll
/usr/lib/postgresql/ltree.dll
/usr/lib/postgresql/misc_utils.dll
/usr/lib/postgresql/moddatetime.dll
/usr/lib/postgresql/noup.dll
/usr/lib/postgresql/pending.dll
/usr/lib/postgresql/pgcrypto.dll
/usr/lib/postgresql/pgstattuple.dll
/usr/lib/postgresql/pg_trgm.dll
/usr/lib/postgresql/refint.dll
/usr/lib/postgresql/rtree_gist.dll
/usr/lib/postgresql/seg.dll
/usr/lib/postgresql/string_io.dll
/usr/lib/postgresql/tablefunc.dll
/usr/lib/postgresql/timetravel.dll
/usr/lib/postgresql/tsearch.dll
/usr/lib/postgresql/tsearch2.dll
/usr/lib/postgresql/user_locks.dll
/usr/lib/postgresql/_int.dll
/usr/lib/postgresql/bin/dbf2pg.exe
/usr/lib/postgresql/bin/findoidjoins.exe
/usr/lib/postgresql/bin/fti.pl
/usr/lib/postgresql/bin/make_oidjoins_check
/usr/lib/postgresql/bin/oid2name.exe
/usr/lib/postgresql/bin/pgbench.exe
/usr/lib/postgresql/bin/pg_autovacuum.exe
/usr/lib/postgresql/bin/pg_dumplo.exe
/usr/lib/postgresql/bin/vacuumlo.exe
/usr/share/doc/Cygwin/postgresql-.README (this file)
/usr/share/doc/postgresql-/* (official docs)
/usr/share/doc/postgresql-/contrib/* (contrib docs)
/usr/share/locale/*/LC_MESSAGES/*.mo (nls files for all backends)
/usr/share/postgresql/conversion_create.sql
/usr/share/postgresql/information_schema.sql
/usr/share/postgresql/pg_hba.conf.sample
/usr/share/postgresql/pg_ident.conf.sample
/usr/share/postgresql/pg_service.conf.sample
/usr/share/postgresql/postgres.bki
/usr/share/postgresql/postgres.description
/usr/share/postgresql/postgresql.conf.sample
/usr/share/postgresql/psqlrc.sample
/usr/share/postgresql/recovery.conf.sample
/usr/share/postgresql/sql_features.txt
/usr/share/postgresql/system_views.sql
/usr/share/postgresql/contrib/* (contrib data)
/usr/share/postgresql/timezone/* (all supported timezones)
/usr/include/ecpgerrno.h
/usr/include/ecpglib.h
/usr/include/ecpgtype.h
/usr/include/ecpg_informix.h
/usr/include/libpq/libpq-fs.h
/usr/include/libpq-fe.h
/usr/include/pgtypes_date.h
/usr/include/pgtypes_error.h
/usr/include/pgtypes_interval.h
/usr/include/pgtypes_numeric.h
/usr/include/pgtypes_timestamp.h
/usr/include/pg_config.h
/usr/include/pg_config_manual.h
/usr/include/pg_config_os.h
/usr/include/postgres_ext.h
/usr/include/sql3types.h
/usr/include/sqlca.h
/usr/include/libpq/*.h
/usr/include/postgresql/*.h
/usr/lib/libecpg.a
/usr/lib/libecpg_compat.a
/usr/lib/libpgtypes.a
/usr/lib/libpostgres.a
/usr/lib/libpq.a
/usr/lib/libpqport.a
/usr/lib/postgresql/pgxs/config/install-sh
/usr/lib/postgresql/pgxs/config/mkinstalldirs
/usr/lib/postgresql/pgxs/src/Makefile.global
/usr/lib/postgresql/pgxs/src/Makefile.port
/usr/lib/postgresql/pgxs/src/Makefile.shlib
/usr/lib/postgresql/pgxs/src/makefiles/pgxs.mk
/usr/lib/postgresql/pgxs/src/nls-global.mk
/usr/lib/postgresql/pgxs/src/utils/dllinit.o

------------------

Port Notes:

---------- postgresql-8.0.3-1 + 8.1beta1-1 -----------

Fixed IPC problems.
Moved pg_dumpall back to /bin for pg_dump to work.
Added /usr/lib/libpostgres.a manually.
Added init.d script:
SOCKETDIR=/tmp/postgresql

---------- postgresql-8.0.0-1 ----------- (NEVER RELEASED)

Still IPC problems

---------- postgresql-8.0.0beta5-1 ----------- (NEVER RELEASED)

Builds OOTB, all patches accepted upstream.
Still IPC problems

---------- postgresql-8.0.0beta4-1 ----------- (NEVER RELEASED)

- pg_ctl register fixed.
- unsolvable concurrency problems since beta3, which are
probably related to cygserver bugs?

---------- postgresql-8.0.0cvs-2 ----------- (NEVER RELEASED)

- Removed broken postinstall and preremove scripts.
Save and restore will have to be done manually.
- Layout: Seperated admin (/usr/sbin) from user applications (/usr/bin)
and contrib bins (usr/lib/postgresql/bin)
- plperl and tablespace was fixed upstream

---------- postgresql-8.0.0cvs-1 -----------

Maintainership changed from Jason Tishler to Reini Urban.
nls and charset translation support (not only -E Latin1). Building via gbs.
Improved IPC and signals handling. Up to 50 max_connections
possible, but 5 are still recommended. max 63 hardlimit on cygwin.
Added new postinstall and preremove recipes to save and restore the
old databases into /var/tmp/db.out.gz.
Added contrib binaries, libs, data and docs. (Wrong. was in 7.4.5 also)

TODO: add /usr/lib/postgresql/ to the postmaster service path.
add init.d service installer/uninstaller as with clamav.
fix tablespace breakage. (symlink => hardlink)

---------- postgresql-7.4.5-1 -----------

First cygserver release, ipc-daemon2 not supported anymore.
Note: If you haven't uninstalled ipc-daemon2, it will be built against it,
and will therefore fail when cygserver is running.


Cygwin port maintained by: Reini Urban
Please report problems, suggestions, etc. dependent on their nature to one of
the following:

pgsql-cygwin@postgresql.org
cygwin@cygwin.com

======================================================================

The official doc/FAQ_CYGWIN is not very verbose. Until this is
fixed, we add some information here.

For the impatient: install as service (hard)

# save old database (optional)
pg_dumpall -g /tmp/$$pgdump-globals.sql
pg_dumpall > /tmp/$$pgdump.sql
# adjust the settings in /etc/rc.d/init.d/postgresql
nano /etc/rc.d/init.d/postgresql
sysbash # /etc/rc.d/init.d/postgresql initdb
/etc/rc.d/init.d/postgresql install
/etc/rc.d/init.d/postgresql start

For the impatient: install as user (easy)

cygrunsrv -S cygserver
initdb -D /usr/share/postgresql/data
pg_ctl start -D /usr/share/postgresql/data -l /var/log/postgresql.log
createdb
psql

Abstract:

README for the Cygwin PostgreSQL 8.x distribution.

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).

The Cygwin PostgreSQL package is very monolithic compared its RPM
counterparts and its contents is roughly analogous to the aggregation
of the following PostgreSQL RPMs:

postgresql-contrib
postgresql-devel
postgresql-docs
postgresql-jdbc
postgresql-libs
postgresql-perl
postgresql-python
postgresql-server

Whats new:

Win32 Native Server, Savepoints, Point-In-Time Recovery, Tablespaces,
Improved Buffer Management, CHECKPOINT, VACUUM, Change Column Types,
New Perl Server-Side Language, CSV support in COPY.

See http://candle.pha.pa.us/main/writings/pgsql/sgml/release.html#RELEASE-8-0
for the full list of major changes with 8.0, and the steps for migration to
version 8.0.

Deprecated features:

* The 8.1 release will remove the function to_char(interval).

* By default, tables in PostgreSQL 8.0 and earlier are created with
OIDs. In the next release, this will not be the case: to create a
table that contains OIDs, the WITH OIDS clause must be specified or
the default_with_oids configuration parameter must be enabled. Users
are encouraged to explicitely specify WITH OIDS if their tables
require OIDs for compatibility with future releases of PostgreSQL.

Recommended usage:

It's very easy to install, but we don't recommend using the
cygwin version of PostgreSQL for "Production" quality databases, nor
high load levels. The cygwin emulation layer introduces a few
limitations, namely the lack of being able to tune PostgreSQL to the
same performance levels of a Unix system or the new Native WinNT built,
and we're also not sure how well the data integrity features of
Windows + cygwin + PostgreSQL work in the event of a system crash,
hardware failure, etc.

It should be suitable for development usage however.

Install:

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/XP. Note that this installation type is very similar to the normal
Unix installation with just some Cygwin/Windows variations. You should
choose the type which best meets your needs and/or is constrained by your
platform.

Regardless of the installation type, the first step is to add "server" to
your CYGWIN environment variable setting and start cygserver. See the
cygserver README:

/usr/share/doc/cygwin/cygserver.README

for more details. If the CYGWIN variable is not set correctly and/or
cygserver is not running then initdb and postmaster will fail to run
properly.

The following is the basic Cygwin PostgreSQL installation procedure (skip if
using the NT services installation below):

1. Install and configure cygserver:

$ cygserver-config

2. Start cygserver:

$ cygserver &

3. Initialize PostgreSQL:

$ initdb

4. Start the PostgreSQL postmaster:

$ postmaster &

5. Connect to PostgreSQL:

$ psql template1

The following is the NT services Cygwin PostgreSQL installation procedure
(with footnotes designated by "[n]"):

1. Install and configure cygserver as a NT service:

# cygserver-config

2. Create the "postgres" user account:

# net user postgres $password /add /fullname:postgres /comment:'PostgreSQL user account' /homedir:"$(cygpath -w /home/postgres)" # [11]
# mkpasswd -l -u postgres >>/etc/passwd

3. Grant the "postgres" user the "Log on as a service" user right:

# cmd /c secpol.msc # [3] [4] [5] [12]

4. Install postmaster as a NT service:

# cygrunsrv --install postmaster --path /usr/bin/postmaster --args "-D /var/postgresql/data -i" --dep cygserver --termsig INT --user postgres --shutdown # [6]

5. Create the PostgreSQL data directory:

# mkdir -p /var/postgresql/data

6. Change ownership of the PostgreSQL data directory:

# chown postgres /var/postgresql/data

7. Start cygserver:

# cygrunsrv -S cygserver # [7]

8. Initialize PostgreSQL (*when running under the "postgres" account*):

$ initdb -D /var/postgresql/data

9. Start postmaster:

# cygrunsrv -S postmaster # [7]

10. Verify postmaster is running:

# cygrunsrv -Q postmaster [13]

11. Connect to PostgreSQL:

# psql -U postgres template1 # [8] [9]

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 under bash.
[2] The "$" prompt indicates running as the "postgres" user under bash.
Log in as "postgres" or use ssh to emulate Unix's "su" command.
[3] Sorry, but I don't know of a generally available command line way of
setting user rights.
[4] On Windows 2000 and XP Pro, this starts the "Local Security Settings"
applet. On Windows NT 4.0, start User Manager, select Policies, and then
select User Rights...
[5] See http://support.microsoft.com/default.aspx?scid=KB;en-us;q259733 for
a Microsoft KB article explaining how to configure user rights.
[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.
[9] One can use PostgreSQL's createuser command or set PGUSER to obviate
the need to specify "-U postgres" on the psql command line.
[11] $password is the password for the postgres user account.
[12] On Windows XP Home, there is no built in way to assign user rights --
use editrights instead:

$ editrights -u postgres -a SeServiceLogonRight

[13] If postmaster fails to start, then examine /var/log/postmaster.log
and/or the NT Event Log.

Source:

As configured, the PostgreSQL source builds OOTB under Cygwin with
some minor required build patches.

Build:

This distribution has been configured as follows:

./configure --enable-nls --with-CXX --with-openssl --with-perl --with-python
--prefix=/usr --sysconfdir=/etc --datadir=/usr/share
--mandir=/usr/share/man --docdir=/usr/share/doc/postgresql--

See postgresql--.sh and postgresql--.patch in the source package for
the exact build recipe for configuring, making, and packaging this distribution.

Test:

On NT/2000/XP, Cygwin PostgreSQL passes all other regression tests
with the serial_schedule and parallel_schedule. postgresql-8.x will
work with typically 40 max_connections determined automatically by
initdb, maximal with 63 (a cygwin limitation).

On 9X/ME, it has been reported that Cygwin PostgreSQL hangs at random
places during the regression test. Unfortunately, at the time of this
writing, no one has tracked down the root cause of these hangs.
(cygserver most likely)

Issues:

1. make check can generate spurious regression test failures due to
overflowing the listen() backlog queue which generates connection
refused errors. One can workaround this (Winsock) limitation, by running
the regression test as follows:

$ make MAX_CONNECTIONS=5 check

2. Cygwin PostgreSQL only supports the "C" locale because Cygwin itself
does not fully support locales. This implies the LANG environment variable
must be unset or set to "C". Otherwise, initdb will fail.

3. Cygwin PostgreSQL can fail to start or not function properly if certain
files and directories have incorrect permissions. The following usually
solves these kinds of problems:

$ chmod a+rwx /tmp
$ chmod a+rx /usr/bin /usr/bin/*
$ chmod a+rw /var/log # could adversely affect other daemons

4. Problems starting as service?
This is a common problem typically related to wrong file permissions
to the PGDATA dir /usr/share/postgresql/data, the pidfile
/usr/share/postgresql/postgresql.pid and the logfile
/var/log/postgresql.log.
See /etc/rc.d/init.d/postgresql, the logfile, start a sysbash
and examine the permissions there.
Google for "cygwin sysbash"

5. It may be necessary to increase cygserver's system wide maximum number of
semaphores (SEMMNS). This change is affected by editing /etc/cygserver.conf,
setting the following parameter:

kern.ipc.semmns 100

and restarting cygserver. Otherwise, PostgreSQL can fail to operate properly.
For example, initdb will fail if postmaster is already running.

6. For correct a service shutdown on system shutdown it is recommended to
use the settings from /etc/rc.d/init.d/postgresql.
i.e. adding --termsig INT --shutdown to cygrunsrv --install.
The TERM signal does a smart shutodown, which waits for all client to disconnect.
The INT signal does a forced shutodown, which closes all client connections.

Homepage:

The primary PostgreSQL web site is:

http://www.postgresql.org

The 8.0.x Documentation is also online at

http://candle.pha.pa.us/main/writings/pgsql/sgml/

______________________________________________________

For starting postgres in cygwin

1. export CYGWIN=server
2. cygrunsrv -S cygserver
3. export PGDATA="pgdata directory"
4. pg_ctl start
5. pg_ctl stop

creating database
1. createdb brokerdb
2. psql brokerdb

Wei Gao's blog

For the longest time, my development relational database of choice has been PostgreSQL. It offers the features that I care about (the simple stuff: create table, select from where order by group by, transactions, JDBC drivers) on the platforms that I use the most often (GNU/Linux, Windows/Cygwin) under a BSD license.

What I like the most is the fact that it comes with the Red Hat distribution since the early days. For the last few years, a Cygwin compiled version also showed up as part of the Cygwin net distribution.

The Cygwin version, however, was broken earlier this summer. And I was without a database on Windows for a while. Finally last week, Reini Urban announced the availability of PostgreSQL 8.0.4 and 8.1.0 for Cygwin.

Setting up PostgreSQL as a Windows service has always been a tricky endeavor. The new release offers some relief by providing an init script that can simplify the process. According to the release notes, it could be as simple as:

/etc/rc.d/init.d/postgresql initdb
/etc/rc.d/init.d/postgresql install
/etc/rc.d/init.d/postgresql start

While this is essentially true, there are some hurdles that I have to jump over to have everything up and running. Let me just say that the script can be improved upon. Here's my Cygwin PostgreSQL 8.1.0 setup first impression:

* First of all, the CYGWIN environment variable needs to contain the word server. (I have CYGWIN=ntsec server.)
* The cygserver service needs to be installed already. (Running cygserver-config once will do it.)
* The initdb task will create the database template in /usr/share/postgresql/data and create a directory /tmp/postgresql for runtime use.
* After a fresh installation, the directory /usr/share/postgresql already contains some files and subdirectories. initdb won't like it if /usr/share/postgresql/data already exists and is non-empty or if /tmp/postgresql exists. (A failed run of initdb will create such a situation.)
* There are plenty of file/directory permission problems that would cause the install or the start steps to fail. All because the service is run as Local System, not the logged in user. Files and directories that need to open up permission include
o /usr/sbin/postgres.exe (needs chmod ugo+rx),
o /usr/share/postgresql/data (needs chmod ugo+rx),
o /tmp/postgresql (needs chmod ugo+rwx),
o /usr/share/postgresql/data/global (needs chmod ug+rwx.)
* The /etc/rc.d/init.d/postgresql script contains a chunk of code to ensure that the CYGWIN environment variable contains the word server. But it went too far. This caused the install task to fail with an error message saying "CYGWIN must contain server" even though my CYGWIN already does.
* The log file at /var/log/postgresql.log provided valuable information while I'm trouble shooting my setup.
* The psql (PostgreSQL's interactive SQL command line tool) seems to look for a UNIX domain socket to connect to at the wrong place (/tmp/.s.PGSQL.5432 instead of /tmp/postgresql/.s.PGSQL.5432.) I have to connect to the TCP socket by running psql -h localhost.
* The bootstrap user Id for PostgreSQL is SYSTEM. So to add my own login to PostgreSQL I need to run /usr/sbin/createuser -U SYSTEM

Here's the portion of /etc/rc.d/init.d/postgresql I modified

# check for CYGWIN containing server
if ! echo $CYGWIN | grep -q server
then
echo "ERROR CYGWIN must contain server for cygserver to work properly"
exit 1
fi

The if line used to say

if [ -n "${CYGWIN%server}" ]

It sounds like a lot written down, but the actual trial and error of the setup is pretty easy and It took me less then 30 minutes to figure all of these out. What is important is that at the end, I have a functional PostgreSQL server running as a Windows service again:

[weiqi@gao] $ psql -h localhost
Welcome to psql 8.1.0, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

weiqi=# create table people (first_name varchar, last_name varchar);
CREATE TABLE
weiqi=# insert into people values ('Weiqi', 'Gao');
INSERT 0 1
weiqi=# select * from people;
first_name | last_name
------------+-----------
Weiqi | Gao
(1 row)

weiqi=# \q
[weiqi@gao] $

Postgresql Silent Install using MSI