Database-SQL-RDBMS HOW-TO document for Linux (PostgreSQL Object Relational Database System) Al Dev (Alavoor Vasudevan) alavoor@yahoo.com v45.0, 27 Jan 2001 This document is a "practical guide" to very quickly setup a SQL Database engine and front end tools on an Unix system. It also dis? cusses 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 "Post? greSQL" on Unix system which can be used as an 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 an 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 1.1 Quantum Computers - Quantum Physics Useful !! 2. Laws of Physics apply to Software! 3. What is PostgreSQL ? 3.1 White Paper 4. Which one? PostgreSQL or MySQL ? 4.1 PostgreSQL defeated Oracle, IBM DB2, MS SQL server and others!! 4.2 MySQL and other duplicate RDBMSes 4.3 Limitations of 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 Creating Triggers and Stored Procedures 7.11 PostgreSQL Documentation 8. Performance Tuning of PostgreSQL server 8.1 OS Tuning for Database server 8.2 Tuning Database server process 9. PostgreSQL Supports Extremely Large Databases greater than 200 Gig 9.1 CPU types - 32-bit or 64-bit 9.2 Multiple CPUs 9.3 Replication Server 10. How can I trust PostgreSQL ? Regression Test Package builds customer confidence 11. Security of Database 11.1 User Authentication 11.2 Host-Based Access Control 11.3 Authentication Methods 11.4 Access Control 11.5 Secure TCP/IP Connection via SSH 11.6 Kerberos Authentication 12. GUI FrontEnd Tool for PostgreSQL (Graphical User Interface) 13. Interface Drivers for PostgreSQL 13.1 ODBC Drivers for PostgreSQL 13.2 UDBC Drivers for PostgreSQL 13.3 JDBC Drivers for PostgreSQL 13.4 Java for PostgreSQL 14. Perl Database Interface (DBI) Driver for PostgreSQL 14.1 Perl interface for PostgreSQL 14.2 Perl Database Interface DBI 14.2.1 WHAT IS DBI ? 14.2.2 DBD driver for PostgreSQL 14.2.3 Technical support for DBI 14.2.4 DBI Documents 14.2.5 Is DBI supported under Windows 95 / NT platforms? 14.2.6 Commercial Support and Training 14.3 Testing Perl interface 15. PostgreSQL Management Tools 15.1 PGACCESS - A GUI Tool for PostgreSQL Management 15.2 GtkSQL Graphical Query Tool for PostgreSQL 15.3 Windows Interactive Query Tool for PostgreSQL (WISQL or MPSQL) 15.4 Interactive Query Tool (ISQL) for PostgreSQL called PSQL 15.5 MPMGR - A Database Management Tool for PostgresSQL 15.6 PgAdmin, PhpPgAdmin tools 15.7 PgBash - SQL shell tool 15.8 Webmin Tool for PostgreSQL 16. CPUs for PostgreSQL 17. Setting up multi-boxes PostgreSQL with just one monitor 18. Web-Application-Servers for PostgreSQL 18.1 PERL Web Application Servers 18.2 PHP Web Application Servers 18.3 Lutris Corp "Enhydra Enterprise" (Java) 18.4 Zope (Python) 18.5 OpenACS (Tcl Language) 18.6 C++, CORBA Web Application Servers 18.7 Pike, Roxen Web Application Server 18.8 Web Application Servers Directory 19. Applications and Tools for PostgreSQL 19.1 PostgreSQL 4GL for web database applications - AppGEN Development System 19.2 WWW Web interface for PostgresSQL - DBENGINE 19.3 Apache Webserver Module for PostgreSQL - NeoSoft NeoWebScript 19.4 HEITML server side extension of HTML and a 4GL language for PostgreSQL 19.5 America On-line AOL Web server for PostgreSQL 19.6 Problem/Project Tracking System Application Tool for PostgreSQL 19.7 Convert dbase dbf files to PostgreSQL 19.8 Convert Microsoft Access MDB database files to PostgreSQL 19.9 Zeos Client 19.10 Report Writer in Java 20. Database Design Tool - Entity Relation Diagram Tool 21. Web Database Design/Implementation tool for PostgreSQL - EARP 21.1 What is EARP ? 21.2 Implementation 21.3 How does it work ? 21.4 Where to get EARP ? 22. PHP Hypertext Preprocessor - Server-side html-embedded scripting language for PostgreSQL 22.1 Major Features 22.2 PHP - Brief History 22.3 So, what can I do with PHP ? 22.4 A simple example 22.5 CGI Redirection 22.5.1 Apache 1.0.x Notes 22.5.2 Netscape HTTPD 22.5.3 NCSA HTTPD 22.6 Running PHP from the command line 22.7 PHPGem package 23. Python Interface for PostgreSQL 23.1 Where to get PyGres ? 23.2 Information and support 23.3 Testing Python interface 24. Gateway between PostgreSQL and the WWW - WDB-P95 24.1 About wdb-p95 24.2 Does the PostgreSQL server, pgperl, and httpd have to be on the same host? 25. "C", "C++", ESQL/C language Interfaces and Bitwise Operators for PostgreSQL 25.1 "C" interface 25.2 "C++" interface 25.3 ESQL/C 25.4 BitWise Operators for PostgreSQL 26. Japanese Kanji Code for PostgreSQL 27. PostgreSQL Port to Windows 95/Windows NT 27.1 Authors of NT port 27.2 Install the Cygwin package 27.3 Tuneup Bash Window 27.4 Install the Andy Piper tools 27.5 Install Ludovic Lange's Cygwin32 IPC package 27.6 Install PostgreSQL 28. Mailing Lists 28.1 E-mail account for PostgreSQL 28.2 English Mailing List 28.3 Archive of Mailing List 28.4 Spanish Mailing List 29. Documentation and Reference Books 29.1 User Guides and Manuals 29.2 Online Documentation 29.3 Useful Reference Textbooks 29.4 ANSI/ISO SQL Specifications documents - SQL 1992, SQL 1998 29.5 Syntax of ANSI/ISO SQL 1992 29.6 Syntax of ANSI/ISO SQL 1998 29.7 SQL Tutorial for beginners 29.8 Temporal Extension to SQL92 29.9 Part 0 - Acquiring ISO/ANSI SQL Documents 29.10 Part 1 - ISO/ANSI SQL Current Status 29.11 Part 2 - ISO/ANSI SQL Foundation 29.12 Part 3 - ISO/ANSI SQL Call Level Interface 29.13 Part 4 - ISO/ANSI SQL Persistent Stored Modules 29.14 Part 5 - ISO/ANSI SQL/Bindings 29.15 Part 6 - ISO/ANSI SQL XA Interface Specialization (SQL/XA) 29.16 Part 7 - ISO/ANSI SQL Temporal 29.16.1 INTRODUCTION 29.16.2 A CASE STUDY - STORING CURRENT INFORMATION 29.16.3 A CASE STUDY - STORING HISTORY INFORMATION 29.16.4 A CASE STUDY - PROJECTION 29.16.5 A CASE STUDY - JOIN 29.16.6 A CASE STUDY - AGGREGATES 29.16.7 SUMMARY 29.17 Part 8 - ISO/ANSI SQL MULTIMEDIA (SQL/MM) 30. Technical support for PostgreSQL 30.1 Commercial Support 31. Economic and Business Aspects 32. List of Other Databases 33. Internet World Wide Web Searching Tips 34. Conclusion 35. FAQ - Questions on PostgreSQL 36. Other Formats of this Document 37. Copyright and License 38. Appendix A - Syntax of ANSI/ISO SQL 1992 39. Appendix B - SQL Tutorial for beginners 39.1 Tutorial for PostgreSQL 39.2 Internet URL pointers 39.3 On-line SQL tutorials 40. Appendix C - Linux Quick Install Instructions 41. Appendix C - Midgard Installation 41.1 Testing Midgard PHP Server 41.2 Security OpenSSL ______________________________________________________________________ 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. PostgreSQL is pronounced as Post-gres-cue-el (Postgres-QL) and not Postgre-es-cue-el. 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 an 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 an 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 an 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 micro- electronics 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 | 2012 | 2020 | 2030 | +--------------------------+---------+---------+---------+---------+--------+---------+--------+ | Feature size(micron) | 0.25 | 0.18 | 0.15 | 0.13 | 0.05 |< 0.00001| atomic | +--------------------------+---------+---------+---------+---------+--------+---------+--------+ | Wafer size(mm) | 200 | 300 | 300 | 300 | 450 | Mol/Bio |Quantum | +--------------------------+---------+---------+---------+---------+--------+---------+--------+ | Min Operating Voltage | 1.8-2.5 | 1.5-1.8 | 1.2-1.5 | 1.2-1.5 | 0.5-0.6| < 0.001 | minute | +--------------------------+---------+---------+---------+---------+--------+---------+--------+ | Max power dissipation | 70 | 90 | 110 | 130 | 175 | 600 | minute | +--------------------------+---------+---------+---------+---------+--------+---------+--------+ | On-chip frequencey (MHz) | 750 | 1,250 | 1,500 | 2,100 | 10,000 | > 50,000| ---- | +--------------------------+---------+---------+---------+---------+--------+---------+--------+ | DRAM capacity | 256 MB | 1 GB | 2 GB | 4 GB | 256 GB | > 1000GB| ---- | +--------------------------+---------+---------+---------+---------+--------+---------+--------+ 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"! 1.1. Quantum Computers - Quantum Physics Useful !! As you can see from above table "Advancement of chip capabilities in future" in the years after 2030, database systems like PostgreSQL will be running on Quantum Computers. Quantum Computers rely on an atomic particle's traits, such as direction of spin, for creating a state. For example, when the spin is up, a particle could be read as "one", when its spin is down, the particle would be read as "zero". Atoms and nuclei can exist in a state of superposition, where the values of one, zero and the range in between can be represented concurrently. By entangling the spins of atoms, "qubits" can become wired together, enabling them to function as a collective whole, bringing about a nonlinear computational power that far surpasses the capabilities of supercomputers available today!! At atomic level Quantum Physics comes to assistance to better understand the behaviour of atomic particles. 2. Laws of Physics apply to Software! In this chapter, it will be shown how science plays an 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 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. There is only one man-made universe) Creating an 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. There are weapons to even terminate the universes (it is not a good idea to give nuke weapons technology to every person). 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. 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 and is not static!! A person from another universe by name 'Brahma' created this universe you are currently living in. Knowledge is the MOTHER of this universe!! you are living was born!! It is a deal similar to how you were born! Without any 'genes' from Mother Knowledge it is not possible create even a small "C" program! At some point our universe will close down (in a big crunch) and all the atoms inside the universe will completely vanish and dissappear! All the atoms that you see inside this universe will be gone! Total number of universes that can be created is INFINITY and similarly total number of operating systems that can be created is also infinity!! It is infinite cyclic process where universes are born and then later die down. There are millions of universes, which are classified into 3 major categories. Infinite number universes and infinite variety of multi-dimensional atoms collapse down into few primary-dimensional-universe. And primary-dimensional universes collapse down into one single focus entity called 'eeshwar' (eeshara is a sanskrit word). 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. There are infinite number of methods to unlock mass and convert it into enery. Mass is a highly concentrated energy. 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. 3. What is PostgreSQL ? PostgreSQL is a free database, complete source code is given to you and is an 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. PostgreSQL is pronounced as Post-gres-cue-el (Postgres-QL) and not Postgre-es-cue-el. 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 an 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 . PostgreSQL is pronounced as Post-gres-cue-el (Postgres-QL) and not Postgre-es-cue-el. 4. Which one? PostgreSQL or MySQL ? 4.1. PostgreSQL defeated Oracle, IBM DB2, MS SQL server and others!! PostgreSQL defeated Oracle 8 (and 8i), IBM DB2, MS SQL server, Sybase, Interbase and MySQL in standard benchmark tests in performance, speed, scalability and reliability! Read the benchmarks at or at 4.2. MySQL and other duplicate RDBMSes 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 an 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. We would take the most advanced and mature open-source SQL server and drop all others as we do not have lots of time (to deal with multiple RDBMSes)!! In fact, you do not have time to deal with just one powerful SQL server like PostgreSQL! 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. I want put the source code of SQL server under your control!!! You do not need hundreds of database systems, all you need is just one best database server which happens to be 'PostgreSQL'. WARNING: It is possible to create infinite number of database systems for a given specification like ANSI SQL!! Features which are missing in MySQL and which PostgreSQL supports are - ? Transactions ? Stored Procedures ? Triggers (update, insert and delete) ? Object oriented databases ? Advanced locking systems, concurrency management under multi-user, mutli-transactions environment ? Sub-queries ? Server-side cursors ? Query caching ? Locking of databases ? Better table join supports (JOIN, UNION, MINUS, INTERSECT, outer join) ? And many more advanced features - too numerous to list here. MySQL is at 4.3. Limitations of MySQL PostgreSQL should be compared with systems like Oracle, both are really true ACID compliant robust systems developed over a very long time. It is very much wrong to compare MySQL with Oracle or MySQL with PostgreSQL. For more details read Why Not MySQL . Hence, it will be a very serious mistake to replace Oracle with MySQL!! If you want to replace Oracle then consider PostgreSQL. 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: (7 U.S. dollars) ? Cheap Bytes Inc Web site: (7 U.S. dollars) ? Debian Main Web site : 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 : 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 ? PostgreSQL source RPM and binaries RPM Click on "Latest News" and click on Redhat RPMs. ? PostgreSQL source RPM and binaries RPM and ftp site is at ? Binaries site for Solaris, HPUX, AIX, IRIX, Linux : WWW Web sites: ? Primary Web site: ? Secondary Web site: ? ? ? ? The ftp sites are listed below :- ? Primary FTP: ? Secondary FTP: ? ? ? ? ? ? ? ? ? ? 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 6. PostgreSQL Quick-Installation Instructions PostgreSQL is pronounced as Post-gres-cue-el (Postgres-QL) and not Postgre-es-cue-el. 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 The maintainer of PostgreSQL RPMs is Lamar Owen and is at lamar.owen@wgcr.org More details about PostgreSQL is at 6.3. Maximum RPM Familiarize with RedHat RPM package manager to manage the PostgreSQL installations. Download the 'Maximum RPM' textbook from 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 and mirrors at webjump , angelfire , geocities , virtualave , bizland , theglobe , spree , infoseek , bcity , 50megs ? 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) ); thilo=> \q 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 - ______________________________________________________________________ root# chown -R postgres.postgres /var/lib/pgsql/examples bash$ cd /var/lib/pgsql/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 - ______________________________________________________________________ root# chown -R postgres.postgres /var/lib/pgsql/examples bash$ cd /var/lib/pgsql/examples/libpq bash$ gcc testlibpq.c -I/usr/include/pgsql -lpq bash$ export PATH=$PATH:. bash$ a.out bash$ cd /var/lib/pgsql/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 or from ? Get postgresql-jdbc-*.rpm ___________________________________________________________________ root# chown -R postgres.postgres /var/lib/pgsql/examples bash$ cd /var/lib/pgsql/examples/jdbc bash$ echo $CLASSPATH --> Should show CLASSPATH=/usr/lib/pgsql/jdbc7.0-1.2.jar:.:/home/java/jdk1.2.2/lib:/usr/lib/pgsql:/usr/lib/pgsql/classes.zip:/usr/lib/pgsql/pg.jar with proper jdbc*.jar version numbers. And the directories /usr/lib/pgsql and /usr/libjdk*/lib should contain *.jar files. bash$ export CLASSPATH=/usr/lib/pgsql/jdbc7.0-1.2.jar:.:/home/java/jdk1.2.2/lib:/usr/lib/pgsql:/usr/lib/pgsql/classes.zip:/usr/lib/pgsql/pg.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 - ______________________________________________________________________ root# chown -R postgres.postgres /var/lib/pgsql/examples bash$ cd /var/lib/pgsql/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 - ______________________________________________________________________ root# chown -R postgres.postgres /var/lib/pgsql/examples bash$ cd /var/lib/pgsql/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 an 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 2. See also /usr/lib/libpsqlodbc.a 6.13. Testing MPSQL Motif-worksheet interfaces Get the RPMs from 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 : 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=: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$ man destroydb (for older versions of pgsql) bash$ dropdb ______________________________________________________________________ It is also possible to destroy a database from within an SQL session by using: ______________________________________________________________________ > drop database ______________________________________________________________________ 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 ______________________________________________________________________ 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 bash$ createuser -h host -p port -i userid ______________________________________________________________________ To drop a postgres user, use shell script 'destroyuser' - ______________________________________________________________________ bash$ man dropuser bash$ man destroyuser (older versions of pgsql) 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 ..... 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 ..... 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 ______________________________________________________________________ Click on Table | New | Design buttons. ______________________________________________________________________ bash$ man psql bash$ psql ..... 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 ______________________________________________________________________ Click on Table | < pick a table > | Open buttons. ______________________________________________________________________ bash$ man psql bash$ psql ..... 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 ______________________________________________________________________ Click on Database | Open buttons. ______________________________________________________________________ bash$ man psql bash$ psql ..... in psql press up/down arrow keys for history line editing or \s psql=> connect ______________________________________________________________________ 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.pgdump To dump all databases - bash$ man pg_dumpall bash$ pg_dumpall -o > db_all.out To reload (restore) a database dumped with pg_dump: bash$ cat database_name.pgdump | psql To reload (restore) all databases dumped with pg_dumpall: bash$ psql -e template1 < db_all.out ______________________________________________________________________ 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 an 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 | gzip > filename.dump.gz Reload with : bash$ createdb bash$ gunzip -c filename.dump.gz | psql Or bash$ cat filename.dump.gz | gunzip | psql Use split: bash$ pg_dump | 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 bash$ cat filename.dump.* | pgsql ______________________________________________________________________ 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. Backup LARGE Objects: Large objects are not handled by pg_dump. The directory contrib/pg_dumplo of the Postgres source tree contains a program that can do that. FILESYSTEM BACKUP : You can use the linux OS tools and commands to backup the entire database. But you must completely shutdown the postgresql database server before doing backup or restore with this method. The filesystem backup or restore may be 2 to 3 times faster than the pg_dump command, but only disadvantage is that you must completely shutdown the database server. It is very highly recommended that you use backup and restore tools like Arkeia, Bru which are given in Mic-Lin analogy list sub-heading "Backup and Restore Utility" at and mirror sites are at webjump , angelfire , geocities , virtualnet , bizland , theglobe , spree , infoseek , bcity , 50megs .The OS commands to use are - ______________________________________________________________________ bash$ man tar bash$ tar -cvf backup.tar /usr/local/pgsql/data or using compression bash$ tar -zcvf backup.tgz /usr/local/pgsql/data ______________________________________________________________________ INCREMENTAL BACKUP : This is in todo list and will appear in future release of PostgreSQL. 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. Creating Triggers and Stored Procedures To create triggers or stored procedures, First run 'createlang' script to install 'plpgsql' in the particular database you are using. If you want by default then install it in 'template1' and subsequent created databases will be clones of template1. See 'createlang' web page in User guide at /usr/doc/postgresql-7.0.2/user/index.html. ______________________________________________________________________ bash$ man createlang bash$ createdb mydb bash$ export PGLIB=/usr/lib/pgsql bash$ createlang plpgsql mydb bash$ createlang plpgsql template1 ______________________________________________________________________ See also the trigger, stored procedures examples in ``''. One sample code from examples RPM: ______________________________________________________________________ create function tg_pfield_au() returns opaque as ' begin if new.name != old.name then update PSlot set pfname = new.name where pfname = old.name; end if; return new; end; create trigger tg_pfield_au after update on PField for each row execute procedure tg_pfield_au(); ______________________________________________________________________ Another trigger example sample code: ______________________________________________________________________ create trigger check_fkeys_pkey_exist before insert or update on fkeys for each row execute procedure check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2'); ______________________________________________________________________ You must also install the TEST package - postgresql-test-7.0.2-2.rpm and read the example sql scripts at - /usr/lib/pgsql/test/regress/sql To see the list of triggers in database do - ______________________________________________________________________ bash$ psql mydb psql=> \? psql=> \dS psql=> \d pg_trigger psql=> select tgname from pg_trigger order by tgname; ______________________________________________________________________ To see the list of functions and stored procedures in database do - ______________________________________________________________________ bash$ psql mydb psql=> \? psql=> \dS psql=> \d pg_proc psql=> select proname, prosrc from pg_proc order by proname; psql=> \df ______________________________________________________________________ 7.11. PostgreSQL Documentation More questions, read the fine manuals of PostgreSQL which are very extensive. PostgreSQL documentation is distributed with package. See the other manuals. The release docs are at . 8. Performance Tuning of PostgreSQL server Generally database server is standalone box connected to network. Since database server is the only unix process which runs on the CPU, you can do several optimizations to speed up the server. 8.1. OS Tuning for Database server To get more bang for a given CPU processing power, do the following:- ? Recompile linux kernel to make it small and lean. Remove items which are not used. See kernel howto at ? Turn off unneccessary unix processes - on linux/unix systems run chkconfig ___________________________________________________________________ bash$ su - root bash# man chkconfig bash# chkconfig --help bash# chkconfig --list | grep on | less From the above list, turn off the processes you do not want to start automatically - bash# chkconfig --level 0123456 off Next time when the machine is booted these services will not be started. Now, shutdown the services manually which you just turning off. bash# cd /etc/rc.d/init.d bash# ./ stop ___________________________________________________________________ ? Do not run any other application processes which are unnecessary. ? Do not leave X-Window running unattended. Because X-window processes consume memory, CPU load and can be a serious security hole from outside attacks. The X-window managers generally used are KDE, GNOME, CDE, XDM and others. You must exit the X-window immediately after using and most of the time you should see command line console login prompt on the database server machine. 8.2. Tuning Database server process General tuning tips: ? Indices can speed up queries. The explain command allows you to see how PostgreSQL is interpreting your query, and which indices are being used. ? Use the cluster command to group data in base tables to match an index. See the man cluster(1) manual page for more details. ? If you are doing a lot of inserts, consider doing them in a large batch using the copy command. This is much faster than individual inserts. ? Statements not in a begin work/commit transaction block are considered to be in their own transaction. Consider performing several statements in a single transaction block. This reduces the transaction overhead. Also consider dropping and recreating indices when making large data changes. ? It is suggested that you purchase the "Performance Tuning guide" and tuning support from PostgreSQL Corp. . Specialized tuning tips: ? Internal tuning of PostgreSQL is a complex topic. You need a sound knowledge of source code and internals of postgresql. It is strongly recommended that only professionals attempt specialized tuning tips given below: ? You can disable fsync() by starting the postmaster with a -o -F option. This will prevent fsync() from flushing to disk after every transaction. But there is risk of losing data due to power/media failure. You can reduce the risk of losing data due to power failure by having the APC UPS (Uninterrupted Power Supply) and media failures by disk RAID systems (Antares-Sparc-Raid system, Software-Raid system, Old-Software-Raid system, Root-Raid system, Boot- Root-Raid system) to gaurd against media failures. ? Use the postmaster -B option to increase the number of shared memory buffers used by the back-end processes. If you make this parameter too high, the postmaster may not start up because you've exceeded your kernel's limit on shared memory space. Each buffer is 8K and the default is 64 buffers. ? Use the back-end -S option to increase the maximum amount of memory used by each backend process for temporary sorts. The -S value is measured in kilobytes, and the default is 512 (i.e., 512K). It is unwise to make this value too large, or you may run out of memory when query invokes several concurrent sorts. 9. PostgreSQL Supports Extremely Large Databases greater than 200 Gig PostgreSQL is already used by many companies supporting large databases. The following techniques are suggested : 9.1. CPU types - 32-bit or 64-bit 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.. 9.2. Multiple CPUs For large databases it is recommended that you use SMP boxes which have 4, 16 or 32 CPUs. Alternatively, you can use 4 or 5 single CPU boxes and you can partition the database into 4 or 5 seperate databases and each database running on a seperate box. Each CPU will be connected with fast NIC (100MBits) ethernet card. For example - if you have 200 tables in a database, you can distribute 200 tables to 4 database each having 50 tables. In this way, you are distributing the load evenly among 4 seperate machines. This is a cheaper alternative to 4-way CPU box. You would use NFS mounts in LAN, to accomplish this task. And each CPU "can see" all the databases i.e all the 200 tables. In future PostgreSQL may provide support for 'Queries across multiple databases' (already in the TODO list), which may appear in upcoming version 7.1. For example, queries across multiple databases using aliases a, b for table names can be like - ______________________________________________________________________ select a.col1, a.col2, b.col4, b.col7 from database1.my_tablea a, database2.my_tableb b where a.col1 = b.col3 and a.col4 = b.col9; update my_tablea set col1 = b.col2 from database1.my_tablea a, database2.my_tableb b where a.col4 = b.col9; ______________________________________________________________________ 9.3. Replication Server Replication server for large enterprises/businesses is available at and from . The support is sold ($$$$s) commercially by PostgreSQL Inc. You use replication server to provide redundancy and high availability. Replication server is a complex, sophisticated product. 10. 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. 11. 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 an 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 an 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. 11.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 an 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. 11.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 an 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''). 11.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 ______________________________________________________________________ 11.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. 11.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 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. 11.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 ------------------------------------------------------ ______________________________________________________________________ 12. 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) or ``''. 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 ? Zope at ``'' ? OpenACS at ``'' ? PHP script and Zend compiler at ``PHP+Zend compiler'' ? X-Designer supports C++, Java and MFC ? Qt for Windows95 and Unix at and ? Code Crusader is on linux cdrom, freeware based on MetroWorks Code Warrior ? Code Warrior from MetroWorks ? GNU Prof C++ IDE from (Redhat) Cygnus ? Borland C++ Builder for Linux ? Borland Java JBuilder for Linux 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 an IDE (integrated development environment) for Tcl/Tk called SpecTcl. ? Lesstiff Motif tool ? Vibe Java/C++ is at ? JccWarrior ? Tcl/Tk ? Object oriented extension of Tcl called INCR at ? Visual TCL site ? Visual TCL Redhat rpm at ? ? ? ? Java FreeBuilder ? SpecTCL ? Java RAD Tool for PostgreSQL Kanchenjunga ? Applixware Tool ? XWPE X Windows Programming Environment or at ? XWB X Windows Work Bench ? NEdit You can also use Borland C++ Builder, Delphi, Borland JBuilder, PowerBuilder on Windows95 connecting to PostgreSQL on unix box through ODBC/JDBC drivers. 13. Interface Drivers for PostgreSQL 13.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 is already included in the distribution. See main web site . It is included on the PostgreSQL CDROM. ? Open source code ODBC project is at ? 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 This is the official PostODBC site. ? FreeODBC package This is a free of cost version of ODBC. ? ODBC 32 Explorer for PostgreSQL 13.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. ? 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. 13.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. ? Sun's Java connectivity to PostgreSQL ? ? 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 ? JDBC FAQ site The JDBC home, guide and FAQ are located at - ? JDBC HOME ? JDBC guide ? JDBC FAQ See the section - ``Testing Java PostgreSQL interface'' 13.4. Java for PostgreSQL Java programmers can find these for PostgreSQL very useful. ? and see postgresql- jdbc-*.rpm ? See the section - ``Testing Java PostgreSQL interface'' 14. Perl Database Interface (DBI) Driver for PostgreSQL 14.1. Perl 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 language has a very long life just like "C" language, and Perl will be in use for thousands of years in the future! Perl runs 10 times faster than Java and sometimes faster than even "C". Java is a very complex system with virtual machine and interpreter which makes it extremely slow, unstable and unreliable. Perl is very simple, fast and object oriented. 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 ? Perl Home page : ? Perl tutorial, look for Tutorial title at : ? Perl FAQ is at : ? First get Mother of all Perl Modules from and type '/usr/bin/cpan', 'man CPAN', see thousands of modules . ? Perl GUI User Interfaces Perl-Qt rpm : and look for PerlQt-1.06-1.i386.rpm ? Perl GUI User Interfaces Perl-Qt : ? Perl GUI User Interfaces Perl-XForms : and look for Xforms4Perl-0.8.4-1.i386.rpm ? Perl GUI User Interfaces Perl-Tk : ? Perl GUI kits : ? Perl Database Interfaces : ? Perl to "C" translator : and look for Compiler-a3.tar.gz ? Compile Perl to to executable. Perl2Exe is a command line utility for converting perl scripts to executable files ? Bourne shell to Perl translator : ? 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.* 14.2. Perl Database Interface DBI 14.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. The Database Drivers (Perl DBI) initiative has standardized the interface to a number of commercial database engines, so that you can move from, say, Oracle to PostgreSQL with a minimum of effort. 14.2.2. DBD driver for PostgreSQL Before you install DBD PostgreSQL (Driver) you must install DBI. Get DBI driver from ? First get Mother of all Perl Modules from and type '/usr/bin/cpan', 'man CPAN', see thousands of modules . ? DBI Modules . ? DBI Modules ? DBI FAQ ? References for Perl DBI ? DBI Mailing Lists ? Perl Database references ? Download DBI rpm (Caution: may be old version) Get DBD-Pg from below ? First get Mother of all Perl Modules from and type '/usr/bin/cpan', 'man CPAN', see thousands of modules . ? DBD Modules . and look for DBD-pg files or at DBD . ? Comprehensive Perl Archive Network CPAN Go here select 'Database' (located above Search box) and click on 'Go' button. ? Pre-compiled package for Windows NT/2000 is available at ? Download DBD rpm (Caution: may be old version) ? Perl Modules (thousands of them) . 14.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 14.2.4. DBI Documents There are a few information sources on DBI. POD documentation: PODs are chunks of documentation usually embedded within perl programs that document the code ``in place'', providing an 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 ? DBI Perl Journal website ? ``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 ? 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 ? European Mailing List Archives 14.2.5. 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 DBI 'emulation layer' for the Win32::ODBC module. It's called DBI::W32ODBC. You will need the Win32::ODBC module. ? Win32 DBI ? Win32 ODBC ? Perl interface to Microsoft SQL server 14.2.6. 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 : ? Support ? Support ? Training 14.3. Testing Perl interface See the section - ``Testing Perl PostgreSQL interface'' 15. PostgreSQL Management Tools 15.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 ? ? 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=: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. 15.2. GtkSQL Graphical Query Tool for PostgreSQL GtkSQL is a graphical query tool (like PostgreSQL's psql). It is released under the GNU GPL license, and was developped using Gtk+ 1.2.3 and PostgreSQL 6.3. The main site of GtkSQL is at Its main features are : 1. multiple SQL buffers 2. SQL keywords, table names and fields autocompletion 3. easy displaying of table definition 4. PostgreSQL and MySQL support (and easy addition of other databases) The current version is GtkSQL v. 0.3. You can get the source from 15.3. 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. ? ? Email: keidav@whidbey.com ? in file tcl_syb/wisql.html ? ? Email: de@ucolick.org 15.4. 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. 15.5. MPMGR - A Database Management Tool for PostgresSQL MPMGR will provide a graphical management interface for PostgresSQL. You can find it at ? ? Email: keidav@mutinybaysoftware.com ? ? Email: keidav@whidbey.com ? in file tcl_syb/wisql.html ? WISQL for PostgreSQL ? Email: de@ucolick.org 15.6. PgAdmin, PhpPgAdmin tools ? PgAdmin tool for Windows 95/NT Database design tool for PostgreSQL for Windows 95/NT ? Web based admin tool - PhpPgAdmin for Postgresql is at 15.7. PgBash - SQL shell tool PgBash has functionality which is similar to psql. And, PgBash provides the useful functionality in making flexible interactive operational environment using bash alias, function, the history editing, etc. The main site of PgBash is at PgBash is a shell which included the "direct SQL" or the "embedded SQL" interface for PostgreSQL by the improvement on the bash(current version 2.03) shell. PgBash can be used as a log-in shell, a sub- shell(the shell started from a shell) and a shell program use. Here, "Direct SQL" shows functionality which immediately outputs the result to the "standard output". And "Embedding SQL" shows functionality which sets the retrieval result into the shell variable and which deals with in shell script language. SQL statement(terminal character is a semicolon) is handled as one shell command, it is possible to execute the SQL statement with pipeline, redirection and background_job options. In addition, by using the exec_sql command, it is possible to execute SQL statement with the various options. The executive example of SQL is shown next. ______________________________________________________________________ prompt> /usr/local/bin/pgbash .....Start of pgbash pgbash> connect to db2@xxx.com user sakaida; .....connect to database pgbash> select * from test limit 100; | more .....with pipeline pgbash> select * from test; > /tmp/sel.dat & .....with redirect+background_Job pgbash> addr='Osaka' pgbash> insert into test values( > 111,'name', .....can start new line > '$addr' .....use shell variable > ); pgbash> connect to db3@yyy.com user postgres; pgbash> set connection db2; .....set current D/B to db2 pgbash> select * from test; .....select db2's test pgbash> exec_sql -d db3 "select * from test3".....change connection to db3 pgbash> ls pgbash> begin; pgbash> declare cur cursor for select * from test; pgbash> fetch in cur into :AA,:BB; .....set shell variables pgbash> echo "AA=$AA, BB=$BB" pgbash> end; pgbash> fc fetch .....edit history and go pgbash> !echo .....retry echo pgbash> disconnect all .....disconnect all connections pgbash> exit .....End of pgbash ______________________________________________________________________ 15.8. Webmin Tool for PostgreSQL The webmin tool (administration of an Unix machine through a, secure if you want, webpage) has a PostgreSQL module as of the latest release (version 0.82). With this module you can add users, groups, databases, tables, view tables. You can find webmin on 16. CPUs for PostgreSQL See the document for list of CPUs available for PostgreSQL and also this document gives details on CPU design. The following CPUs (both 64-bit and 32-bit) are available for PostgreSQL. All these CPUs run Linux. ? Main CPU site is : Google Search engine CPU site "Computers>Hardware>Components>Microprocessors" The following is GNU/GPL open-source CPU list: ? Open-source CPU site - Google Search "Computers>Hardware>Open Source" ? OpenRISC 1000 Free 32-bit processor IP core competing with proprietary ARM and MIPS is at ? OpenRISC 2000 is at ? European Space Agency's ESA-32bit and ESA-64bit CPUs "LEON" Sparc ? GNU/GPL Freedom 64-bit F-CPU or at mirror site at ? STM 32-bit, 2-way superscalar RISC CPU ? Free microprocessor and DSP IP cores written in Verilog or VHDL ? Free hardware cores to speed development ? Opencores org - open source, free IP cores ? Linux open hardware and free EDA systems ? ARM CPU ? Cogent CPUs The following is commercial CPU list: ? Russian E2k 64-bit CPU (Very fast CPU !!!) website : Elbrus is now partnered (alliance) with Sun Microsystems of USA ? Korean CPU from Samsung 64-bit CPU original from DEC Alpha Alpha-64bit CPU is at Now there is collaboration between Samsumg, Compaq of USA on Alpha CPU ? Intel IA 64 ? Transmeta crusoe CPU and in near future Transmeta's 64-bit CPU ? Sun Ultra-sparc 64-bit CPU or ? MIPS RISC CPUs ? Silicon Graphics MIPS Architecture CPUs ? IDT MIPS Architecture CPUs ? IBM Power PC (motorola) ? Motorola embedded processors. SPS processor based on PowerPC, M- CORE, ColdFire, M68k, or M68HC cores ? Hitachi SuperH 64-bit RISC processor SH7750 sold at $40 per cpu in quantities of 10,000 ? Fujitsu 64-bit processor ? HAL-Fujitsu (California) Super-Sparc 64-bit processor also compatible to Sun's sparc architecture. ? 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.. ? Other CPUs from other countries (Taiwan, Korea, Japan) ?? Let me know... Other important CPU sites are at - ? World-wide 24-hour news on CPUs ? The computer architecture site is at ? ARM CPU ? Great CPUs ? Microdesign resources 17. 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 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 4-port Manual KVM switch (PS/2) is about $89.99 Part No. DDS1354 ? Network Technologies Inc (120 dollars/PC 8 ports) which lists ? Scene Double Inc, England ? Cybex corporation ? Raritan Inc ? RealStar Solutions Inc ? Belkin Inc ? Better Box Communications Ltd. ? 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 ? Online store ? Bidding store Get RedHat (or some other distribution of) Linux cdrom from below - ? Linux System Labs Web site: 7 (U.S. dollars) ? Cheap Bytes Inc Web site: 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! 18. 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 Perl based Application Servers like Mason, WIRM, Velocigen, Enhydra(Java) and Zope(Python) and commercial Web- Application-Servers are IBM Websphere, BEA Weblogic. It is recommeded that you use secure web server like Apache + mod_ssl + OpenSSL. See Redhat StrongHold secure server at . Web Application Servers can be classified according to the programming language which they support. You must choose a Web Application server based on the programming language which you like the most. Classifications of Web Application servers are: ? Based on PERL language ? Based on PHP language (which is similar to PERL, little Java-like) ? Based on Python language (Object oriented scripting language) ? Based on Java language (Sun Microsystems Java) ? Based on Tcl language (Tcl/Tk - called "Tickle" scripting language) ? Based on C++ language (C++ and CORBA) ? Based on Pike (C++ like scripting language) 18.1. PERL Web Application Servers Perl language has a very long life just like "C" language, and Perl will be in use for a long time in the future! Perl runs 3 times faster than Java for some operations (but Java runs faster than perl for some operations). Java is a very complex system with virtual machine and interpreter which makes it extremely slow, unstable and unreliable. Perl is very simple, fast and object oriented. Also Perl programs can be easily compiled for even better performance. Use Perl2Exe which is a command line utility for converting perl scripts to executable files The following Web Applicaiton servers are available for PERL ? Mason is a powerful Perl-based web site development and delivery engine. With Mason you can embed Perl code in your HTML and construct pages from shared, reusable components. ? BingoX is an open source, object oriented Web Application Framework written in mod_perl meant to dramatically reduce the time required to build large dynamic, database driven web sites and applications. ? SmartWorker is a collection of Perl classes that allow you to build web applications like they were true applications and not just HTML templates with random embedded code. SmartWorker ? Apache-Perl integration project With mod_perl it is possible to write Apache modules entirely in Perl. In addition, the persistent interpreter embedded in the server avoids the overhead of starting an external interpreter and the penalty of Perl start-up time. Visit and also see mod_perl_garden project at ? Apache::ASP provides an Active Server Pages port to the Apache Web Server with Perl as the host scripting language. Apache::ASP allows a developer to create dynamic web applications with session management and embedded perl code. There are also many powerful extensions, including XML taglibs, XSLT rendering, and new events not originally part of the ASP API. ? WIRM (Web Interface Repository Manager) is a Perl-based application server that provides a high-level programming environment for developing web information systems. The WIRM consists of an object- relational database and a suite of Perl interfaces for visualizing, integrating and analyzing heterogeneous multimedia data. WIRM provides facilities for creating context-sensitive views over a multimedia database, allowing developers to rapidly build dynamic web sites that adapt their content and presentation to multiple classes of end-users. Visit ? EmbPerl Embperl gives you the power to embed Perl code in your HTML documents. Using Perl means being able to use a very elaborate programming language, which is widely used for WWW purposes. You can also use hundreds of Perl modules which have already been written - including DBI - for database access to a growing number of database systems. ? ePerl interprets an ASCII file bristled with Perl 5 program statements by evaluating the Perl 5 code while passing through the plain ASCII data. It can operate in various ways: As a stand-alone Unix filter or integrated Perl 5 module for general file generation tasks and as a powerful Webserver scripting language for dynamic HTML page programming. ? XPP XPP stands for 'XPP Parses Perl' or 'XPML Page Parser', and is a fast/efficient HTML parser that parses embedded perl, as well as HTML like tags, from dynamic html pages called XPML pages. ? Gamla - a perl-based RAD and application server The gamla project aims to create a Rapid Application Development (RAD) tool and a web application server based on Perl. All the source code produced by the Gamla project will be under the public domain. Gamla at ? AxKit is an XML Application Server for Apache (and mod_perl). It provides on-the-fly conversion from XML to any format, such as HTML, WAP or text using either W3C standard techniques, or flexible custom code. AxKit also uses a built-in Perl interpreter to provide some amazingly powerful techniques for XML transformation. The emphasis with AxKit is on separation of content from presentation. The pipelining technique that AxKit uses allows content to be converted to a presentable format in stages, allowing certain platforms to see data differently to others. AxKit allows web designers to focus on web site design, content developers to work on a purely content basis, and webmasters to focus on their core competencies. Commercial Web Application Servers for Perl: ? Zelerate AllCommerce is a commerce, content, customer and relationship management system. This high- performance, scalable Internet application is written in Perl and uses a backend database. ? VelociGen serves dynamic content stored in XML, the database or live data feeds as fast as static HTML - up to 60x faster than CGI without the need to modify your exiting application. VelociGen also makes new development easier with server-side XML tags, crash protection and load balancing across multiple machines. VelociGen plugs seamlessly into any Web server on any platform, increasing server performance and speeding the response times of dynamic content driven web sites. VelociGen can process large volumes of simultaneous requests as much as 10x faster than Java Servlets and 4x faster than Cold Fusion. Velocigen 18.2. PHP Web Application Servers The following Web Applicaiton servers are available for PHP ? Midgard PHP Web Application server is based on the PHP scripting language and PHP runs extremely fast - faster than Java. The main site of Midgard is at PHP can be compiled with Zend compiler and optimizer . PHP runs very fast and is about 5 to 10 times faster than Java. See ``Midgard Installation'' and also PHP HOWTO at ? Ariadne is a web application system. It consists of a complete framework for the easy development and management of web applications, using PHP. The system uses a modular approach, using abstract interfaces for all transactions. This results in maximum freedom to change parts of the systems workings or add new functionality without needing to reprogram other parts 18.3. Lutris Corp "Enhydra Enterprise" (Java) Enhydra supports PostgreSQL database. Enhydra is an immensely popular Java/XML/J2EE Web-Application-Server created by 'Lutris Corporation'. It is the world's best Java/XML/J2EE Web-Application server. It supports EJB, Servlets, JSP, JNDI, JDBC, JTA, CORBA, XMLC/Rocks, DODS and internationalization. It is used by many large fortune 500 companies in US and Europe. Companies like "French Telecom" are directly sponsoring the Enhydra. It is written in 100% pure Java and is available from . Enhydra is an open source code project but is commercially sold and supported by Lutris Corp. Visit See tutorial on setting up the PostgreSQL with Enhydra and see also Setup database with Enhydra . You would use Borland Corp's JBuilder along with Enhydra. JBuilder is at See also Enterprise Java HOWTO at 18.4. Zope (Python) 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 Python is at 18.5. OpenACS (Tcl Language) OpenACS (Open ArsDigita Community System) is an advanced toolkit for building scalable, community-oriented web applications. It relies on AOLserver, a web/application server, and PostgreSQL, a true ACID-compliant RDBMS. These are two high-quality products available for free under open-source licenses. ACS is created by ArsDigita, , their ACS (ArsDigita Community System) attempts to be as DB independent as possible, though it is based on Oracle (hence Open ACS have to take time out to port it). See also 18.6. C++, CORBA Web Application Servers ? PortalSphere Web Application Server is built in C++ and runs on Unix (and Linux) for ultimate in speed and stability. Strictly adhering to the CORBA standard, PortalSphere supports both the standard Internet HTTP communications protocol and the IIOP point- to-point protocol for ultra-high-speed client-server links. Coupled with direct (native) access to all popular databases, these features give PortalSphere lightening-fast performance and the unique inherent ability to support real-time events over the internet. PortalSphere is - Up to 100 X faster than HTTP/CGI, Direct (native) access to most popular databases, Scalable to 10,000+ concurrent user sessions. Visit PortalSphere at . ? FlashPoint C++,C,PERL Web Application Server project exists to support high speed web application service in a multi-threaded environment, to support a variety of development languages including C & C++, and to support good software engineering practices to a degree difficult in many other environments. It can be used alongside Apache, and in some instances can replace it, depending on your needs Visit and download the from FlashPoint Redhat RPM