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| ---- |
+--------------------------+---------+---------+---------+---------+--------+---------+--------+
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"!
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.
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!! 'Brahma' loved on 'Mother knowledge' before the baby universe in which 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.
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.
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
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.
PostgreSQL details in nutshell:
PostgreSQL is pronounced as Post-gres-cue-el (Postgres-QL) and not Postgre-es-cue-el.
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 http://www.aldev.8m.com or at http://aldev.webjump.com
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 -
MySQL is at http://www.tcx.se
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.
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 :
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.
Binaries only distribution of PostgreSQL:
WWW Web sites:
The ftp sites are listed below :-
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.
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.
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
# 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=<hostname>:0.0
bash$ man pgaccess
bash$ pgaccess mydatabase
bash$ cd /usr/doc/postgresql*
See also "Installation Steps" from http://www.ramifordistat.net/postgres
The maintainer of PostgreSQL RPMs is Lamar Owen and is at lamar.owen@wgcr.org More details about PostgreSQL is at http://www.postgresql.org
Familiarize with RedHat RPM package manager to manage the PostgreSQL installations. Download the 'Maximum RPM' textbook from http://www.RPM.org look for the filename maximum-rpm.ps.gz And read it on linux using the gv command -
# gv maximum-rpm.ps.gz
Examples are needed to do testing of various interfaces to PostgreSQL. Install the postgresql examples directory from -
Install examples package, see Examples RPM 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
Install examples package, see Examples RPM and then do -
root# chown -R postgres.postgres /var/lib/pgsql/examples bash$ cd /var/lib/pgsql/examples/perl5 bash$ perl ./example.pl
bash$ perl -I/usr/lib/perl5/site_perl/5.005/i386-linux-thread ./example.pl
Read the example.pl file for using perl interface.
Install examples package, see Examples RPM 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$
Install examples package, see Examples RPM and also install the following -
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$
Install examples package, see Examples RPM 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
Install examples package, see Examples RPM and then do -
root# chown -R postgres.postgres /var/lib/pgsql/examples bash$ cd /var/lib/pgsql/examples/sql Under-development..
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=<hostname of your machine>:0.0 bash$ createdb mydb bash$ pgaccess mydb
Get the RPMs from http://www.mutinybaysoftware.com
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
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
Refer also to Quick Installation chapter.
You can use the user friendly GUI called 'pgaccess' to create and drop databases, or you can use the command line 'psql' utility.
If you are logged in as root, switch user to 'postgres' : # xhost + (To give display access for pgaccess) # su - postgres bash$ man createdb bash$ createdb mydatabase bash$ man psql bash$ psql mydatabase ..... in psql press up/down arrow keys for history line editing or \s bash$ export DISPLAY=<hostname>:0.0 bash$ man pgaccess bash$ pgaccess mydatabase
To drop the database do :
bash$ man dropdb bash$ man destroydb (for older versions of pgsql) bash$ dropdb <dbname>
> drop database <dbname>
To create new users, login as unix user 'postgres'. You can use user friendly GUI tool called 'pgacess' to create, drop users.
bash$ man pgaccess bash$ pgaccess <database_name>
You can also use command line scripts. Use the shell script called 'createuser' which invokes psql
bash$ man createuser bash$ createuser <username> bash$ createuser -h host -p port -i userid <username>
To drop a postgres user, use shell script 'destroyuser' -
bash$ man dropuser bash$ man destroyuser (older versions of pgsql) bash$ destroyuser
Currently, there is no easy interface to set up user groups. You have to explicitly insert/update the pg_group table. For example:
bash$ su - postgres
bash$ psql <database_name>
..... in psql press up/down arrow keys for history line editing or \s
psql=> insert into pg_group (groname, grosysid, grolist)
psql=> values ('posthackers', '1234', '{5443, 8261}' );
INSERT 58224
psql=> grant insert on foo to group posthackers;
CHANGE
psql=>
grosysid The group id. This is an int4, and should be unique for each group.
grolist The list of pg_user IDs that belong in the group. This is an int4[].
To drop the group:
bash$ su - postgres bash$ psql <database_name> ..... in psql press up/down arrow keys for history line editing or \s psql=> delete from pg_group where groname = 'posthackers';
You can use user friendly GUI tool 'pgaccess' or command line tool 'psql' to create, edit or drop a table in a database.
bash$ man pgaccess bash$ pgaccess <database_name>
bash$ man psql bash$ psql <database_name> ..... in psql press up/down arrow keys for history line editing or \s
You can use user friendly GUI tool 'pgaccess' or command line tool 'psql' to create, edit or drop records in a database table.
bash$ man pgaccess bash$ pgaccess <database_name>
bash$ man psql bash$ psql <database_name> ..... in psql press up/down arrow keys for history line editing or \s
You can use user friendly GUI tool 'pgaccess' or command line tool 'psql' to switch active database.
bash$ man pgaccess bash$ pgaccess <database_name>
bash$ man psql bash$ psql <database_name> ..... in psql press up/down arrow keys for history line editing or \s psql=> connect <database_name> <user>
PostgreSQL provides two utilities to back up your system: pg_dump to backup individual databases, and pg_dumpall to back up all the databases in just one step.
bash$ su - postgres bash$ man pd_dump bash$ pd_dump <database_name> > database_name.pgdump 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 <database_name> To reload (restore) all databases dumped with pg_dumpall: bash$ psql -e template1 < db_all.out
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 <database_name> | gzip > filename.dump.gz Reload with : bash$ createdb <database_name> bash$ gunzip -c filename.dump.gz | psql <database_name> Or bash$ cat filename.dump.gz | gunzip | psql <database_name> Use split: bash$ pg_dump <database_name> | split -b 1m - filename.dump. Note: There is a dot (.) after filename.dump in the above command!! You can reload with: bash$ man createdb bash$ createdb <database_name> bash$ cat filename.dump.* | pgsql <database_name>
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 http://aldev.8m.com 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.
See the chapter on PostgreSQL Security.
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
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
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;
' language 'plpgsql';
create trigger tg_pfield_au after update
on PField for each row execute procedure tg_pfield_au();
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
More questions, read the fine manuals of PostgreSQL which are very extensive. PostgreSQL documentation is distributed with package. See the 'User's Guide', 'Programmer's Guide', 'Administrator's Guide' and other manuals. The release docs are at http://www.postgresql.org/users-lounge/docs.
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.
To get more bang for a given CPU processing power, do the following:-
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 <service name> 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# ./<service name> stop
General tuning tips:
Specialized tuning tips:
PostgreSQL is already used by many companies supporting large databases. The following techniques are suggested :
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..
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 'Queries across multiple databases', NFS mounts in LAN, 'CREATE VIEW' for read-only tables 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;
Replication server for large enterprises/businesses is available at http://www.erserver.com and from http://www.pgsql.com. 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.
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.
Database security is addressed at several levels:
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:
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
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).
The following authentication methods are supported for both Unix and TCP/IP domain sockets:
The following authentication methods are supported for TCP/IP domain sockets only:
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
Postgres provides mechanisms to allow users to limit the access to their data that is provided to other users.
You can use ssh to encrypt the network connection between clients and a Postgres server. Done properly, this should lead to an adequately secure network connection.
The documentation for ssh provides most of the information to get started. Please refer to http://www.heimhardt.de/htdocs/ssh.html for better insight. A step-by-step explanation can be done in just two steps.
Running a secure tunnel via ssh: A step-by-step explanation can be done in just two steps.
ssh -L 3333:wit.mcs.anl.gov:5432 postgres@wit.mcs.anl.gov
psql -h localhost -p 3333 -d mpw
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):
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
------------------------------------------------------
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 Enhydra (Java based) or Zope (Python based) or OpenACS .
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 -
Language choices in the order of preference are -
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.
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..
UDBC is a static version of ODBC independent of driver managers and DLL support, used to embed database connectivity support directly into applications.
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.
The JDBC home, guide and FAQ are located at -
Java programmers can find these for PostgreSQL very useful.
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.
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.
Before you install DBD PostgreSQL (Driver) you must install DBI. Get DBI driver from
Get DBD-Pg from below
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
See also -
The mailing lists that users may participate in are:
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.
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 :
See the section -
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
# man xhost # xhost + # su - postgres bash$ man pgaccess bash$ export DISPLAY=<hostname>:0.0 bash$ pgaccess mydatabase
Features of PgAccess
PgAccess windows - Main window, Table builder, Table(query) view, Visual query builder.
Tables
Queries
Sequences
Future implementation will have
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.
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 http://gtksql.sourceforge.net
Its main features are :
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.
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.
MPMGR will provide a graphical management interface for PostgresSQL. You can find it at
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 http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html
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
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
See the document http://metalab.unc.edu/LDP/HOWTO/CPU-Design-HOWTO.html 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.
The following is GNU/GPL open-source CPU list:
The following is commercial CPU list:
Other important CPU sites are at -
If you do want to spend money on hardware switches than you can use VNC (Virtual Network Computing) Technology from the telecom giant AT & T. VNC is GPLed and is a free software. Using VNC you can run PostgreSQL programs on computers without monitors and display on remote boxes with monitors!! But the boxes must be connected via ethernet Network Interface Cards. VNC is at http://www.uk.research.att.com/vnc
You can stack up multiple CPU-boxes and connect to just one monitor and use the KVM (Keyboard, Video, Monitor) switch box to select the host. This saves space and avoids a lot of clutter and also eliminates monitor, keyboard and the mouse (saving anywhere from 100 to 500 US dollars per set).
Using this switch box, you can stack up many PostgreSQL servers (development, test, production), Web servers, ftp servers, Intranet servers, Mail servers, News servers in a tower shelf. The switch box can be used for controlling Windows 95/NT or OS/2 boxes as well.
Please check out these sites:
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"
Get RedHat (or some other distribution of) Linux cdrom from below -