Database-SQL-RDBMS HOW-TO document for Linux
(PostgreSQL
Object Relational Database System)
Al Dev (Alavoor Vasudevan)
alavoor@yahoo.com
v26.0, 24 June 2000
This document is a "practical guide" to very quickly setup a SQL
Database engine and front end tools on a Unix system.
It also discusses the International standard language ANSI/ISO SQL and reviews
the merits/advantages of the SQL database engine developed by the
world-wide internet in an "open development" environment.
It is about HOW-TO setup a next generation Object Relational SQL Database
"PostgreSQL" on Unix system which can be used as a Application Database
Server or as a Web Database Server.
PostgreSQL attempts to implement current and future International ISO/ANSI SQL standards.
This document also gives information on the database interface programs like Front
End GUIs, RAD tools (Rapid Application Development), ODBC, JDBC
drivers, "C", "C++", Java, Perl programming interfaces and Web
Database Tools.
Information given here applies to all Unix/Windows NT
platforms and to all other SQL databases.
It will be very useful for
people who are new to Databases, SQL language and PostgreSQL.
This
document also has SQL tutorial, SQL syntax which would be very helpful
for beginners.
Experienced people will find this document as a useful
reference guide. For students, the information given here will enable
them to get the source code for PostgreSQL relational database system,
from which they can learn as to how a RDBMS SQL database engine is
created.
Table of Contents
1. Introduction
2. Laws of Physics apply to Software!
3. What is PostgreSQL ?
3.1 White Paper
4. Which one? PostgreSQL or MySQL ?
5. Where to get it ?
6. PostgreSQL Quick-Installation Instructions
6.1 Install and Test
6.2 PostgreSQL RPMs
6.3 Maximum RPM
6.4 Examples RPM
6.5 Testing PyGreSQL - Python interface
6.6 Testing Perl - Perl interface
6.7 Testing libpq, libpq++ interfaces
6.8 Testing Java interfaces
6.9 Testing ecpg interfaces
6.10 Testing SQL examples - User defined types and functions
6.11 Testing Tcl/Tk interfaces
6.12 Testing ODBC interfaces
6.13 Testing MPSQL Motif-worksheet interfaces
6.14 Verification
6.15 Emergency Bug fixes
7. Quick Start Guide
7.1 Creating, Dropping, Renaming Database
7.2 Creating, Dropping users
7.3 Creating, Dropping Groups
7.4 Create, Edit, Drop a table
7.5 Create, Edit, Drop records in a table
7.6 Switch active Database
7.7 Backup and Restore database
7.8 Security of database
7.9 Online help
7.10 PostgreSQL Documentation
8. PostgreSQL Supports Extremely Large Databases greater than 200 Gig
9. How can I trust PostgreSQL ? Regression Test Package builds customer confidence
10. Security of Database
10.1 User Authentication
10.2 Host-Based Access Control
10.3 Authentication Methods
10.4 Access Control
10.5 Secure TCP/IP Connection via SSH
10.6 Kerberos Authentication
11. GUI FrontEnd Tool for PostgreSQL (Graphical User Interface)
12. Interface Drivers for PostgreSQL
12.1 ODBC Drivers for PostgreSQL
12.2 UDBC Drivers for PostgreSQL
12.3 JDBC Drivers for PostgreSQL
12.4 Java for PostgreSQL
13. Perl Database Interface (DBI) Driver for PostgreSQL
13.1 Perl 5 interface for PostgreSQL
13.2 Perl Database Interface DBI
- 13.2.1 WHAT IS DBI ?
- 13.2.2 DBI driver for PostgreSQL DBD-Pg-0.89
- 13.2.3 Technical support for DBI
- 13.2.4 What is DBI, DBperl, Oraperl and *perl?
- 13.2.5 DBI specifications
- 13.2.6 Compilation problems or "It fails the test"
- 13.2.7 Is DBI supported under Windows 95 / NT platforms?
- 13.2.8 Is DBI any use for CGI programming?
- 13.2.9 How do I get faster connection times with DBD Oracle and CGI?
- 13.2.10 How do I get persistent connections with DBI and CGI?
- 13.2.11 ``When I run a perl script from the command line, it works, but, when I run it under the httpd, it fails!' ' Why?
- 13.2.12 Multi-threading with DBI?
- 13.2.13 How can I invoke stored procedures with DBI?
- 13.2.14 How can I get return values from stored procedures with DBI?
- 13.2.15 How can I create or drop a database with DBI?
- 13.2.16 How are NULL values handled by DBI?
- 13.2.17 What are these func methods all about?
- 13.2.18 Commercial Support and Training
13.3 Testing Perl interface
14. PostgreSQL Management Tools
14.1 PGACCESS - A GUI Tool for PostgreSQL Management
14.2 Windows Interactive Query Tool for PostgreSQL (WISQL or MPSQL)
14.3 Interactive Query Tool (ISQL) for PostgreSQL called PSQL
14.4 MPMGR - A Database Management Tool for PostgresSQL
15. CPUs for PostgreSQL
16. Setting up multi-boxes PostgreSQL with just one monitor
17. Web-Application-Servers for PostgreSQL
17.1 Lutris Corp "Enhydra"
17.2 Zope
18. Applications and Tools for PostgreSQL
18.1 PostgreSQL 4GL for web database applications - AppGEN Development System
18.2 WWW Web interface for PostgresSQL - DBENGINE
18.3 Apache Webserver Module for PostgreSQL - NeoSoft NeoWebScript
18.4 HEITML server side extension of HTML and a 4GL language for PostgreSQL
18.5 America On-line AOL Web server for PostgreSQL
18.6 Problem/Project Tracking System Application Tool for PostgreSQL
18.7 Convert dbase dbf files to PostgreSQL
18.8 Convert Microsoft Access MDB database files to PostgreSQL
Inhalt
1. Introduction
The purpose of this document is to provide comprehensive list of
pointers/URLs to quickly setup PostgreSQL and also to advocate the
benefits of Open Source Code system like PostgreSQL, Linux.
Each and every computer system in the world needs a database to
store/retrieve the information.
The primary reason you use the
computer is to store, retrieve and process information and do all
these very quickly, thereby saving you time. At the same time, the
system must be simple, robust, fast, reliable, economical and very
easy to use.
Database is the most VITAL SYSTEM as it stores mission
critical information of every company in this world. Each and every
industry in this world needs a database system. Industries like
telecom, automobile, banks, airlines, etc.. will not function
efficiently without a database system.
The most popular database
systems are based on the International Standard Organisation (ISO) SQL
specifications and ANSI SQL (American) standards. The current
specifications widely used in the industry are ISO/ANSI SQL 1992.
Upcoming standard is the SQL 1998/99 which is also called SQL-3 is
still under development.
Popular database like Oracle, Sybase and
Informix systems are based on these standards or are trying to
implement these standards.
Without a standard like ANSI/ISO SQL, it would be very difficult for
the customer to develop a application once and run on all the database
systems. End user wants to develop an application ONCE using ISO SQL,
ODBC, JDBC and deploy on all variety of database systems in the world.
The world's most popular FREE Database which implements some of the
ISO SQL, ANSI SQL/98, SQL/92 and ANSI SQL/89 RDBMS is PostgreSQL.
PostgreSQL is next generation Object relational database and is
targeting on full compliance of SQL standards like ISO/ANSI SQL.
PostgreSQL is the only free RDBMS in the world which supports Object
databases and SQL. This document will tell you how-to install the
database, how to set up the Web database, application database, front
end GUIs and interface programs.
It is strongly advised that you MUST
write your database applications 100 % compliant to standards of
ISO/ANSI SQL, ODBC, JDBC so that your application is portable across
multiple databases like PostgreSQL, Oracle, Sybase, Informix etc.
You get the highest quality, and lot many features with PostgreSQL as
it follows 'Open Source Code development model'. Open Source Code
model is the one where the complete source code is given to you and
the development takes place on the internet by a extremely vast
network of human brains. Future trend shows that most of the software
development will take place on the so called "Information Super-
Highway" which spans the whole globe. In the coming years, internet
growth will be explosive which will further fuel rapid adoption of
PostgreSQL by the industry.
By applying the principles of statistics, mathematics and science to
software quality, you get the best quality of software only in a 'Open
Source Code System' like PostgreSQL, wherein the source code is open
to a very vast number of human brains inter-connected by the
information super-highway.
Greater the number of human brains
working, the better will be the quality of software. Open Source Code
model will also prevent RE-INVENTION OF WHEELS, eliminates DUPLICATION
OF WORK and will be very economical, saves time in distribution and
follows the modern economic laws of optimizing the national and global
resources.
Once a software work is done by others, then you DO NOT
need to re-do that again. You will not be wasting your valuable time
on something which had already been WELL DONE. Your time is extremely
precious and it must be utilized efficiently, because you have only 8
hours a day for doing work. As we will be entering the 21st century,
there will be a change in the way that you get software for your use.
Everybody will give first preference for the open source softwares
like PostgreSQL, Linux.
If you buy binaries, you will not get any equity and ownership of
source code. Source code is a very valuable asset and binaries have no
value.
Buying software may become a thing of the past. You only need
to buy good hardware, it is worth spending money on the hardware and
get the software from internet. Important point is that it is the
computer hardware which is doing bulk of the work.
Hardware is the
real work horse and software is just driving it. Computer hardware is
so much more complex that only 6 nations in the world so far have
demonstrated the capability of designing and manufacturing computer
chips/hardware.
Design and manufacturing of computer chips is a
advanced technology. It is a very complex process, capital intensive,
requires large investments in plant and production machines which deal
with 0.18 micron (even smaller than 0.18) technology. On a single
small silicon chip millions of transistors/circuits are densely
packed.
Companies like Applied Material, AMD, Intel, Cyrix, Hitachi,
IBM and others spent significant number of man-years to master the
high-technology like Chip Design, Micro-electronics and Nano-
electronics.
Micro means (one-millionth of meter 10^-6), Nano means
(one-billionth of meter 10^-9). Current technology uses microelectronics
of about 0.35 micron using aluminum as conductors and 0.25
micron sizes using copper as conductors of electrons.
In near future
the technology of 0.10 micron with copper and even nano-electronics
will be used to make computer chips. Aluminum conductors will be
phased out by copper on computer chips, as copper is a better
conductor of electrons.
In photolithography process extreme
ultraviolet, X-ray or electron-beam techniques will be used to etch
circuits for feature size less than 0.15 micron. In about 20 years
from now, silicon chips will be phased out by molecular computers and
bio chips which will be billions of times faster than silicon chips.
Molecules are a group of atoms. And atoms are tiny particles which
makes up everything that you see in this world.
Molecular computers
will use the molecules of matter as ultra-fast electronic on/off
switches. When the switch is ON it indicates 1, and when it is OFF it
indicates 0. All the computer programs in this world are based on
binary (numbers 1 and 0). Table below shows the progress and future
advancement trends of computer chips.
Advancement of chip capabilities in future
********************************************
| Item/Year |
1997 |
1999 |
2001 |
2003 |
| Feature size(micron) |
0.25 |
0.18 |
0.15 |
0.13 |
| Min Operating Voltage |
1.8-2.5 |
1.5-1.8 |
1.2-1.5 |
1.2-1.5 |
| Max power dissipation |
70 |
90 |
110 |
130 |
| On-chip frequencey (MHz) |
750. |
1,250 |
1,500 |
2,100 |
| DRAM capacity |
256 MB |
1 GB |
2 GB |
4 GB |
As you can see, it is hardware that is high technology and important
and software is labor intensive but is a less difficult technology.
On other hand, each and every country in the world develops/makes
software.
In fact, any person in this world with a small low-cost PC
can write software.
Databases like Oracle, Informix, Sybase, IBM DB2 (Unix) are written
using the "C" language and binaries are created by compiling the
source code and then they are shipped out to customers.
Oracle,
Sybase, Informix databases are 100 % "C" programs!!
Since a lot of work had been done on PostgreSQL for the past 14 years,
it does not make sense to re-create from scratch another database
system which satisfies ANSI/ISO SQL.
It will be a great advantage to
take the existing code and add missing features or enhancements to
PostgreSQL and start using it immediately.
Prediction is that demand for "Internet products" like PostgreSQL will
grow exponentially as it is capable of maintaining a high quality, low
cost, extremely large user-base and developer-base.
Those nations
which do not use the 'Internet products' will be seriously missing
"World-wide Internet Revolution" and will be left far behind other
countries.
The reason is "Internet" itself is the world's LARGEST
"software company" and is a large software "power house"!
Inhalt
2. Laws of Physics apply to Software!
In this chapter, it will be shown how science plays a important role
in the creation of various objects like software, this universe, mass,
atoms, energy and even yourself!
This chapter also shows why
knowledge of science is very important before you start using the
products of science.
The golden rule is - "You MUST not use a product without understanding
how it is created!!"
This rule applies to everything - database
sytems, computer system, operating system, this universe and even your
own human body!
It means that you should have complete source code and
information about the system.
It is important to understand how human
body and atoms inside human body works since humans are creating
PostgreSQL, MS Windows95 etc..
Creation is a very important step.
Persons who are using the objects
of science must know how it is created. This applies to even computer
systems and PostgreSQL.
A majority of people do not have knowledge of
science and hence do not know how systems like MS Windows NT/95,
Oracle, human body and this universe are created. A vast majority of
people do not know what made the universe and MS Windows 95/NT and
what is inside it.
Complex systems are built from very simple basic
building blocks like - millions of universes are created, each
universe in turn has millions of super-clusters, each super-cluster
has millions of galaxies, each galaxy has millions of stars, some
stars system have many planets, each planet in turn is made up
billions of atoms.(In the history of this world, only one universe was
created by a man in ancient India eons ago, but no other case had been
reported in the modern history. Nations around the world are trying to
create a universe).
Creating a universe is a much more advanced
technology and is more advanced than the atomic bomb which was dropped
on Hiroshima and Nagasaki causing horrible destruction.
Modern
nuclear weapons are so tiny and powerful that if such a single nuclear
bomb is dropped in pacific ocean then it can completely vaporise the
planet earth! The total variety of weapons are infinity!!
Nuclear
weapons and other more powerful divine weapons were used in the battle
field in ancient India! Nobody believed Albert Eienstein (a scientist
of 1900's) when he said nuclear weapons can be made which can vaporise
big cities.
And today nobody believes that man can create a universe.
Software like MS Windows 95 is created simply by "C" and assembler
language programs which simply uses 1 and 0 and universes like ours
are created simply by dashing TWO dissimilar but proper of combination
of tiny atomic particles of other dimensions. (Something interesting
happened just before dashing of tiny particles)
A human body is
created by dashing two dissimilar but proper combination of tiny
cells!! (Something interesting happened just before dashing of tiny
cells) Humans inherited the properties of this universe.
The universe
you are currently living in was NOT there - all the atoms inside the
universe was not there and not even TIME was existing!! Baby universe
was born during big bang and started expanding and kept growing. Even
today our universe is still expanding!!
A person from another
universe by name 'Brahma' created this universe you are currently
living in. It is indeed possible for man to create a new universe.
Total number of universes that can be created is INFINITY and
similarly total number of operating systems that can be created is
also infinity!! Infinite number universes and infinite variety of
multi-dimensional atoms collapse down into few primary-dimensional-
universe. Very advanced mathematical equations support this theory.
The laws of science and statistics favour the open-source code system
like PostgreSQL and Linux. As the internet speed is increasing
everyday, and internet is becoming more and MORE reliable, the open-
source code system will gain very rapid momentum.
And, if rules of
statistics and laws of physics are correct, awareness of science grows
and when IGNORANT people start learning science then the closed
source-code systems will eventually vanish from this planet.
Developing a project like PostgreSQL requires resources like energy
and time, hence PostgreSQL is a product of energy and time. Since
energy and time can be explained only by science, there is a direct
co-relation between physics and software projects like PostgreSQL,
Linux.
Laws of science (Physics) applies everywhere and at all the
times, to anything that you do, even while you are developing the
software projects.
Physics is in action even while you are talking (sound waves), walking
(friction between ground and your feet), reading a book or writing
software. Every science in this world has a deep root in mathematics,
including PostgreSQL. PostgreSQL uses 'Modern Algebra' which is a tiny
branch of mathematics.
Modern algebra deals with 'Set Theory',
'Relational Algebra', science of Groups, Rings, Collections, Sets,
Unions, Intersections, Exclusions, Domains, Lists, etc...
The software like PostgreSQL is existing today because of the energy
and time.
And mass and energy are ONE and the SAME entity. The fact
that mass and energy are same was unknown to people 100 years ago!
And even today it is unknown to world population that internet is the
largest software "power house" and the largest "software company" in
the world!
Cells in the human brains consume energy while processing (creating
software), by converting the chemical energy from food into electrical
and heat energy.
Even while you are reading this paragraph, the cells
in your brain are burning out the fuel and are using tiny amounts of
energy.
All of these implies that human brain is a thermodynamic heat
engine. Because human brain is a thermodynamic engine, the laws of
thermodynamics applies to brain and hence thermodynamics has indirect
effects on software like PostgreSQL.
There can be infinite number of colors, computer langauages, computer
chip designs and theories but there CANNOT be ONE SINGLE PERFECT
color, computer language, design or system!
What you can have is only
a NEAR PERFECT color(wavelength), system, database, or theory!
Nature is like a KALIEDOSCOPE - there are infinite number of
dimensions, infinite variety particles of other dimensions but they
all combine into very few primary dimensions and vice-versa.
By combining the energies of millions of people around the world via
internet it is possible to achieve a NEAR PERFECT system (including a
database software system).
Individually, the energy of each person
will be minute, but by networking a large number of people, the total
energy will be huge which can be focused on a project to generate a
near perfect system.
The energy is measured in Joules, kiloJoules or kilograms of mass, and
time is measured in seconds or hours.
And power is energy divided by
time and is measured in Watts or kiloWatts .
Energy of each person = y Joules
or in terms of mass
Energy of each person = y grams
The conversion factor between mass and energy is E = m * c * c
where 'c' is the speed of light and 'm' is the mass.
Time = 8 hours (This is constant since each person has only 8 hours a day)
Power = Energy / Time
= (y / (8 * 60 * 60) ) Watts
Total Power of the world = n * (y / (8 * 60 * 60) ) Watts
where n = number of persons working on the project.
From the above equation it is clear that increasing the 'n' will
greatly improve the quality of product. Greater the 'n' then greater
will be the power (in KiloWatts).
You can wonder how much total
energy (in KiloJoules) and total power (in KiloWatts) the global
internet can focus on a system like Linux and PostgreSQL!
It is very clear that internet can network a vast number of people,
which implies internet has a lot of energy and time which can produce
much higher quality software products in much shorter time as compared
to commercial companies.
Even very big companies like Microsoft and
IBM cannot overpower and overrule the laws of Physics but will
eventually SURRENDER UNTO laws of science!
Conclusion is - because of laws of science, 'open source code' system
like PostgreSQL, Linux will prevail and will be always much better
than 'closed source code' system and it is possible to prove this
statement scientifically.
Man should not waste time creating too many
duplicate software products.
Inhalt
3. What is PostgreSQL ?
PostgreSQL is a free database, complete source code is given to you
and is a Object-Relational Database System targetting on ANSI ISO/SQL
1998, 92 and runs on diverse hardware platforms and Operating systems.
The ultimate objective and the final goal of PostgreSQL is to become
100 % compliant to ANSI/ISO SQL and also to become the number ONE open
generic Database in the world.
Today, PostgreSQL is the most advanced system in the world and it is
surprising that many commercial database systems could not match the
quality, features and capabilities of PostgreSQL !!
PostgreSQL is the
joint effort of many nations around the globe and is a project similar
to International Space Station.
PostgreSQL will remain the number one
database system for many decades into future since it is a open-source
code system.
The fundamental idea behind PostgreSQL is - once a module of code is
written than you should not waste even a milli-second of your time
trying to re-invent it!!
Informix Universal server (released 1997) is based on earlier version
of PostgreSQL because Informix bought Illustra Inc. and integrated
with Informix.
Illustra database was based on Postgres (earlier
version of PostgreSQL).
PostgreSQL is an enhancement of the POSTGRES database management
system, a next-generation DBMS research prototype.
While PostgreSQL
retains the powerful data model and rich data types of POSTGRES, it
replaces the PostQuel query language with an extended subset of SQL.
PostgreSQL development is being performed by a team of Internet
developers who all subscribe to the PostgreSQL development mailing
list. The current coordinator is Marc G. Fournier
· scrappy@postgreSQL.org
This team is now responsible for all current and future development
of PostgreSQL.
Ofcourse, the database customer himself is the
developer of PostgreSQL! The development load is distributed among
a very large number of database end-users on internet.
The authors of PostgreSQL 1.01 were Andrew Yu and Jolly Chen.
The
original Postgres code, from which PostgreSQL is derived, was the
effort of many graduate students, undergraduate students, and staff
programmers and working under the direction of Professor Michael
Stonebraker at the University of California, Berkeley.
Millions of PostgreSQL is installed as Database servers, Web database
servers and Application data servers.
It is very sophisticated object
relational database system (ORDBMS).
PostgreSQL runs on Solaris, SunOS, HPUX, AIX, Linux, Irix, Digital
Unix, BSDi,NetBSD, FreeBSD, SCO unix, NEXTSTEP, Unixware and all and
every flavor of Unix.
Port to Windows NT is done using Cygnus cygwin32
package.
PostgreSQL and related items in this document are subject to the
COPYRIGHT from University of California, Berkeley.
3.1. White Paper
PostgreSQL details in nutshell:
· Title: PostgreSQL SQL RDBMS Database (Object Relational
Database Management System)
· Current Version: 7.0.1
· Age: PostgreSQL is 15 years old. Developed since 1985
· Authors: Developed by millions/universities/companies on
internet for the past 15 YEARS
The white paper on PostgreSQL is at
Inhalt
4. Which one? PostgreSQL or MySQL ?
MySQL is another open-source SQL server, but it does not support
transactions.
It is suitable for very small databases and does not
support advanced SQL functionalities.
Whereas PostgreSQL is a
enterprise strength database supporting transactions and almost all
SQL constructs.
PostgreSQL is much more advanced than commercial
databases like Oracle, Sybase and Informix.
PostgreSQL supports very
advanced locking mechanisms and many more advanced features which are
not available in commercial database systems!!
In near future development of MySQL will be dropped, since MySQL is
duplicate product working towards ANSI SQL.
And all the MySQL users
will be migrated to PostgreSQL.
Also MySQL is a 'quasi-commercial'
product unlike PostgreSQL which is open-source and there is no license
fee.
There is no need for another SQL database system as PostgreSQL
is already here in this world!!
Duplicate products like MySQL confuse the user base and causes
division of resources.
For a "NEAR PERFECT" system there must be only
one system and everybody in the world must work on it!!
Duplicate
products cause more harm than good and hence division of resources
must be strongly discouraged.
This already happened in case of
commercial database systems like Oracle, Sybase, Informix and MS SQL
server which caused splintering of user base and often they are
incompatible.
WARNING: It is possible to create infinite number of database systems
for a given specification like ANSI SQL!!
MySQL is at http://www.tcx.se
Inhalt
5. Where to get it ?
You can buy Redhat Linux CDROM, Debian Linux CDROM or Slackware Linux
CDROM which already contains the PostgreSQL in package form (both
source code and binaries) from :
· Linux System Labs Web site: http://www.lsl.com/ (7 U.S.
dollars)
· Cheap Bytes Inc Web site: http://www.cheapbytes.com/ (7 U.S.
dollars)
· Debian Main Web site : http://www.debian.org/vendors.html
PostgreSQL organisation is also selling 'PostgreSQL CDROM' which
contains the complete source code and binaries for many Unix operating
systems as well as full documentation.
· PostgreSQL CDROM from main Web site at :
http://www.postgresql.org 30 (U.S. dollars)
Binaries only distribution of PostgreSQL:
· The maintainer of PostgreSQL RPMs is Lamar Owen and is at
lamar.owen@wgcr.org
· PostgreSQL source RPM and binaries RPM
http://www.ramifordistat.net/postgres
· PostgreSQL source RPM and binaries RPM http://www.postgresql.org
Click on "Latest News" and click on Redhat RPMs.
· PostgreSQL source RPM and binaries RPM
http://www.redhat.com/pub/contrib/i386/ and ftp site is at
ftp://ftp.redhat.com/pub/contrib/i386/
· Binaries site for Solaris, HPUX, AIX, IRIX, Linux :
ftp://ftp.postgresql.org/pub/bindist
WWW Web sites:
· Primary Web site: http://www.postgresql.org/
· Secondary Web site: http://logical.thought.net/postgres95/
· http://www.itm.tu-clausthal.de/mirrors/postgres95/
· http://s2k-ftp.cs.berkeley.edu:8000/postgres95/
· http://xenium.pdi.net/PostgreSQL/
· http://s2k-ftp.cs.berkeley.edu:8000/postgres95/
The ftp sites are listed below :-
· Primary FTP: ftp://ftp.postgresql.org/pub
· Secondary FTP: ftp://ftp.chicks.net/pub/postgresql
· ftp://ftp.emsi.priv.at/pub/postgres/
· ftp://ftp.itm.tu-clausthal.de/pub/mirrors/postgres95
· ftp://rocker.sch.bme.hu/pub/mirrors/postgreSQL
· ftp://ftp.jaist.ac.jp/pub/dbms/postgres95
· ftp://ftp.luga.or.at/pub/postgres95
· ftp://postgres95.vnet.net:/pub/postgres95
· ftp://ftpza.co.za/mirrors/postgres
· ftp://sunsite.auc.dk/pub/databases/postgresql
· ftp://ftp.task.gda.pl/pub/software/postgresql
· ftp://xenium.pdi.net/pub/PostgreSQL
PostgreSQL source code is also available at all the mirror sites of
sunsite unc (total of about 1000 sites around the globe). It is inside
the Red Hat Linux distribution in /pub/contrib/i386/postgresql.rpm
file.
· For list of mirror sites go to ftp://sunsite.unc.edu
Inhalt
6. PostgreSQL Quick-Installation Instructions
This chapter will help you to install and run the database very
quickly in less than 5 minutes.
6.1. Install and Test
Quick Steps to Install, Test, Verify and run PostgreSQL Login as root.
# cd /mnt/cdrom/RedHat/RPMS
# man rpm
# ls postgre*.rpm
# rpm -qpl postgre*.rpm | less (to see list of files)
# rpm -qpi postgre*.rpm (to see info of package)
# cat /etc/passwd | grep postgres
Note: If you see a 'postgres' user, you may need to backup and clean
up the postgres home directory postgres and delete the unix user
'postgres' or rename the unix user 'postgres' to something like 'post
gres2'. Install must be "clean slate"
# rpm -i postgre*.rpm (Must install all packages clients, devel, data
and main for pgaccess to work )
# man chkconfig
# chkconfig --add postgresql (to start pg during booting)
# /etc/rc.d/init.d/postgresql start (to start up postgres)
# man xhost
# xhost + (To give display access for pgaccess)
# su - postgres
bash$ man createdb
bash$ createdb mydatabase
bash$ man psql
bash$ psql mydatabase
..... in psql press up/down arrow keys for history line editing or \s
bash$ export DISPLAY=:0.0
bash$ man pgaccess
bash$ pgaccess mydatabase
Now you can start rapidly BANGING away SQL commands at psql or pgac
cess !!
bash$ cd /usr/doc/postgresql*
Here read all the FAQs, User, Programmer, Admin guides and tutorials.
6.2. PostgreSQL RPMs
See also "Installation Steps" from
http://www.ramifordistat.net/postgres
The maintainer of PostgreSQL RPMs is Lamar Owen and is at
lamar.owen@wgcr.org More details about PostgreSQL is at
http://www.postgresql.org
6.3. Maximum RPM
Familiarize with RedHat RPM package manager to manage the PostgreSQL
installations. Download the 'Maximum RPM' textbook from
http://www.RPM.org
look for the filename maximum-rpm.ps.gz And read
it on linux using the gv command -
# gv maximum-rpm.ps.gz
There is also rpm2deb which converts the RPM packages to Debian linux
packages.
6.4. Examples RPM
Examples are needed to do testing of various interfaces to PostgreSQL.
Install the postgresql examples directory from -
· Linux cdrom - postgresql-*examples.rpm
· postgresql-*examples.rpm from http://www.aldev.8m.com or
http://www.aldev.webjump.com
· PostgreSQL source code tree postgresql*.src.rpm and look for
examples, testing or tutorial directories
6.5. Testing PyGreSQL - Python interface
Install examples package, see ``'' and then do -
bash$ cd /usr/lib/pgsql/python
bash$ createdb thilo
bash$ psql thilo
thilo=> create table test (aa char(30), bb char(30) );
bash$ /usr/bin/python
>>> import _pg
>>> db = _pg.connect('thilo', 'localhost')
>>> db.query("INSERT INTO test VALUES ('ping', 'pong')")
>>> db.query("SELECT * FROM test")
eins|zwei
----+----
ping|pong
(1 row)
>>>CTRL+D
bash$
..... Seems to work - now install it properly
bash$ su - root
# cp /usr/lib/pgsql/python/_pg.so /usr/lib/python1.5/lib-dynload
6.6. Testing Perl - Perl interface
Install examples package, see ``'' and then do -
bash$ cd /usr/doc/postgresql-6.5.3/examples/perl5
bash$ perl ./example.pl
Note: If the above command does not work then do this. Gloabl var
@INC should include the Pg.pm module in directory site_perl hence use
-I option below
bash$ perl -I/usr/lib/perl5/site_perl/5.005/i386-linux-thread ./example.pl
.... You ran the perl which is accessing PostgreSQL database!!
Read the example.pl file for using perl interface.
6.7. Testing libpq, libpq++ interfaces
Install examples package, see ``'' and then do -
bash$ su root --> to change ownership of examples
# chown -R postgres /usr/doc/postgresql-6.5.3/examples
# exit
bash$ cd /usr/doc/postgresql-6.5.3/examples/libpq
bash$ gcc testlibpq.c -I/usr/include/pgsql -lpq
bash$ export PATH=$PATH:.
bash$ a.out
bash$ cd /usr/doc/postgresql-6.5.3/examples/libpq++
bash$ g++ testlibpq0.cc -I/usr/include/pgsql -I/usr/include/pgsql/libpq++
-lpq++ -lpq -lcrypt
bash$ ./a.out (Note: Ignore Error messages if you get any - as below)
> create table foo (aa int, bb char(4));
No tuples returned...
status = 1
Error returned: fe_setauthsvc: invalid name: , ignoring...
> insert into foo values ('4535', 'vasu');
No tuples returned...
status = 1
Error returned: fe_setauthsvc: invalid name: , ignoring...
> select * from foo;
aa |bb |
----- |-----|
4535 |vasu |
Query returned 1 row.
>
>CTRL+D
bash$
.... You ran direct C/C++ interfaces to PostgreSQL database!!
6.8. Testing Java interfaces
Install examples package, see ``'' and also install the following -
· Get JDK jdk-*glibc*.rpm from
ftp://ftp.redhat.com/pub/contrib/i386 or from
http://www.blackdown.org
· Get postgresql-jdbc-*.rpm ftp://ftp.redhat.com/pub/contrib/i386
bash$ cd /usr/doc/postgresql-6.5.3/examples/jdbc
bash$ echo $CLASSPATH
--> Should show CLASSPATH=/usr/lib/jdk-x.x.x/lib/classes.zip
where x.x.x is proper version numbers.
bash$ export CLASSPATH=$CLASSPATH:.:/usr/lib/pgsql/jdbc6.5-1.2.jar
Edit all psql.java file and comment out the 'package' line.
bash$ javac psql.java
bash$ java psql jdbc:postgresql:template1 postgres < password>[1] select * from pg_tables;
tablename tableowner hasindexes hasrules
pg_type postgres true false false
pg_attribute postgres true false false
[2]
CTRL+C
bash$
.... You ran direct Java interfaces to PostgreSQL database!
6.9. Testing ecpg interfaces
Install examples package, see ``'' and then do -
bash$ cd /usr/doc/postgresql-6.5.3/examples/ecpg
bash$ ecpg test1.pgc -I/usr/include/pgsql
bash$ cc test1.c -I/usr/include/pgsql -lecpg -lpq -lcrypt
bash$ createdb mm
bash$ ./a.out
.... You ran Embedded "C"-SQL to PostgreSQL database!
6.10. Testing SQL examples - User defined types and functions
Install examples package, see ``'' and then do -
bash$ cd /usr/doc/postgresql-6.5.3/examples/sql
Under-development..
6.11. Testing Tcl/Tk interfaces
Example of Tcl/Tk interfaces is pgaccess program. Read the file
/usr/bin/pgaccess using a editor -
bash$ view /usr/bin/pgaccess
bash$ export DISPLAY=:0.0
bash$ createdb mydb
bash$ pgaccess mydb
6.12. Testing ODBC interfaces
1. Get the win32 pgsql odbc driver from
http://www.insightdist.com/psqlodbc/
2. See also /usr/lib/libpsqlodbc.a
6.13. Testing MPSQL Motif-worksheet interfaces
Get the RPMs from http://www.mutinybaysoftware.com
6.14. Verification
To verify the top quality of PostgreSQL, run the Regression test
package :- Login as root -
# rpm -i postgresql*test.rpm
And see README file or install the source code tree which has regress directory
# rpm -i postgresql*.src.rpm
# cd /usr/src/redhat/SPECS
# more postgresql*.spec (to see what system RPM packages you need to
install)
# rpm -bp postgresql*.spec (.. this will prep the package)
Regression test needs the Makefiles and some header files like *fmgr*.h
which can be built by -
# rpm --short-circuit -bc postgresql*.spec ( .. use short circuit to
bypass!)
Abort the build by CTRL+C, when you see 'make -C common SUBSYS.o'
By this time configure is successful and all makefiles and headers
are created. You do not need to proceed any further
# cd /usr/src/redhat/BUILD
# chown -R postgres postgresql*
# su - postgres
bash$ cd /usr/src/redhat/BUILD/postgresql-6.5.3/src/test/regress
bash$ more README
bash$ make clean; make all runtest
bash$ more regress.out
6.15. Emergency Bug fixes
Sometimes emergency bug fix patches are released after the GA release
of PostgreSQL. You can apply these optional patches depending upon the
needs of your application. Follow these steps to apply the patches -
Change directory to postgresql source directory
# rpm -i postgresql*.src.rpm
# cd /usr/src/postgresql6.5.3
# man patch
# patch -p0 < patchfile
# make clean
# make
The patch files are located in
· PostgreSQL patches : ftp://ftp.postgresql.org/pub/patches
Inhalt
7. Quick Start Guide
Refer also to ``Quick Installation'' chapter.
7.1. Creating, Dropping, Renaming Database
You can use the user friendly GUI called 'pgaccess' to create and drop
databases, or you can use the command line 'psql' utility.
If you are logged in as root, switch user to 'postgres' :
# xhost + (To give display access for pgaccess)
# su - postgres
bash$ man createdb
bash$ createdb mydatabase
bash$ man psql
bash$ psql mydatabase
..... in psql press up/down arrow keys for history line editing or \s
bash$ export DISPLAY=[hostname]:0.0
bash$ man pgaccess
bash$ pgaccess mydatabase
Now you can start rapidly BANGING away SQL commands at psql or pgac
cess !!
To drop the database do :
bash$ man dropdb
bash$ dropdb [dbname]
It is also possible to destroy a database from within an SQL session
by using:
> drop database [dbname]
To rename a database see ``Backup and Restore''
7.2. Creating, Dropping users
To create new users, login as unix user 'postgres'. You can use user
friendly GUI tool called 'pgacess' to create, drop users.
bash$ man pgaccess
bash$ pgaccess [database_name]
and click on "Users" tab and then click Object|New or Object|Delete
You can also use command line scripts. Use the shell script called
'createuser' which invokes psql
bash$ man createuser
bash$ createuser [username]
bash$ createuser -h host -p port -i userid [username]
To drop a postgres user, use shell script 'destroyuser' -
bash$ man destroyuser
bash$ destroyuser
7.3. Creating, Dropping Groups
Currently, there is no easy interface to set up user groups. You have
to explicitly insert/update the pg_group table. For example:
bash$ su - postgres
bash$ psql [database_name]
..... in psql press up/down arrow keys for history line editing or \s
psql=> insert into pg_group (groname, grosysid, grolist)
psql=> values ('posthackers', '1234', '{5443, 8261}' );
INSERT 58224
psql=> grant insert on foo to group posthackers;
CHANGE
psql=>
The fields in pg_group are: groname The group name. This name should
be purely alphanumeric; do not include underscores or other punctua
tion.
grosysid The group id. This is an int4, and should be unique for each
group.
grolist The list of pg_user IDs that belong in the group. This is an
int4[].
To drop the group:
bash$ su - postgres
bash$ psql [database_name]
..... in psql press up/down arrow keys for history line editing or \s
psql=> delete from pg_group where groname = 'posthackers';
7.4. Create, Edit, Drop a table
You can use user friendly GUI tool 'pgaccess' or command line tool
'psql' to create, edit or drop a table in a database.
bash$ man pgaccess
bash$ pgaccess [database_name]
Click on Table | New | Design buttons.
bash$ psql [database_name]
..... in psql press up/down arrow keys for history line editing or \s
At psql prompt, give standard SQL statements like 'create table',
'alter table' or 'drop table' to manipulate the tables.
7.5. Create, Edit, Drop records in a table
You can use user friendly GUI tool 'pgaccess' or command line tool
'psql' to create, edit or drop records in a database table.
bash$ man pgaccess
bash$ pgaccess [database_name]
Click on Table | { pick a table } | Open buttons.
bash$ man psql
bash$ psql [database_name]
..... in psql press up/down arrow keys for history line editing or \s
At psql prompt, give standard SQL statements like 'insert into
table_name', 'update table_name' or 'delete from table_name' to manip
ulate the tables.
7.6. Switch active Database
You can use user friendly GUI tool 'pgaccess' or command line tool
'psql' to switch active database.
bash$ man pgaccess
bash$ pgaccess [database_name]
Click on Database | Open buttons.
bash$ man psql
bash$ psql [database_name]
..... in psql press up/down arrow keys for history line editing or \s
psql=> connect [database_name] [user]
7.7. Backup and Restore database
PostgreSQL provides two utilities to back up your system: pg_dump to
backup individual databases, and pg_dumpall to back up all the
databases in just one step.
bash$ su - postgres
bash$ man pd_dump
bash$ pd_dump [database_name] > database_name.pgdump
and can be restored using:
bash$ cat database_name.pgdump | psql [database_name]
This technique can be used to move databases to new locations, and to
rename existing databases.
WARNING: Every database should be backed up on a regular basis.
Since
PostgreSQL manages its own files in the file sysetem, it is not
advisable to rely on system backups of your file system for your
database backups; there is no guarantee that the files will be in a
usable, consistent state after restoration.
BACKUP LARGE DATABASES: Since Postgres allows tables larger than the
maximum file size on your system, it can be problematic to dump the
table to a file, because the resulting file likely will be larger than
the maximum size allowed by your system.
As pg_dump writes to stdout,
you can just use standard unix tools to work around this possible
problem:
Use compressed dumps:
bash$ pg_dump [database_name] | gzip > filename.dump.gz
reload with:
bash$ createdb [database_name]
bash$ gunzip -c filename.dump.gz | psql [database_name]
or
bash$ cat filename.dump.gz | gunzip | psql [database_name]
Use split:
bash$ pg_dump [database_name] | split -b 1m - filename.dump.
Note: There is a dot (.) after filename.dump in the above command!!
You can reload with:
bash$ man createdb
bash$ createdb [database_name]
bash$ cat filename.dump.* | pgsql [database_name]
Of course, the name of the file (filename) and the content of the
pg_dump output need not match the name of the database. Also, the
restored database can have an arbitrary new name, so this mechanism is
also suitable for renaming databases.
To dump all the databases in PostgreSQL use pg_dumpall
bash$ man pg_dumpall
bash$ pg_dumpall -o > db.out
To reload:
bash$ psql -e template1 < db.out
7.8. Security of database
See the chapter on ``PostgreSQL Security''.
7.9. Online help
It is very important that you should know how to use online help
facilities of PostgreSQL, since it will save you lot of time and
provides very quick access to information.
See the online man pages on various commands like createdb,
createuser, etc..
bash$ man createdb
See also online help of psql, by typing \h at psql prompt
bash$ psql mydatabase
psql> \h
Tip: In psql press up/down arrow keys for history line editing or \s
7.10. PostgreSQL Documentation
More questions, read the fine manuals of PostgreSQL which are very
extensive.
PostgreSQL documentation is distributed with package. See
the other manuals.
Inhalt
8. PostgreSQL Supports Extremely Large Databases greater than 200 Gig
Performance of 32-bit cpu machines will decline rapidly when the
database size exceeds 5 GigaByte.
You can run 30 gig database on
32-bit cpu but the performance will be degraded.
Machines with 32-bit
cpu imposes a limitation of 2 GB on RAM, 2 GB on file system sizes and
other limitations on the operating system.
Use the special filesystems
for linux made by SGI, IBM or HP or ext3-fs to support file-sizes
greater than 2 GB on 32-bit linux machines.
For extremely large databases, it is strongly advised to use 64-bit
machines like Digital Alpha cpu, Sun Ultra-sparc 64-bit cpu, Silicon
graphics 64-bit cpu, Intel Merced IA-64 cpu, HPUX 64bit machines or
IBM 64-bit machines.
Compile PostgreSQL under 64-bit cpu and it can
support huge databases and large queries. Performance of PostgreSQL
for queries on large tables and databases will be several times faster
than PostgreSQL on 32-bit cpu machines.
Advantage of 64-bit machines
are that you get very large memory addressing space and the operating
system can support very large file-systems, provide better performance
with large databases, support much larger memory (RAM), have more
capabilities etc..
Inhalt
9. How can I trust PostgreSQL ? Regression Test Package builds cus
tomer confidence
Thanks to "Laws of Physics", it is possible to SCIENTIFICALLY verify
whether PostgreSQL is working as per ISO/ANSI SQL specifications. To
validate PostgreSQL, regression test package (src/test/regress) is
included in the distribution.
Regression test package will verify the
standard SQL operations as well as the extensibility capabilities of
PostgreSQL. The test package already contains hundreds of SQL test
programs.
You should use the computer's high-speed power to validate the
PostgreSQL, instead of using human brain power. Computers can carry
out software regression tests millions or even billions of times
faster than humans can.
Modern computers can run billions of SQL
tests in a very short time. In the near future the speed of computer
will be several zillion times faster than human brain! Hence, it
makes sense to use the power of computer to validate the software.
You can add more tests just in case you need to, and can upload to the
primary PostgreSQL web site if you feel that it will be useful to
others on internet.
Regression test package helps build customer
confidence and trust in PostgreSQL and facilitates rapid deployment of
PostgreSQL on production systems.
Regression test package can be taken as a "VERY SOLID" technical
document mutually agreed upon between the developers and end-users.
PostgreSQL developers extensively use the regression test package
during development period and also before releasing the software to
public to ensure good quality.
Capablilities of PostgreSQL are directly reflected by the regression
test package. If a functionality, syntax or feature exists in the
regression test package then it is supported, and all others which are
NOT listed in the package MAY not be supported by PostgreSQL!!
You may
need to verify those and add it to regression test package.
Inhalt
10. Security of Database
Database security is addressed at several levels:
· Database file protection. All files stored within the database are
protected from reading by any account other than the postgres
superuser account
· Connections from a client to the database server are, by default,
allowed only via a local UNIX socket, not via TCP/IP sockets. The
back-end must be started with the -i option to allow nonlocal
clients to connect.
· Client connections can be restricted by IP address and/or username
via the pg_hba.conf file in $PG_DATA.
· Client connections may be authenticated via other external
packages.
· Each user in Postgres is assigned a username and (optionally) a
password. By default, users do not have write access to databases
they did not create.
· Users may be assigned to groups, and table access may be restricted
based on group priveleges.
10.1. User Authentication
Authentication is the process by which the backend server and
postmaster ensure that the user requesting access to data is in fact
who he/she claims to be.
All users who invoke Postgres are checked
against the contents of the pg_user class to ensure that they are
authorized to do so. However, verification of the user's actual
identity is performed in a variety of ways:
· From the user shell: A backend server started from a user shell
notes the user's (effective) user-id before performing a setuid to
the user-id of user postgres.
The effective user-id is used as the
basis for access control checks. No other authentication is
conducted.
· From the network: If the Postgres system is built as distributed,
access to the Internet TCP port of the postmaster process is
available to anyone.
The DBA configures the pg_hba.conf file in the
$PGDATA directory to specify what authentication system is to be
used according to the host making the connection and which database
it is connecting to.
See pg_hba.conf(5) (man 5 pg_hba.conf) for a
description of the authentication systems available. Of course,
host-based authentication is not fool-proof in Unix, either. It is
possible for determined intruders to also masquerade the
origination host. Those security issues are beyond the scope of
Postgres.
10.2. Host-Based Access Control
Host-based access control is the name for the basic controls
PostgreSQL exercises on what clients are allowed to access a database
and how the users on those clients must authenticate themselves. Each
database system contains a file named pg_hba.conf, in its $PGDATA
directory, which controls who can connect to each database.
Every
client accessing a database must be covered by one of the entries in
pg_hba.conf. Otherwise all attempted connections from that client will
be rejected with a "User authentication failed" error message.
See online man page of pg_hba.conf(5) (man 5 pg_hba.conf).
The general format of the pg_hba.conf file is of a set of records, one
per line. Blank lines and lines beginning with a hash character ("#")
are ignored. A record is made up of a number of fields which are
separated by spaces and/or tabs.
Connections from clients can be made using Unix domain sockets or
Internet domain sockets (ie. TCP/IP). Connections made using Unix
domain sockets are controlled using records of the following format:
local database authentication method
where
database specifies the database that this record applies to. The value
all specifies that it applies to all databases.
authentication method specifies the method a user must use to
authenticate themselves when connecting to that database using Unix
domain sockets. The different methods are described below.
Connections made using Internet domain sockets are controlled using
records of the following format.
host database TCP/IP-address TCP/IP-mask authentication method
The TCP/IP address is logically and'ed to both the specified TCP/IP
mask and the TCP/IP address of the connecting client.
If the two
resulting values are equal then the record is used for this
connection. If a connection matches more than one record then the
earliest one in the file is used. Both the TCP/IP address and the
TCP/IP mask are specified in dotted decimal notation.
If a connection
fails to match any record then the reject authentication method is
applied (see ``Authentication Methods'').
10.3. Authentication Methods
The following authentication methods are supported for both Unix and
TCP/IP domain sockets:
· trust The connection is allowed unconditionally.
· reject The connection is rejected unconditionally.
· crypt The client is asked for a password for the user. This is sent
encrypted (using crypt(3)) and compared against the password held
in the pg_shadow table. If the passwords match, the connection is
allowed.
· password The client is asked for a password for the user. This is
sent in clear and compared against the password held in the
pg_shadow table.
If the passwords match, the connection is allowed.
An optional password file may be specified after the password
keyword which is used to match the supplied password rather than
the pg_shadow table. See pg_passwd.
The following authentication methods are supported for TCP/IP domain
sockets only:
· krb4 Kerberos V4 is used to authenticate the user.
· krb5 Kerberos V5 is used to authenticate the user.
· ident The ident server on the client is used to authenticate the
user (RFC 1413). An optional map name may be specified after the
ident keyword which allows ident user names to be mapped onto
Postgres user names.
Maps are held in the file
$PGDATA/pg_ident.conf.
Here are some examples:
# Trust any connection via Unix domain sockets.
local trust
# Trust any connection via TCP/IP from this machine.
host all 127.0.0.1 255.255.255.255 trust
# We don't like this machine.
host all 192.168.0.10 255.255.255.0 reject
# This machine can't encrypt so we ask for passwords in clear.
host all 192.168.0.3 255.255.255.0 password
# The rest of this group of machines should provide encrypted passwords.
host all 192.168.0.0 255.255.255.0 crypt
10.4. Access Control
Postgres provides mechanisms to allow users to limit the access to
their data that is provided to other users.
· Database superusers Database super-users (i.e., users who have
pg_user.usesuper set) silently bypass all of the access controls
described below with two exceptions:
manual system catalog updates
are not permitted if the user does not have pg_user.usecatupd set,
and destruction of system catalogs (or modification of their
schemas) is never allowed.
· Access Privilege The use of access privilege to limit reading,
writing and setting of rules on classes is covered in SQL
grant/revoke(l).
· Class removal and schema modification Commands that destroy or
modify the structure of an existing class, such as alter, drop
table, and drop index, only operate for the owner of the class. As
mentioned above, these operations are never permitted on system
catalogs.
10.5. Secure TCP/IP Connection via SSH
You can use ssh to encrypt the network connection between clients and
a Postgres server. Done properly, this should lead to an adequately
secure network connection.
The documentation for ssh provides most of the information to get
started. Please refer to http://www.heimhardt.de/htdocs/ssh.html for
better insight.
A step-by-step explanation can be done in just two
steps.
Running a secure tunnel via ssh: A step-by-step explanation can be
done in just two steps.
· Establish a tunnel to the back-end machine, like this:
ssh -L 3333:wit.mcs.anl.gov:5432 postgres@wit.mcs.anl.gov
· The first number in the -L argument, 3333, is the port number of
your end of the tunnel. The second number, 5432, is the remote end
of the tunnel -- the port number your backend is using.
The name
or the address in between the port numbers belongs to the server
machine, as does the last argument to ssh that also includes the
optional user name.
Without the user name, ssh will try the name
you are currently logged on as on the client machine. You can use
any user name the server machine will accept, not necessarily those
related to postgres.
· Now that you have a running ssh session, you can connect a postgres
client to your local host at the port number you specified in the
previous step.
If it's psql, you will need another shell because
the shell session you used in step 1 is now occupied with ssh.
psql -h localhost -p 3333 -d mpw
· Note that you have to specify the -h argument to cause your client
to use the TCP socket instead of the Unix socket. You can omit the
port argument if you chose 5432 as your end of the tunnel.
10.6. Kerberos Authentication
Kerberos is an industry-standard secure authentication system suitable
for distributed computing over a public network.
Availability:
The Kerberos authentication system is not distributed
with Postgres. Versions of Kerberos are typically available as
optional software from operating system vendors. In addition, a source
code distribution may be obtained through MIT Project Athena.
Note: You may wish to obtain the MIT version even if your vendor provides a version, since
some vendor ports have been deliberately crippled or rendered non-interoperable with the MIT
version.
Inquiries regarding your Kerberos should be directed to your vendor or
MIT Project Athena. Note that FAQLs (Frequently-Asked Questions Lists)
are periodically posted to the Kerberos mailing list (send mail to
subscribe), and USENET news group.
Installation:
Installation of Kerberos itself is covered in detail in
the Kerberos Installation Notes .
Make sure that the server key file
(the srvtab or keytab) is somehow readable by the Postgres account.
Postgres and its clients can be compiled to use either Version 4 or
Version 5 of the MIT Kerberos protocols by setting the KRBVERS
variable in the file src/Makefile.global to the appropriate value. You
can also change the location where Postgres expects to find the
associated libraries, header files and its own server key file. After
compilation is complete, Postgres must be registered as a Kerberos
service.
See the Kerberos Operations Notes and related manual pages
for more details on registering services.
Operation:
After initial installation, Postgres should operate in all
ways as a normal Kerberos service. For details on the use of
authentication, see the PostgreSQL User's Guide reference sections for
postmaster and psql.
In the Kerberos Version 5 hooks, the following assumptions are made
about user and service naming(also, see Table below):
· User principal names (anames) are assumed to contain the actual
Unix/Postgres user name in the first component.
· The Postgres service is assumed to be have two components, the
service name and a hostname, canonicalized as in Version 4 (i.e.,
with all domain suffixes removed).
Table: Kerberos Parameter Examples
----------------------------------------------------------
Parameter Example
----------------------------------------------------------
user frew@S2K.ORG
user aoki/HOST=miyu.S2K.Berkeley.EDU@S2K.ORG
host postgres_dbms/ucbvax@S2K.ORG
----------------------------------------------------------
Inhalt
11. GUI FrontEnd Tool for PostgreSQL (Graphical User Interface)
Web browser will be the most popular GUI front-end in the future.
It
is recommended that you migrate all of your "legacy" Windows 95/NT
applications to Web-based application.
You should use Web-Application Servers like ``'' (Java based) or ``''
(Python based).
Best web-scripting (and compiling) language is ``PHP+Zend compiler''
PHP is extremely powerful as it combines the power of Perl, Java, C++,
Javascript into one single language and it runs on all OSes - unixes
and Windows NT/95.
The best tools in the order of preference are -
· Enhydra at ``'' plus Borland Java JBuilder for Linux
http://www.inprise.com
· Zope at ``''
· PHP script and Zend compiler at ``PHP+Zend compiler''
· X-Designer supports C++, Java and MFC http://www.ist.co.uk/xd
· Qt for Windows95 and Unix at http://www.troll.no and
ftp://ftp.troll.no
· Code Crusader is on linux cdrom, freeware based on MetroWorks Code
Warrior
http://www.kaze.stetson.edu/cdevel/code_crusader/about.html
· Code Warrior from MetroWorks http://www.metrowerks.com
· GNU Prof C++ IDE from (Redhat) http://www.redhat.com Cygnus
http://www.cygnus.com
· Borland C++ Builder for Linux http://www.inprise.com
· Borland Java JBuilder for Linux http://www.inprise.com
Language choices in the order of preference are -
1. Java but its programs run very slow and has license fees. C++ is 5
times faster than Java!!
2. Python (Powerful object oriented scripting language).
3. PHP Web server scripting, HTML, DHTML with Javascrpt client
scripting and Java-Applets.
4. Perl scripting language using Perl-Qt or Perl-Tk ``''
5. Omnipresent and Omnipotent language C++ (GNU g++):
· Fast CGI(written in GNU C++) with Javascript/Java-Applets as Web-
GUI-frontend.
· GNU C++ and QtEZ or QT
· GNU C++ with Lesstiff or Motif.
There are other tools available - PostgreSQL has Tcl/Tk interface
library in the distribution called 'pgTcl'.
There is a IDE
(integrated development environment) for Tcl/Tk called SpecTcl.
· Lesstiff Motif tool
ftp://ftp.redhat.com/pub/contrib/i386/lesstiff*.rpm
· Vibe Java/C++ is at http://www.LinuxMall.com/products/00487.html
· JccWarrior ftp://ftp.redhat.com/pub/contrib/i386/jcc*.rpm
· Tcl/Tk http://www.scriptics.com
· Object oriented extension of Tcl called INCR at
http://www.tcltk.com
· Visual TCL site http://www.neuron.com
· Visual TCL Redhat rpm at
ftp://ftp.redhat.com/pub/contrib/i386/visualtcl*.rpm
· http://sunscript.sun.com/
· http://sunscript.sun.com/TclTkCore/
· ftp://ftp.sunlabs.com/pub/tcl/tcl8.0a2.tar.Z
· Java FreeBuilder ftp://ftp.redhat.com/pub/contrib/i386/free*.rpm
· SpecTCL ftp://ftp.redhat.com/pub/contrib/i386/spec*.rpm
· Java RAD Tool for PostgreSQL Kanchenjunga
http://www.man.ac.uk/~whaley/kj/kanch.html
· Applixware Tool http://www.redhat.com
· XWPE X Windows Programming Environment
http://www.rpi.edu/~payned/xwpe/
ftp://ftp.redhat.com/pub/contrib/i386/xwpe*.rpm
· XWB X Windows Work Bench
ftp://ftp.redhat.com/pub/contrib/i386/xwb*.rpm
· NEdit ftp://ftp.redhat.com/pub/contrib/i386/nedit*.rpm
You can also use Borland C++ Builder, Delphi, Borland JBuilder,
PowerBuilder on Windows95 connecting to PostgreSQL on unix box
through ODBC/JDBC drivers.
Inhalt
12. Interface Drivers for PostgreSQL
12.1. ODBC Drivers for PostgreSQL
ODBC stands for 'Open DataBase Connectivity' established by Microsoft,
is a popular standard for accessing information from various databases
from different vendors.
Applications written using the ODBC drivers
are guaranteed to work with various databases like PostgreSQL, Oracle,
Sybase, Informix etc..
· PostODBC http://www.insightdist.com/psqlodbc is already included
in the distribution. See main web site
http://www.postgresql.org.
It is included on the PostgreSQL
CDROM.
· Open source code ODBC project is at http://www.iodbc.org
· http://www.openlinksw.com Open Link Software Corporation is
selling ODBC for PostgreSQL and other databases. Open Link also is
giving away free ODBC (limited seats) check them out.
· Insight ODBC for PostgreSQL http://www.insightdist.com/psqlodbc
This is the official PostODBC site.
· FreeODBC package http://www.ids.net/~bjepson/freeODBC/ This is a
free of cost version of ODBC.
12.2. UDBC Drivers for PostgreSQL
UDBC is a static version of ODBC independent of driver managers and
DLL support, used to embed database connectivity support directly into
applications.
· http://www.openlinksw.com Open Link Software Corporation is
selling UDBC for PostgreSQL and other databases. Open Link also is
giving away free UDBC (limited seats) check them out.
12.3. JDBC Drivers for PostgreSQL
JDBC stands for 'Java DataBase Connectivity'.
Java is a platform
independent programming language developed by Sun Microsystems. Java
programmers are encouraged to write database applications using the
JDBC to facilitate portability across databases like PostgreSQL,
Oracle, informix, etc.
If you write Java applications you can get JDBC
drivers for PostgreSQL from the following sites:
JDBC driver is already included in the PostgreSQL distribution in
postgresql-jdbc*.rpm.
· http://www.demon.co.uk/finder/postgres/index.html
Sun's Java
connectivity to PostgreSQL
· ftp://ftp.ai.mit.edu/people/rst/rst-jdbc.tar.gz
· http://www.openlinksw.com Open Link Software Corporation is
selling JDBC for PostgreSQL and other databases. Open Link also is
giving away free JDBC (limited seats) check them out.
· JDBC UK site http://www.retep.org.uk/postgres
· JDBC FAQ site http://eagle.eku.edu/tools/jdbc/faq.html
The JDBC home, guide and FAQ are located at -
· JDBC HOME http://splash.javasoft.com/jdbc
· JDBC guide
http://www.javasoft.com/products/jdk/1.1/docs/guide/jdbc
· JDBC FAQ http://javanese.yoyoweb.com/JDBC/FAQ.txt
See the section - ``Testing Java PostgreSQL interface''
12.4. Java for PostgreSQL
Java programmers can find these for PostgreSQL very useful.
· ftp://ftp.redhat.com/pub/contrib/i386> and see postgresql-
jdbc-*.rpm
· http://www.blackdown.org
See the section - ``Testing Java PostgreSQL interface''
Inhalt
13. Perl Database Interface (DBI) Driver for PostgreSQL
13.1. Perl 5 interface for PostgreSQL
PERL is an acronym for 'Practical Extraction and Report Language'.
Perl is available on each and every operating system and hardware
platform in the world.
You can use Perl on Windows95/NT, Apple
Macintosh iMac, all flavors of Unix (Solaris, HPUX, AIX, Linux, Irix,
SCO etc..), mainframe MVS, desktop OS/2, OS/400, Amdahl UTS and many
others.
Perl runs EVEN on many unpopular or generally-unknown
operating systems and hardware!!
So do not be surprised if you see
perl running on a very rarely used operating system. You can imagine
the vast extent of the user base and developer base of Perl.
Perl interface for PostgreSQL is included in the distribution of
PostgreSQL. Check in src/pgsql_perl5 directory.
· Pgsql_perl5 contact Email: E.Mergl@bawue.de
· Another source from -
ftp://ftp.kciLink.com/pub/PostgresPerl-1.3.tar.gz
· Perl Home page : http://www.perl.com/perl/index.html
· Perl tutorial, look for Tutorial title at :
http://reference.perl.com/
· Perl FAQ is at :
http://www.yahoo.com/Computers_and_Internet/Programming_Languages/Perl/
· Perl GUI User Interfaces Perl-Qt rpm :
ftp://ftp.redhat.com/pub/contrib/i386 and look for
PerlQt-1.06-1.i386.rpm
· Perl GUI User Interfaces Perl-Qt :
http://www.accessone.com/~jql/perlqt.html
· Perl GUI User Interfaces Perl-XForms :
and look for
Xforms4Perl-0.8.4-1.i386.rpm
· Perl GUI User Interfaces Perl-Tk :
ftp://ftp.redhat.com/pub/contrib/i386
· Perl GUI kits : http://reference.perl.com/query.cgi?ui
· Perl Database Interfaces :
http://reference.perl.com/query.cgi?database
· Perl to "C" translator : http://www.perl.com/CPAN-
local/modules/by-module/B/ and look for Compiler-a3.tar.gz
· Bourne shell to Perl translator :
http://www.perl.com/CPAN/authors/id/MERLYN/sh2perl-0.02.tar.gz
· awk to Perl a2p and sed to Perl s2p is included with the PERl
distribution.
· See also the newsgroups for PERL at comp.lang.perl.*
13.2. Perl Database Interface DBI
13.2.1. WHAT IS DBI ?
The Perl Database Interface (DBI) is a database access Application
Programming Interface (API) for the Perl Language.
The Perl DBI API
specification defines a set of functions, variables and conventions
that provide a consistent database interface independent of the actual
database being used.
· DBI FAQ author Descartes Hermetica is at descarte@hermetica.com
13.2.2. DBI driver for PostgreSQL DBD-Pg-0.89
Get DBD-Pg-0.89.tar.gz from below
· DBD-Pg-0.89 : http://www.perl.com/CPAN/modules/by-module/DBD/
· Comprehensive Perl Archive Network CPAN http://www.perl.com/CPAN
· DBI drivers list and DBI module pages
http://www.hermetica.com/technologia/perl/DBI
· DBI information is at http://www.fugue.com/dbi/
· Primary ftp site ftp://ftp.demon.co.uk/pub/perl/db
· Miscellaneous DBI link http://www-ccs.cs.umass.edu/db.html
· Miscellaneous DBI link
http://www.odmg.org/odmg93/updates_dbarry.html
· Miscellaneous DBI link http://www.jcc.com/sql_stnd.html
· PostgreSQL database http://www.postgresql.org
13.2.3. Technical support for DBI
· Send comments and bug-reports to and include the output of perl -v,
and perl -V, the version of PostgreSQL, the version of DBD-Pg, and
the version of DBI in your bug-report.
E.Mergl@bawue.de
13.2.4. What is DBI, DBperl, Oraperl and *perl?
To quote Tim Bunce, the architect and author of DBI:
``DBI is a database access Application Programming Interface (API) for
the Perl Language. The DBI API Specification defines a set of
functions, variables and conventions that provide a consistent
database interface independent of the actual database being used.''
In simple language, the DBI interface allows users to access multiple
database types transparently.
So, if you connecting to an Oracle,
Informix, mSQL, Sybase or whatever database, you don't need to know
the underlying mechanics of the 3GL layer. The API defined by DBI will
work on all these database types.
A similar benefit is gained by the ability to connect to two different
databases of different vendor within the one perl script, ie, I want
to read data from an Oracle database and insert it back into an
Informix database all within one program.
The DBI layer allows you to
do this simply and powerfully.
Here's a list of DBperl modules, their corresponding DBI counterparts
and support information. DBI driver queries should be directed to the
dbi-users mailing list.
Module Name Database Required Author DBI
----------- ----------------- ------ ---
Sybperl Sybase Michael Peppler DBD::Sybase
http://www.mbay.net/~mpeppler
Oraperl Oracle 6 & 7 Kevin Stock DBD::Oracle
Ingperl Ingres Tim Bunce & DBD::Ingres
Ted Lemon
Interperl Interbase Buzz Moschetti DBD::Interbase
Uniperl Unify 5.0 Rick Wargo None
Pgperl Postgres Igor Metz DBD::Pg
Btreeperl NDBM John Conover SDBM?
Ctreeperl C-Tree John Conover None
Cisamperl Informix C-ISAM Mathias Koerber None
Duaperl X.500 Directory Eric Douglas None
User Agent
However, some DBI modules have DBperl emulation layers, so, DBD::Ora
cle comes with an Oraperl emulation layer, which allows you to run
legacy oraperl scripts without modification.
The emulation layer
translates the oraperl API calls into DBI calls and executes them
through the DBI switch.
13.2.5. DBI specifications
There are a few information sources on DBI.
· DBI Specification
http://www.hermetica.com/technologia/perl/DBI/doc/dbispec
POD documentation PODs are chunks of documentation usually embedded
within perl programs that document the code ``in place'', providing
a useful resource for programmers and users of modules.
POD for DBI
and drivers is beginning to become more commonplace, and
documentation for these modules can be read with the following
commands.
The POD for the DBI Specification can be read with the command
$ perldoc DBI
Users of the Oraperl emulation layer bundled with DBD::Oracle, may
read up on how to program with the Oraperl interface by typing:
$ perldoc Oraperl
Users of the DBD::mSQL module may read about some of the private
functions and quirks of that driver by typing:
$ perldoc DBD::mSQL
The Frequently Asked Questions is also available as
POD documentation. Read this by typing:
$ perldoc DBI::FAQ
POD in general - Information on writing POD, and on the philosophy
of POD in general, can be read by typing:
$ perldoc perlpod
Users with the Tk module installed may be interested to learn there is
a Tk-based POD reader available called tkpod, which formats POD in a
convenient and readable way.
See also -
· Information from DBI mailing lists
http://www.hermetica.com/technologia/perl/DBI/tidbits
· DBI Perl Journal website http://www.tpj.com
· ``DBperl'' This article, published in the November 1996 edition of
``Dr. Dobbs Journal''.
· ``The Perl5 Database Interface'' a book to be written by Alligator
Descartes and published by O'Reilly and Associates.
The mailing lists that users may participate in are:
· Mailing lists http://www.fugue.com/dbi
· dbi-announce Email: dbi-announce-request@fugue.com with a message
body of 'subscribe'
· dbi-dev For developers Email: dbi-dev-request@fugue.com with a
message body of 'subscribe'
· dbi-users general discussion Email: dbi-users-request@fugue.com
with a message body of 'subscribe'
· US Mailing List Archives
http://outside.organic.com/mail-
archives/dbi-users/>
· European Mailing List Archives
http://www.rosat.mpe-
garching.mpg.de/mailing-lists/PerlDB-Interest
13.2.6. Compilation problems or "It fails the test"
If you have a core dump, try the Devel::CoreStack module for
generating a stack trace from the core dump. Devel::CoreStack can be
found on CPAN at:
· http://www.perl.com/cgi-bin/cpan_mod?module=Devel::CoreStack
Email the dbi-users Mailing List stack trace, module versions, perl
version, test cases, operating system versions and any other pertinent
information. The more information you send, the quicker developers
can track problems down.
13.2.7. Is DBI supported under Windows 95 / NT platforms?
The DBI and DBD::Oracle Win32 ports are now a standard part of DBI,
so, downloading DBI of version higher than 0.81 should work fine.
You
can access Microsoft Access and SQL-Server databases from DBI via
ODBC. Supplied with DBI-0.79 (and later) is an experimental DBI
'emulation layer' for the Win32::ODBC module. It's called
DBI::W32ODBC.
You will need the Win32::ODBC module.
· Win32 DBI http://www.hermetica.com/technologia/perl/DBI/win32
· Win32 ODBC http://www.roth.net
13.2.8. Is DBI any use for CGI programming?
In a word, yes! DBI is hugely useful for CGI programming! In fact, CGI
programming is one of two top uses for DBI.
DBI confers the ability to CGI programmers to power WWW-fronted
databases to their users, which provides users with vast quantities of
ordered data to play with. DBI also provides the possibility that, if
a site is receiving far too much traffic than their database server
can cope with, they can upgrade the database server behind the scenes
with no alterations to the CGI scripts.
13.2.9. How do I get faster connection times with DBD Oracle and CGI?
The Apache httpd maintains a pool of httpd children to service client
requests. Using the Apache mod_perl module by Doug MacEachern, the
perl interpreter is embedded with the httpd children. The CGI, DBI,
and your other favorite modules can be loaded at the startup of each
child. These modules will not be reloaded unless changed on disk.
For
more information on Apache, see the Apache Project's WWW site:
· Apache Project WWW site http://www.apache.org
· Mod_perl module http://www.perl.com/cgi-
bin/cpan_mod?module=mod_perl
13.2.10. How do I get persistent connections with DBI and CGI?
Using Edmund Mergl's Apache::DBI module, database logins are stored in
a hash with each of these httpd child. If your application is based on
a single database user, this connection can be started with each
child. Currently, database connections cannot be shared between httpd
children.
Apache::DBI can be downloaded from CPAN via:
· http://www.perl.com/cgi-bin/cpan_mod?module=Apache::DBI
13.2.11. ``When I run a perl script from the command line, it works,
but, when I run it under the httpd, it fails!'' Why?
Basically, a good chance this is occurring is due to the fact that the
user that you ran it from the command line as has a correctly
configured set of environment variables, in the case of DBD::Oracle,
variables like $ORACLE_HOME, $ORACLE_SID or TWO_TASK.
The httpd
process usually runs under the user id of nobody, which implies there
is no configured environment. Any scripts attempting to execute in
this situation will correctly fail. To solve this problem, set the
environment for your database in a BEGIN ( ) block at the top of your
script. This will solve the problem.
Similarly, you should check your
httpd error logfile for any clues, as well as the ``Idiot's Guide To
Solving Perl / CGI Problems'' and ``Perl CGI Programming FAQ'' for
further information. It is unlikely the problem is DBI-related.
Read
BOTH these documents carefully!
· Guide to Solving Perl CGI problems
http://www.perl.com/perl/faq/index.html
13.2.12. Multi-threading with DBI?
For some OCI example code for Oracle that has multi-threaded SELECT
statements, see:
· http://www.hermetica.com/technologia/oracle/oci/orathreads.tar.gz
13.2.13. How can I invoke stored procedures with DBI?
Assuming that you have created a stored procedure within the target
database, eg, an Oracle database, you can use $dbh->do to immediately
execute the procedure. For example,
$dbh->do( "BEGIN someProcedure END" );
13.2.14. How can I get return values from stored procedures with DBI?
Remember to perform error checking, though!
$sth = $dbh->prepare( "BEGIN foo(:1, :2, :3); END;" );
$sth->bind_param(1, $a);
$sth->bind_param_inout(2, \$path, 2000);
$sth->bind_param_inout(3, \$success, 2000);
$sth->execute;
13.2.15. How can I create or drop a database with DBI?
Database creation and deletion are concepts that are entirely too
abstract to be adequately supported by DBI. For example, Oracle does
not support the concept of dropping a database at all!
Also, in
Oracle, the database server essentially is the database, whereas in
mSQL, the server process runs happily without any databases created in
it. The problem is too disparate to attack.
Some drivers, therefore,
support database creation and deletion through the private func
methods. You should check the documentation for the drivers you are
using to see if they support this mechanism.
13.2.16. How are NULL values handled by DBI?
NULL values in DBI are specified to be treated as the value undef.
NULLs can be inserted into databases as NULL, for example:
$rv = $dbh->do( "INSERT INTO table VALUES( NULL )" );
but when queried back, the NULLs should be tested against undef. This
is standard across all drivers.
13.2.17. What are these func methods all about?
The func method is defined within DBI as being an entry point for
database-specific functionality, eg, the ability to create or drop
databases. Invoking these driver-specific methods is simple, for
example, to invoke a createDatabase method that has one argument, we
would write:
$rv = $dbh->func( 'argument', 'createDatabase' );
Software developers should note that the func methods are non-portable
between databases.
13.2.18. Commercial Support and Training
PERL CLINIC : The Perl Clinic can arrange commercial support contracts
for Perl, DBI, DBD::Oracle and Oraperl. Support is provided by the
company with whom Tim Bunce, author of DBI, works.
For more
information on their services, please see :
· http://www.perl.co.uk/tpc
13.3. Testing Perl interface
See the section - ``Testing Perl PostgreSQL interface''
Inhalt
14. PostgreSQL Management Tools
14.1. PGACCESS - A GUI Tool for PostgreSQL Management
PgAccess is a Tcl/Tk interface to PostgreSQL. It is already included
in the distribution of PostgreSQL.
You may want to check out this web
site for a newer copy
· http://www.flex.ro/pgaccess
· If you have any comment, suggestion for improvements, e-mail to :
teo@flex.ro
Usage of pgaccess -
# man xhost
# xhost +
# su - postgres
bash$ man pgaccess
bash$ export DISPLAY=[hostname]:0.0
bash$ pgaccess mydatabase
Features of PgAccess
PgAccess windows - Main window, Table builder, Table(query) view,
Visual query builder.
Tables
· opening tables for viewing, max 200 records (changed by preferences
menu)
· column resizing, dragging the vertical grid line (better in table
space rather than in the table header)
· text wrap in cells - layout saved for every table
· import/export to external files (SDF,CSV)
· filter capabilities (enter filter like (price>3.14)
· sort order capabilities (enter manually the sort field(s))
· editing in place
· improved table generator assistant
· improved field editing
Queries
· define , edit and stores "user defined queries"
· store queries as views
· execution of queries
· viewing of select type queries result
· query deleting and renaming
· Visual query builder with drag & drop capabilities. For any of you
who had installed the Tcl/Tk plugin for Netscape Navigator, you can
see it at work clicking here
Sequences
· defines sequences, delete them and inspect them Functions
· define, inspect and delete functions in SQL language
Future implementation will have
· table design (add new fields, renaming, etc.)
· function definition
· report generator
· basic scripting
INFORMATION ABOUT LIBGTCL
You will need the PostgreSQL to Tcl interface library libgtcl, lined
as a Tcl/Tk 'load'-able module.
The libpgtcl and the source is located
in the PostgreSQL directory /src/interfaces/libpgtcl.
Specifically,
you will need a libpgtcl library that is 'load'-able from Tcl/Tk. This
is technically different from an ordinary PostgreSQL loadable object
file, because libpgtcl is a collection of object files.
Under Linux,
this is called libpgtcl.so. You can download from the above site a
version already compiled for Linux i386 systems. Just copy libpgtcl.so
into your system library director (/usr/lib).
One of the solutions is
to remove from the source the line containing load libpgtcl.so and to
load pgaccess.tcl not with wish, but with pgwish (or wishpg) that wish
that was linked with libpgtcl library.
If you get crypt not found during compilation pgaccess source tree
then use -lcrypt.
14.2. Windows Interactive Query Tool for PostgreSQL (WISQL or MPSQL)
MPSQL provides users with a graphical SQL interface to PostgresSQL.
MPSQL is similar to Oracle's SQL Worksheet and Microsoft SQL Server's
query tool WISQL. It has nice GUI and has history of commands. Also
you can cut and paste and it has other nice features to improve your
productivity.
· http://www.troubador.com/~keidav/index.html
· Email: keidav@whidbey.com
· http://www.ucolick.org/~de/ in file tcl_syb/wisql.html
· http://www.troubador.com/~keidav/index.html
· Email: de@ucolick.org
14.3. Interactive Query Tool (ISQL) for PostgreSQL called PSQL
ISQL is for character command line terminals.
This is included in the
distribution, and is called "psql". Very similar to Sybase ISQL,
Oracle SQLplus. At unix prompt give command 'psql' which will put you
in psql> prompt.
bash# su - postgres
bash$ man psql
bash$ psql mydatabase
Type \h to see help of commands.
Very user friendly and easy to use. Can also be accessed from shell
scripts.
14.4. MPMGR - A Database Management Tool for PostgresSQL
MPMGR will provide a graphical management interface for PostgresSQL.
You can find it at
· http://www.mutinybaysoftware.com/
· Email: keidav@mutinybaysoftware.com
· http://www.troubador.com/~keidav/index.html
· Email: keidav@whidbey.com
· http://www.ucolick.org/~de in file tcl_syb/wisql.html
· WISQL for PostgreSQL http://www.ucolick.org/~de/Tcl/pictures
· Email: de@ucolick.org
Inhalt
15. CPUs for PostgreSQL
The following CPUs (both 64-bit and 32-bit) are available for
PostgreSQL. All these CPUs run Linux.
· GNU/GPL Freedom 64-bit F-CPU http://f-cpu.tux.org
· Russian E2k 64-bit CPU (The world's fastest CPU as of June, 2000
???!!!)
website : http://www.elbrus.ru/roadmap/e2k.html
Elbrus
is now partnered (alliance) with Sun Microsystems of USA
· Korean CPU from Samsung 64-bit CPU original from DEC Alpha
http://www.samsungsemi.com Alpha-64bit CPU is at
http://www.alpha-processor.com
Now there is collaboration between
Samsumg, Compaq of USA on Alpha CPU
· Intel IA 64 http://developer.intel.com/design/ia-64
· Transmeta crusoe CPU and in near future Transmeta's 64-bit CPU
· Sun Ultra-sparc 64-bit CPU
· Silicon Graphics MIPS Architecture CPUs
http://www.sgi.com/processors
· IBM Power PC (motorola)
http://www.motorola.com/SPS/PowerPC/index.html
· Seimens Pyramid CPU from Pyramid Technologies
· Intel X86 series 32-bit CPUs Pentiums, Celeron etc..
· AMDs X86 series 32-bit CPUs K-6, Athlon etc..
· National's Cyrix X86 series 32-bit CPUs Cyrix etc..
· European Space Agency's ESA-32bit and ESA-64bit CPUs
· Other CPUs from other countries ?? Let me know...
Inhalt
16. Setting up multi-boxes PostgreSQL with just one monitor
If you do want to spend money on hardware switches than you can use
VNC (Virtual Network Computing) Technology from the telecom giant AT &
T. VNC is GPLed and is a free software. Using VNC you can run
PostgreSQL programs on computers without monitors and display on
remote boxes with monitors!! But the boxes must be connected via
ethernet Network Interface Cards. VNC is at
http://www.uk.research.att.com/vnc
You can stack up multiple CPU-boxes and connect to just one monitor
and use the KVM (Keyboard, Video, Monitor) switch box to select the
host. This saves space and avoids a lot of clutter and also
eliminates monitor, keyboard and the mouse (saving anywhere from 100
to 500 US dollars per set).
Using this switch box, you can stack up many PostgreSQL servers
(development, test, production), Web servers, ftp servers, Intranet
servers, Mail servers, News servers in a tower shelf. The switch box
can be used for controlling Windows 95/NT or OS/2 boxes as well.
Please check out these sites:
· DataComm Warehouse Inc at 1-800-328-2261.
They supply all varieties
of computer hardware http://www.warehouse.com 4-port Manual KVM
switch (PS/2) is about $89.99 Part No. DDS1354
· Network Technologies Inc
http://www.networktechinc.com/servswt.html (120 dollars/PC 8
ports) which lists
· Scene Double Inc, England
http://www.scene.demon.co.uk/qswitch.htm
· Cybex corporation http://www.cybex.com
· Raritan Inc http://www.raritan.com
· RealStar Solutions Inc http://www.real-star.com/kvm.htm
· Belkin Inc http://www.belkin.com
· Better Box Communications Ltd.
http://www.betterbox.com/info.html
· Go to nearest hardware store and ask for "Server Switch" also known
as "KVM Auto Switches".
Search engine yahoo to find more companies with "Server Switches" or
"KVM Switches".
It is strongly recommended to have a dedicated unix box for each
PostgreSQL data-server for better performance.
No other application
program/processes should run on this box. See the Business section of
your local newspapers for local vendors selling only intel box, 13"
monochrome monitor (very low cost monitor). Local vendors sell just
the hardware without any Microsoft Windows/DOS.
You do not need a
color monitor for the database server, as you can do remote
administration from color PC workstation.
You can buy bare-bone computer hardware from online stores. You can
get good rates in "Online Auctions"
· Online store and auction hall http://www.egghead.com
· Online store http://www.buy.com
· Bidding store http://www.ubid.com
Get RedHat (or some other distribution of) Linux cdrom from below -
· Linux System Labs Web site: http://www.lsl.com/ 7 (U.S.
dollars)
· Cheap Bytes Inc Web site: http://www.cheapbytes.com/ 7 (U.S.
dollars)
Make sure that the hardware you purchase is supported by Redhat
Linux.
Check the ftp site of Redhat for recommended hardware like
SCSI adapters, video cards before buying. For just $ 600 you will
get a powerful intel box with Redhat Linux running PostgreSQL.
Use
odbc/jdbc/perl/tcl to connect to PostgreSQL from Windows95, OS/2,
Unix Motif or web browser (e.g. Redbaron, Opera, Netscape, 20
others). (Web browsers are very fast becoming the standard GUI
client).
Using KVM switch you can control many cpu boxes by just one monitor
and one keyboard!
Inhalt
17. Web-Application-Servers for PostgreSQL
Several Web-Application-Servers work with PostgreSQL both open-source
and commercial versions. The popular open-source Web-Application-
Servers are Enhydra and Zope and commercial Web-Application-Servers
are IBM Websphere, BEA Weblogic.
17.1. Lutris Corp "Enhydra"
Enhydra supports PostgreSQL database.
Enhydra is a immensely popular
Java/XML Web-Application-Server created by 'Lutris Corporation'. It is
the world's best Java/XML Web-Application server. It supports EJB,
Servlets, JSP, JNDI, JDBC, JTA, CORBA, XMLC/Rocks, DODS and
internationalization.
It is written in 100% pure Java and is
available from http://www.enhydra.org. Enhydra is a open source code
project but is commercially sold and supported by Lutris Corp.
Visit
http://www.lutris.com
You would use Borland Corp's JBuilder along with Enhydra. JBuilder is
at http://www.inprise.com
See also Enterprise Java HOWTO at
http://www.linuxdoc.org/HOWTO/Enterprise-Java-for-Linux-HOWTO.html
17.2. Zope
Python is becoming immensely popular "pure" object-oriented scripting
language.
Zope is a Web-Application server and provides interfaces to
PostgreSQL. Zope is available at http://www.zope.org Python is at
http://www.python.org
Inhalt
18. Applications and Tools for PostgreSQL
18.1. PostgreSQL 4GL for web database applications - AppGEN Develop
ment System
AppGEN can be downloaded from
· http://www.man.ac.uk/~whaley/ag/appgen.html
· ftp://ftp.mcc.ac.uk/pub/linux/ALPHA/AppGEN
.
AppGEN is a high level fourth generation language and application
generator for producing World Wide Web (WWW) based applications.
These applications are typically used over the internet or within a
corporate intranet. AppGEN applications are implemented as C
scripts conforming to the Common Gateway Interface (CGI) standard
supported by most Web Servers.
To use AppGEN you will need the following :-
PostgresSQL, relational database management system
A CGI compatible web server such as NCSA's HTTPD
An ansi C compiler such as GCC
AppGEN consists of the following Unix (Linux) executables :-
- defgen, which produces a basic template application from a logical
data structure. The applications are capable of adding, updating,
deleting and searching for records within the database whilst
automatically maintaining referential integrity.
- appgen, the AppGEN compiler which compiles the appgen source code
into CGI executable C source and HTML formatted documents ready for
deployment on a web server.
- dbf2sql, a utility fo converting dBase III compatible .dbf files
into executable SQL scripts. This enables data stored in most
DOS/Windows based database packages to be migrated to a SQL server
such as PostgresSQL.
- In addition, AppGEN comprises of a collection of HTML documents,
GIF files and Java applets which are used at runtime by the system.
And of course, like all good software, the full source code is
included.
The author, Andrew Whaley, can be contacted on
· andrew@arthur.smuht.nwest.nhs.uk
18.2. WWW Web interface for PostgresSQL - DBENGINE
dbengine a plug 'n play Web interface for PostgreSQL created by Ingo
Ciechowski. It is at
· http://www.cis-computer.com/software/dbengine
About DBENGINE : dbengine is an interface between the WWW and
Postgres95 which provides simple access to any existing database
within just a few minutes.
PHP gives you a Perl like language in your documents, but no real Perl
while AppGen and wdb-p95 require that you create some configuration
file for each of your databases -- sound's like you'll first of all
have to learn some sort of new meta language before you can get
started.
Unlike other tools you don't have to learn any special programming or
scripting language to get started with dbengine. Also there's no
configuration file for each database, so you don't have to get
familiar with such a new structure. However - in case you want to
gain access to the full features of dbengine it'd be a good idea to
know the Perl language.
The whole system can be configured by simple manipulations of an
additional database that contains closer information about how to
visualize your database access. You can even specify virtual Fields
which are calculated on the fly right before they're displayed on the
screen.
18.3. Apache Webserver Module for PostgreSQL - NeoSoft NeoWebScript
Apache is a well-known Web Server. And a module to interface
PostgreSQL to Apache Webserver is at -
· http://www.neosoft.com/neowebscript/
NeoWebScript is a programming language that allows both simple and
complex programs to be embedded into HTML files.
When an HTML page containing embedded NeoWebScript is requested, the
NeoWebScript-enabled webserver executes the embedded script(s),
producing a webpage containing customized content created by the
program.
NeoWebScript is a fast, secure, easy to learn way to do powerful,
server-based interactive programming directly in the HTML code in web
pages. With NeoWebScript, counters, email forms, graffiti walls, guest
books and visitor tracking are all easy, even for a beginning
programmer. See how well NeoWebScript holds its' own vs. PERL and
JavaScript.
If you'd like to install NeoWebScript on your webserver, your
Webmaster needs to read our Sysop FAQ to get started.
Theory of
Operations will explain how NeoWebScript works, while installation
will take them through the steps. Management deals with configuration
issues and running the server, tests let you verify correct
NeoWebScript operation, and troubleshooting deals with server
problems.
There is no cost to you to use NeoWebScript-2.2 for your ISP, your
intranet, or your extranet. You'll see a full license when you
register to download, but it costs $ 99 if you want to embed it in
your own product or use it in a commerce (eg. SSL) server.
NeoWebScript is a module for the Apache webserver that allows you to
embed the Tcl/Tk programming language in your webpages as a scripting
tool. It was invented by Karl Lehenbauer, NeoSoft's Chief Technical
Officer, and documented, enhanced and extended by NeoSoft's
programmers and technical writers.
The Apache webserver is the world's most popular webserver, accounting
for 68 % of the sites polled.
Tcl/Tk is the powerful, free, cross-platform scripting language
developed by Dr. John Ousterhout. In his own words
"Tcl/Tk lets software developers get the job done ten times faster
than with toolkits based on C or C++. It's also a great glue language
for making existing applications work together and making them more
graphical and Internet-aware."
Karl Lehenbauer, Founder and Chief Technical Officer of NeoSoft, has
been part of Tcl/Tk development from the very beginning. Together
with Mark Diehkans, they authored Extended Tcl, also known as TclX or
NeoSoft Tcl, a powerful set of extensions to the language. Many of the
current core Tcl commands originated in Extended Tcl, and were then
imported into the core language by Dr. Ousterhout.
NeoSoft Inc., 1770 St. James Place, Suite 500, Houston, TX 77056 USA
18.4. HEITML server side extension of HTML and a 4GL language for
PostgreSQL
Tool heitml is another way to interface postgres with the world wide
web. For more details contact
Helmut Emmelmann H.E.I. Informationssyteme GmbH
Wimpfenerstrasse 23 Tel. 49-621-795141
68259 Mannheim Germany Fax. 49-621-795161
· E-mail Mr.Helmut Emmelmann at emmel@h-e-i.de
· Heitml main web site http://www.heitml.com
· Heitml secondary web site http://www.h-e-i.deom
heitml is a server side extension of HTML and a 4GL language at the
same time. People can write web applications in the HTML style by
using new HTML-like tags.
heitml (pronounced "Hi"-TML) is an extension of HTML and a full-
featured 4th generation language that enables Web-based Applications
to interact with data stored in SQL databases, without resorting to
complex CGI scripts.
heitml extends HTML on the sever side, dynamically converting ".hei"
files to HTML format and so is compatible with any web browser.It
embraces the familiar, easy-to-use HTML syntax and provides a large
assortment of pre-developed Tags and Libraries to take care of tasks
that formerly required CGI. As XML, heitml provides user defined tags.
With heitml the user defined markup can be translated to HTML and send
to a browser.
heitml targets both HTML designers and professional programmers alike.
HTML designers can use heitml Tags to build dynamic web pages, access
SQL databases, or create complete web applications.
Counters,
registration databases, search forms, email forms, or hierarchical
menues can all be created simply by using the pre-developed HTML-like
Tags found in the many Component Libraries.
For programmers heitml embeds a complete forth generation language in
HTML
(e.g. (if), (while), and (let) Tags),
plus powerful expression evaluation with integer, real, boolean,
string, and tuple data types.
Tuples have reference semantics as in
modern object oriented languages and are stored on a heap. heitml
variables including all complex data structures stored on the heap
maintain their values between pages using the Session Mode. It is pos
sible to define your own tags or environment tags and even re-define
HTML-tags.
heitml makes it possible to
- - - develop Web Sites in a structured and modular way, drastically
reducing maintenance overhead.
- - - develop intelligent and interactive Web Sites, with content that
dynamically adapts itself to user needs.
- - - show the content of SQL databases with no programming other than
to use our library of prefined "dba" Tags.
- - - develop complex database and Catalog Shopping applications using
Session Variables
heitml runs on Linux with any Web Server using the CGI interface, and
is especially fast (avoiding the CGI overhead) within the APACHE Web
Server using the apache API. Currently MSQL (Version 1 and 2),
PostgreSQL (Version 6), mysql, and the yard databases are supported).
heitml also works on Linux, BSDi, Solaris and SunOS, as well as
Windows NT with CGI and ISAPI and ODBC and Windows 95.
heitml (on linux) is free for research, non-commercial and private
usage. Commercial Web Sites must pay a licensing fee.
The fully
operational version of heitml is available for a trial period
downloaded freely. (Note, however, that each ".hei" Web Page you
develop will display a message identifying it as the version for non-
commercial use. After registration, you will receive a key to switch
off the message without having to re-install the program.)
heitml (pronounced "Hi"-TML) significantly extends and enhances the
functionality of HTML by definable tags and full programming features.
This makes dynamic content and database applications possible simply
within the HTML world, without CGI and without external scripting or
programming languages.
This means you, as an HTML author, can embed
applications in your web pages, simply by using some new tags without
CGI and without programming. As an advanced user or programmer on the
other hand you can create and program powerful tag libraries. This
approach makes heitml suitable for HTML newcomers and professional
programmers alike. heitml runs on the web server and dynamically
generates HTML, so heitml is compatible with the internet standards
and with any web browser.
It allows full access to databases while
shielding the user from any unneccessary CGI complexity. heitml has
been developed according to the newst research and in compiler
construction and transaction systems.
heitml pages are developed just the same way as HTML pages, with a
text editor or HTML editor, and placed on the web server as usual.
However now pages can contain dynamic heitml tags and access tag
libraries.
You can use these tags to access the database, to create
dynamic content, to send emails, and even to create powerful
applications like registration databases and shopping systems.
HTML newcomers and professional programmers alike will be amazed at
how quickly and easily they can design exciting applications like our
Interactive Guestbook without resorting to complex and difficult to
learn CGI scripts, simply by using the tools provided in our dba
Library.
heitml is accompanied by a wide range of tag libraries, to create
guestbooks, database maintenance applications, extensible query forms,
powerful email forms or structure your web site using a hierarchic
menu. These tools are ready to go, just add the corresponding tags to
your web site.
As an experienced programmer you can make fully use of the heitml
persistent dynamic tuple architecture : heitml is not just a scripting
language with dynamic typing, full power expression evaluation,
recursive procedures and extensive parameter passing features, but it
also features persistent dynamic tuples to automatically keep session
data of any size.
18.5. America On-line AOL Web server for PostgreSQL
The no-cost commercial webserver, AOLserver supports database
connections to PostgreSQL for more information see
· AOL Web Server home http://www.aolserver.com
· Introduction to AOLserver by Philip Greenspun
http://photo.net/wtr/aolserver/introduction-1.html
AOLserver is a fast, fully multithreaded, Tcl enabled webserver.
But not only that, it is a complete database-backed web development
platform. With AOLserver you can have multiple pooled connections
to PostgreSQL (and other RBDMSs) that can be shared among different
threads.
AOLserver has a Tcl and C APIs that allow you to develop
powerful dynamic websites. All this since 1995. It is licensed
under the APL (AOLserver Public License) or the GPL, thus being
totally free software.
The Tcl API is the most useful for web
sites. AOLserver has a set of powerful Tcl calls, such as
ns_sendmail (to send e-mail), ns_httpget (to fetch a URL),
ns_schedule (a cron-like feature to schedule procedures to run at
specific times), etc. You can also extend AOLserver's capabilities
very easily with the Tcl API.
Each AOLserver virtual server can
have its own "library" of private Tcl scripts that are parsed by
AOLserver and become accessible to any page within that virtual
server.
You can develop pages for AOLserver in three ways: - Plain
HTML - .tcl pages -- these are tcl programs that can return HTML
via the ns_write call. - .adp pages -- AOL Dynamic Pages. You
develop your pages in plain HTML but you can scape to Tcl code by
using <% %> or <%= %> much alike PHP or ASP. While AOLserver is a
great webserver with a superb architecture, where it really shines
is in database connectivity.
AOLserver has its own database
abstraction layer that enables you to have it connected to
different RDBMSs without changing your code at all. The connections
do the RDBMS are pooled, persistent and are shared among different
threads.
This allows for very fast connections and efficient use
of resources. AOLserver has drivers for all major RDBMSs:
PostgreSQL, Oracle, Sybase, Informix, Illustra, Solid, Interbase,
MySQL.
18.6. Problem/Project Tracking System Application Tool for PostgreSQL
This is at
· http://www.homeport.org/~shevett/pts/
18.7. Convert dbase dbf files to PostgreSQL
The program dbf2msql works fine with mSQL and PostgreSQL. You can find
it at
· ftp://ftp.nerosworld.com/pub/SQL/dbf2sql/
· ftp://ftp.postgresql.org/pub/incoming/dbf2pg-3.0.tar.gz
This program was written by Maarten Boekhold, Faculty of Electrical
Engineering TU Delft, NL Computer Architecture and Digital Technique
section
· M.Boekhold@et.tudelft.nl
You can also use a python method to read dbf files and load into a
postgres database.
· See http://www.python.org
18.8. Convert Microsoft Access MDB database files to PostgreSQL
MDB Tools is a planned set of libraries and utilities to facilitate
exporting data from MS Access databases (mdb files) into a multiuser
database such as Oracle, Sybase, DB2, Informix, MySQL, Postgresql, or
similar.
· Get MDB tool from http://mdbtools.sourceforge.net
· Mailing list
http://lists.sourceforge.net/mailman/listinfo/mdbtools-dev
Inhalt
[gekürzt und und HTML-konvertiert: joe-eis]