PPOOSSTTGGRREESS IINNSSTTAALLLLAATTIIOONN IINNSSTTRRUUCCTTIIOONNSS Release 4.1 DDooccuummeenntt OOvveerrvviieeww Introduction Site Requirements Hardware Software Distribution Tape Expertise Configuration Operating System Disk Requirements Kernel Installing POSTGRES Preparation Finding a Good Place for POSTGRES Creating the POSTGRES Directory Creating the POSTGRES User Loading POSTGRES From Tape Loading POSTGRES From a Tar File Configuration Kernel Reconfiguration for Sparcs Kernel Reconfiguration for DEC's Running the Pre-installed POSTGRES System Compiling and Installing POSTGRES Creating the Initial Database Testing Running POSTGRES The POSTGRES Postmaster The POSTGRES Terminal Monitor The POSTGRES Backend POSTGRES Support Programs Optional Installation Installing LIBPQ, the POSTGRES Frontend Library POSTGRES Header Files Wisconsin Benchmark Database Minimal Installation Documentation Miscellaneous Bug Reports Consulting POSTGRES Mailing List 11.. IInnttrroodduuccttiioonn This document gives installation instructions for the POST- GRES database system under development at the University of California, Berkeley. POSTGRES is distributed in source code format and is the property of the Regents of the Uni- versity of California. However, the University will grant unlimited commercialization rights for any derived work on the condition that an educational license to the derived work is obtained. For further information, consult the Berkeley Campus Software Office, 295 Evans Hall, University of California, Berkeley, CA 94720. The University and the POSTGRES development group provide no warranty as to the fitness of the code for any purpose what- soever, and cannot guarantee assistance in fixing problems. This is _u_n_s_u_p_p_o_r_t_e_d software. 22.. SSiittee RReeqquuiirreemmeennttss 22..11.. HHaarrddwwaarree POSTGRES currently has been tested by the POSTGRES develop- ment team on Sun Microsystems SPARC architecture machines running SunOS 4.1 (Solaris 1.x). (POSTGRES will nnoott run on machines running SunOS 5 (Solaris 2.x)). POSTGRES also runs on DECstation 3100s and 5000s running Ultrix 4.1 and higher. In order to use POSTGRES, your machine should have at least 8 megabytes of memory and you will require at least 45 megabytes of disk space to hold source, binaries, and user databases. If you choose to compile POSTGRES for source- level debugging, you will need roughly twice as much disk space. See the section on compilation for details. The Ultrix version requires a kernel which allows 4 megabytes of shared memory. Also, the original release of Ultrix 4.3 has a kernel bug that causes the operating system to hang when running POSTGRES. The bug is in the shared memory module in the kernel, and a patch is available from DEC which fixes the bug. Contact your DEC representative for patch number CLD-CXO09447. (This patch has been included in Ultrix 4.3a.) 22..22.. SSooffttwwaarree 22..33.. DDiissttrriibbuuttiioonn TTaappee These instructions assume you have a POSTGRES Version 4.1 distribution tape (in either 1600bpi 9-track, SCSI car- tridge, or DEC TK50 cartridge format) or a POSTGRES tar file. This release comes with a pre-installed version of POSTGRES ready to run for the two platforms we officially support. This is a test to see if our users can run the 22 pre-installed system to avoid compiling the sources from scratch. Should any problems arise with the pre-installed system, it is recommended that the sources be rebuilt from scratch to rule out any compatibility problems between the system we compiled on here and your own. There are two potential releases you may want (same source, different binaries). The tar version of the Ultrix release is ppoosstt-- ggrreess--vv44rr11..uullttrriixx..ttaarr..ZZ and the SPARC release is ppoossttggrreess-- vv44rr11..ssppaarrcc..ttaarr..ZZ available via anonymous ftp from ppoosstt-- ggrreess..bbeerrkkeelleeyy..eedduu. No sources-only tar file is currently available. 22..44.. EExxppeerrttiissee Once a site is properly configured and POSTGRES is installed, very little UNIX expertise is required to main- tain things. However, initially setting things up for your site to run POSTGRES may be difficult and we advise that the person installing POSTGRES be familiar with system adminis- tration procedures. Also note that various steps require superuser privilege on the system, so we advise that your site's system administrator read this document also. 22..55.. CCoonnffiigguurraattiioonn This section briefly describes the configuration you need to run POSTGRES. Read this to familiarize yourself with the procedure. Detailed instructions for making appropriate modifications to your system are given later in this docu- ment. 22..55..11.. OOppeerraattiinngg SSyysstteemm POSTGRES expects things to be configured for BSD by default. If the default on your site is to use the SunOS System V compiler and libraries then you may have to make some changes to this procedure before compiling POSTGRES. To compile this release from sources requires a new version of make which comes from the latest BSD release. A boot- strapping version of the make sources is included with this release, as is a precompiled binary with its configuration files. The make program is installed using the name bbmmaakkee to avoid any conflict with your native make. 22..55..22.. DDiisskk RReeqquuiirreemmeennttss POSTGRES requires 50 megabytes of disk space, preferably on a single partition, to compile and install from sources. If you run the pre-installed system, you only need 25 megabytes to hold the executables and sources. 33 22..55..33.. KKeerrnneell POSTGRES makes use of the optional System V shared memory operations provided by SunOS and DEC Ultrix which require a properly configured kernel which is in general different than the factory-shipped generic kernel. See the section on kernel configuration for details. 33.. IInnssttaalllliinngg PPOOSSTTGGRREESS There are potentially two routes you can go to install this system. Either you can choose to run the pre-installed sys- tem shipped with the release, or you can compile and install the sources from scratch. In either case your kernel must be configured to support the shared memory and semaphore operations required by the code. Assuming you have run POSTGRES in the past, you may already be properly configured to run. Also, should you opt to run the pre-installed sys- tem, let us reiterate that if you run into any complica- tions, it would be wise to recompile the sources from scratch before reporting any problems to our mailing list. We are, however, interested in any feedback you may have on the pre-installed system. In both cases you need to decide where to load the system. 33..11.. SStteepp 11 -- PPrreeppaarraattiioonn Some of the tasks involved in this step normally fall in the domain of the site's system administrator and may require superuser privilege. If possible, we advise you to have your system administrator perform these steps. 33..11..11.. FFiinndd aa ggoooodd ppllaaccee ffoorr PPOOSSTTGGRREESS You should locate a disk partition with at least 50 megabytes of free space available for POSTGRES. If you plan to run the pre-installed system, it would be best if you loaded the release into the directory //uussrr//llooccaall//ppoossttggrreess, as that is what the release was configured for. It is not mandatory, though, as the only relevent dependency is that the programs are setup to look for the database directory in //uussrr//llooccaall//ppoossttggrreess//ddaattaa, but that can be changed by setting the environment variable PGDATA somwhere else before start- ing the postmaster. Also note that you can install the sys- tem anywhere, and then make a symbolic link from //uussrr//llooccaall//ppoossttggrreess to the place you really loaded the sys- tem. If you are compiling the system from scratch, it doesn't matter where you load the system at all (as long as there is space). 44 33..11..22.. CCrreeaattiinngg tthhee PPOOSSTTGGRREESS ddiirreeccttoorryy Once you have decided, create the directory to hold the release if it doesn't already exist. Then ccdd to this direc- tory and type ppwwdd. This is the full path of the directory you will install POSTGRES in. For example: # ddff Filesystem kbytes used avail capacity Mounted on /dev/xy0a 8421 6703 875 88% / /dev/xy0f 10829 6743 3003 69% /pub.MC68020 /dev/xy2h 110811 81181 18548 81% /usr3 /dev/xy2g 221279 167405 31746 84% /b /dev/xy1g 221279 138365 60786 69% /usr/local /dev/xy1a 8179 944 6417 13% /tmp /dev/xy0h 119999 101623 6376 94% /usr.MC68020 /dev/xy0g 156033 135499 4930 96% /usr2 /dev/rf0d 539421 465026 20452 96% /a _/_u_s_r_/_l_o_c_a_l _l_o_o_k_s _l_i_k_e _a _g_o_o_d _p_l_a_c_e _(_i_t _h_a_s _6_0 _m_e_g_s _f_r_e_e_) _s_o _w_e _d_e_c_i_d_e _t_o _c_r_e_a_t_e _t_h_e _P_O_S_T_G_R_E_S _d_i_r_e_c_t_o_r_y _t_h_e_r_e_._._. # ccdd //uussrr//llooccaall # mmkkddiirr ppoossttggrreess # ccdd ppoossttggrreess # ppwwdd /usr/local/postgres # 33..22.. CCrreeaattiinngg tthhee PPOOSSTTGGRREESS uusseerr Finally, we need to create a user called POSTGRES whose shell is //bbiinn//ccsshh. Though not necessary, it is convenient to make the home directory of the POSTGRES user the pathname where we install the system. This can be done using the "adduser" procedures particular to your platform and site. See your system administration manual for details. The reason we make a "postgres" login is so that when we install the system and create the database directory, every- thing is owned by this "postgres" user. Then, we start the postmaster when logged in as this "postgres" user, and all the backends are also started by the user "postgres" and are able to access the databases. Although you can make this work without creating a "postgres" login, we provide no sup- port for doing so (you are, as they say, "on your own"). Note that the restriction about having a "postgres" login with UID 6 was removed in POSTGRES Version 4.0.1. 55 33..33.. SStteepp 22 -- LLooaaddiinngg PPOOSSTTGGRREESS Now you are ready to load the POSTGRES files onto your sys- tem. To do this, you will need either a distribution tape or a POSTGRES tar file (available via anonymous FTP from ppoossttggrreess..bbeerrkkeelleeyy..eedduu). If you are loading POSTGRES from a tape, follow these instructions; if you are loading from a tar file obtained via FTP, skip to the section "Loading POSTGRES from a Tar File." 33..33..11.. LLooaaddiinngg PPOOSSTTGGRREESS ffrroomm aa TTaappee Login as "postgres" and change directories to the postgres directory that was created. 3. Run ttaarr with the following options % ttaarr xxvvff _<_t_a_p_e_-_d_e_v_i_c_e_> where _<_t_a_p_e_-_d_e_v_i_c_e_> is the name for your tape device, i.e., //ddeevv//rrmmtt00, //ddeevv//rrsstt88, etc. The file ppoossttggrreess--vv44rr11..XXXXXX..ttaarr..ZZ will appear in your POST- GRES home directory, where XXX is the name of the platform. You may need to re-wind your tape to get it out of your tape drive - see your system administrator for instructions. Proceed to the next section "Loading POSTGRES from a Tar File." 33..44.. LLooaaddiinngg PPOOSSTTGGRREESS ffrroomm aa TTaarr FFiillee If you are not logged in as ppoossttggrreess already, do so now. For the purpose of this discussion, the POSTGRES tar file will be called ppoossttggrreess--vv44rr11..XXXXXX..ttaarr..ZZ. Uncompress the tar file. % uunnccoommpprreessss ppoossttggrreess--vv44rr11..XXXXXX..ttaarr..ZZ A larger file should now be in the POSTGRES home directory, and the ".Z" ending should be gone, so it is now named ppoosstt-- ggrreess--vv44rr11..XXXXXX..ttaarr. Extract POSTGRES from the tar file using the following com- mand: % ttaarr xxvvff ppoossttggrreess--vv44rr11..XXXXXX..ttaarr 66 Lots of file names and such should appear on the screen. This step may take several minutes. Now do an llss: % llss The output should look something like: CCOOPPYYRRIIGGHHTT RREEAADDMMEE ddoocc llooccaall ssrrcc IINNIITTIIAALL__SSEETTUUPP bbiinn iinncclluuddee mmaann MMaakkeeffiillee ddaattaa lliibb oobbjj At this point you have loaded the POSTGRES files. Remove the tar file to get back the space. % rrmm ppoossttggrreess--vv44rr11..XXXXXX..ttaarr 33..55.. SStteepp 33 -- CCoonnffiigguurraattiioonn This step requires familiarity with configuring a UNIX ker- nel. If you are unfamiliar with this procedure, we advise you to read the section on configuring a kernel in the SunOS or DEC system administration manual carefully. This task requires superuser privilege and should probably not be done without the assistance of your system administrator. We assume that whoever undergoes this procedure has an under- standing of the process and procedures involved. POSTGRES uses shared memory segments which must be compiled into the kernel of the host which will act as the POSTGRES server. If you try to run a POSTGRES backend process on a machine without enough shared memory, the backend will abort with an error message. This is by far the most complicated part of the installation so these steps should be performed by someone with system administration experience. Again, we advise you to consult the system administration section of your manual before doing this step. For a brief discussion of shared memory, you may want to consult the manual pages for sshhmmggeett(()), sshhmmoopp(()), sshhmmccttll(()), etc. Now proceed to the appropriate section for your machine. 77 33..55..11.. KKeerrnneell rreeccoonnffiigguurraattiioonn ffoorr SSppaarrccss In order to reconfigure Sun or Sparc kernel, you will have to become the superuser and add some lines to //uussrr//ssyyss//ccoonnff//KKEERRNNEELL (your kernel config file) and //uussrr//ssyyss//ccoonnff//ppaarraamm..cc (your kernel parameters file). We _s_t_r_o_n_g_l_y advise you to make a spare copy of your system's original config and parameter files before you make any changes. The following lines should be added to //uussrr//ssyyss//ccoonnff//KKEERRNNEELL: ooppttiioonnss IIPPCCMMEESSSSAAGGEE ## SSyysstteemmVV IIPPCC MMeessssaaggee FFaacciilliittyy ooppttiioonnss IIPPCCSSEEMMAAPPHHOORREE ## SSyysstteemmVV IIPPCC SSeemmaapphhoorree FFaacciilliittyy ooppttiioonnss IIPPCCSSHHMMEEMM ## SSyysstteemmVV IIPPCC SShhaarreedd--MMeemmoorryy FFaacciilliittyy ooppttiioonnss EEMMOORREEIIPPCCSS ## mmoorree sseemmaapphhoorreess aanndd sshhaarreedd mmeemmoorryy ((ffoorr 88MM)) At Berkeley, we substitute the line: ooppttiioonnss EEMMOORREEIIPPCCSS ## mmoorree sseemmaapphhoorreess aanndd sshhaarreedd mmeemmoorryy ((ffoorr 88MM)) with the line: ooppttiioonnss TTTTMMOORREEIIPPCCSS ## mmoorree sseemmaapphhoorreess aanndd sshhaarreedd mmeemmoorryy ((ffoorr 3322MM)) to allocate more shared memory so that we can run more POST- GRES backends at the same time. Either of the lines will result in a kernel that has enough shared memory allocated. Also add the following lines to the _t_o_p of //uussrr//ssyyss//ccoonnff//ppaarraamm..cc: //** ** LLOOCCAALL DDEEFFIINNIITTIIOONNSS SSTTAARRTT **// ##iiffddeeff EEMMOORREESSEEMMSS ##ddeeffiinnee EEMMOORREEIIPPCCSS ##eennddiiff //** ddeeffiinneedd((EEMMOORREESSEEMMSS)) **// ##iiffddeeff TTTTMMOORREESSEEMMSS ##ddeeffiinnee TTTTMMOORREEIIPPCCSS ##eennddiiff //** ddeeffiinneedd((TTTTMMOORREESSEEMMSS)) **// ##iiffddeeff EEMMOORREEIIPPCCSS ##ddeeffiinnee SSEEMMMMNNII 3300 //** ## ooff sseemmaapphhoorree iiddeennttiiffiieerrss **// ##ddeeffiinnee SSEEMMMMNNSS 118800 //** ## ooff sseemmaapphhoorreess iinn ssyysstteemm **// 88 ##ddeeffiinnee SSEEMMUUMMEE 1100 //** mmaaxx ## ooff uunnddoo eennttrriieess ppeerr pprroocceessss **// ##ddeeffiinnee SSEEMMMMNNUU 3300 //** ## ooff uunnddoo ssttrruuccttuurreess iinn ssyysstteemm **// ##ddeeffiinnee SSHHMMSSIIZZEE 11553366 //** mmaaxx ttoottaall sshhaarreedd mmeemmoorryy ssyysstteemm wwiiddee ((iinn KKbbyytteess)) **// ##ddeeffiinnee SSHHMMSSEEGG 66 //** mmaaxx aattttaacchheedd sshhaarreedd mmeemmoorryy sseeggmmeennttss ppeerr pprroocceessss **// ##ddeeffiinnee SSHHMMMMNNII 110000 //** ## ooff sshhaarreedd mmeemmoorryy iiddeennttiiffiieerrss **// ##eennddiiff //** ddeeffiinneedd((EEMMOORREEIIPPCCSS)) **// ##iiffddeeff TTTTMMOORREEIIPPCCSS ##ddeeffiinnee SSEEMMMMNNII 6600 //** ## ooff sseemmaapphhoorree iiddeennttiiffiieerrss **// ##ddeeffiinnee SSEEMMMMNNSS 338844 //** ## ooff sseemmaapphhoorreess iinn ssyysstteemm **// ##ddeeffiinnee SSEEMMUUMMEE 1100 //** mmaaxx ## ooff uunnddoo eennttrriieess ppeerr pprroocceessss **// ##ddeeffiinnee SSEEMMMMNNUU 3300 //** ## ooff uunnddoo ssttrruuccttuurreess iinn ssyysstteemm **// ##ddeeffiinnee SSHHMMSSIIZZEE 88119922 //** mmaaxx ttoottaall sshhaarreedd mmeemmoorryy ssyysstteemm wwiiddee ((iinn KKbbyytteess)) **// ##ddeeffiinnee SSHHMMSSEEGG 66 //** mmaaxx aattttaacchheedd sshhaarreedd mmeemmoorryy sseeggmmeennttss ppeerr pprroocceessss **// ##ddeeffiinnee SSHHMMMMNNII 110000 //** ## ooff sshhaarreedd mmeemmoorryy iiddeennttiiffiieerrss **// ##eennddiiff //** ddeeffiinneedd((TTTTMMOORREEIIPPCCSS)) **// //** ** LLOOCCAALL DDEEFFIINNIITTIIOONNSS EENNDD **// After adding these lines, run ccoonnffiigg over the config file, install the new kernel (saving a copy of the old kernel first!) and reboot. 33..55..22.. KKeerrnneell rreeccoonnffiigguurraattiioonn ffoorr DDEECCss In order to reconfigure your DECstation 3100 or 5000 Ultrix kernel, you will have to become the superuser and add some lines to //uussrr//ssyyss//ccoonnff//KKEERRNNEELL (your kernel config file). Remember that Ultrix 4.3 has a kernel bug that causes the operating system to hang when running POSTGRES. You should apply the DEC supplied patch at this point if you have not already. The following lines should be added to //uussrr//ssyyss//ccoonnff//KKEERRNNEELL: ssmmmmaaxx 225566 ssmmsseegg 1122 ssmmbbrrkk 11002244 After adding these lines, run ccoonnffiigg over the configuration file, install the new kernel (saving a copy of the old ker- nel first!) and reboot. 99 33..66.. RRuunnnniinngg tthhee pprree--iinnssttaalllleedd PPOOSSTTGGRREESS ssyysstteemm Assuming you have loaded the system into //uussrr//llooccaall//ppoossttggrreess, there is very little else to do. Add //uussrr//llooccaall//ppoossttggrreess//bbiinn to your shell path. If you run ccsshh, you would put sseett ppaatthh==((//uussrr//llooccaall//ppoossttggrreess//bbiinn $$ppaatthh)) in the ..llooggiinn for the "postgres" user. If you run sshh or kksshh, put PPAATTHH==//uussrr//llooccaall//ppoossttggrreess//bbiinn::$$PPAATTHH eexxppoorrtt PPAATTHH in your ..pprrooffiillee. Then log back in so the change takes effect. Now run the following command % nneewwbbkkii This will reset the userid of the special POSTGRES user of the database to have the proper value. Even though the nneeww-- bbkkii command allows you to enter a login name, you should select the default value of ppoossttggrreess. After doing this, you may wish to install the precompiled version of bbmmaakkee into //uussrr//llooccaall//bbiinn. This will allow you to recompile POSTGRES right away without bootstrapping bbmmaakkee. To do this, ccdd into the directory called llooccaall. You will see two directories and a README. Become the superuser and do the following: # ccpp bbiinn//bbmmaakkee //uussrr//llooccaall//bbiinn # mmkkddiirr //uussrr//llooccaall//lliibb //uussrr//llooccaall//lliibb//mmkk _(_y_o_u _m_a_y _g_e_t _a_n _e_r_r_o_r _i_f _t_h_i_s _d_i_r_e_c_t_o_r_y _a_l_r_e_a_d_y _e_x_i_s_t_s _- _i_g_n_o_r_e _i_t_) # ccpp lliibb//mmkk//** //uussrr//llooccaall//lliibb//mmkk If you installed POSTGRES somewhere other than //uussrr//llooccaall//ppoossttggrreess, everything can still work right if you set the environment variable PGDATA for the special POSTGRES user. Lets assume you installed POSTGRES in the directory //pprriivvaattee//ppaacckkaaggeess//ppoossttggrreess. If you run ccsshh, put the follow- ing in the ..llooggiinn for the POSTGRES user sseetteennvv PPGGDDAATTAA //pprriivvaattee//ppaacckkaaggeess//ppoossttggrreess//ddaattaa If you run sshh or kksshh, put the following in PPGGDDAATTAA==//pprriivvaattee//ppaacckkaaggeess//ppoossttggrreess//ddaattaa eexxppoorrtt PPGGDDAATTAA 1100 This will instruct the postmaster and backends to look in that directory for the databases. You can now skip to the section called "Creating the initial database." 33..77.. CCoommppiilliinngg aanndd IInnssttaalllliinngg PPOOSSTTGGRREESS The sources for POSTGRES are all under the directory ssrrcc//. The first step is to install the new bbmmaakkee program. By the way, you can look at the previous section entitled "Running the pre-installed system" in the paragraph about bbmmaakkee, and just copy the pre-compiled bbmmaakkee to //uussrr//llooccaall//{{bbiinn,,lliibb}} if you want and skip step 2) in this section. What follows is a step by step list to compile and install this release from sources: - build step 1: customization - CCdd into the ssrrcc// directory and edit the file MMaakkee-- ffiillee..gglloobbaall. By default the PORTNAME is uullttrriixx44. Change this to ssppaarrcc if you are running on a SPARC-based machine. Also, if you do not want bbmmaakkee installed into //uussrr//llooccaall//bbiinn and //uussrr//llooccaall//lliibb, change the values of TOOLSBINDIR and TOOLSLIBDIR and make sure these directories exist. At this point you can also change where the POSTGRES exe- cutable files are installed and where it looks for the database directory. In general the rest of the configura- tion switches are self-explanatory. The only one which must be set is PORTNAME. - build step 2: bootstrapping the new bmake program - CCdd into ssrrcc//ttoooollss//bbmmaakkee and type % mmaakkee --ff MMaakkeeffiillee..bboooott You may get warning messages during this bootstrapping pro- cess about "illegal combination of pointer and integer" -- just ignore them. This should compile and install the bbmmaakkee program and its supporting files, including the POSTGRES- related Makefile templates. If all went well you will now be able to use the new make program by typing bbmmaakkee, assum- ing you have //uussrr//llooccaall//bbiinn in your shell path. You may have to type rreehhaasshh if you run ccsshh. - build step 3: compile and install the world - CCdd back to the ssrrcc// directory (i.e., ccdd ....//....) and type: % bbmmaakkee aallll iinnssttaallll 1111 This builds and installs the entire system. The MMaakkeeffiillees contain directives for running all the underlying MMaakkeeffiillees in all the directories, so the whole thing should unfold and compile beautifully and install to the target directory. Should this not be the case, it would be a good idea to save the results of the compile in a file. If you run ccsshh, you could type % bbmmaakkee aallll iinnssttaallll >>&& mmkk..lloogg and if you run kksshh or sshh, type % bbmmaakkee aallll iinnssttaallll >> mmkk..lloogg 22>>&&11 This will save the results in the file mmkk..lloogg so you can inspect it later. This would be an ideal opportunity to get some doughnuts and coffee. 33..88.. CCrreeaattiinngg tthhee iinniittiiaall ddaattaabbaassee POSTGRES databases are stored in the directory ......//ppoosstt-- ggrreess//ddaattaa. After you have compiled POSTGRES, you will need to create the initial database. If you haven't already put the path to the POSTGRES executable programs in the shell path, do the following (assuming POSTGRES was loaded into //uussrr//llooccaall//ppoossttggrreess). If you run ccsshh, you would put sseett ppaatthh==((//uussrr//llooccaall//ppoossttggrreess//bbiinn $$ppaatthh)) in the ..llooggiinn for the POSTGRES user. If you run sshh or kksshh, put PPAATTHH==//uussrr//llooccaall//ppoossttggrreess//bbiinn::$$PPAATTHH eexxppoorrtt PPAATTHH in your ..pprrooffiillee. Then log back in so the change takes effect. Now you can create the initial database by running the fol- lowing command: % iinniittddbb If that completes successfully, congratulations. Now, to make the system operational you must run the post- master. The section after "Testing" discusses this. 33..99.. SStteepp 44 -- TTeessttiinngg We suggest you run the regression tests to make sure the release was installed successfully. You must have installed 1122 the bbmmaakkee program to run the test. Also, you need to have the postmaster running to run the test, so type the follow- ing: % ppoossttmmaasstteerr && Next, create a new user using the ccrreeaatteeuusseerr command (see the reference manual). DDoo nnoott run the regression test script as user "postgres"! Change directories to ssrrcc//rreeggrreessss//rreeggrreessss. Type the follow- ing commands: % ccdd ssrrcc//rreeggrreessss//rreeggrreessss % bbmmaakkee rruunntteesstt This will run a whole slew of regression tests and might take a long time to run. When it's done, the output of the test is in the file oobbjj//rreeggrreessss..oouutt. You can compare this to a sample run that we supply in the file ssaamm-- ppllee..rreeggrreessss..oouutt with the following command: % ddiiffff ssaammppllee..rreeggrreessss..oouutt oobbjj//rreeggrreessss..oouutt Unfortunately you will see many differences corresponding to the timestamp lines and occasional lines where object id numbers (OID's) are different. We regret that we do not have a more sophisticated script for weeding out these innocuous differences at this time. If you see any differ- ences not corresponding to time stamps or OIDS, there may be a problem. Have your local POSTGRES expert take a look at it. 44.. RRuunnnniinngg PPOOSSTTGGRREESS POSTGRES is designed to be a multiuser system. In practice, POSTGRES consists of three (or more) processes: +o the postmaster, +o the terminal monitor, and +o the backend. Users are expected to use the terminal monitor for direct access to the database. The terminal monitor sends commands to the postmaster which forwards commands to a backend. 1133 44..11.. TThhee PPOOSSTTGGRREESS PPoossttmmaasstteerr The postmaster is a process which manages communication between the user's terminal monitor and a POSTGRES backend. Without a running postmaster, the terminal monitor will not be able to connect to a backend. In general, the postmaster must be running for you (or others) to run any of the normal POSTGRES commands. Always start the postmaster when logged in as the special "postgres" user, otherwise the system will not be able to access the database files. To start it, type % ppoossttmmaasstteerr && Now you can leave the postmaster running if you wish to keep the database system accessible. In general, when the post- master is running, the system is running. When it is not running, none of the frontend programs will work. 44..22.. TThhee PPOOSSTTGGRREESS TTeerrmmiinnaall MMoonniittoorr The POSTGRES terminal monitor is a front-end user interface to the POSTGRES backend. Lets assume _d_a_t_a_b_a_s_e is the name of the database you want to use. It is an error if _d_a_t_a_b_a_s_e does not exist, so to cre- ate the database, you would type % ccrreeaatteeddbb _d_a_t_a_b_a_s_e Now we will run the monitor: % mmoonniittoorr _d_a_t_a_b_a_s_e WWeellccoommee ttoo tthhee PPOOSSTTGGRREESS tteerrmmiinnaall mmoonniittoorr GGoo ** _T_h_e _`_`_*_'_' _i_s _t_h_e _t_e_r_m_i_n_a_l _m_o_n_i_t_o_r _p_r_o_m_p_t_. _W_e _a_r_e _n_o_w _t_a_l_k_i_n_g _t_o _t_h_e _b_a_c_k_e_n_d_, _s_o _l_e_t_'_s _s_e_n_d _a _s_i_m_p_l_e _t_e_s_t _q_u_e_r_y_: _l_i_s_t _t_h_e _n_a_m_e_s _a_n_d _u_s_e_r _i_d_s _o_f _t_h_e _P_O_S_T_G_R_E_S _u_s_e_r_s_. _W_e _t_e_r_m_i_n_a_t_e _t_h_e _q_u_e_r_y _w_i_t_h _a _\_g _-_- _t_h_e _`_`_g_o_'_' _c_o_m_m_a_n_d _t_o _t_h_e _t_e_r_m_i_n_a_l _m_o_n_i_t_o_r_. ** rreettrriieevvee ((uu..uusseennaammee,, uu..uusseessyyssiidd)) ffrroomm uu iinn ppgg__uusseerr\\gg QQuueerryy sseenntt ttoo bbaacckkeenndd iiss ""rreettrriieevvee ((uu..uusseennaammee,, uu..uusseessyyssiidd)) ffrroomm uu iinn ppgg__uusseerr"" ---------------------------------------------------------- || uusseennaammee || uusseessyyssiidd || ---------------------------------------------------------- || ppoossttggrreess || 66 || 1144 ---------------------------------------------------------- || mmiikkee || 779999 || ---------------------------------------------------------- || sspp || 11551111 || ---------------------------------------------------------- || jjhhiinnggrraann || 994433 || ---------------------------------------------------------- || cciimmaarrrroonn || 22335599 || ---------------------------------------------------------- || ggoohh || 11999944 || ---------------------------------------------------------- || oonngg || 22880022 || ---------------------------------------------------------- || hhoonngg || 22446699 || ---------------------------------------------------------- || mmaaoo || 11880066 || ---------------------------------------------------------- || mmaarrcc || 22443355 || ---------------------------------------------------------- || mmaarrggoo || 22669977 || ---------------------------------------------------------- || ssuulllliivvaann || 11551177 || ---------------------------------------------------------- || kkeemmnniittzz || 33449911 || ---------------------------------------------------------- || cchhooii || 33889988 || ---------------------------------------------------------- || mmeerr || 33666655 || ---------------------------------------------------------- GGoo _O_k_a_y_, _t_h_i_s _w_o_r_k_e_d_, _t_o_o_. _N_o_w _w_e_'_l_l _q_u_i_t_. ** \\qq %% 44..33.. TThhee PPOOSSTTGGRREESS BBaacckkeenndd The POSTGRES backend is the process which does all the "real" work. This process is started by the postmaster when the postmaster receives a connection from a terminal moni- tor, so you should not normally need to start up the backend yourself. Should you wish to start the backend and talk to it directly (without a terminal monitor) you can do this by typing: % ppoossttggrreess _d_a_t_a_b_a_s_e where _d_a_t_a_b_a_s_e is the name of the database you wish to use. If you run a backend in this manner, you will be talking to 1155 the backend parser directly. We recommend using the termi- nal monitor; if you are using POSTGRES as a multiuser sys- tem, running the backend can result in locking failures and corrupt databases, as the Postmaster handles shared resources such as semaphores and shared memory. In general, never do this if there is a postmaster running. In addi- tion, returned tuples are displayed more usefully and input is buffered better. The backend is used interactively pri- marily during debugging. 44..44.. PPOOSSTTGGRREESS SSuuppppoorrtt PPrrooggrraammss Included in POSTGRES are a handful of support programs. Most of these are used internally by the system but here is a list of them for your information. initdb - creates the initial template database cleardbdir - totally destroys the data/ directory, allowing a new initdb createdb - creates new POSTGRES databases createuser - add a new user to the POSTGRES system destroydb - destroys POSTGRES databases destroyuser - delete a user from the database system ipcclean - frees up garbage shared memory from failed backends newbki - adjust userid of "postgres" in the database pg_version - make version numbers for createdb pg_id - gets user id's - used by various commands pagedoc - disk page doctor shmemdoc - shared memory buffer pool doctor vacuum - database vacuum cleaner pg_copytree - copy the source tree (probably shouldn't be shipped) icopy - inversion filesystem file management utility pcat - inversion filesystem cat command pcd - inversion filesystem cd command pls - inversion filesystem ls command pmkdir - inversion filesystem mkdir command pmv - inversion filesystem mv command ppwd - inversion filesystem pwd command prm - inversion filesystem rm command prmdir - inversion filesystem rmdir command 55.. OOppttiioonnaall IInnssttaallllaattiioonn 55..11.. IInnssttaalllliinngg LLIIBBPPQQ,, tthhee PPOOSSTTGGRREESS ffrroonntteenndd lliibbrraarryy The file ......//lliibb//lliibbppqq..aa is created when you install the system (or on the pre-installed system). This library con- tains various routines intended for use by frontend pro- grams. You use this library if you want to execute POSTGRES queries from a C program. If you plan on doing software development, you may wish to copy this file to //uussrr//lliibb so that the C compiler can reference it with --llppqq. If you do not, you will have to use the --LL directive to the cccc and lldd 1166 commands so that they can find lliibbppqq..aa. 55..22.. PPoossttggrreess hheeaaddeerr ffiilleess The directory ......//iinncclluuddee contains copies of all the header files that front-end applications might need. You can com- pile a frontend program with the --II directive to cccc as illustrated in the following example: % cccc --II//uussrr//llooccaall//ppoossttggrreess//iinncclluuddee --oo _f_o_o _f_o_o_._c --llppqq This feature of copying just the necessary header files into was incorporated right before we produced the release and has not had sufficient testing. If your frontend programs complain about not being able to find header files, either add the missing header files to the iinncclluuddee// directory and notify us of the problem, or just point the --II compiler directive directly into the source as was done in the past. E.g., % cccc --II//uussrr//llooccaall//ppoossttggrreess//ssrrcc//bbaacckkeenndd --oo _f_o_o _f_o_o_._c --llppqq If you do this, you may need to add the following ##ddeeffiinnees in your source to set the PORTNAME -- add them prior to any ##iinncclluuddees: ##ddeeffiinnee PPOORRTTNNAAMMEE uullttrriixx44 ##ddeeffiinnee PPOORRTTNNAAMMEE__uullttrriixx44 for Ultrix, or ##ddeeffiinnee PPOORRTTNNAAMMEE ssppaarrcc ##ddeeffiinnee PPOORRTTNNAAMMEE__ssppaarrcc for SPARCs. We're sorry for any inconvenience this may cause, and fully intend that the iinncclluuddee// directory be more rigorously tested in the next release. 55..33.. WWiissccoonnssiinn BBeenncchhmmaarrkk DDaattaabbaassee In ......//ppoossttggrreess//ssrrcc//rreeggrreessss//bbeenncchh are files which are the queries used in the POSTGRES version of the Wisconsin bench- mark. The Wisconsin benchmark illustrates basic relational performance using B-tree indices on nontrivial amounts of data. To run the benchmark, ccdd to that directory and type % bbmmaakkee rruunntteesstt 1177 55..44.. MMiinniimmaall IInnssttaallllaattiioonn It is the intention that everything below the ssrrcc// directory can be removed, however we didn't have time to test this. Specifically, the iinncclluuddee// directory may not have all the necessary files to compile all the frontend programs, so you may might get burned if you remove the source. But if you'd like to try it out, we'd love to know if it works for you. 66.. DDooccuummeennttaattiioonn Plain text and PostScript versions of the manual pages and documents are available in the directories mmaann// and ddoocc// at the top level. To recreate these documents, there are cor- responding directories in ssrrcc//{{mmaann,,ddoocc}}. They are currently configured to require ggrrooffff and friends, but you may be able to change the MMaakkeeffiillee to use other facilities if you have them. If you change directories to mmaann// and ddoocc// and type: % bbmmaakkee % bbmmaakkee iinnssttaallll in each, it will format and install the documents into the corresponding destination directories. 77.. MMiisscceellllaanneeoouuss 77..11.. BBuugg rreeppoorrttss If you find a bug in POSTGRES, please send mail to bbuugg--ppoossttggrreess@@ppoossttggrreess..BBeerrkkeelleeyy..EEDDUU or uuuunneett!!uuccbbvvaaxx!!ppoossttggrreess!!bbuugg--ppoossttggrreess describing as precisely as possible the command that caused the problem, concise instructions on how to repeat the bug, and a script showing the bug. If possible, a stack trace (generated using a debugger such as ddbbxx or ggddbb) should also be provided. (The backend program will leave its core dumps in the directory PGDATA//bbaassee//yyoouurr__ddaattaabbaassee, where "your_database" is the name of your database.) 77..22.. CCoonnssuullttiinngg This software is unsupported, public domain software. Although we are interested in feedback, it is impossible for us to make any commitment to support in a research environ- ment. 1188 If you do want to talk directly to the POSTGRES group, elec- tronic mail is strongly preferred. We can be reached via the Internet as ppoosstt__qquueessttiioonnss@@ppoossttggrreess..BBeerrkkeelleeyy..EEDDUU or uuuunneett!!uuccbbvvaaxx!!ppoossttggrreess!!ppoosstt__qquueessttiioonnss We can also be reached at (510) 643-6448, Monday through Friday, between 1 and 4 PM Pacific Time. 77..33.. PPoossttggrreess MMaaiilliinngg LLiisstt A mailing list for POSTGRES announcements and discussion is available for anyone who is interested. If you wish to sub- scribe to this mailing list, send mail to ppoossttggrreess--rreeqquueesstt@@ppoossttggrreess..BBeerrkkeelleeyy..EEDDUU or uuuunneett!!uuccbbvvaaxx!!ppoossttggrreess!!ppoossttggrreess--rreeqquueesstt with AADDDD as the subject. Note that mail sent to this address is processed eelleeccttrroonniiccaallllyy. (Deletion requests are handled by sending mail to the same address with subject DDEELL.) The mailing list itself is called ppoossttggrreess@@ppoossttggrreess..BBeerrkkeelleeyy..EEDDUU or uuuunneett!!uuccbbvvaaxx!!ppoossttggrreess!!ppoossttggrreess and all mail sent to this address will be will be routed to the mailing list membership. This list is distributed to over 500 sites around the world, so please do NNOOTT send administrative requests to this address. If you have any problems with the mailing list, send mail to ppoosstt__qquueessttiioonnss. 1199