2 PostgreSQL Installation Guide
3 The PostgreSQL Development Team
8 PostgreSQL is Copyright © 1996-2000 by PostgreSQL Inc.
15 Currently Supported Platforms
19 Installation Procedure
20 4. Configuration Options
30 Postgres, developed originally in the UC Berkeley Computer
31 Science Department, pioneered many of the object-relational
32 concepts now becoming available in some commercial databases.
33 It provides SQL92/SQL3 language support, transaction integrity,
34 and type extensibility. PostgreSQL is an open-source descendant
35 of this original Berkeley code.
\f
37 Chapter 1. Introduction
40 This installation procedure makes some assumptions about the
41 desired configuration and runtime environment for your system.
42 This may be adequate for many installations, and is almost
43 certainly adequate for a first installation. But you may want
44 to do an initial installation up to the point of unpacking the
45 source tree and installing documentation, and then print or
46 browse the Administrator's Guide.
\f
51 This manual describes version 7.0 of Postgres. The Postgres
52 developer community has compiled and tested Postgres on a
53 number of platforms. Check the web site
54 (http://www.postgresql.org/docs/admin/ports.htm) for the latest
57 Currently Supported Platforms
60 At the time of publication, the following platforms have been
63 Table 2-1. Supported Platforms
64 OS Processor Version Reported Remarks
65 AIX RS6000 v7.0 2000-04-05 Andreas Zeugswetter
66 4.3.2 (Andreas.Zeugswetter@telecom.at)
67 BSDI x86 v7.0 2000-04-04 Bruce Momjian
68 4.01 (maillist@candle.pha.pa.us)
69 Compaq Alpha v7.0 2000-04-11 Andrew McMurry
70 Tru64 (andrew.mcmurry@astro.uio.no)
71 FreeBSD x86 v7.0 2000-04-04 Marc Fournier
73 HPUX PA-RISC v7.0 2000-04-12 Both 9.0x and 10.20. Tom Lane
75 IRIX MIPS v6.5.3 2000-02-18 MIPSPro 7.3.1.1m N32 build. Kevin
76 6.5.6f Wheatley (hxpro@cinesite.co.uk)
77 Linux Alpha v7.0 2000-04-05 With published patches.
78 2.0.x Ryan Kirkpatrick
80 Linux armv4l v7.0 2000-04-17 Regression test needs work.
82 (segfault@hardline.org)
83 Linux x86 v7.0 2000-03-26 Lamar Owens
84 2.2.x (lamar.owen@wgcr.org)
85 Linux MIPS v7.0 2000-04-13 Cobalt Qube. Tatsuo Ishii
86 2.0.x (t-ishii@sra.co.jp)
87 Linux Sparc v7.0 2000-04-02 Tom Szybist
88 2.2.5 (szybist@boxhill.com)
89 Linux PPC603e v7.0 2000-04-13 Tatsuo Ishii
90 PPC R4 (t-ishii@sra.co.jp)
91 mklinux PPC750 v7.0 2000-04-13 Tatsuo Ishii
93 NetBSD arm32 v7.0 2000-04-08 Patrick Welche
94 1.4 (prlw1@newn.cam.ac.uk)
95 NetBSD x86 v7.0 2000-03-26 Patrick Welche
96 1.4U (prlw1@newn.cam.ac.uk)
97 NetBSD m68k v7.0 2000-04-10 Mac 8xx. Henry B. Hotz
99 NetBSD- Sparc v7.0 2000-04-13 Tom I Helbekkmo
100 /sparc (tih@kpnQwest.no)
101 QNX x86 v7.0 2000-04-01 Dr. Andreas Kardos
102 4.25 (kardos@repas-aeg.de)
103 SCO Open x86 v6.5 1999-05-25 Andrew Merrill
104 Server 5 (andrew@compclass.com)
105 SCO UW7 x86 v7.0 2000-04-18 See FAQ. Billy G. Allie
107 Solaris x86 v7.0 2000-04-12 Marc Fournier
109 Solaris Sparc v7.0 2000-04-12 Peter Eisentraut
110 2.5-.7 (peter_e@gmx.net)
111 SunOS Sparc v7.0 2000-04-13 Tatsuo Ishii
112 4.1.4 (t-ishii@sra.co.jp)
113 Windows x86 v7.0 2000-04-02 No server-side. Magnus Hagander
114 Win32 (mha@sollentuna.net)
115 WinNT x86 v7.0 2000-03-30 Uses Cygwin library. Daniel Horak
116 Cygwin (horak@sit.plzen-city.cz)
121 Note: For Windows NT, the server-side port of Postgres uses
122 the RedHat/Cygnus Cygwin library and toolset. For Windows
123 9x, no server-side port is available due to OS limitations.
125 Unsupported Platforms
128 Platforms listed for v6.3.x-v6.5.x should also work with v7.0,
129 but we did not receive explicit confirmation of such at the
130 time this list was compiled. We include these here to let you
131 know that these platforms could be supported if given some
133 At the time of publication, the following platforms have not
134 been tested for v7.0 or v6.5.x:
136 Table 2-2. Unsupported Platforms
137 OS Processor Version Reported Remarks
138 BeOS x86 v7.0 2000-05-01 Client-side coming soon?
140 (adam@newsnipple.com)
141 DGUX m88k v6.3 1998-03-01 v6.4 probably OK. Needs new
142 5.4R4.11 maintainer. Brian E Gallew
144 NetBSD NS32532 v6.4 1998-10-27 Date/time math annoyances.
145 current Jon Buller (jonb@metronet.com)
146 NetBSD VAX v6.3 1998-03-01 v7.0 should work. Tom I Helbekkmo
147 1.3 (tih@kpnQwest.no)
148 SVR4 4.4 m88k v6.2.1 1998-03-01 v6.4.x will need TAS spinlock
149 code. Doug Winterburn
150 (dlw@seavme.xroads.com)
151 SVR4 MIPS v6.4 1998-10-28 No 64-bit int. Frank Ridderbusch
152 (ridderbusch.pad@sni.de)
153 Ultrix MIPS, VAX v6.x 1998-03-01 No recent reports; obsolete?
157 There are a few platforms which have been attempted and which
158 have been reported to not work with the standard distribution.
159 Others listed here do not provide sufficient library support
162 Table 2-3. Incompatible Platforms
163 OS Processor Version Reported Remarks
164 MacOS all v6.x 1998-03-01 Not library compatible; use
166 NextStep x86 v6.x 1998-03-01 Client-only support; v1.0.9
167 worked with patches David
173 Chapter 3. Installation
176 Installation instructions for PostgreSQL 7.0.
178 If you haven't gotten the PostgreSQL distribution, get it from
179 ftp.postgresql.org (ftp://ftp.postgresql.org), then unpack it:
181 > gunzip postgresql-7.0.tar.gz
182 > tar -xf postgresql-7.0.tar
183 > mv postgresql-7.0 /usr/src
191 Building PostgreSQL requires GNU make. It will not work with
192 other make programs. On GNU/Linux systems GNU make is the
193 default tool, on other systems you may find that GNU make is
194 installed under the name gmake. We will use that name from now
195 on to indicate GNU make, no matter what name it has on your
196 system. To test for GNU make enter
201 If you need to get GNU make, you can find it at
203 Up to date information on supported platforms is at
204 http://www.postgresql.org/docs/admin/ports.htm
205 (http://www.postgresql.org/docs/admin/ports.htm). In general,
206 most Unix-compatible platforms with modern libraries should be
207 able to run PostgreSQL. In the doc subdirectory of the
208 distribution are several platform-specific FAQ and README
209 documents you might wish to consult if you are having trouble.
210 Although the minimum required memory for running PostgreSQL
211 can be as little as 8MB, there are noticeable speed
212 improvements when expanding memory up to 96MB or beyond. The
213 rule is you can never have too much memory.
214 Check that you have sufficient disk space. You will need about
215 30 Mbytes for the source tree during compilation and about 5
216 Mbytes for the installation directory. An empty database takes
217 about 1 Mbyte, otherwise they take about five times the amount
218 of space that a flat text file with the same data would take.
219 If you run the regression tests you will temporarily need an
221 To check for disk space, use
226 Considering today's prices for hard disks, getting a large and
227 fast hard disk should probably be in your plans before putting
228 a database into production use.
230 Installation Procedure
233 PostgreSQL Installation
234 For a fresh install or upgrading from previous releases of
236 1. Create the PostgreSQL superuser account. This is the user
237 the server will run as. For production use you should create
238 a separate, unprivileged account (postgres is commonly
239 used). If you do not have root access or just want to play
240 around, your own user account is enough.
241 Running PostgreSQL as root, bin, or any other account with
242 special access rights is a security risk; don't do it. The
243 postmaster will in fact refuse to start as root.
244 You need not do the building and installation itself under
245 this account (although you can). You will be told when you
246 need to login as the database superuser.
247 2. Configure the source code for your system. It is this step
248 at which you can specify your actual installation path for
249 the build process and make choices about what gets
250 installed. Change into the src subdirectory and type:
253 followed by any options you might want to give it. For a
254 first installation you should be able to do fine without
255 any. For a complete list of options, type:
258 Some of the more commonly used ones are:
261 Selects a different base directory for the installation
262 of PostgreSQL. The default is /usr/local/pgsql.
265 If you want to use locales.
268 Allows the use of multibyte character encodings. This is
269 primarily for languages like Japanese, Korean, or Chinese.
272 Builds the Perl interface and plperl extension language.
273 Please note that the Perl interface needs to be installed
274 into the usual place for Perl modules (typically under
275 /usr/lib/perl), so you must have root access to perform
276 the installation step. (It is often easiest to leave out
277 --with-perl initially, and then build and install the Perl
278 interface after completing the installation of PostgreSQL
282 Builds the ODBC driver package.
285 Builds interface libraries and programs requiring Tcl/Tk,
286 including libpgtcl, pgtclsh, and pgtksh.
288 3. Compile the program. Type
291 The compilation process can take anywhere from 10 minutes
292 to an hour. Your mileage will most certainly vary. Remember
294 The last line displayed will hopefully be
295 All of PostgreSQL is successfully made. Ready to install.
298 4. If you want to test the newly built server before you
299 install it, you can run the regression tests at this point.
300 The regression tests are a test suite to verify that
301 PostgreSQL runs on your machine in the way the developers
302 expected it to. For detailed instructions see Regression
303 Test. (Be sure to use the "parallel regress test" method,
304 since the sequential method only works with an
305 already-installed server.)
306 5. If you are not upgrading an existing system then skip to
308 You now need to back up your existing database. To dump
309 your fairly recent post-6.0 database installation, type
310 > pg_dumpall > db.out
312 If you wish to preserve object id's (oids), then use the -o
313 option when running pg_dumpall. However, unless you have a
314 special reason for doing this (such as using OIDs as keys in
315 tables), don't do it.
316 Make sure to use the pg_dumpall command from the version
317 you are currently running. 7.0's pg_dumpall will not work on
318 older databases. However, if you are still using 6.0, do not
319 use the pg_dumpall script from 6.0 or everything will be
320 owned by the PostgreSQL superuser after you reload. In that
321 case you should grab pg_dumpall from a later 6.x.x release.
322 If you are upgrading from a version prior to Postgres95
323 v1.09 then you must back up your database, install
324 Postgres95 v1.09, restore your database, then back it up
329 You must make sure that your database is not updated
330 in the middle of your backup. If necessary, bring down
331 postmaster, edit the permissions in file
332 /usr/local/pgsql/data/pg_hba.conf to allow only you on,
333 then bring postmaster back up.
337 6. If you are upgrading an existing system then kill the
338 database server now. Type
339 > ps ax | grep postmaster
342 > ps -e | grep postmaster
344 (It depends on your system which one of these two works. No
345 harm can be done by typing the wrong one.) This should list
346 the process numbers for a number of processes, similar to
348 263 ? SW 0:00 (postmaster)
349 777 p1 S 0:00 grep postmaster
351 Type the following line, with pid replaced by the process
352 id for process postmaster (263 in the above case). (Do not
353 use the id for the process "grep postmaster".)
358 Tip: On systems which have PostgreSQL started at boot
359 time, there is probably a startup file that will
360 accomplish the same thing. For example, on a Redhat Linux
361 system one might find that
362 > /etc/rc.d/init.d/postgres.init stop
366 Also move the old directories out of the way. Type the
368 > mv /usr/local/pgsql /usr/local/pgsql.old
370 (substitute your particular paths).
371 7. Install the PostgreSQL executable files and libraries. Type
375 You should do this step as the user that you want the
376 installed executables to be owned by. This does not have to
377 be the same as the database superuser; some people prefer to
378 have the installed files be owned by root.
379 8. If necessary, tell your system how to find the new shared
380 libraries. How to do this varies between platforms. The most
381 widely usable method is to set the environment variable
383 > LD_LIBRARY_PATH=/usr/local/pgsql/lib
384 > export LD_LIBRARY_PATH
386 on sh, ksh, bash, zsh or
387 > setenv LD_LIBRARY_PATH /usr/local/pgsql/lib
389 on csh or tcsh. You might want to put this into a shell
390 startup file such as /etc/profile.
391 On some systems the following is the preferred method, but
392 you must have root access. Edit file /etc/ld.so.conf to add
396 Then run command /sbin/ldconfig.
397 If in doubt, refer to the manual pages of your system. If
398 you later on get a message like
399 psql: error in loading shared libraries
400 libpq.so.2.1: cannot open shared object file: No such file
403 then the above was necessary. Simply do this step then.
404 9. Create the database installation (the working data files).
405 To do this you must log in to your PostgreSQL superuser
406 account. It will not work as root.
407 > mkdir /usr/local/pgsql/data
408 > chown postgres /usr/local/pgsql/data
410 > /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
413 The -D option specifies the location where the data will be
414 stored. You can use any path you want, it does not have to
415 be under the installation directory. Just make sure that the
416 superuser account can write to the directory (or create it,
417 if it doesn't already exist) before starting initdb. (If you
418 have already been doing the installation up to now as the
419 PostgreSQL superuser, you may have to log in as root
420 temporarily to create the data directory underneath a
421 root-owned directory.)
422 10. The previous step should have told you how to start up
423 the database server. Do so now. The command should look
425 > /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data
427 This will start the server in the foreground. To make it
428 detach to the background, you can use the -S option, but
429 then you won't see any log messages the server produces. A
430 better way to put the server in the background is
431 > nohup /usr/local/pgsql/bin/postmaster -D
432 /usr/local/pgsql/data \
433 </dev/null >>server.log 2>>1 &
436 11. If you are upgrading from an existing installation,
437 dump your data back in:
438 > /usr/local/pgsql/bin/psql -d template1 -f db.out
440 You also might want to copy over the old pg_hba.conf file
441 and any other files you might have had set up for
442 authentication, such as password files.
444 This concludes the installation proper. To make your life more
445 productive and enjoyable you should look at the following
446 optional steps and suggestions.
447 o Life will be more convenient if you set up some environment
448 variables. First of all you probably want to include
449 /usr/local/pgsql/bin (or equivalent) into your PATH. To do
450 this, add the following to your shell startup file, such as
451 ~/.bash_profile (or /etc/profile, if you want it to affect
453 > PATH=$PATH:/usr/local/pgsql/bin
456 Furthermore, if you set PGDATA in the environment of the
457 PostgreSQL superuser, you can omit the -D for postmaster and
459 o You probably want to install the man and HTML documentation.
461 > cd /usr/src/pgsql/postgresql-7.0/doc
464 This will install files under /usr/local/pgsql/doc and
465 /usr/local/pgsql/man. To enable your system to find the man
466 documentation, you need to add a line like the following to a
468 > MANPATH=$MANPATH:/usr/local/pgsql/man
471 The documentation is also available in Postscript format. If
472 you have a Postscript printer, or have your machine already
473 set up to accept Postscript files using a print filter, then
474 to print the User's Guide simply type
475 > cd /usr/local/pgsql/doc
476 > gunzip -c user.ps.tz | lpr
478 Here is how you might do it if you have Ghostscript on your
479 system and are writing to a laserjet printer.
480 > gunzip -c user.ps.gz \
481 | gs -sDEVICE=laserjet -r300 -q -dNOPAUSE -sOutputFile=-
485 Printer setups can vary wildly from system to system. If in
486 doubt, consult your manuals or your local expert.
487 The Adminstrator's Guide should probably be your first
488 reading if you are completely new to PostgreSQL, as it
489 contains information about how to set up database users and
491 o Usually, you will want to modify your computer so that it
492 will automatically start the database server whenever it
493 boots. This is not required; the PostgreSQL server can be run
494 successfully from non-privileged accounts without root
496 Different systems have different conventions for starting up
497 daemons at boot time, so you are advised to familiarize
498 yourself with them. Most systems have a file /etc/rc.local or
499 /etc/rc.d/rc.local which is almost certainly no bad place to
500 put such a command. Whatever you do, postmaster must be run
501 by the PostgreSQL superuser (postgres) and not by root or any
502 other user. Therefore you probably always want to form your
503 command lines along the lines of su -c '...' postgres.
504 It might be advisable to keep a log of the server output. To
505 start the server that way try:
506 > nohup su -c 'postmaster -D /usr/local/pgsql/data >
507 server.log 2>&1' postgres &
510 Here are a few more operating system specific suggestions.
511 o Edit file rc.local on NetBSD or file rc2.d on SPARC Solaris
512 2.5.1 to contain the following single line:
513 > su postgres -c "/usr/local/pgsql/bin/postmaster -S -D
514 /usr/local/pgsql/data"
517 o In FreeBSD 2.2-RELEASE edit /usr/local/etc/rc.d/pgsql.sh to
518 contain the following lines and make it chmod 755 and chown
521 [ -x /usr/local/pgsql/bin/postmaster ] && {
522 su -l pgsql -c 'exec /usr/local/pgsql/bin/postmaster
523 -D/usr/local/pgsql/data
524 -S -o -F > /usr/local/pgsql/errlog' &
528 You may put the line breaks as shown above. The shell is
529 smart enough to keep parsing beyond end-of-line if there is
530 an expression unfinished. The exec saves one layer of shell
531 under the postmaster process so the parent is init.
532 o In RedHat Linux add a file /etc/rc.d/init.d/postgres.init
533 which is based on the example in contrib/linux/. Then make a
534 softlink to this file from /etc/rc.d/rc5.d/S98postgres.init.
536 o Run the regression tests against the installed server (using
537 the sequential test method). If you didn't run the tests
538 before installation, you should definitely do it now. For
539 detailed instructions see Regression Test.
540 To start experimenting with Postgres, set up the paths as
541 explained above and start the server. To create a database,
552 to connect to that database. At the prompt you can enter SQL
553 commands and start experimenting.
\f
554 Chapter 4. Configuration Options
557 Parameters for Configuration (configure)
560 The full set of parameters available in configure can be
567 The following parameters may be of interest to installers:
569 Directories to install PostgreSQL in:
570 --prefix=PREFIX install architecture-independent files
572 --bindir=DIR user executables in DIR [EPREFIX/bin]
573 --libdir=DIR object code libraries in DIR
575 --includedir=DIR C header files in DIR
577 --mandir=DIR man documentation in DIR [PREFIX/man]
578 Features and packages:
579 --disable-FEATURE do not include FEATURE
580 --enable-FEATURE[=ARG] include FEATURE [ARG=yes]
581 --with-PACKAGE[=ARG] use PACKAGE [ARG=yes]
582 --without-PACKAGE do not use PACKAGE
583 --enable and --with options recognized:
584 --with-template=template
585 use operating system template file
586 see template directory
587 --with-includes=dirs look for header files for tcl/tk, etc
588 --with-libraries=dirs look for additional libraries in DIRS
589 --with-libs=dirs alternate for --with-libraries
590 --enable-locale enable locale support
591 --enable-recode enable cyrillic recode support
592 --enable-multibyte enable multibyte character support
593 --with-pgport=portnum change default postmaster port
594 --with-maxbackends=n set default maximum number of processes
595 --with-tcl build Tcl interfaces and pgtclsh
596 --with-tclconfig=tcldir
597 tclConfig.sh and tkConfig.sh location
598 --with-perl build Perl interface and plperl
599 --with-odbc build ODBC driver package
600 --with-odbcinst=odbcdir
601 default directory for odbcinst.ini
602 --enable-cassert enable assertion checks
603 --enable-debug build with debugging symbols (-g)
605 use specific C compiler
607 use specific C++ compiler
608 --without-CXX prevent building C++ code
612 Some systems may have trouble building a specific feature of
613 Postgres. For example, systems with a damaged C++ compiler may
614 need to specify --without-CXX to instruct the build procedure
615 to skip construction of libpq++.
616 Use the --with-includes and --with-libraries options if you
617 want to build Postgres using include files or libraries that
618 are not installed in your system's standard search path. For
619 example, you might use these to build with an experimental
620 version of Tcl. If you need to specify more than one
621 nonstandard directory for include files or libraries, do it
624 --with-includes="/opt/tcl/include /opt/perl5/include"
629 Parameters for Building (make)
632 Many installation-related parameters can be set in the
633 building stage of Postgres installation.
634 In most cases, these parameters should be placed in a file,
635 Makefile.custom, intended just for that purpose. The default
636 distribution does not contain this optional file, so you will
637 create it using a text editor of your choice. When upgrading
638 installations, you can simply copy your old Makefile.custom to
639 the new installation before doing the build.
640 Alternatively, you can set variables on the make command line:
642 make [ variable=value [...] ]
646 A few of the many variables that can be specified are:
649 Top of the installation tree.
652 Location of applications and utilities.
655 Location of object libraries, including shared libraries.
658 Location of include files.
661 Location of installation-wide psqlODBC (ODBC) configuration
664 There are other optional parameters which are not as commonly
665 used. Many of those listed below are appropriate when doing
666 Postgres server code development.
669 Set flags for the C compiler. Should be assigned with "+="
670 to retain relevant default parameters.
673 Set flags for the yacc/bison parser. -v might be used to
674 help diagnose problems building a new parser. Should be
675 assigned with "+=" to retain relevant default parameters.
678 Enable Tcl interface building.
681 DocBook HTML style sheets for building the documentation
682 from scratch. Not used unless you are developing new
683 documentation from the DocBook-compatible SGML source
684 documents in doc/src/sgml/.
687 DocBook style sheets for building printed documentation
688 from scratch. Not used unless you are developing new
689 documentation from the DocBook-compatible SGML source
690 documents in doc/src/sgml/.
692 Here is an example Makefile.custom for a PentiumPro Linux
696 # Thomas Lockhart 1999-06-01
698 POSTGRESDIR= /opt/postgres/current
703 HSTYLE= /home/tgl/SGML/db118.d/docbook/html
704 PSTYLE= /home/tgl/SGML/db118.d/docbook/print
714 Note: Written by Oleg Bartunov. See Oleg's web page
715 (http://www.sai.msu.su/~megera/postgres/) for additional
716 information on locale and Russian language support.
718 While doing a project for a company in Moscow, Russia, I
719 encountered the problem that postgresql had no support of
720 national alphabets. After looking for possible workarounds I
721 decided to develop support of locale myself. I'm not a
722 C-programer but already had some experience with locale
723 programming when I work with perl (debugging) and glimpse.
724 After several days of digging through the Postgres source tree
725 I made very minor corections to src/backend/utils/adt/varlena.c
726 and src/backend/main/main.c and got what I needed! I did
727 support only for LC_CTYPE and LC_COLLATE, but later LC_MONETARY
728 was added by others. I got many messages from people about this
729 patch so I decided to send it to developers and (to my
730 surprise) it was incorporated into the Postgres distribution.
731 People often complain that locale doesn't work for them. There
732 are several common mistakes:
733 o Didn't properly configure postgresql before compilation. You
734 must run configure with --enable-locale option to enable
735 locale support. Didn't setup environment correctly when
736 starting postmaster. You must define environment variables
737 LC_CTYPE and LC_COLLATE before running postmaster because
738 backend gets information about locale from environment. I use
739 following shell script (runpostgres):
742 export LC_CTYPE=koi8-r
743 export LC_COLLATE=koi8-r
744 postmaster -B 1024 -S -D/usr/local/pgsql/data/ -o
747 and run it from rc.local as
748 /bin/su - postgres -c "/home/postgres/runpostgres"
751 o Broken locale support in OS (for example, locale support in
752 libc under Linux several times has changed and this caused a
753 lot of problems). Latest perl has also support of locale and
754 if locale is broken perl -v will complain something like:
755 8:17[mira]:~/WWW/postgres>setenv LC_CTYPE not_exist
756 8:18[mira]:~/WWW/postgres>perl -v
757 perl: warning: Setting locale failed.
758 perl: warning: Please check that your locale settings:
760 LC_CTYPE = "not_exist",
762 are supported and installed on your system.
763 perl: warning: Falling back to the standard locale
767 o Wrong location of locale files! Possible locations include:
768 /usr/lib/locale (Linux, Solaris), /usr/share/locale (Linux),
769 /usr/lib/nls/loc (DUX 4.0). Check man locale to find the
770 correct location. Under Linux I did a symbolic link between
771 /usr/lib/locale and /usr/share/locale to be sure that the
772 next libc will not break my locale.
775 What are the Benefits?
778 You can use ~* and order by operators for strings contain
779 characters from national alphabets. Non-english users
780 definitely need that. If you won't use locale stuff just
781 undefine the USE_LOCALE variable.
783 What are the Drawbacks?
786 There is one evident drawback of using locale - its speed! So,
787 use locale only if you really need it.
789 Kerberos Authentication
792 Kerberos is an industry-standard secure authentication system
793 suitable for distributed computing over a public network.
798 The Kerberos authentication system is not distributed with
799 Postgres. Versions of Kerberos are typically available as
800 optional software from operating system vendors. In addition, a
801 source code distribution may be obtained through MIT Project
802 Athena (ftp://athena-dist.mit.edu).
804 Note: You may wish to obtain the MIT version even if your
805 vendor provides a version, since some vendor ports have been
806 deliberately crippled or rendered non-interoperable with the
809 Users located outside the United States of America and Canada
810 are warned that distribution of the actual encryption code in
811 Kerberos is restricted by U. S. Government export regulations.
812 Inquiries regarding your Kerberos should be directed to your
813 vendor or MIT Project Athena (info-kerberos@athena.mit.edu).
814 Note that FAQLs (Frequently-Asked Questions Lists) are
815 periodically posted to the Kerberos mailing list
816 (mailto:kerberos@athena.mit.edu) (send mail to subscribe
817 (mailto:kerberos-request@athena.mit.edu)), and USENET news
818 group (news:comp.protocols.kerberos).
823 Installation of Kerberos itself is covered in detail in the
824 Kerberos Installation Notes . Make sure that the server key
825 file (the srvtab or keytab) is somehow readable by the Postgres
827 Postgres and its clients can be compiled to use either Version
828 4 or Version 5 of the MIT Kerberos protocols by setting the
829 KRBVERS variable in the file src/Makefile.global to the
830 appropriate value. You can also change the location where
831 Postgres expects to find the associated libraries, header files
832 and its own server key file.
833 After compilation is complete, Postgres must be registered as
834 a Kerberos service. See the Kerberos Operations Notes and
835 related manual pages for more details on registering services.
840 After initial installation, Postgres should operate in all
841 ways as a normal Kerberos service. For details on the use of
842 authentication, see the PostgreSQL User's Guide reference
843 sections for postmaster and psql.
844 In the Kerberos Version 5 hooks, the following assumptions are
845 made about user and service naming:
846 o User principal names (anames) are assumed to contain the
847 actual Unix/Postgres user name in the first component.
848 o The Postgres service is assumed to be have two components,
849 the service name and a hostname, canonicalized as in Version
850 4 (i.e., with all domain suffixes removed).
854 Table 4-1. Kerberos Parameter Examples
858 user aoki/HOST=miyu.S2K.Berkeley.EDU@S2K.-
860 host postgres_dbms/ucbvax@S2K.ORG
864 Support for Version 4 will disappear sometime after the
865 production release of Version 5 by MIT.
\f
867 Chapter 5. Release Notes
873 This release contains improvements in many areas,
874 demonstrating the continued growth of PostgreSQL. There are
875 more improvements and fixes in 7.0 than in any previous
876 release. The developers have confidence that this is the best
877 release yet; we do our best to put out only solid releases, and
878 this one is no exception.
879 Major changes in this release:
882 Foreign keys are now implemented, with the exception of
883 PARTIAL MATCH foreign keys. Many users have been asking for
884 this feature, and we are pleased to offer it.
887 Continuing on work started a year ago, the optimizer has
888 been improved, allowing better query plan selection and
889 faster performance with less memory usage.
892 psql, our interactive terminal monitor, has been updated
893 with a variety of new features. See the psql manual page for
897 SQL92 join syntax is now supported, though only as INNER
898 JOINs for this release. JOIN, NATURAL JOIN, JOIN/USING,
899 JOIN/ON are available, as are column correlation names.
905 A dump/restore using pg_dump is required for those wishing to
906 migrate data from any previous release of Postgres. For those
907 upgrading from 6.5.*, you may instead use pg_upgrade to upgrade
908 to this release; however, a full dump/reload installation is
909 always the most robust method for upgrades.
910 Interface and compatibility issues to consider for the new
912 o The date/time types datetime and timespan have been
913 superceded by the SQL92-defined types timestamp and interval.
914 Although there has been some effort to ease the transition by
915 allowing Postgres to recognize the deprecated type names and
916 translate them to the new type names, this mechanism may not
917 be completely transparent to your existing application.
918 o The optimizer has been substantially improved in the area of
919 query cost estimation. In some cases, this will result in
920 decreased query times as the optimizer makes a better choice
921 for the preferred plan. However, in a small number of cases,
922 usually involving pathological distributions of data, your
923 query times may go up. If you are dealing with large amounts
924 of data, you may want to check your queries to verify
926 o The JDBC and ODBC interfaces have been upgraded and
928 o The string function CHAR_LENGTH is now a native function.
929 Previous versions translated this into a call to LENGTH,
930 which could result in ambiguity with other types implementing
931 LENGTH such as the geometric types.
941 Prevent function calls exceeding maximum number of arguments (Tom)
942 Improve CASE construct (Tom)
943 Fix SELECT coalesce(f1,0) FROM int4_tbl GROUP BY f1 (Tom)
944 Fix SELECT sentence.words[0] FROM sentence GROUP BY
945 sentence.words[0] (Tom)
946 Fix GROUP BY scan bug (Tom)
947 Improvements in SQL grammar processing (Tom)
948 Fix for views involved in INSERT ... SELECT ... (Tom)
949 Fix for SELECT a/2, a/2 FROM missing_target GROUP BY a/2 (Tom)
950 Fix for subselects in INSERT ... SELECT (Tom)
951 Prevent INSERT ... SELECT ... ORDER BY (Tom)
952 Fixes for relations greater than 2GB, including vacuum
953 Improve propagating system table changes to other backends (Tom)
954 Improve propagating user table changes to other backends (Tom)
955 Fix handling of temp tables in complex situations (Bruce, Tom)
956 Allow table locking at table open, improving concurrent
958 Properly quote sequence names in pg_dump (Ross J. Reedstrom)
959 Prevent DROP DATABASE while others accessing
960 Prevent any rows from being returned by GROUP BY if no rows
962 Fix SELECT COUNT(1) FROM table WHERE ...' if no rows matching
964 Fix pg_upgrade so it works for MVCC (Tom)
965 Fix for SELECT ... WHERE x IN (SELECT ... HAVING SUM(x) > 1) (Tom)
966 Fix for "f1 datetime DEFAULT 'now'" (Tom)
967 Fix problems with CURRENT_DATE used in DEFAULT (Tom)
968 Allow comment-only lines, and ;;; lines too. (Tom)
969 Improve recovery after failed disk writes, disk full (Hiroshi)
970 Fix cases where table is mentioned in FROM but not joined (Tom)
971 Allow HAVING clause without aggregate functions (Tom)
972 Fix for "--" comment and no trailing newline, as seen in perl
973 Improve pg_dump failure error reports (Bruce)
974 Allow sorts and hashes to exceed 2GB file sizes (Tom)
975 Fix for pg_dump dumping of inherited rules (Tom)
976 Fix for NULL handling comparisons (Tom)
977 Fix inconsistent state caused by failed CREATE/DROP (Hiroshi)
978 Fix for dbname with dash
979 Prevent DROP INDEX from interfering with other backends (Tom)
980 Fix file descriptor leak in verify_password()
981 Fix for "Unable to identify an operator =$" problem
982 Fix ODBC so no segfault if CommLog and Debug enabled
984 Fix for recursive exit call (Massimo)
985 Fix for extra-long timezones (Jeroen van Vianen)
986 Make pg_dump preserve primary key information (Peter E)
987 Prevent databases with single quotes (Peter E)
988 Prevent DROP DATABASE inside transaction (Peter E)
989 ecpg memory leak fixes (Stephen Birch)
990 Fix for SELECT null::text, SELECT int4fac(null)
991 and SELECT 2 + (null) (Tom)
992 Y2K timestamp fix (Massimo)
993 Fix for VACUUM 'HEAP_MOVED_IN was not expected' errors (Tom)
994 Fix for views with tables/columns containing spaces (Tom)
995 Prevent permissions on indexes (Peter E)
996 Fix for spinlock stuck problem on error (Hiroshi)
997 Fix ipcclean on Linux
998 Fix handling of NULL constraint conditions (Tom)
999 Fix memory leak in odbc driver (Nick Gorham)
1000 Fix for permission check on UNION tables (Tom)
1001 Fix to allow SELECT 'a' LIKE 'a' (Tom)
1002 Fix for SELECT 1 + NULL (Tom)
1004 Fix log() on numeric type (Tom)
1005 Deprecate ':' and ';' operators
1006 Allow vacuum of temporary tables
1007 Disallow inherited columns with the same name as new columns
1008 Recover or force failure when disk space is exhausted(Hiroshi)
1009 Fix INSERT INTO ... SELECT with AS columns matching result columns
1010 Fix INSERT ... SELECT ... GROUP BY groups by target columns not
1012 Fix CREATE TABLE test (a char(5) DEFAULT text '', b int4)
1014 Fix UNION with LIMIT
1015 Fix CREATE TABLE x AS SELECT 1 UNION SELECT 2
1016 Fix CREATE TABLE test(col char(2) DEFAULT user)
1017 Fix mismatched types in CREATE TABLE ... DEFAULT
1018 Fix SELECT * FROM pg_class where oid in (0,-1)
1019 Fix SELECT COUNT('asdf') FROM pg_class WHERE oid=12
1020 Prevent user who can create databases can modifying pg_database
1022 Fix btree to give a useful elog when key > 1/2 (page - overhead)
1024 Fix INSERT of 0.0 into DECIMAL(4,4) field (Tom)
1028 New CLI interface include file sqlcli.h, based on SQL3/SQL98
1029 Remove all limits on query length, row length limit still
1031 Update jdbc protocol to 2.0 (Jens Glaser (jens@jens.de))
1032 Add TRUNCATE command to quickly truncate relation (Mike Mascari)
1033 Fix to give super user and createdb user proper update catalog
1035 Allow ecpg bool variables to have NULL values (Christof)
1036 Issue ecpg error if NULL value for variable with no NULL
1037 indicator (Christof)
1038 Allow ^C to cancel COPY command (Massimo)
1039 Add SET FSYNC and SHOW PG_OPTIONS commands (Massimo)
1040 Function name overloading for dynamically-loaded C functions
1042 Add CmdTuples() to libpq++(Vince)
1043 New CREATE CONSTRAINT TRIGGER and SET CONSTRAINTS commands (Jan)
1044 Allow CREATE FUNCTION/WITH clause to be used for all types
1045 configure --enable-debug adds -g (Peter E)
1046 configure --disable-debug removes -g (Peter E)
1047 Allow more complex default expressions (Tom)
1048 First real FOREIGN KEY constraint trigger functionality (Jan)
1049 Add FOREIGN KEY ... MATCH FULL ... ON DELETE CASCADE (Jan)
1050 Add FOREIGN KEY ... MATCH <unspecified> referential actions
1052 Allow WHERE restriction on ctid (physical heap location) (Hiroshi)
1053 Move pginterface from contrib to interface directory,
1054 rename to pgeasy (Bruce)
1055 Change pgeasy connectdb() parameter ordering (Bruce)
1056 Require SELECT DISTINCT target list to have all ORDER BY
1058 Add Oracle's COMMENT ON command (Mike Mascari (mascarim@yahoo))
1059 libpq's PQsetNoticeProcessor function now returns previous
1061 Prevent PQsetNoticeProcessor from being set to NULL (Peter E)
1062 Make USING in COPY optional (Bruce)
1063 Allow subselects in the target list (Tom)
1064 Allow subselects on the left side of comparison operators (Tom)
1065 New parallel regression test (Jan)
1066 Change backend-side COPY to write files with permissions 644
1068 Force permissions on PGDATA directory to be secure, even if it
1070 Added psql LASTOID variable to return last inserted oid (Peter E)
1071 Allow concurrent vacuum and remove pg_vlock vacuum lock file (Tom)
1072 Add permissions check for vacuum (Peter E)
1073 New libpq functions to allow asynchronous connections:
1074 PQconnectStart(), PQconnectPoll(), PQresetStart(), PQresetPoll(),
1075 PQsetenvStart(), PQsetenvPoll(), PQsetenvAbort (Ewan Mellor)
1076 New libpq PQsetenv() function (Ewan Mellor)
1077 create/alter user extension (Peter E)
1078 New postmaster.pid and postmaster.opts under $PGDATA (Tatsuo)
1079 New scripts for create/drop user/db (Peter E)
1080 Major psql overhaul (Peter E)
1081 Add const to libpq interface (Peter E)
1082 New libpq function PQoidValue (Peter E)
1083 Show specific non-aggregate causing problem with GROUP BY (Tom)
1084 Make changes to pg_shadow recreate pg_pwd file (Peter E)
1085 Add aggregate(DISTINCT ...) (Tom)
1086 Allow flag to control COPY input/output of NULLs (Peter E)
1087 Make postgres user have a password by default (Peter E)
1088 Add CREATE/ALTER/DROP GROUP (Peter E)
1089 All administration scripts now support --long options
1091 Vacuumdb script now supports --all option (Peter E)
1092 ecpg new portable FETCH syntax
1093 Add ecpg EXEC SQL IFDEF, EXEC SQL IFNDEF, EXEC SQL ELSE, EXEC
1094 SQL ELIF and EXEC SQL ENDIF directives
1095 Add pg_ctl script to control backend startup (Tatsuo)
1096 Add postmaster.opts.default file to store startup flags (Tatsuo)
1097 Allow --with-mb=SQL_ASCII
1098 Increase maximum number of index keys to 16 (Bruce)
1099 Increase maximum number of function arguments to 16 (Bruce)
1100 Allow configuration of maximum number of index keys and
1102 Allow unprivileged users to change their passwords (Peter E)
1103 Password authentication enabled; required for new users (Peter E)
1104 Disallow dropping a user who owns a database (Peter E)
1105 Change initdb option --with-mb to --enable-multibyte
1106 Add option for initdb to prompts for superuser password (Peter E)
1107 Allow complex type casts like col::numeric(9,2) and
1108 col::int2::float8 (Tom)
1109 Updated user interfaces on initdb, initlocation, pg_dump,
1111 New pg_char_to_encoding() and pg_encoding_to_char() (Tatsuo)
1112 New libpq functions PQsetClientEncoding(), PQclientEncoding()
1114 Libpq non-blocking mode (Alfred Perlstein)
1115 Improve conversion of types in casts that don't specify a length
1116 New plperl internal programming language (Mark Hollomon)
1117 Allow COPY IN to read file that do not end with a newline (Tom)
1118 Indicate when long identifiers are truncated (Tom)
1119 Allow aggregates to use type equivalency (Peter E)
1120 Add Oracle's to_char(), to_date(), to_datetime(), to_timestamp(),
1121 to_number() conversion functions (Karel Zak <zakkr@zf.jcu.cz>)
1122 Add SELECT DISTINCT ON (expr [, expr ...]) targetlist ... (Tom)
1123 Check to be sure ORDER BY is compatible with DISTINCT (Tom)
1124 Add NUMERIC and int8 types to ODBC
1125 Improve EXPLAIN results for Append, Group, Agg, Unique (Tom)
1126 Add ALTER TABLE ... ADD FOREIGN KEY (Stephan Szabo)
1127 Allow SELECT .. FOR UPDATE in PL/pgSQL (Hiroshi)
1128 Enable backward sequential scan even after reaching EOF (Hiroshi)
1129 Add btree indexing of boolean values, >= and <= (Don Baccus)
1130 Print current line number when COPY FROM fails (Massimo)
1131 Recognize POSIX time zone e.g. "PST+8" and "GMT-8" (Thomas)
1132 Add DEC as synonym for DECIMAL (Thomas)
1133 Add SESSION_USER as SQL92 keyword (== CURRENT_USER) (Thomas)
1134 Implement SQL92 column aliases (aka correlation names) (Thomas)
1135 Implement SQL92 join syntax (Thomas)
1136 INTERVAL reserved word allowed as a column identifier (Thomas)
1137 Implement REINDEX command (Hiroshi)
1138 Accept ALL in aggregate function SUM(ALL col) (Tom)
1139 Prevent GROUP BY from using column aliases (Tom)
1140 New psql \encoding option (Tatsuo)
1141 Allow PQrequestCancel() to terminate when in waiting-for-lock
1143 Allow negation of a negative number in all cases
1144 Add ecpg descriptors (Christof, Michael)
1145 Allow CREATE VIEW v AS SELECT f1::char(8) FROM tbl
1146 Allow casts with length, like foo::char(8)
1147 Add support for SJIS user defined characters (Tatsuo)
1148 Larger views/rules supported
1149 Make libpq's PQconndefaults() thread-safe (Tom)
1150 Disable // as comment to be ANSI conforming, should use -- (Tom)
1151 Allow column aliases on views CREATE VIEW name (collist)
1152 Fixes for views with subqueries (Tom)
1153 Allow UPDATE table SET fld = (SELECT ...) (Tom)
1154 SET command options no longer require quotes
1155 Update pgaccess to 0.98.6
1156 New SET SEED command
1157 New pg_options.sample file
1158 New SET FSYNC command (Massimo)
1159 Allow pg_descriptions when creating tables
1160 Allow pg_descriptions when creating types, columns, and functions
1161 Allow psql \copy to allow delimiters(Peter E)
1162 Allow psql to print nulls as distinct from "" [null](Peter E)
1166 Many array fixes (Tom)
1167 Allow bare column names to be subscripted as arrays (Tom)
1168 Improve type casting of int and float constants (Tom)
1169 Cleanups for int8 inputs, range checking, and type conversion (Tom)
1170 Fix for SELECT timespan('21:11:26'::time) (Tom)
1171 netmask('x.x.x.x/0') is 255.255.255.255 instead of 0.0.0.0
1173 Add btree index on NUMERIC (Jan)
1174 Perl fix for large objects containing NUL characters
1176 ODBC fix for for large objects (free)
1177 Fix indexing of cidr data type
1178 Fix for Ethernet MAC addresses (macaddr type) comparisons
1179 Fix for date/time types when overflows happen (Tom)
1180 Allow array on int8 (Peter E)
1181 Fix for rounding/overflow of NUMERIC type, like NUMERIC(4,4) (Tom)
1182 Allow NUMERIC arrays
1183 Fix bugs in NUMERIC ceil() and floor() functions (Tom)
1184 Make char_length()/octet_length including trailing blanks (Tom)
1185 Made abstime/reltime use int4 instead of time_t (Peter E)
1186 New lztext data type for compressed text fields
1187 Revise code to handle coercion of int and float constants (Tom)
1188 Start at new code to implement a BIT and BIT VARYING type
1190 NUMERIC now accepts scientific notation (Tom)
1191 NUMERIC to int4 rounds (Tom)
1192 Convert float4/8 to NUMERIC properly (Tom)
1193 Allow type conversion with NUMERIC (Thomas)
1194 Make ISO date style (2000-02-16 09:33) the default (Thomas)
1195 Add NATIONAL CHAR [ VARYING ] (Thomas)
1196 Allow NUMERIC round and trunc to accept negative scales (Tom)
1197 New TIME WITH TIME ZONE type (Thomas)
1198 Add MAX()/MIN() on time type (Thomas)
1199 Add abs(), mod(), fac() for int8 (Thomas)
1200 Rename functions to round(), sqrt(), cbrt(), pow() for float8
1202 Add transcendental math functions for float8 (Thomas)
1203 Add exp() and ln() for NUMERIC type (Jan)
1204 Rename NUMERIC power() to pow() (Thomas)
1205 Improved TRANSLATE() function (Edwin Ramirez, Tom)
1206 Allow X=-Y operators (Tom)
1207 Allow SELECT float8(COUNT(*))/(SELECT COUNT(*) FROM t)
1208 FROM t GROUP BY f1; (Tom)
1209 Allow LOCALE to use indexes in regular expression searches (Tom)
1210 Allow creation of functional indexes to use default types
1214 Prevent exponential space usage with many AND's and OR's (Tom)
1215 Collect attribute selectivity values for system columns (Tom)
1216 Reduce memory usage of aggregates (Tom)
1217 Fix for LIKE optimization to use indexes with multi-byte
1219 Fix r-tree index optimizer selectivity (Thomas)
1220 Improve optimizer selectivity computations and functions (Tom)
1221 Optimize btree searching when many equal keys exist (Tom)
1222 Enable fast LIKE index processing only if index present (Tom)
1223 Re-use free space on index pages with duplicates (Tom)
1224 Improve hash join processing (Tom)
1225 Prevent descending sort if result is already sorted(Hiroshi)
1226 Allow commuting of index scan query qualifications (Tom)
1227 Prefer index scans when ORDER BY/GROUP BY is required (Tom)
1228 Allocate large memory requests in fix-sized chunks for
1230 Fix vacuum's performance reducing memory requests (Tom)
1231 Implement constant-expression simplification
1232 (Bernard Frankpitt, Tom)
1233 Use secondary columns to be used to determine start of index
1235 Prevent quadruple use of disk space when sorting (Tom)
1236 Faster sorting by calling fewer functions (Tom)
1237 Create system indexes to match all system caches
1239 Make system caches use system indexes (Bruce)
1240 Make all system indexes unique (Bruce)
1241 Improve pg_statistics management to help VACUUM speed (Tom)
1242 Flush backend cache less frequently (Tom, Hiroshi)
1243 COPY now reuses previous memory allocation, improving
1245 Improve optimization cost estimation (Tom)
1246 Improve optimizer estimate of range queries
1247 (x > lowbound AND x < highbound) (Tom)
1248 Use DNF instead of CNF where appropriate (Tom, Taral)
1249 Further cleanup for OR-of-AND WHERE-clauses (Tom)
1250 Make use of index in OR clauses
1251 (x = 1 AND y = 2) OR (x = 2 AND y = 4) (Tom)
1252 Smarter optimizer for random index page access (Tom)
1253 New SET variable to control optimizer costs (Tom)
1254 Optimizer queries based on LIMIT, OFFSET, and EXISTS
1255 qualifications (Tom)
1256 Reduce optimizer internal housekeeping of join paths for
1258 Major subquery speedup (Tom)
1259 Fewer fsync writes when fsync is not disabled (Tom)
1260 Improved LIKE optimizer estimates (Tom)
1261 Prevent fsync in SELECT-only queries (Vadim)
1262 Index creation uses psort, since psort now faster (Tom)
1263 Allow creation of sort temp tables > 1 Gig
1267 Fix for linux PPC compile
1268 New generic expression-tree-walker subroutine (Tom)
1269 Change form() to varargform() to prevent portability problems
1270 Improved range checking for large integers on Alphas
1271 Clean up #include in /include directory (Bruce)
1272 Add scripts for checking includes (Bruce)
1273 Remove un-needed #include's from *.c files (Bruce)
1274 Change #include's to use <> and "" as appropriate (Bruce)
1275 Enable WIN32 compilation of libpq
1276 Alpha spinlock fix from Uncle George (gatgul@voicenet.com)
1277 Overhaul of optimizer data structures (Tom)
1278 Fix to cygipc library (Yutaka Tanida)
1279 Allow pgsql to work on newer Cygwin snapshots (Dan)
1280 New catalog version number (Tom)
1282 Rename heap_replace to heap_update
1283 Update for QNX (Dr. Andreas Kardos)
1284 New platform-specific regression handling (Tom)
1285 Rename oid8 -> oidvector and int28 -> int2vector (Bruce)
1286 Included all yacc and lex files into the distribution (Peter E.)
1287 Remove lextest, no longer needed (Peter E)
1288 Fix for libpq and psql on Win32 (Magnus)
1289 Change datetime and timespan into timestamp and interval (Thomas)
1290 Fix for plpgsql on BSDI
1291 Add SQL_ASCII test case to the regression test (Tatsuo)
1292 configure --with-mb now deprecated (Tatsuo)
1294 NetBSD fixes Johnny C. Lam (lamj@stat.cmu.edu)
1295 Fixes for Alpha compiles
1296 New multibyte encodings
\f
1298 Chapter 6. Regression Test
1301 Regression test instructions and analysis.
1303 The PostgreSQL regression tests are a comprehensive set of
1304 tests for the SQL implementation embedded in PostgreSQL. They
1305 test standard SQL operations as well as the extended
1306 capabilities of PostgreSQL.
1307 There are two different ways in which the regression tests can
1308 be run: the "sequential" method and the "parallel" method. The
1309 sequential method runs each test script in turn, whereas the
1310 parallel method starts up multiple server processes to run
1311 groups of tests in parallel. Parallel testing gives confidence
1312 that interprocess communication and locking are working
1313 correctly. Another key difference is that the sequential test
1314 procedure uses an already-installed postmaster, whereas the
1315 parallel test procedure tests a system that has been built but
1316 not yet installed. (The parallel test script actually does an
1317 installation into a temporary directory and fires up a private
1318 postmaster therein.)
1319 Some properly installed and fully functional PostgreSQL
1320 installations can "fail" some of these regression tests due to
1321 artifacts of floating point representation and time zone
1322 support. The tests are currently evaluated using a simple diff
1323 comparison against the outputs generated on a reference system,
1324 so the results are sensitive to small system differences. When
1325 a test is reported as "failed", always examine the differences
1326 between expected and actual results; you may well find that the
1327 differences are not significant.
1328 The regression tests were originally developed by Jolly Chen
1329 and Andrew Yu, and were extensively revised/repackaged by Marc
1330 Fournier and Thomas Lockhart. From PostgreSQL v6.1 onward the
1331 regression tests are current for every official release.
1333 Regression Environment
1336 The regression testing notes below assume the following
1337 (except where noted):
1338 o Commands are Unix-compatible. See note below.
1339 o Defaults are used except where noted.
1340 o User postgres is the Postgres superuser.
1341 o The source path is /usr/src/pgsql (other paths are
1343 o The runtime path is /usr/local/pgsql (other paths are
1346 Normally, the regression tests should be run as the postgres
1347 user since the 'src/test/regress' directory and sub-directories
1348 are owned by the postgres user. If you run the regression test
1349 as another user the 'src/test/regress' directory tree must be
1350 writeable by that user.
1351 It was formerly necessary to run the postmaster with system
1352 time zone set to PST, but this is no longer required. You can
1353 run the regression tests under your normal postmaster
1354 configuration. The test script will set the PGTZ environment
1355 variable to ensure that timezone-dependent tests produce the
1356 expected results. However, your system must provide library
1357 support for the PST8PDT time zone, or the timezone-dependent
1358 tests will fail. To verify that your machine does have this
1359 support, type the following:
1366 The "date" command above should have returned the current
1367 system time in the PST8PDT time zone. If the PST8PDT database
1368 is not available, then your system may have returned the time
1369 in GMT. If the PST8PDT time zone is not available, you can set
1370 the time zone rules explicitly:
1372 setenv PGTZ PST8PDT7,M04.01.0,M10.05.03
1376 The directory layout for the regression test area is:
1378 Table 6-1. Directory Layout
1379 Directory Description
1380 input Source files that are converted using make all
1381 into some of the .sql files in the sql
1383 output Source files that are converted using make all
1384 into .out files in the expected subdirectory.
1385 sql .sql files used to perform the regression tests.
1386 expected .out files that represent what we expect the
1387 results to look like.
1388 results .out files that contain what the results actually
1389 look like. Also used as temporary storage for table
1391 tmp_check Temporary installation created by parallel testing
1397 Regression Test Procedure
1400 Commands were tested on RedHat Linux version 4.2 using the
1401 bash shell. Except where noted, they will probably work on most
1402 systems. Commands like ps and tar vary wildly on what options
1403 you should use on each platform. Use common sense before typing
1406 Postgres Regression Test
1407 1. Prepare the files needed for the regression test with:
1408 cd /usr/src/pgsql/src/test/regress
1412 You can skip "gmake clean" if this is the first time you
1413 are running the tests.
1414 This step compiles a C program with PostgreSQL extension
1415 functions into a shared library. Localized SQL scripts and
1416 output-comparison files are also created for the tests that
1417 need them. The localization replaces macros in the source
1418 files with absolute pathnames and user names.
1419 2. If you intend to use the "sequential" test procedure, which
1420 tests an already-installed postmaster, be sure that the
1421 postmaster is running. If it isn't already running, start
1422 the postmaster in an available window by typing
1425 or start the postmaster daemon running in the background by
1428 nohup postmaster > regress.log 2>&1 &
1430 The latter is probably preferable, since the regression
1431 test log will be quite lengthy (60K or so, in Postgres 7.0)
1432 and you might want to review it for clues if things go
1435 Note: Do not run postmaster from the root account.
1438 3. Run the regression tests. For a sequential test, type
1439 cd /usr/src/pgsql/src/test/regress
1442 For a parallel test, type
1443 cd /usr/src/pgsql/src/test/regress
1446 The sequential test just runs the test scripts using your
1447 already-running postmaster. The parallel test will perform a
1448 complete installation of Postgres into a temporary
1449 directory, start a private postmaster therein, and then run
1450 the test scripts. Finally it will kill the private
1451 postmaster (but the temporary directory isn't removed
1453 4. You should get on the screen (and also written to file
1454 ./regress.out) a series of statements stating which tests
1455 passed and which tests failed. Please note that it can be
1456 normal for some of the tests to "fail" due to
1457 platform-specific variations. See the next section for
1458 details on determining whether a "failure" is significant.
1459 Some of the tests, notably "numeric", can take a while,
1460 especially on slower platforms. Have patience.
1461 5. After running the tests and examining the results, type
1462 cd /usr/src/pgsql/src/test/regress
1465 to recover the temporary disk space used by the tests. If
1466 you ran a sequential test, also type
1474 The actual outputs of the regression tests are in files in the
1475 ./results directory. The test script uses diff to compare each
1476 output file against the reference outputs stored in the
1477 ./expected directory. Any differences are saved for your
1478 inspection in ./regression.diffs. (Or you can run diff
1479 yourself, if you prefer.)
1480 The files might not compare exactly. The test script will
1481 report any difference as a "failure", but the difference might
1482 be due to small cross-system differences in error message
1483 wording, math library behavior, etc. "Failures" of this type do
1484 not indicate a problem with Postgres.
1485 Thus, it is necessary to examine the actual differences for
1486 each "failed" test to determine whether there is really a
1487 problem. The following paragraphs attempt to provide some
1488 guidance in determining whether a difference is significant or
1491 Error message differences
1494 Some of the regression tests involve intentional invalid input
1495 values. Error messages can come from either the Postgres code
1496 or from the host platform system routines. In the latter case,
1497 the messages may vary between platforms, but should reflect
1498 similar information. These differences in messages will result
1499 in a "failed" regression test which can be validated by
1502 Date and time differences
1505 Most of the date and time results are dependent on timezone
1506 environment. The reference files are generated for timezone
1507 PST8PDT (Berkeley, California) and there will be apparent
1508 failures if the tests are not run with that timezone setting.
1509 The regression test driver sets environment variable PGTZ to
1510 PST8PDT to ensure proper results.
1511 Some of the queries in the "timestamp" test will fail if you
1512 run the test on the day of a daylight-savings time changeover,
1513 or the day before or after one. These queries assume that the
1514 intervals between midnight yesterday, midnight today and
1515 midnight tomorrow are exactly twenty-four hours ... which is
1516 wrong if daylight-savings time went into or out of effect
1518 There appear to be some systems which do not accept the
1519 recommended syntax for explicitly setting the local time zone
1520 rules; you may need to use a different PGTZ setting on such
1522 Some systems using older timezone libraries fail to apply
1523 daylight-savings corrections to pre-1970 dates, causing
1524 pre-1970 PDT times to be displayed in PST instead. This will
1525 result in localized differences in the test results.
1527 Floating point differences
1530 Some of the tests involve computing 64-bit (float8) numbers
1531 from table columns. Differences in results involving
1532 mathematical functions of float8 columns have been observed.
1533 The float8 and geometry tests are particularly prone to small
1534 differences across platforms. Human eyeball comparison is
1535 needed to determine the real significance of these differences
1536 which are usually 10 places to the right of the decimal point.
1537 Some systems signal errors from pow() and exp() differently
1538 from the mechanism expected by the current Postgres code.
1543 Several of the tests involve operations on geographic date
1544 about the Oakland/Berkley CA street map. The map data is
1545 expressed as polygons whose vertices are represented as pairs
1546 of float8 numbers (decimal latitude and longitude). Initially,
1547 some tables are created and loaded with geographic data, then
1548 some views are created which join two tables using the polygon
1549 intersection operator (##), then a select is done on the view.
1550 When comparing the results from different platforms,
1551 differences occur in the 2nd or 3rd place to the right of the
1552 decimal point. The SQL statements where these problems occur
1555 QUERY: SELECT * from street;
1556 QUERY: SELECT * from iexit;
1564 There is at least one case in the "random" test script that is
1565 intended to produce random results. This causes random to fail
1566 the regression test once in a while (perhaps once in every five
1567 to ten trials). Typing
1569 diff results/random.out expected/random.out
1572 should produce only one or a few lines of differences. You
1573 need not worry unless the random test always fails in repeated
1574 attempts. (On the other hand, if the random test is never
1575 reported to fail even in many trials of the regress tests, you
1576 probably should worry.)
1578 The "expected" files
1581 The ./expected/*.out files were adapted from the original
1582 monolithic expected.input file provided by Jolly Chen et al.
1583 Newer versions of these files generated on various development
1584 machines have been substituted after careful (?) inspection.
1585 Many of the development machines are running a Unix OS variant
1586 (FreeBSD, Linux, etc) on Ix86 hardware. The original
1587 expected.input file was created on a SPARC Solaris 2.4 system
1588 using the postgres5-1.02a5.tar.gz source tree. It was compared
1589 with a file created on an I386 Solaris 2.4 system and the
1590 differences were only in the floating point polygons in the 3rd
1591 digit to the right of the decimal point. The original
1592 sample.regress.out file was from the postgres-1.01 release
1593 constructed by Jolly Chen. It may have been created on a DEC
1594 ALPHA machine as the Makefile.global in the postgres-1.01
1595 release has PORTNAME=alpha.
1597 Platform-specific comparison files
1600 Since some of the tests inherently produce platform-specific
1601 results, we have provided a way to supply platform-specific
1602 result comparison files. Frequently, the same variation applies
1603 to multiple platforms; rather than supplying a separate
1604 comparison file for every platform, there is a mapping file
1605 that defines which comparison file to use. So, to eliminate
1606 bogus test "failures" for a particular platform, you must
1607 choose or make a variant result file, and then add a line to
1608 the mapping file, which is "resultmap".
1609 Each line in the mapping file is of the form
1611 testname/platformnamepattern=comparisonfilename
1614 The test name is just the name of the particular regression
1615 test module. The platform name pattern is a pattern in the
1616 style of expr(1) (that is, a regular expression with an
1617 implicit ^ anchor at the start). It is matched against the
1618 platform name as printed by config.guess. The comparison file
1619 name is the name of the substitute result comparison file.
1620 For example: the int2 regress test includes a deliberate entry
1621 of a value that is too large to fit in int2. The specific error
1622 message that is produced is platform-dependent; our reference
1625 ERROR: pg_atoi: error reading "100000": Numerical result
1629 but a fair number of other Unix platforms emit
1631 ERROR: pg_atoi: error reading "100000": Result too large
1634 Therefore, we provide a variant comparison file,
1635 int2-too-large.out, that includes this spelling of the error
1636 message. To silence the bogus "failure" message on HPPA
1637 platforms, resultmap includes
1639 int2/hppa=int2-too-large
1642 which will trigger on any machine for which config.guess's
1643 output begins with 'hppa'. Other lines in resultmap select the
1644 variant comparison file for other platforms where it's
projects / postgresql / blob