_P_O_S_T_G_R_E_S _I_N_S_T_A_L_L_A_T_I_O_N _I_N_S_T_R_U_C_T_I_O_N_S Release 4.2 _D_o_c_u_m_e_n_t _O_v_e_r_v_i_e_w 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 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 _1_. _I_n_t_r_o_d_u_c_t_i_o_n 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. _2_. _S_i_t_e _R_e_q_u_i_r_e_m_e_n_t_s _2_._1_. _H_a_r_d_w_a_r_e POSTGRES currently has been tested by the POSTGRES develop- ment team on the following systems: +o Digital Equipment DECstation 3000 series (Alpha AXP architecture) machines running Alpha OSF/1 1.3 and higher. +o Digital Equipment DECstation 3100 and 5000 series (MIPS architecture) machines running Ultrix 4.2 and higher. +o Hewlett-Packard H-P 9000 Series 700 and 800 (PA-RISC architecture) machines running HP-UX 9.00 and higher. +o International Business Machines RS/6000 (POWER architec- ture) machines running AIX 3.2.3 and higher. +o Sun Microsystems (SPARC architecture) machines running SunOS 4.1.2 (Solaris 1.0.1) and higher. (POSTGRES will nnoott run on machines running SunOS 5.x (Solaris 2.x)). 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 _2_._2_. _S_o_f_t_w_a_r_e _2_._3_. _D_i_s_t_r_i_b_u_t_i_o_n _T_a_p_e These instructions assume you have a POSTGRES Version 4.2 distribution tape (in either SCSI cartridge or DEC TK50 car- tridge format) or a POSTGRES tar file. The previous version of POSTGRES came with pre-compiled binaries for the SPARC and Ultrix platforms, however this release is a source dis- tribution only. The source code is available in tar format over the Internet via anonymous ftp from the site s2k- ftp.CS.Berkeley.EDU. Look in the directory ppuubb//ppoossttggrreess//ppoossttggrreess--vv44rr22. The compressed tar file con- taining the complete source distribution is named ppoossttggrreess-- vv44rr22..ttaarr..ZZ. _2_._4_. _E_x_p_e_r_t_i_s_e 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 recommend that your site's system administrator read this document also. _2_._5_. _C_o_n_f_i_g_u_r_a_t_i_o_n 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. _2_._5_._1_. _O_p_e_r_a_t_i_n_g _S_y_s_t_e_m 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. The make program is installed using the name bbmmaakkee to avoid any conflict with your native make. _2_._5_._2_. _D_i_s_k _R_e_q_u_i_r_e_m_e_n_t_s POSTGRES requires 50 megabytes of disk space, preferably on a single partition, to compile and install from sources. 33 _2_._5_._3_. _K_e_r_n_e_l POSTGRES makes use of the System V inter-process communica- tion (shared memory and semaphore) operations provided by the operating system. SunOS and Ultrix require a properly configured kernel which is in general different than the factory-shipped generic kernel. See the section on kernel configuration for details. _3_. _I_n_s_t_a_l_l_i_n_g _P_O_S_T_G_R_E_S To install the system you must load the sources into your filesystem and build the system from scratch. To run the compiled programs your kernel may need to be configured to support the shared memory and semaphore operations required by the code. Assuming you have run POSTGRES in the past, your machine may already be properly configured. _3_._1_. _S_t_e_p _1 _- _P_r_e_p_a_r_a_t_i_o_n 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. _3_._1_._1_. _F_i_n_d _a _g_o_o_d _p_l_a_c_e _f_o_r _P_O_S_T_G_R_E_S First you should locate a disk partition with at least 50 megabytes of free space available for POSTGRES. For exam- ple: # 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_._._. _3_._1_._2_. _C_r_e_a_t_i_n_g _t_h_e _P_O_S_T_G_R_E_S _d_i_r_e_c_t_o_r_y Once you have decided, create the directory to hold the release if it doesn't already exist. Then ccdd to this 44 directory and type ppwwdd. This is the full path of the direc- tory you will install POSTGRES in. For example: # ccdd //uussrr//llooccaall # mmkkddiirr ppoossttggrreess # ccdd ppoossttggrreess # ppwwdd /usr/local/postgres # _3_._2_. _C_r_e_a_t_i_n_g _t_h_e _P_O_S_T_G_R_E_S _u_s_e_r 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. You can make this work with- out creating a "postgres" login but this is highly discour- aged -- all POSTGRES processes run with this user id, and making yourself the POSTGRES user essentially grants all of your database users the ability to become _y_o_u without a password. Note that the restriction about having a "postgres" login with UID 6 was removed in POSTGRES Version 4.0.1. _3_._3_. _S_t_e_p _2 _- _L_o_a_d_i_n_g _P_O_S_T_G_R_E_S 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. 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." _3_._3_._1_. _L_o_a_d_i_n_g _P_O_S_T_G_R_E_S _f_r_o_m _a _T_a_p_e Login as "postgres" and change directories to the postgres directory that was created. Run ttaarr with the following options % ttaarr xxvvffpp _<_t_a_p_e_-_d_e_v_i_c_e_> 55 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--vv44rr22..ttaarr..ZZ will appear in your POSTGRES home directory. 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." _3_._4_. _L_o_a_d_i_n_g _P_O_S_T_G_R_E_S _f_r_o_m _a _T_a_r _F_i_l_e If you are not logged in as ppoossttggrreess already, do so now. Uncompress the tar file. % uunnccoommpprreessss ppoossttggrreess--vv44rr22..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--vv44rr22..ttaarr. Extract POSTGRES from the tar file using the following com- mand: % ttaarr xxvvffpp ppoossttggrreess--vv44rr22..ttaarr 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--vv44rr22..ttaarr 66 _3_._5_. _S_t_e_p _3 _- _K_e_r_n_e_l _C_o_n_f_i_g_u_r_a_t_i_o_n 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 sys- tem 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 understanding 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 tthheessee sstteeppss sshhoouulldd bbee ppeerrffoorrmmeedd bbyy ssoommeeoonnee wwiitthh ssyysstteemm aaddmmiinniissttrraattiioonn eexxppeerriieennccee. 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((33)), sshhmmoopp((33)), sshhmmccttll((33)), etc. Now proceed to the appropriate section for your machine. _3_._5_._1_. _K_e_r_n_e_l _r_e_c_o_n_f_i_g_u_r_a_t_i_o_n _f_o_r _S_p_a_r_c_s We previously suggested that you reconfigure your kernel under SunOS. However, the parameters in the generic kernel of current releases of SunOS ought to suffice. (We never reconfigure our kernels and have not noticed any problems while running several ppoossttmmaasstteerrs at once.) _3_._5_._2_. _K_e_r_n_e_l _r_e_c_o_n_f_i_g_u_r_a_t_i_o_n _f_o_r _D_E_C_s 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 77 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. _3_._6_. _C_o_m_p_i_l_i_n_g _a_n_d _I_n_s_t_a_l_l_i_n_g _P_O_S_T_G_R_E_S The sources for POSTGRES are all under the directory ssrrcc//. _3_._6_._1_. _B_u_i_l_d _S_t_e_p _1_: _C_u_s_t_o_m_i_z_a_t_i_o_n CCdd into the ssrrcc// directory and edit the file MMaakkee-- ffiillee..gglloobbaall. You may change the various configuration options here, such as where the POSTGRES executable files are installed and where it looks for the database directory. The configuration switches are fairly self-explanatory, but we will go over some of the more commonly-changed options. The PORTNAME option mmuusstt be set correctly. By default the PORTNAME is uullttrriixx44, but aix alpha hpux sparc ultrix4 are all valid choices. The top-level directory where POSTGRES binaries, documenta- tion, header files, libraries, and databases are installed is controlled by the variable POSTGRESDIR. This variable defaults to //uussrr//llooccaall//ppoossttggrreess. Whether you change this variable or not, make sure that the directory to which POST- GRESDIR refers exists before proceeding. If you do not want bbmmaakkee installed into //uussrr//llooccaall//bbiinn and //uussrr//llooccaall//lliibb, change the values of TOOLSBINDIR and TOOL- SLIBDIR and make sure that these directories exist before proceeding. Standards notwithstanding, every system has an iinnssttaallll pro- gram with slightly different options and behavior. Our Makefiles assume that iinnssttaallll accepts BSD (as opposed to System V) options. For example, the iinnssttaallll that comes with SunOS and Ultrix accept BSD options. However, the default OSF/1, HP-UX and AIX installation programs do not. You can set the INSTALL variable to control which program is called. On OSF/1, set INSTALL to //bbiinn//iinnssttaallllbbssdd. On AIX, use //uussrr//uuccbb//iinnssttaallll instead of //bbiinn//iinnssttaallll. On HP-UX, you will have to find a BSD-compatible installation program and set INSTALL to the location of this program. bbssddiinnsstt, which comes with the MIT X Window System distribution (in 88 mmiitt//uuttiill//ssccrriippttss), is widely available (we include it in the ssrrcc//ttoooollss directory) and works acceptably. The GNU iinnssttaallll program does not. If you are not installing POSTGRES using superuser privi- leges, or you are installing POSTGRES without creating a "postgres" user, you may have to set the values of several variables that control the system ownership. For example, "janeuser" who is a member of group "users" may have to set: BBIINNGGRRPP== uusseerrss BBIINNOOWWNN== jjaanneeuusseerr LLIIBBGGRRPP== uusseerrss LLIIBBOOWWNN== jjaanneeuusseerr MMAANNGGRRPP== uusseerrss MMAANNOOWWNN== jjaanneeuusseerr to prevent the iinnssttaallll program from complaining. If you absolutely insist on having a POSTGRES user other than "postgres", set the variable POSTGRESLOGIN to the name of that user. _3_._6_._2_. _O_n _H_P_-_U_X _o_n_l_y_: _i_n_s_t_a_l_l_i_n_g _b_s_d_i_n_s_t We told you about this above, but in case you missed it, you will have to install bbssddiinnsstt from ssrrcc//ttoooollss//bbssddiinnsstt if you don't have it installed already. You can just copy the shell script bbssddiinnsstt..sshh into some convenient place (probably //uussrr//llooccaall//bbiinn) as bbssddiinnsstt. _3_._6_._3_. _B_u_i_l_d _S_t_e_p _2_: _B_o_o_t_s_t_r_a_p_p_i_n_g _"_b_m_a_k_e_" Because the bbmmaakkee program is normally installed into //uussrr//llooccaall//{{bbiinn,,lliibb}} and that directory is usually only writable by root, it will be necessary to temporarily become root to install bbmmaakkee. If you don't have root privilege you can change where bbmmaakkee is installed by changing the value of the TOOLSBINDIR and TOOLSLIBDIR options in ssrrcc//MMaakkeeffiillee..gglloobbaall, otherwise you will need to ssuu(1) to root. Next make sure that the mmaakkee program in your PATH environment variable is the system-provided mmaakkee (usually //bbiinn//mmaakkee) and NNOOTT GNU mmaakkee (many users have reported prob- lems getting old versions of GNU mmaakkee to work). CCdd into ssrrcc//ttoooollss//bbmmaakkee and type % ..//BBoooottssttrraapp _p_o_r_t_n_a_m_e where _p_o_r_t_n_a_m_e is the value of PORTNAME (ultrix4, sparc, etc). This should compile and install the bbmmaakkee program and its supporting files, including the POSTGRES-related Make- file templates. If all went well you will now be able to 99 use the new make program by typing bbmmaakkee, assuming you have //uussrr//llooccaall//bbiinn in your shell path. You may have to type rreehhaasshh if you run ccsshh. _3_._6_._4_. _O_n _U_l_t_r_i_x _o_n_l_y_: _b_u_i_l_d_i_n_g _a_n_d _i_n_s_t_a_l_l_i_n_g _l_i_b_d_l This version of POSTGRES uses the ddllooppeenn((33)) interface to implement dynamic function loading. Since there is no such vendor-supplied interface on the Ultrix platform you must build and install a version that we provide. The same previous discussion about being root also holds true here. However, if you cannot become root to install this library, then you will have to change the backend Make- file to look for this library where you have decided to put it using the -L loader flag. To compile and install the library simply ccdd into ssrrcc//ttoooollss//lliibbddll and type % bbmmaakkee aallll iinnssttaallll This will build and install the lliibbddll..aa library into TOOL- SLIBDIR (this is //uussrr//llooccaall//lliibb by default). If your linker/loader doesn't search here by default (it usually does), you may have to rraannlliibb((11)) on it. _3_._6_._5_. _O_n _A_I_X _o_n_l_y_: _i_n_s_t_a_l_l_i_n_g _m_k_l_d_e_x_p_o_r_t CCdd into ssrrcc//ttoooollss//mmkkllddeexxppoorrtt and type % bbmmaakkee aallll iinnssttaallll _3_._6_._6_. _B_u_i_l_d _S_t_e_p _3_: _C_o_m_p_i_l_e _a_n_d _I_n_s_t_a_l_l CCdd back to the ssrrcc// directory (i.e., ccdd ....//....) and type: % bbmmaakkee aallll iinnssttaallll 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 into 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 1100 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. _3_._7_. _C_r_e_a_t_i_n_g _t_h_e _i_n_i_t_i_a_l _d_a_t_a_b_a_s_e 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 the POSTGRES programs were loaded into //uussrr//llooccaall//ppoossttggrreess//bbiinn). 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 ppoosstt-- mmaasstteerr. The section after "Testing" discusses this. _3_._8_. _S_t_e_p _4 _- _T_e_s_t_i_n_g We suggest you run the regression tests to make sure the release was installed successfully. Also, you need to have the ppoossttmmaasstteerr running to run the test, so type the follow- ing: % TTZZ==GGMMTT00 % eexxppoorrtt TTZZ % ppoossttmmaasstteerr && if you use sshh or kksshh. If you use ccsshh, type % sseetteennvv TTZZ GGMMTT00 % ppoossttmmaasstteerr && instead. The bit about "TZ" is just to make sure that your 1111 regression output will be comparable to ours; you don't have to do this every time you start the ppoossttmmaasstteerr. Next, cre- ate a new user using the ccrreeaatteeuusseerr command (see the Refer- ence Manual). DDoo nnoott run the regression test script as user "postgres"! Change directories to ssrrcc//rreeggrreessss//rreeggrreessss. % ccdd ssrrcc//rreeggrreessss//rreeggrreessss Make sure that the oobbjj subdirectory is writable by the "postgres" user, as some of the tests involve the "postgres" user copying data into that directory. (We ship it that way, but if you left the "p" flag off of your ttaarr command, it will be set according to your umask.) Type the following command to run the test: % bbmmaakkee cclleeaann aallll 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: % sshh cchheecckkddiiffff It may take a little while to run as the regression results are quite large. There will be many differences correspond- ing to the timestamp lines and occasional lines where object id numbers (OIDs) are different. The cchheecckkddiiffff program attempts to weed out these innocuous differences. If you see any differences not corresponding to timestamps or OIDs, there may be a problem. Have your local POSTGRES expert take a look at it. _4_. _R_u_n_n_i_n_g _P_O_S_T_G_R_E_S POSTGRES is designed to be a multiuser system. In practice, POSTGRES consists of three (or more) processes: +o the postmaster, +o a front-end program (often the terminal monitor), and +o the backend server. Users are expected to use the terminal monitor for direct access to the database. The terminal monitor sends commands to the ppoossttmmaasstteerr which forwards commands to a backend. 1122 _4_._1_. _T_h_e _P_O_S_T_G_R_E_S _P_o_s_t_m_a_s_t_e_r The ppoossttmmaasstteerr is a process which manages communication between the a front-end program such as the terminal monitor and a POSTGRES backend. Without a running ppoossttmmaasstteerr, the front-end program will not be able to connect to a backend. In general, the ppoossttmmaasstteerr must be running for you (or oth- ers) to run any of the normal POSTGRES commands. Always start the ppoossttmmaasstteerr when logged in as the special "post- gres" user, otherwise the system will not be able to access the database files. To start it, type % ppoossttmmaasstteerr && _4_._2_. _T_h_e _P_O_S_T_G_R_E_S _T_e_r_m_i_n_a_l _M_o_n_i_t_o_r 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 || ---------------------------------------------------------- || mmiikkee || 779999 || ---------------------------------------------------------- 1133 || 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 %% _4_._3_. _T_h_e _P_O_S_T_G_R_E_S _B_a_c_k_e_n_d The POSTGRES backend is the process which does all the "real" work. This process is started by the ppoossttmmaasstteerr when it receives a connection from a terminal monitor, 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 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 ppoossttmmaasstteerr handles shared 1144 resources such as semaphores and shared memory. In short: never do this if there is a ppoossttmmaasstteerr running. In addi- tion, when using the terminal monitor, returned tuples are displayed more usefully and input is buffered better. The backend should only be used interactively during debugging. _4_._4_. _P_O_S_T_G_R_E_S _S_u_p_p_o_r_t _P_r_o_g_r_a_m_s 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 reindexdb - rebuild system catalog indices after disaster shmemdoc - shared memory buffer pool doctor vacuum - database vacuum cleaner 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 _5_. _O_p_t_i_o_n_a_l _I_n_s_t_a_l_l_a_t_i_o_n _5_._1_. _I_n_s_t_a_l_l_i_n_g _L_I_B_P_Q_, _t_h_e _P_O_S_T_G_R_E_S _f_r_o_n_t_e_n_d _l_i_b_r_a_r_y The file ......//lliibb//lliibbppqq..aa is created when you install the system. This library contains various routines intended for use by frontend programs. 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//llooccaall//lliibb or //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 commands so that they can find lliibbppqq..aa. 1155 _5_._2_. _P_o_s_t_g_r_e_s _h_e_a_d_e_r _f_i_l_e_s The directory ......//iinncclluuddee contains copies of most of the header files that front-end applications might need. You can compile 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 Occassionally a front-end program source might reference header files from the postgres source that were not copied into the include directory. If your front-end program com- plains 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___P_O_R_T_N_A_M_E For example, if you are running on the ultrix4 port, you would put ##ddeeffiinnee PPOORRTTNNAAMMEE__uullttrriixx44 in your program, or if you're running the sparc port put ##ddeeffiinnee PPOORRTTNNAAMMEE__ssppaarrcc in your program source. _5_._3_. _W_i_s_c_o_n_s_i_n _B_e_n_c_h_m_a_r_k _D_a_t_a_b_a_s_e 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 _5_._4_. _M_i_n_i_m_a_l _I_n_s_t_a_l_l_a_t_i_o_n It is the intention that everything below the ssrrcc// directory can be removed. Sometimes however, as stated earlier, the 1166 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. Should you really wish to remove the source to reclaim space you could always copy all the header files from the backend into the include directory (preserving the directory structure of course). _6_. _D_o_c_u_m_e_n_t_a_t_i_o_n 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. In general, recreat- ing documents from their source is very difficult due to differences in macro packages and formatting programs (in short, good luck!). _7_. _M_i_s_c_e_l_l_a_n_e_o_u_s _7_._1_. _B_u_g _r_e_p_o_r_t_s 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.) _7_._2_. _C_o_n_s_u_l_t_i_n_g This software is unsupported, public domain software. Although we are interested in feedback, it is impossible for us to make any commitment to provide support in a research environment. 1177 If you do want to talk directly to the POSTGRES group, elec- tronic mail is the only method. We can be reached via the Internet as ppoosstt__qquueessttiioonnss@@ppoossttggrreess..BBeerrkkeelleeyy..EEDDUU or uuuunneett!!uuccbbvvaaxx!!ppoossttggrreess!!ppoosstt__qquueessttiioonnss Please be aware that this was the last release of POSTGRES from the University of California, and for all practical purposes there is no longer a POSTGRES group. However some of us may still be reading mail for a while and it is likely that some correspondence will occur. _7_._3_. _P_o_s_t_g_r_e_s _M_a_i_l_i_n_g _L_i_s_t 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 _e_l_e_c_t_r_o_n_i_c_a_l_l_y. (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. As of the time of this release, this mailing list was being distributed to over 600 sites around the world, so please do _N_O_T send administrative requests to this address. If you have any problems with the mailing list, send mail to ppoosstt__qquueessttiioonnss. 1188