Σχόλια 0

Το κείμενο του εγγράφου

Database−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)Table of ContentsDatabase−SQL−RDBMSHOW−TOdocumentforLinux(PostgreSQLObjectRelationalDatabaseSystem)AlDev(AlavoorVasudevan)alavoor@yahoo.com...............................................................................11.Introduction..........................................................................................................................................12.LawsofPhysicsapplytoSoftware!.....................................................................................................13.WhatisPostgreSQL?..........................................................................................................................14.Whichone?PostgreSQLorMySQL?.................................................................................................15.Wheretogetit?...................................................................................................................................16.PostgreSQLQuick−InstallationInstructions ......................................................................................27.QuickStartGuide................................................................................................................................28.PerformanceTuningofPostgreSQLserver.........................................................................................29.PostgreSQLSupportsExtremelyLargeDatabasesgreaterthan200Gig............................................210.HowcanItrustPostgreSQL?RegressionTestPackagebuildscustomerconfidence.....................211.SecurityofDatabase .........................................................................................................................312.GUIFrontEndToolforPostgreSQL(GraphicalUserInterface)......................................................313.InterfaceDriversforPostgreSQL......................................................................................................314.PerlDatabaseInterface(DBI)DriverforPostgreSQL .....................................................................315.PostgreSQLManagementTools........................................................................................................316.CPUsforPostgreSQL........................................................................................................................317.Settingupmulti−boxesPostgreSQLwithjustonemonitor..............................................................318.Web−Application−ServersforPostgreSQL......................................................................................319.ApplicationsandToolsforPostgreSQL............................................................................................420.DatabaseDesignTool−EntityRelationDiagramTool....................................................................421.WebDatabaseDesign/ImplementationtoolforPostgreSQL−EARP..............................................422.PHPHypertextPreprocessor−Server−sidehtml−embeddedscriptinglanguageforPostgreSQL .423.PythonInterfaceforPostgreSQL.......................................................................................................424.GatewaybetweenPostgreSQLandtheWWW−WDB−P95...........................................................525."C","C++",ESQL/ClanguageInterfacesandBitwiseOperatorsforPostgreSQL..........................526.JapaneseKanjiCodeforPostgreSQL................................................................................................527.PostgreSQLPorttoWindows95/WindowsNT................................................................................528.MailingLists......................................................................................................................................529.DocumentationandReferenceBooks................................................................................................530.TechnicalsupportforPostgreSQL.....................................................................................................631.EconomicandBusinessAspects........................................................................................................632.ListofOtherDatabases......................................................................................................................633.InternetWorldWideWebSearchingTips.........................................................................................634.Conclusion.........................................................................................................................................635.FAQ−QuestionsonPostgreSQL......................................................................................................636.OtherFormatsofthisDocument........................................................................................................637.CopyrightandLicense.......................................................................................................................6Appendix..................................................................................................................................................638.AppendixA−SyntaxofANSI/ISOSQL1992 ................................................................................639.AppendixB−SQLTutorialforbeginners .......................................................................................640.AppendixC−LinuxQuickInstallInstructions ................................................................................641.AppendixC−MidgardInstallation ..................................................................................................61.Introduction..........................................................................................................................................71.1QuantumComputers−QuantumPhysicsUseful!!..........................................................................92.LawsofPhysicsapplytoSoftware!.....................................................................................................9Database−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)iTable of Contents3.WhatisPostgreSQL?........................................................................................................................113.1WhitePaper......................................................................................................................................124.Whichone?PostgreSQLorMySQL?...............................................................................................134.1PostgreSQLdefeatedOracle,IBMDB2,MSSQLserverandothers!!..........................................134.2MySQLandotherduplicateRDBMSes..........................................................................................134.3LimitationsofMySQL.....................................................................................................................145.Wheretogetit?.................................................................................................................................146.PostgreSQLQuick−InstallationInstructions ...................................................................................156.1InstallandTest.................................................................................................................................156.2PostgreSQLRPMs...........................................................................................................................166.3MaximumRPM...............................................................................................................................166.4ExamplesRPM ..............................................................................................................................166.5TestingPyGreSQL−Pythoninterface ..........................................................................................166.6TestingPerl−Perlinterface ..........................................................................................................176.7Testinglibpq,libpq++interfaces ...................................................................................................176.8TestingJavainterfaces ...................................................................................................................186.9Testingecpginterfaces ..................................................................................................................196.10TestingSQLexamples−Userdefinedtypesandfunctions ........................................................196.11TestingTcl/Tkinterfaces...............................................................................................................196.12TestingODBCinterfaces...............................................................................................................196.13TestingMPSQLMotif−worksheetinterfaces................................................................................196.14Verification....................................................................................................................................206.15EmergencyBugfixes.....................................................................................................................207.QuickStartGuide..............................................................................................................................207.1Creating,Dropping,RenamingDatabase........................................................................................217.2Creating,Droppingusers.................................................................................................................217.3Creating,DroppingGroups..............................................................................................................227.4Create,Edit,Dropatable.................................................................................................................227.5Create,Edit,Droprecordsinatable................................................................................................237.6SwitchactiveDatabase....................................................................................................................237.7BackupandRestoredatabase .........................................................................................................247.8Securityofdatabase.........................................................................................................................257.9Onlinehelp.......................................................................................................................................257.10CreatingTriggersandStoredProcedures......................................................................................267.11PostgreSQLDocumentation..........................................................................................................278.PerformanceTuningofPostgreSQLserver.......................................................................................278.1OSTuningforDatabaseserver ......................................................................................................278.2TuningDatabaseserverprocess......................................................................................................289.PostgreSQLSupportsExtremelyLargeDatabasesgreaterthan200Gig..........................................289.1CPUtypes−32−bitor64−bit..........................................................................................................289.2MultipleCPUs.................................................................................................................................299.3ReplicationServer ...........................................................................................................................2910.HowcanItrustPostgreSQL?RegressionTestPackagebuildscustomerconfidence...................2911.SecurityofDatabase ......................................................................................................................3011.1UserAuthentication.......................................................................................................................3011.2Host−BasedAccessControl..........................................................................................................3111.3AuthenticationMethods ...............................................................................................................32Database−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)iiTable of Contents11.4AccessControl...............................................................................................................................3211.5SecureTCP/IPConnectionviaSSH..............................................................................................3311.6KerberosAuthentication................................................................................................................3312.GUIFrontEndToolforPostgreSQL(GraphicalUserInterface)....................................................3413.InterfaceDriversforPostgreSQL....................................................................................................3513.1ODBCDriversforPostgreSQL.....................................................................................................3513.2UDBCDriversforPostgreSQL.....................................................................................................3613.3JDBCDriversforPostgreSQL ......................................................................................................3613.4JavaforPostgreSQL......................................................................................................................3614.PerlDatabaseInterface(DBI)DriverforPostgreSQL ..................................................................3714.1PerlinterfaceforPostgreSQL........................................................................................................3714.2PerlDatabaseInterfaceDBI..........................................................................................................37WHATISDBI?.......................................................................................................................38DBDdriverforPostgreSQL.....................................................................................................38TechnicalsupportforDBI........................................................................................................38DBIDocuments........................................................................................................................38IsDBIsupportedunderWindows95/NTplatforms?.............................................................39CommercialSupportandTraining...........................................................................................4014.3TestingPerlinterface ....................................................................................................................4015.PostgreSQLManagementTools......................................................................................................4015.1PGACCESS−AGUIToolforPostgreSQLManagement...........................................................4015.2GtkSQLGraphicalQueryToolforPostgreSQL...........................................................................4115.3WindowsInteractiveQueryToolforPostgreSQL(WISQLorMPSQL).....................................4215.4InteractiveQueryTool(ISQL)forPostgreSQLcalledPSQL.......................................................4215.5MPMGR−ADatabaseManagementToolforPostgresSQL........................................................4215.6PgAdmin,PhpPgAdmintools........................................................................................................4315.7PgBash−SQLshelltool................................................................................................................4315.8WebminToolforPostgreSQL.......................................................................................................4416.CPUsforPostgreSQL......................................................................................................................4417.Settingupmulti−boxesPostgreSQLwithjustonemonitor............................................................4518.Web−Application−ServersforPostgreSQL....................................................................................4618.1PERLWebApplicationServers ..................................................................................................4718.2PHPWebApplicationServers .....................................................................................................4818.3LutrisCorp"EnhydraEnterprise"(Java) .....................................................................................4918.4Zope(Python) ..............................................................................................................................4918.5OpenACS(TclLanguage) ...........................................................................................................4918.6C++,CORBAWebApplicationServers .....................................................................................4918.7Pike,RoxenWebApplicationServer...........................................................................................5018.8WebApplicationServersDirectory .............................................................................................5019.ApplicationsandToolsforPostgreSQL..........................................................................................5019.1PostgreSQL4GLforwebdatabaseapplications−AppGENDevelopmentSystem....................5019.2WWWWebinterfaceforPostgresSQL−DBENGINE................................................................5119.3ApacheWebserverModuleforPostgreSQL−NeoSoftNeoWebScript.......................................5219.4HEITMLserversideextensionofHTMLanda4GLlanguageforPostgreSQL..........................5319.5AmericaOn−lineAOLWebserverforPostgreSQL.....................................................................5419.6Problem/ProjectTrackingSystemApplicationToolforPostgreSQL...........................................5519.7ConvertdbasedbffilestoPostgreSQL..........................................................................................55Database−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)iiiTable of Contents19.8ConvertMicrosoftAccessMDBdatabasefilestoPostgreSQL....................................................5519.9ZeosClient.....................................................................................................................................5619.10ReportWriterinJava...................................................................................................................5620.DatabaseDesignTool−EntityRelationDiagramTool..................................................................5621.WebDatabaseDesign/ImplementationtoolforPostgreSQL−EARP............................................5621.1WhatisEARP?.............................................................................................................................5621.2Implementation..............................................................................................................................5721.3Howdoesitwork?........................................................................................................................5721.4WheretogetEARP?.....................................................................................................................5722.PHPHypertextPreprocessor−Server−sidehtml−embeddedscriptinglanguageforPostgreSQL 722.1MajorFeatures...............................................................................................................................5822.2PHP−BriefHistory.......................................................................................................................5922.3So,whatcanIdowithPHP?........................................................................................................5922.4Asimpleexample..........................................................................................................................5922.5CGIRedirection.............................................................................................................................60Apache1.0.xNotes...................................................................................................................60NetscapeHTTPD......................................................................................................................61NCSAHTTPD..........................................................................................................................6122.6RunningPHPfromthecommandline...........................................................................................6122.7PHPGempackage..........................................................................................................................6223.PythonInterfaceforPostgreSQL.....................................................................................................6223.1WheretogetPyGres?...................................................................................................................6223.2Informationandsupport.................................................................................................................6223.3TestingPythoninterface ...............................................................................................................6324.GatewaybetweenPostgreSQLandtheWWW−WDB−P95.........................................................6324.1Aboutwdb−p95.............................................................................................................................6324.2DoesthePostgreSQLserver,pgperl,andhttpdhavetobeonthesamehost?..............................6325."C","C++",ESQL/ClanguageInterfacesandBitwiseOperatorsforPostgreSQL........................6425.1"C"interface ..................................................................................................................................6425.2"C++"interface .............................................................................................................................6425.3ESQL/C .........................................................................................................................................6425.4BitWiseOperatorsforPostgreSQL...............................................................................................6526.JapaneseKanjiCodeforPostgreSQL..............................................................................................6527.PostgreSQLPorttoWindows95/WindowsNT..............................................................................6527.1AuthorsofNTport.........................................................................................................................6627.2InstalltheCygwinpackage ...........................................................................................................6627.3TuneupBashWindow....................................................................................................................6627.4InstalltheAndyPipertools ..........................................................................................................6727.5InstallLudovicLange'sCygwin32IPCpackage ..........................................................................6727.6InstallPostgreSQL .......................................................................................................................6828.MailingLists....................................................................................................................................6928.1E−mailaccountforPostgreSQL....................................................................................................6928.2EnglishMailingList......................................................................................................................6928.3ArchiveofMailingList.................................................................................................................7028.4SpanishMailingList......................................................................................................................7029.DocumentationandReferenceBooks..............................................................................................7029.1UserGuidesandManuals..............................................................................................................70Database−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)ivTable of Contents29.2OnlineDocumentation...................................................................................................................7029.3UsefulReferenceTextbooks..........................................................................................................7129.4ANSI/ISOSQLSpecificationsdocuments−SQL1992,SQL1998 ...........................................7229.5SyntaxofANSI/ISOSQL1992.....................................................................................................7229.6SyntaxofANSI/ISOSQL1998.....................................................................................................7229.7SQLTutorialforbeginners............................................................................................................7229.8TemporalExtensiontoSQL92......................................................................................................7229.9Part0−AcquiringISO/ANSISQLDocuments ..........................................................................7329.10Part1−ISO/ANSISQLCurrentStatus......................................................................................7729.11Part2−ISO/ANSISQLFoundation...........................................................................................7929.12Part3−ISO/ANSISQLCallLevelInterface.............................................................................8029.13Part4−ISO/ANSISQLPersistentStoredModules...................................................................8029.14Part5−ISO/ANSISQL/Bindings...............................................................................................8229.15Part6−ISO/ANSISQLXAInterfaceSpecialization(SQL/XA)...............................................8229.16Part7−ISO/ANSISQLTemporal..............................................................................................82INTRODUCTION....................................................................................................................83ACASESTUDY−STORINGCURRENTINFORMATION................................................83ACASESTUDY−STORINGHISTORYINFORMATION.................................................83ACASESTUDY−PROJECTION..........................................................................................84ACASESTUDY−JOIN.........................................................................................................85ACASESTUDY−AGGREGATES.......................................................................................86SUMMARY..............................................................................................................................8729.17Part8−ISO/ANSISQLMULTIMEDIA(SQL/MM)................................................................8830.TechnicalsupportforPostgreSQL...................................................................................................8930.1CommercialSupport......................................................................................................................8931.EconomicandBusinessAspects......................................................................................................9032.ListofOtherDatabases....................................................................................................................9033.InternetWorldWideWebSearchingTips.......................................................................................9134.Conclusion.......................................................................................................................................9135.FAQ−QuestionsonPostgreSQL....................................................................................................9136.OtherFormatsofthisDocument......................................................................................................9237.CopyrightandLicense.....................................................................................................................9338.AppendixA−SyntaxofANSI/ISOSQL1992 .............................................................................9439.AppendixB−SQLTutorialforbeginners ..................................................................................13639.1TutorialforPostgreSQL..............................................................................................................13639.2InternetURLpointers..................................................................................................................13639.3On−lineSQLtutorials..................................................................................................................13740.AppendixC−LinuxQuickInstallInstructions ...........................................................................13741.AppendixC−MidgardInstallation .............................................................................................13741.1TestingMidgardPHPServer......................................................................................................13841.2SecurityOpenSSL ......................................................................................................................138Database−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)vDatabase−SQL−RDBMS HOW−TO document forLinux (PostgreSQL Object Relational DatabaseSystem)Al Dev (Alavoor Vasudevan) alavoor@yahoo.comv45.0, 27 Jan 2001This document is a "practical guide" to very quickly setup a SQL Database engine and front end tools on anUnix system. It also discusses the International standard language ANSI/ISO SQL and reviews themerits/advantages of the SQL database engine developed by the world−wide internet in an "opendevelopment" environment. It is about HOW−TO setup a next generation Object Relational SQL Database"PostgreSQL" on Unix system which can be used as an Application Database Server or as a Web DatabaseServer. PostgreSQL attempts to implement current and future International ISO/ANSI SQL standards. Thisdocument also gives information on the database interface programs like Front End GUIs, RAD tools (RapidApplication Development), ODBC, JDBC drivers, "C", "C++", Java, Perl programming interfaces and WebDatabase Tools. Information given here applies to all Unix/Windows NT platforms and to all other SQLdatabases. It will be very useful for people who are new to Databases, SQL language and PostgreSQL. Thisdocument also has SQL tutorial, SQL syntax which would be very helpful for beginners. Experienced peoplewill find this document as an useful reference guide. For students, the information given here will enablethem to get the source code for PostgreSQL relational database system, from which they can learn as to howa RDBMS SQL database engine is created.1. Introduction•1.1 Quantum Computers − Quantum Physics Useful !!2.Laws of Physics apply to Software!3.What is PostgreSQL ?•3.1 White Paper4.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 MySQL5.Where to get it ?Database−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)16.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 fixes7.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 Documentation8.Performance Tuning of PostgreSQL server•8.1 OS Tuning for Database server•8.2 Tuning Database server process9.PostgreSQL Supports Extremely Large Databasesgreater than 200 Gig•9.1 CPU types − 32−bit or 64−bit•9.2 Multiple CPUs•9.3 Replication Server10.How can I trust PostgreSQL ? Regression Test Packagebuilds customer confidenceDatabase−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)6. PostgreSQL Quick−Installation Instructions 211.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 Authentication12.GUI FrontEnd Tool for PostgreSQL (Graphical UserInterface)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 PostgreSQL14.Perl Database Interface (DBI) Driver for PostgreSQL•14.1 Perl interface for PostgreSQL•14.2 Perl Database Interface DBI•14.3 Testing Perl interface15.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 PostgreSQL16.CPUs for PostgreSQL17.Setting up multi−boxes PostgreSQL with just onemonitor18.Web−Application−Servers for PostgreSQL•18.1 PERL Web Application Servers•18.2 PHP Web Application ServersDatabase−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)11. Security of Database 3•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 Directory19.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 Java20.Database Design Tool − Entity Relation Diagram Tool21.Web Database Design/Implementation tool forPostgreSQL − 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−sidehtml−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.6 Running PHP from the command line•22.7 PHPGem package23.Python Interface for PostgreSQL•23.1 Where to get PyGres ?•23.2 Information and support•23.3 Testing Python interfaceDatabase−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)19. Applications and Tools for PostgreSQL 424.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 BitwiseOperators for PostgreSQL•25.1 "C" interface•25.2 "C++" interface•25.3 ESQL/C•25.4 BitWise Operators for PostgreSQL26.Japanese Kanji Code for PostgreSQL27.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 PostgreSQL28.Mailing Lists•28.1 E−mail account for PostgreSQL•28.2 English Mailing List•28.3 Archive of Mailing List•28.4 Spanish Mailing List29.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 InterfaceDatabase−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)24. Gateway between PostgreSQL and the WWW − WDB−P95 5•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.17 Part 8 − ISO/ANSI SQL MULTIMEDIA (SQL/MM)30.Technical support for PostgreSQL•30.1 Commercial Support31.Economic and Business Aspects32.List of Other Databases33.Internet World Wide Web Searching Tips34. Conclusion35.FAQ − Questions on PostgreSQL36.Other Formats of this Document37.Copyright and LicenseAppendix38.Appendix A − Syntax of ANSI/ISO SQL 199239.Appendix B − SQL Tutorial for beginners•39.1 Tutorial for PostgreSQL•39.2 Internet URL pointers•39.3 On−line SQL tutorials40.Appendix C − Linux Quick Install Instructions41.Appendix C − Midgard Installation•41.1 Testing Midgard PHP Server•41.2 Security OpenSSLDatabase−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)30. Technical support for PostgreSQL 61. IntroductionThe purpose of this document is to provide comprehensive list of pointers/URLs to quickly setup PostgreSQLand 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 primaryreason 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 andvery easy to use. Database is the most VITAL SYSTEM as it stores mission critical information of everycompany in this world. Each and every industry in this world needs a database system. Industries liketelecom, automobile, banks, airlines, etc.. will not function efficiently without a database system. The mostpopular database systems are based on the International Standard Organisation (ISO) SQL specifications andANSI SQL (American) standards. The current specifications widely used in the industry are ISO/ANSI SQL1992. Upcoming standard is the SQL 1998/99 which is also called SQL−3 is still under development. Populardatabase like Oracle, Sybase and Informix systems are based on these standards or are trying to implementthese standards.Without a standard like ANSI/ISO SQL, it would be very difficult for the customer to develop an applicationonce 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/92and ANSI SQL/89 RDBMS is PostgreSQL. PostgreSQL is next generation Object relational database and istargeting on full compliance of SQL standards like ISO/ANSI SQL. PostgreSQL is the only free RDBMS inthe 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 stronglyadvised 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 Codedevelopment model'. Open Source Code model is the one where the complete source code is given to you andthe development takes place on the internet by an extremely vast network of human brains. Future trendshows 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 fuelrapid adoption of PostgreSQL by the industry.By applying the principles of statistics, mathematics and science to software quality, you get the best qualityof software only in a 'Open Source Code System' like PostgreSQL, wherein the source code is open to a veryvast number of human brains inter−connected by the information super−highway. Greater the number ofhuman brains working, the better will be the quality of software. Open Source Code model will also preventRE−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 globalresources. Once a software work is done by others, then you DO NOT need to re−do that again. You will notbe wasting your valuable time on something which had already been WELL DONE. Your time is extremelyprecious and it must be utilized efficiently, because you have only 8 hours a day for doing work. As we willbe entering the 21st century, there will be a change in the way that you get software for your use. Everybodywill give first preference for the open source softwares like PostgreSQL, Linux.Database−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)1. Introduction 7If you buy binaries, you will not get any equity and ownership of source code. Source code is a very valuableasset and binaries have no value. Buying software may become a thing of the past. You only need to buygood hardware, it is worth spending money on the hardware and get the software from internet. Importantpoint is that it is the computer hardware which is doing bulk of the work. Hardware is the real work horseand software is just driving it. Computer hardware is so much more complex that only 6 nations in the worldso far have demonstrated the capability of designing and manufacturing computer chips/hardware. Designand manufacturing of computer chips is an advanced technology. It is a very complex process, capitalintensive, requires large investments in plant and production machines which deal with 0.18 micron (evensmaller than 0.18) technology. On a single small silicon chip millions of transistors/circuits are denselypacked. Companies like Applied Material, AMD, Intel, Cyrix, Hitachi, IBM and others spent significantnumber of man−years to master the high−technology like Chip Design, Micro−electronics andNano−electronics. Micro means (one−millionth of meter 10^−6), Nano means (one−billionth of meter10^−9). Current technology uses micro−electronics of about 0.35 micron using aluminum as conductors and0.25 micron sizes using copper as conductors of electrons. In near future the technology of 0.10 micron withcopper and even nano−electronics will be used to make computer chips. Aluminum conductors will bephased out by copper on computer chips, as copper is a better conductor of electrons. In photolithographyprocess extreme ultraviolet, X−ray or electron−beam techniques will be used to etch circuits for feature sizeless than 0.15 micron. In about 20 years from now, silicon chips will be phased out by molecular computersand bio chips which will be billions of times faster than silicon chips. Molecules are a group of atoms. Andatoms are tiny particles which makes up everything that you see in this world. Molecular computers will usethe molecules of matter as ultra−fast electronic on/off switches. When the switch is ON it indicates 1, andwhen 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 aless difficult technology.On other hand, each and every country in the world develops/makes software. In fact, any person in thisworld with a small low−cost PC can write software.Databases like Oracle, Informix, Sybase, IBM DB2 (Unix) are written using the "C" language and binariesare 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−createfrom scratch another database system which satisfies ANSI/ISO SQL. It will be a great advantage to take theDatabase−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)1. Introduction 8existing 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 ofmaintaining a high quality, low cost, extremely large user−base and developer−base. Those nations which donot use the 'Internet products' will be seriously missing "World−wide Internet Revolution" and will be left farbehind other countries. The reason is "Internet" itself is the world's LARGEST "software company" and is alarge 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 anatomic particle's traits, such as direction of spin, for creating a state. For example, when the spin is up, aparticle could be read as "one", when its spin is down, the particle would be read as "zero". Atoms andnuclei can exist in a state of superposition, where the values of one, zero and the range in between can berepresented concurrently. By entangling the spins of atoms, "qubits" can become wired together, enablingthem to function as a collective whole, bringing about a nonlinear computational power that farsurpasses the capabilities of supercomputers available today!! At atomic level Quantum Physics comes toassistance 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 likesoftware, this universe, mass, atoms, energy and even yourself! This chapter also shows why knowledge ofscience 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 ruleapplies to everything − database sytems, computer system, operating system, this universe and even your ownhuman body! It means that you should have complete source code and information about the system. It isimportant to understand how human body and atoms inside human body works since humans are creatingPostgreSQL, 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 ofscience and hence do not know how systems like MS Windows NT/95, Oracle, human body and this universeare created. A vast majority of people do not know what made the universe and MS Windows 95/NT andwhat is inside it. Complex systems are built from very simple basic building blocks like − millions ofuniverses are created, each universe in turn has millions of super−clusters, each super−cluster has millions ofgalaxies, each galaxy has millions of stars, some stars have many planets, each planet in turn is made upbillions of atoms.(In the history of this world, only one universe was created by a man in ancient India eonsago, but no other case had been reported in the modern history. There is only one man−madeuniverse) Creating an universe is a much more advanced technology and is more advanced than the atomicbomb which was dropped on Hiroshima and Nagasaki causing horrible destruction. Modern nuclearweapons are so tiny and powerful that if such a single nuclear bomb is dropped in pacific ocean then it cancompletely vaporise the planet earth! The total variety of weapons are infinity. There are weapons to eventerminate the universes (it is not a good idea to give nuke weapons technology to every person). Nuclearweapons and other more powerful divine weapons were used in the battle field in ancient India! Nobodybelieved Albert Eienstein (a scientist of 1900's) when he said nuclear weapons can be made which canvaporise big cities.Database−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)1.1 Quantum Computers − Quantum Physics Useful !!9Software like MS Windows 95 is created simply by "C" and assembler language programs which simply uses1 and 0 and universes like ours are created simply by dashing TWO dissimilar but proper of combination oftiny atomic particles of other dimensions. (Something interesting happened just before dashing of tinyparticles) 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 thisuniverse. The universe you are currently living in was NOT there − all the atoms inside the universe was notthere and not even TIME was existing!! Baby universe was born during big bang and started expanding andkept growing. Even today our universe is still expanding and is not static!! A person from another universe byname 'Brahma' created this universe you are currently living in. Knowledge is the MOTHER of thisuniverse!! 'Brahma' loved on 'Mother knowledge' before the baby universe in which you are living wasborn!! It is a deal similar to how you were born! Without any 'genes' from Mother Knowledge it is notpossible 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 willcompletely 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 systemsthat can be created is also infinity!! It is infinite cyclic process where universes are born and then later diedown. There are millions of universes, which are classified into 3 major categories. Infinite number universesand infinite variety of multi−dimensional atoms collapse down into few primary−dimensional−universe. Andprimary−dimensional universes collapse down into one single focus entity called 'eeshwar' (eeshara is asanskrit 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 theinternet speed is increasing everyday, and internet is becoming more and MORE reliable, the open−sourcecode 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 closedsource−code systems will eventually vanish from this planet.Developing a project like PostgreSQL requires resources like energy and time, hence PostgreSQL is aproduct of energy and time. Since energy and time can be explained only by science, there is a directco−relation between physics and software projects like PostgreSQL, Linux. Laws of science (Physics) applieseverywhere 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 yourfeet), 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. Modernalgebra 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 areONE 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 100years ago! And even today it is unknown to world population that internet is the largest software "powerhouse" and the largest "software company" in the world!Cells in the human brains consume energy while processing (creating software), by converting the chemicalenergy from food into electrical and heat energy. Even while you are reading this paragraph, the cells in yourbrain are burning out the fuel and are using tiny amounts of energy. All of these implies that human brain is athermodynamic heat engine. Because human brain is a thermodynamic engine, the laws of thermodynamicsapplies to brain and hence thermodynamics has indirect effects on software like PostgreSQL.Database−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)1.1 Quantum Computers − Quantum Physics Useful !!10There can be infinite number of colors, computer langauages, computer chip designs and theories but thereCANNOT be ONE SINGLE PERFECT color, computer language, design or system! What you can have isonly a NEAR PERFECT color(wavelength), system, database, or theory! Nature is like aKALIEDOSCOPE − there are infinite number of dimensions, infinite variety particles of other dimensionsbut 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 aNEAR PERFECT system (including a database software system). Individually, the energy of each personwill be minute, but by networking a large number of people, the total energy will be huge which can befocused 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 Joulesor in terms of massEnergy of each person = y gramsThe conversion factor between mass and energy is E = m * c * cwhere '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) ) WattsTotal Power of the world = n * (y / (8 * 60 * 60) ) Wattswhere 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. Greaterthe '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 energyand time which can produce much higher quality software products in much shorter time as compared tocommercial companies. Even very big companies like Microsoft and IBM cannot overpower and overrule thelaws 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 prevailand will be always much better than 'closed source code' system and it is possible to prove this statementscientifically. 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 DatabaseSystem targetting on ANSI ISO/SQL 1998, 92 and runs on diverse hardware platforms and Operatingsystems. The ultimate objective and the final goal of PostgreSQL is to become 100 % compliant to ANSI/ISOSQL 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 commercialdatabase systems could not match the quality, features and capabilities of PostgreSQL !! PostgreSQL is theDatabase−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)3. What is PostgreSQL ?11joint 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 anopen−source code system.The fundamental idea behind PostgreSQL is − once a module of code is written than you should not wasteeven 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 boughtIllustra Inc. and integrated with Informix. Illustra database was based on Postgres (earlier version ofPostgreSQL).PostgreSQL is an enhancement of the POSTGRES database management system, a next−generation DBMSresearch prototype. While PostgreSQL retains the powerful data model and rich data types of POSTGRES, itreplaces 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 thePostgreSQL development mailing list. The current coordinator is Marc G. Fournier•scrappy@postgreSQL.orgThis team is now responsible for all current and future development of PostgreSQL. Ofcourse, the databasecustomer himself is the developer of PostgreSQL! The development load is distributed among a very largenumber of database end−users on internet.The authors of PostgreSQL 1.01 were Andrew Yu and Jolly Chen. The original Postgres code, from whichPostgreSQL is derived, was the effort of many graduate students, undergraduate students, and staffprogrammers and working under the direction of Professor Michael Stonebraker at the University ofCalifornia, 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, SCOunix, NEXTSTEP, Unixware and all and every flavor of Unix. Port to Windows NT is done using Cygnuscygwin32 package.PostgreSQL and related items in this document are subject to the COPYRIGHT from University ofCalifornia, Berkeley.3.1 White PaperPostgreSQL 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 YEARSThe white paper on PostgreSQL is at http://www.greatbridge.com.PostgreSQL is pronounced as Post−gres−cue−el (Postgres−QL) and not Postgre−es−cue−el.Database−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)3.1 White Paper 124.Which one? PostgreSQL or MySQL ?4.1 PostgreSQL defeated Oracle, IBM DB2, MS SQL serverand others!!PostgreSQL defeated Oracle 8 (and 8i), IBM DB2, MS SQL server, Sybase, Interbase and MySQL instandard benchmark tests in performance, speed, scalability and reliability! Read the benchmarks athttp://www.aldev.8m.comor at http://aldev.webjump.com4.2 MySQL and other duplicate RDBMSesMySQL is another open−source SQL server, but it does not support transactions. It is suitable for very smalldatabases and does not support advanced SQL functionalities. Whereas PostgreSQL is an enterprise strengthdatabase supporting transactions and almost all SQL constructs. PostgreSQL is much more advanced thancommercial databases like Oracle, Sybase and Informix. PostgreSQL supports very advanced lockingmechanisms 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 towardsANSI SQL. We would take the most advanced and mature open−source SQL server and drop all others as wedo not have lots of time (to deal with multiple RDBMSes)!! In fact, you do not have time to deal with justone powerful SQL server like PostgreSQL! And all the MySQL users will be migrated to PostgreSQL. AlsoMySQL 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 "NEARPERFECT" system there must be only one system and everybody in the world must work on it!! Duplicateproducts cause more harm than good and hence division of resources must be strongly discouraged. Thisalready happened in case of commercial database systems like Oracle, Sybase, Informix and MS SQL serverwhich caused splintering of user base and often they are incompatible. I want put the source code of SQLserver under your control!!!You do not need hundreds of database systems, all you need is just one best database server which happens tobe 'PostgreSQL'.WARNING: It is possible to create infinite number of database systems for a given specification like ANSISQL!!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−transactionsenvironment• Sub−queries• Server−side cursors• Query caching• Locking of databasesDatabase−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)4. Which one? PostgreSQL or MySQL ?13• Better table join supports (JOIN, UNION, MINUS, INTERSECT, outer join)• And many more advanced features − too numerous to list here.MySQL is at http://www.tcx.se4.3 Limitations of MySQLPostgreSQL should be compared with systems like Oracle, both are really true ACID compliant robustsystems developed over a very long time. It is very much wrong to compare MySQL with Oracle or MySQLwith PostgreSQL. For more details readWhy Not MySQL. Hence, it will be a very serious mistake to replaceOracle 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 alreadycontains the PostgreSQL in package form (both source code and binaries) from :•Linux System Labs Web site: http://www.lsl.com/ (7 U.S. dollars)•Cheap Bytes Inc Web site: http://www.cheapbytes.com/ (7 U.S. dollars)•Debian Main Web site : http://www.debian.org/vendors.htmlPostgreSQL organisation is also selling 'PostgreSQL CDROM' which contains the complete source code andbinaries for many Unix operating systems as well as full documentation.•PostgreSQL CDROM from main Web site at : http://www.postgresql.org 30 (U.S. dollars)Binaries only distribution of PostgreSQL:•The maintainer of PostgreSQL RPMs is Lamar Owen and is at lamar.owen@wgcr.org•PostgreSQL source RPM and binaries RPM http://www.ramifordistat.net/postgres•PostgreSQL source RPM and binaries RPM http://www.postgresql.org Click on "Latest News" andclick on Redhat RPMs.•PostgreSQL source RPM and binaries RPM http://www.redhat.com/pub/contrib/i386/ and ftp site isat ftp://ftp.redhat.com/pub/contrib/i386/•Binaries site for Solaris, HPUX, AIX, IRIX, Linux : ftp://ftp.postgresql.org/pub/bindistWWW Web sites:•Primary Web site: http://www.postgresql.org/•Secondary Web site: http://logical.thought.net/postgres95/•http://www.itm.tu−clausthal.de/mirrors/postgres95/•http://s2k−ftp.cs.berkeley.edu:8000/postgres95/•http://xenium.pdi.net/PostgreSQL/•http://s2k−ftp.cs.berkeley.edu:8000/postgres95/The ftp sites are listed below :−•Primary FTP: ftp://ftp.postgresql.org/pubDatabase−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)4.3 Limitations of MySQL 14•Secondary FTP: ftp://ftp.chicks.net/pub/postgresql•ftp://ftp.emsi.priv.at/pub/postgres/•ftp://ftp.itm.tu−clausthal.de/pub/mirrors/postgres95•ftp://rocker.sch.bme.hu/pub/mirrors/postgreSQL•ftp://ftp.jaist.ac.jp/pub/dbms/postgres95•ftp://ftp.luga.or.at/pub/postgres95•ftp://postgres95.vnet.net:/pub/postgres95•ftp://ftpza.co.za/mirrors/postgres•ftp://sunsite.auc.dk/pub/databases/postgresql•ftp://ftp.task.gda.pl/pub/software/postgresql•ftp://xenium.pdi.net/pub/PostgreSQLPostgreSQL source code is also available at all the mirror sites of sunsite unc (total of about 1000 sitesaround the globe). It is inside the Red Hat Linux distribution in /pub/contrib/i386/postgresql.rpm file.•For list of mirror sites go to ftp://sunsite.unc.edu6.PostgreSQL Quick−Installation InstructionsPostgreSQL 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 TestQuick 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 postgresNote: If you see a 'postgres' user, you may need to backup and clean up the postgres home directorypostgres and delete the unix user 'postgres' or rename the unix user 'postgres' to something like 'postgres2'.Install must be "clean slate"# rpm −i postgre*.rpm (Must install all packages clients, devel, dataand 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 − postgresbash$ man createdbbash$ createdb mydatabaseDatabase−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)6. PostgreSQL Quick−Installation Instructions 15bash$ man psqlbash$ psql mydatabase..... in psql press up/down arrow keys for history line editing or \sbash$ export DISPLAY=<hostname>:0.0bash$ man pgaccessbash$ pgaccess mydatabaseNow you can start rapidly BANGING away SQL commands at psql or pgaccess.bash$ cd /usr/doc/postgresql*Here read all the FAQs, User, Programmer, Admin guides and tutorials.6.2 PostgreSQL RPMsSee also "Installation Steps"from http://www.ramifordistat.net/postgresThe maintainer of PostgreSQL RPMs is Lamar Owen and is at lamar.owen@wgcr.org More details aboutPostgreSQL is at http://www.postgresql.org6.3 Maximum RPMFamiliarize with RedHat RPM package manager to manage the PostgreSQL installations. Download the'Maximum RPM' textbook from http://www.RPM.org look for the filename maximum−rpm.ps.gz And read iton linux using the gv command −# gv maximum−rpm.ps.gzThere is also rpm2deb which converts the RPM packages to Debian linux packages.6.4 Examples RPMExamples are needed to do testing of various interfaces to PostgreSQL. Install the postgresql examplesdirectory from −• Linux cdrom − postgresql−*examples.rpm•postgresql−*examples.rpm from http://www.aldev.8m.comand 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 directories6.5 Testing PyGreSQL − Python interfaceInstall examples package, seeExamples RPM and then do −Database−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)6.2 PostgreSQL RPMs 16bash$ cd /usr/lib/pgsql/pythonbash$ createdb thilobash$ psql thilothilo=> create table test (aa char(30), bb char(30) );thilo=> \qbash$ /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+Dbash$..... Seems to work − now install it properlybash$ su − root# cp /usr/lib/pgsql/python/_pg.so /usr/lib/python1.5/lib−dynload6.6 Testing Perl − Perl interfaceInstall examples package, seeExamples RPM and then do −root# chown −R postgres.postgres /var/lib/pgsql/examplesbash$ cd /var/lib/pgsql/examples/perl5bash$ perl ./example.plNote: If the above command does not work then do this. Gloabl var @INC should include the Pg.pm modulein directory site_perl hence use −I option belowbash$ 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++ interfacesInstall examples package, seeExamples RPM and then do −root# chown −R postgres.postgres /var/lib/pgsql/examplesbash$ cd /var/lib/pgsql/examples/libpqbash$ gcc testlibpq.c −I/usr/include/pgsql −lpqbash$ export PATH=$PATH:.bash$ a.outDatabase−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)6.6 Testing Perl − Perl interface 17bash$ cd /var/lib/pgsql/examples/libpq++bash$ g++ testlibpq0.cc −I/usr/include/pgsql −I/usr/include/pgsql/libpq++−lpq++ −lpq −lcryptbash$ ./a.out (Note: Ignore Error messages if you get any − as below)> create table foo (aa int, bb char(4));No tuples returned...status = 1Error returned: fe_setauthsvc: invalid name: , ignoring...> insert into foo values ('4535', 'vasu');No tuples returned...status = 1Error returned: fe_setauthsvc: invalid name: , ignoring...> select * from foo;aa |bb |−−−−−|−−−−−|4535 |vasu |Query returned 1 row.>>CTRL+Dbash$.... You ran direct C/C++ interfaces to PostgreSQL database!!6.8 Testing Java interfacesInstall examples package, seeExamples RPM and also install the following −•Get JDK jdk−*glibc*.rpm from ftp://ftp.redhat.com/pub/contrib/i386 or fromhttp://www.blackdown.org•Get postgresql−jdbc−*.rpm ftp://ftp.redhat.com/pub/contrib/i386root# chown −R postgres.postgres /var/lib/pgsql/examplesbash$ cd /var/lib/pgsql/examples/jdbcbash$ echo $CLASSPATH−−> Should showCLASSPATH=/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.jarwith 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.jarEdit all psql.java file and comment out the 'package' line.bash$ javac psql.javabash$ java psql jdbc:postgresql:template1 postgres < password >[1] select * from pg_tables;tablename tableowner hasindexes hasrulespg_type postgres true false falsepg_attribute postgres true false false[2]CTRL+Cbash$.... You ran direct Java interfaces to PostgreSQL database!Database−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)6.8 Testing Java interfaces 186.9 Testing ecpg interfacesInstall examples package, seeExamples RPM and then do −root# chown −R postgres.postgres /var/lib/pgsql/examplesbash$ cd /var/lib/pgsql/examples/ecpgbash$ ecpg test1.pgc −I/usr/include/pgsqlbash$ cc test1.c −I/usr/include/pgsql −lecpg −lpq −lcryptbash$ createdb mmbash$ ./a.out.... You ran Embedded "C"−SQL to PostgreSQL database!6.10 Testing SQL examples − User defined types andfunctionsInstall examples package, seeExamples RPM and then do −root# chown −R postgres.postgres /var/lib/pgsql/examplesbash$ cd /var/lib/pgsql/examples/sqlUnder−development..6.11 Testing Tcl/Tk interfacesExample of Tcl/Tk interfaces is pgaccess program. Read the file /usr/bin/pgaccess using an editor −bash$ view /usr/bin/pgaccessbash$ export DISPLAY=<hostname of your machine>:0.0bash$ createdb mydbbash$ pgaccess mydb6.12 Testing ODBC interfaces1.Get the win32 pgsql odbc driver from http://www.insightdist.com/psqlodbc/2. See also /usr/lib/libpsqlodbc.a6.13 Testing MPSQL Motif−worksheet interfacesGet the RPMs from http://www.mutinybaysoftware.comDatabase−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)6.9 Testing ecpg interfaces 196.14 VerificationTo verify the top quality of PostgreSQL, run the Regression test package :− Login as root −# rpm −i postgresql*test.rpmAnd 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 toinstall)# rpm −bp postgresql*.spec (.. this will prep the package)Regression test needs the Makefiles and some header files like *fmgr*.hwhich can be built by −# rpm −−short−circuit −bc postgresql*.spec ( .. use short circuit tobypass!)Abort the build by CTRL+C, when you see 'make −C common SUBSYS.o'By this time configure is successful and all makefiles and headersare created. You do not need to proceed any further# cd /usr/src/redhat/BUILD# chown −R postgres postgresql*# su − postgresbash$ cd /usr/src/redhat/BUILD/postgresql−6.5.3/src/test/regressbash$ more READMEbash$ make clean; make all runtestbash$ more regress.out6.15 Emergency Bug fixesSometimes emergency bug fix patches are released after the GA release of PostgreSQL. You can apply theseoptional 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# makeThe patch files are located in•PostgreSQL patches : ftp://ftp.postgresql.org/pub/patches7.Quick Start GuideRefer also toQuick Installation chapter.Database−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)6.14 Verification 207.1 Creating, Dropping, Renaming DatabaseYou can use the user friendly GUI called 'pgaccess' to create and drop databases, or you can use thecommand line 'psql' utility.If you are logged in as root, switch user to 'postgres' :# xhost + (To give display access for pgaccess)# su − postgresbash$ man createdbbash$ createdb mydatabasebash$ man psqlbash$ psql mydatabase..... in psql press up/down arrow keys for history line editing or \sbash$ export DISPLAY=<hostname>:0.0bash$ man pgaccessbash$ pgaccess mydatabaseNow you can start rapidly BANGING away SQL commands at psql or pgaccess !!To drop the database do :bash$ man dropdbbash$ man destroydb (for older versions of pgsql)bash$ dropdb <dbname>It is also possible to destroy a database from within an SQL session by using:> drop database <dbname>To rename a database seeBackup and Restore7.2 Creating, Dropping usersTo create new users, login as unix user 'postgres'. You can use user friendly GUI tool called 'pgacess' tocreate, drop users.bash$ man pgaccessbash$ pgaccess <database_name>and click on "Users" tab and then click Object|New or Object|DeleteYou can also use command line scripts. Use the shell script called 'createuser' which invokes psqlDatabase−SQL−RDBMS HOW−TO document for Linux (PostgreSQL Object Relational Database System)7.1 Creating, Dropping, Renaming Database 21bash$ man createuserbash$ createuser <username>bash$ createuser −h host −p port −i userid <username>To drop a postgres user, use shell script 'destroyuser' −bash$ man dropuserbash$ man destroyuser (older versions of pgsql)bash$ destroyuser7.3 Creating, Dropping GroupsCurrently, there is no easy interface to set up user groups. You have to explicitly insert/update thepg_group table. For example:bash$ su − postgresbash$ psql <database_name>..... in psql press up/down arrow keys for history line editing or \spsql=> insert into pg_group (groname, grosysid, grolist)psql=> values ('posthackers', '1234', '{5443, 8261}' );INSERT 58224psql=> grant insert on foo to group posthackers;CHANGEpsql=>The fields in pg_group are: groname The group name. This name should be purely alphanumeric; do notinclude underscores or other punctuation.grosysid The group id. This is an int4, and should be unique for each group.