PostgreSQL is included on the CD distributed with this book, but you may want to visit the PostgreSQL website to see if there is a newer version available. Many FTP sites make the source files for PostgreSQL available for download; a complete list of FTP mirrors can be found at http://www.postgresql.org/.
Once you have connected to a PostgreSQL FTP mirror, you will see the stable releases located within a directory beginning with v followed by a version (such as v7.1.3/). There should also be a symbolic link to the most recent stable release's directory called latest/.
Within this sub-directory is a list of package files. The complete PostgreSQL installation package is named postgresql-[version].tar.gz and should be the largest file in the list. The following sub-packages are also made available for download, and may be installed in any combination (though at least base is required):
The base package contains the bare minimum of source code required to build and run PostgreSQL.
The docs package contains the PostgreSQL documentation in HTML format. Note that the PostgreSQL man pages are automatically installed with the base package.
The opt package contains several optional extensions to PostgreSQL, such as the interfaces for C++ (libpq++), JDBC, ODBC, Perl, Python, and Tcl. It also contains the source required for multi-byte support.
The test package contains the regression test suite. This package is required to run regression tests after compiling PostgreSQL.
Before you start compiling source, first create a UNIX user account to own and the PostgreSQL database files. Typically, this user is named postgres, but it can be named anything that you choose. For consistency throughout the book, the user postgres is considered the PostgreSQL root or superuser.
You will need to have root access to create the PostgreSQL superuser. On a Linux machine, you can use the command shown in Example 2-5 to add the postgres user.
Do not try to use the root user as the PostgreSQL superuser. Doing so presents a large security issue.
Once you have acquired the source for PostgreSQL, you should copy the PostgreSQL source package to a temporary compilation directory. This directory is the location within which you will install and configure PostgreSQL. Here, you will extract the contents from the tar.gz file, and proceed with installation.
Bear in mind that this will not be the location of the installed database files. This is just a temporary location for configuration and compilation of the source package itself. If you have downloaded the PostgreSQL package from the Internet, it is probably not saved in your intended compilation directory (unless you explicitly chose to save there).
A common convention for building source on UNIX and Linux machines is to build within the /usr/local/src path. You will most likely need root privileges to access this path. As such, the remaining examples in this chapter will involve the root user until otherwise specified.
If you are a user of a commercial Linux distribution, we strongly suggest that you verify whether or not you have PostgreSQL already installed. On RPM-based systems, such as SuSe, Mandrake, or RedHat, this can be done by using the following command: rpm -qa | grep -i postgres. If you do have PostgreSQL installed, there is a good chance that it is outdated. You will want to download and install the latest version of PostgreSQL available. An RPM installation of PostgreSQL will sometimes install scripts and programs such as postmaster and psql into globally accessible directories. This can cause conflicts with source-built versions, so before installing a new version, be sure to remove the RPM by using the rpm -e <package name> command.
To unpack the PostgreSQL source code on a Linux system, first move (or copy, from the CD) the compressed source file into /usr/local/src; most people move their source files here to keep them separate from their home directories, or other locations they may keep downloaded files.
After copying the source there (or to an alternate path where you prefer to unpack it), use tar to unpack the source files. The commands to perform these actions are shown in Example 2-6.
Example 2-6. Unpacking the PostgreSQL Source Package
[root@host root]# mv postgresql-7.1.3.tar.gz /usr/local/src [root@host root]# cd /usr/local/src [root@host src]# tar -xzvf postgresql-7.1.3.tar.gz postgresql-7.1.3/ postgresql-7.1.3/ChangeLogs/ postgresql-7.1.3/ChangeLogs/ChangeLog-7.1-7.1.1 postgresql-7.1.3/ChangeLogs/ChangeLog-7.1RC1-to-7.1RC2 postgresql-7.1.3/ChangeLogs/ChangeLog-7.1RC2-to-7.1RC3 postgresql-7.1.3/ChangeLogs/ChangeLog-7.1RC3-to-7.1rc4 postgresql-7.1.3/ChangeLogs/ChangeLog-7.1beta1-to-7.1beta3 postgresql-7.1.3/ChangeLogs/ChangeLog-7.1beta3-to-7.1beta4 postgresql-7.1.3/ChangeLogs/ChangeLog-7.1beta4-to-7.1beta5 postgresql-7.1.3/ChangeLogs/ChangeLog-7.1beta5-to-7.1beta6 postgresql-7.1.3/ChangeLogs/ChangeLog-7.1beta6-7.1RC1 postgresql-7.1.3/ChangeLogs/ChangeLog-7.1rc4-7.1 postgresql-7.1.3/ChangeLogs/ChangeLog-7.1.1-7.1.2 postgresql-7.1.3/ChangeLogs/ChangeLog-7.1.2-7.1.3 postgresql-7.1.3/COPYRIGHT [...] [root@host root]# chown -R postgres.postgres postgresql-7.1.3
Notice the last command used in Example 2-6. The command is chown -R postgres.postgres postgresql-7.1.3. This command grants the ownership of the PostgreSQL source directory tree to postgres, which in turn enables you to compile PostgreSQL as the postgres user. Once the extraction and ownership change has completed, you can switch to the postgres user to compile PostgreSQL, resulting in all compiled files automatically being owned by postgres.
For reference purposes, the following list is a description of the tar options used to extract the PostgreSQL source distribution:
tar will extract from the passed filename (as opposed to creating a new file).
tar will print verbose output as files are extracted. You may omit this flag if you do not wish to see each file as it is unpacked.
tar will use gunzip to decompress the source. This option assumes that you are using the GNU tools; other versions of tar may not support the z flag. In the event that you are not using the GNU tools, you will need to manually unzip the file using gunzip before you can unpack it with tar.
tar will use the filename following the f parameter to determine which file to extract. In our examples, this file is postgresql-7.1.3.tar.gz.
After you have completed the extraction of the files, change into the newly created directory (e.g., /usr/local/src/postgres-7.1.3). The remaining installation steps will take place in that directory.
Before compilation, you must configure the source, and specify installation options specific to your needs. This is done with the configure script.
The configure script is also used to check for software dependencies that are required to compile PostgreSQL. As configure checks for dependencies, it will create the necessary files for use with the gmake command.
To use the default installation script, issue the following command: ./configure. To specify options that will enable certain non-default features, append the option to the ./configure command. For a list of all the available configuration options, use ./configure --help
There is a good chance that the default source configuration that configure uses will not be the setup you require. For a well-rounded PostgreSQL installation, we recommend you use at least the following options:
Allows you to build C++ programs for use with PostgreSQL by building the libpq++ library
Allows you to connect to PostgreSQL with programs that have a compatible ODBC driver (such as Microsoft Access)
Allows multi-byte characters to be used, such as non-English language characters (e.g., Kanji)
Sets [number] as the maximum number of allowed connections (32, by default)
You can also specify other options from the list in Table 2-1.
Table 2-1. Configuration options
Specifies that files should be installed under the directory provided with PREFIX, instead of the default installation directory (/usr/local/pgsql).
Specifies that architecture-dependent executable files should be installed under the directory supplied with EXEC-PREFIX.
Specifies that user executable files (such as psql) should be installed into the directory supplied with DIRECTORY.
Specifies that the database should install data files used by PostgreSQL's program suite (as well as sample configuration files) into the directory supplied with DIRECTORY. Note that the directory here is not used as an alternate database data directory; it is merely the directory where read-only files used by the program suite are installed.
Specifies that system configuration files should be installed into the directory supplied with DIRECTORY. By default, these are put into the etc folder within the specified base installation directory.
Specifies that library files should be stored in the directory supplied with DIRECTORY. If you are running Linux, this directory should also be entered into the ld.so.conf file.
Specifies that C and C++ header files should be installed into the directory supplied with DIRECTORY. By default, include files are stored in the include folder within the base installation directory.
Specifies that documentation files should be installed into the directory supplied with DIRECTORY. This does not include PostgreSQL's man files.
Specifies that man files should be installed into the directory supplied with DIRECTORY.
Specifies that the colon-separated list of directories supplied with DIRECTORIES should be searched with the purpose of locating additional header files.
Specifies that the colon-separated list of directories supplied with DIRECTORIES should be searched with the purpose of locating additional libraries.
Enables locale support. The use of locale support will incur a performance penalty and should only be enabled if you are are not in an English-speaking location.
Enables the use of the recode translation library.
Enables multibyte encoding. Enabling this option allows the support of non-ASCII characters; this is most useful with languages such as Japanese, Korean, and Chinese, which all use nonstandard character encoding.
Specifies that the the port number supplied with NUMBER should be used as the default port by PostgreSQL. This can be changed when starting the postmaster application.
Specifies that the C++ interface library should be compiled during installation. You will need this library if you plan to develop C++ applications for use with PostgreSQL.
Specifies that the PostgreSQL Perl interface module should be compiled during installation. This module will need to be installed in a directory that is usually owned by root, so you will most likely need to be logged in as the root user to complete installation with this option chosen. This configuration option is only required if you plan to use the pl/Perl procedural language.
Specifies that the PostgreSQL Python interface module should be compiled during installation. As with the --with-perl option, you will most likely need to log in as the root user to complete installation with this option. This option is only required if you plan to use the pl/Python procedural language.
Specifies that Tcl support should be included in the installation. This option will install PostgreSQL applications and extensions that require Tcl, such as pgaccess (a popular graphical database client) and the pl/Tcl procedural language.
Specifies that Tcl support should be compiled without additional support for Tk, the graphical application tool kit. Using this option with the --with-tcl option specifies that PostgreSQL Tcl applications that require Tk (such as pgtksh and pgaccess) should not be installed.
--with-tclconfig= DIRECTORY, --with-tkconfig= DIRECTORY
Specifies that the Tcl or Tk (depending on the option) configuration file (either tclConfig.sh or tkConfig.sh) is located in the directory supplied with DIRECTORY, instead of the default directory. These two files are installed by Tcl/Tk, and the information within them is required by PostgreSQL's Tcl/Tk interface modules.
Enables support for ODBC.
Specifies that the ODBC driver should look in the directory supplied with DIRECTORY for its odbcinst.ini file. By default, this file is held in the etc directory, which is located in the installation directory.
--with-krb4= DIRECTORY, --with-krb5= DIRECTORY
Enables support for the Kerberos authentication system. The use of Kerberos is not covered in this book.
Specifies the name of the Kerberos service principal. By default, postgres is set as the service principal name.
Enables the use of SSL to support encrypted database connections. To build support for SSL, OpenSSL must be configured correctly and installed in the directory supplied with DIRECTORY. This option is required if you plan on using the stunnel tool.
Enables Java/JDBC support. The Ant and JDK packages are required for PostgreSQL to compile correctly with this feature enabled.
Enables the use of the syslog daemon for logging. You will need to specify that you wish to use syslog for logging at runtime if you wish to use it.
Enables the compilation of all PostgreSQL libraries and applications with debugging symbols. This will slow down performance and increase binary file size, but the debugging symbols are useful to help diagnose bugs and problems that can be encountered with PostgreSQL.
Enables assertion checking. This feature slows down performance and should be used only during development of PostgreSQL database itself.
If you compile PostgreSQL and find that you are missing a feature, you can return to this step, reconfigure, and continue with the subsequent steps to build and install PostgreSQL. If you choose to come back to this step and reconfigure the PostgreSQL source before installing, be sure to use the gmake clean command from the top-level directory of the source tree (e.g., /usr/local/src/postgresql-[version] ). This will remove any leftover object files, and partially compiled files.
After using the configure command, begin compiling the PostgreSQL source by entering the gmake command.
Remember that on Linux machines, you should be able to use make instead of gmake. BSD users should use gnumake.
Example 2-7. Compiling the Source with GNU make
[postgres@host postgresql-7.1.3]# gmake gmake -C doc all gmake: Entering directory /usr/local/src/postgresql-7.1.3/doc' gmake: Nothing to be done for all'. gmake: Leaving directory /usr/local/src/postgresql-7.1.3/doc' gmake -C src all gmake: Entering directory /usr/local/src/postgresql-7.1.3/src' gmake -C backend all gmake: Entering directory /usr/local/src/postgresql-7.1.3/src/backend' gmake -C utils fmgroids.h gmake: Entering directory /usr/local/src/postgresql-7.1.3/src/backend/utils' [...]
At this point you may want to get some coffee. Depending on the speed of your machine, the PostgreSQL compilation could take 10 minutes, an hour, or even more. After the compilation has finished, the following message should appear:
All of PostgreSQL is successfully made. Ready to install.
Running the regression tests is an optional, but recommended, step. The regression tests help verify that PostgreSQL will run as expected after you have compiled the source. The tests check tasks such as standard SQL operations, as well as extended capabilities of PostgreSQL. The regression tests can point out possible (but not necessarily probable) problems which may arise when running PostgreSQL.
If you decide you would like to run the regression tests, do so by using the gmake check command, as shown in Example 2-8.
Example 2-8. Making Regression Tests
[postgres@host postgresql-7.1.3]# gmake check gmake -C doc all gmake: Entering directory /usr/local/src/postgresql-7.1.3/doc' gmake: Nothing to be done for all'. gmake: Leaving directory /usr/local/src/postgresql-7.1.3/doc' [...]
The gmake check command will build a test installation of PostgreSQL within the source tree, and display a list of all the checks it is running. As each test completes, the success or failure will be reported. Items that fail the check will have a failed message printed, rather than the successful ok message. If any checks fail, gmake check will display output similar to that found in Example 2-9, though the number of tests failed may be higher on your system than the number in the example.
Example 2-9. Regression Check Output
======================= 1 of 76 tests failed. ======================= The differences that caused some tests to fail can be viewed in the file ./regression.diffs'. A copy of the test summary that you see above is saved in the file ./regression.out'.
The files referenced in Example 2-9 (regression.diffs and regression.out) are placed within the source tree at src/test/regress. If the source tree is located in /usr/local/src, the full path to the directory files would be /usr/local/src/postgresql-[version]/src/test/regress.
The regression tests will not always pick up every possible error. For example, this can be due to inconsistencies in locale settings (such as time zone support), or hardware-specific issues (such as floating-point results). As with any application, be sure to perform your own requirements testing while developing with PostgreSQL.
You cannot run the regression tests as the root user. Be sure to run gmake check as the postgres user.
After you have configured and compiled the PostgreSQL source code, it is time to install the compiled libraries, binaries, and data files into a more appropriate home on the system. If you are upgrading from a previous version of PostgreSQL, be sure to back up your database before beginning this step. Information on performing PostgreSQL database backups can be found in Chapter 9.
Installation of the compiled files is accomplished with the commands demonstrated in Example 2-10. When executed in the manner shown in Example 2-10, the su command temporarily logs you in as the root user to execute the required commands. You must have the root password to execute both of the commands shown in Example 2-10.
If you specified a non-default installation directory in Step 3, use the directory you specified instead of /usr/local/pgsql.
Example 2-10. The "gmake install" Command
$ su -c "gmake install" Password: gmake -C doc install gmake: Entering directory /usr/local/src/postgresql-7.1.3/doc' mkdir /usr/local/pgsql mkdir /usr/local/pgsql/man mkdir /usr/local/pgsql/doc mkdir /usr/local/pgsql/doc/html [...] $ su -c "chown -R postgres.postgres /usr/local/pgsql" Password:
The su -c "gmake install" command will install the freshly compiled source either into the directory structure you chose in Step 3 with the --prefix configuration option, or, if this was left unspecified, into the default directory of /usr/local/pgsql. The use of the su -c "chown -R postgres.postgres /usr/local/pgsql" command ensures that the postgres user owns the PostgreSQL installation directories. Using the su -c command lets you save a step by only logging you in as the root user for the duration of the command's execution.
If you chose to configure the PostgreSQL source with the Perl or Python interface, but did not have root access, you can still install the interfaces manually. Use the commands demonstrated in Example 2-11 to install the Perl and Python modules manually.
Example 2-11. Installing Perl and Python modules manually
$ su -c "gmake -C src/interfaces/perl5 install" Password: Password: gmake: Entering directory /usr/local/src/postgresql-7.1.3/src/interfaces/perl5' perl Makefile.PL Checking if your kit is complete... Looks good Writing Makefile for Pg gmake -f Makefile clean [...] $ su -c "gmake -C src/interfaces/python install" Password: gmake: Entering directory /usr/local/src/postgresql-7.1.3/src/interfaces/python' sed -e 's,@libpq_srcdir@,../../../src/interfaces/libpq,g' \ -e 's,@libpq_builddir@,../../../src/interfaces/libpq,g' \ -e 's%@EXTRA_LIBS@% -lz -lcrypt -lresolv -lnsl -ldl -lm -lbsd -lreadline -ltermcap %g' \ -e 's%@INCLUDES@%-I../../../src/include%g' \ [...]
You may also want to install the header files for PostgreSQL. This is important, because the default installation will only install the header files for client application development. If you are going to be using some of PostgreSQL's advanced functionality, such as user-defined functions or developing applications in C that use the libpq library, you will need this functionality. To install the required header files, perform the commands demonstrated in Example 2-12.
The use of the PostgreSQL environment variables is not required. However, they are helpful when performing tasks within PostgreSQL, including starting and shutting down the postmaster processes.
The environment variables that should be set are for the man pages and the bin directory. You can do so by adding the following statements into the /etc/profile file. This should work for any sh-based shell, including Bash and KSH.
PATH=$PATH:/usr/local/pgsql/bin MANPATH=$MANPATH:/usr/local/pgsql/man export PATH MANPATH
Depending on how your system handles shared libraries, you may need to inform the operating system of where your PostgreSQL shared libraries are located. Systems such as Linux, FreeBSD, NetBSD, OpenBSD, Irix, HP/UX, and Solaris will most likely not need to do this.
In a default installation, shared libraries will be located in /usr/local/pgsql/lib (this may be different, depending on whether you changed it with the --prefix configuration option). One of the most common ways to accomplish this is to set the LD_LIBRARY_PATH environment variable to /usr/local/pgsql/lib. See Example 2-13 for an example of doing this in Bourne-style shells and Example 2-14 for an example of doing this in csh and tcsh.
Example 2-13. Setting LD_LIBRARY_PATH in a Bash Shell
$ LD_LIBRARY_PATH=/usr/local/pgsql/lib $ export LD_LIBRARY_PATH
If you are logged in as the root user, instead of using the su -c command in the previous steps, you will now need to login as the postgres user you added in step 1. Once you are logged in as the postgres user, issue the command shown in Example 2-15.
The -D option in the previous command is the location where the data will be stored. This location can also be set with the PGDATA environment variable. If you have set PGDATA, the -D option is unnecessary.
If you would like to use a different directory to hold these data files, make sure the postgres user account can write to that directory. When you execute initdb you will see something similar to what is shown in Example 2-16.
Example 2-16. Output from "initdb"
$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data This database system will be initialized with username "postgres." This user will own all the data files and must also own the server process. Creating directory /usr/local/pgsql/data Creating directory /usr/local/pgsql/data/base Creating directory /usr/local/pgsql/data/global Creating directory /usr/local/pgsql/data/pg_xlog Creating template1 database in /usr/local/pgsql/data/base/1 DEBUG: database system was shut down at 2001-08-24 16:36:35 PDT DEBUG: CheckPoint record at (0, 8) DEBUG: Redo record at (0, 8); Undo record at (0, 8); Shutdown TRUE DEBUG: NextTransactionId: 514; NextOid: 16384 DEBUG: database system is in production state Creating global relations in /usr/local/pgsql/data/global DEBUG: database system was shut down at 2001-08-24 16:36:38 PDT DEBUG: CheckPoint record at (0, 108) DEBUG: Redo record at (0, 108); Undo record at (0, 0); Shutdown TRUE DEBUG: NextTransactionId: 514; NextOid: 17199 DEBUG: database system is in production state Initializing pg_shadow. Enabling unlimited row width for system tables. Creating system views. Loading pg_description. Setting lastsysoid. Vacuuming database. Copying template1 to template0. Success. You can now start the database server using: /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data or /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
Remember that while you can instruct PostgreSQL to use a different data directory by specifying the directory location with the -D option, this path must be initialized through initdb.
When the initdb command has completed, it will provide you with information on starting the PostgreSQL server. The first command displayed will start postmaster in the foreground. Enter that command as it is shown in Example 2-17.
Example 2-17. Running "postmaster" in the Foreground
$ /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data
Starting PostgreSQL in the foreground is not normally required. We suggest the use of the second command displayed. The second command will start postmaster in the background. It uses pg_ctl to start the postmaster service, as shown in Example 2-18.
Example 2-18. Running "postmaster" in the background
$ /usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data
The major difference between the first command and the second command is that the second runs postmaster in the background, as well as prints the DEBUG information into /dev/null. The DEBUG information is sent to /dev/null by redirecting standard error (stderr) to standard out (stdout), and then redirecting standard out (stdout) result to /dev/null. Once the command is executed, it will display a process id (pid). For normal operation, it is better to run postmaster in the background. However, there are times when running postmaster in the foreground can be useful for debugging purposes.
The pg_ctl application can be used to start and stop the PostgreSQL server. See Chapter 9 for more on this command.
The SysV script will allow the graceful control of the PostgreSQL database through the use of the runlevel system. The SysV script can be used for starting, stopping, and status-checking of PostgreSQL. It is known to work with most Red Hat based versions of Linux, including Mandrake; it should also work with other SysV-style systems (e.g., UnixWare, Solaris, etc.) with little modification. The script is named linux. To use it, you will first need to copy the linux script to your init.d directory. You may require root access to do this.
First, move to the directory where you unpacked the PostgreSQL source. In our case, the path to that directory is /usr/local/src/postgresql-7.1.3/. Then, issue a cp command to copy the script from contrib/start-scripts into the init.d directory. Example 2-19 demonstrates how to do this on a Red Hat Linux system.
Example 2-19. Copying the Linux Script
$ cd /usr/local/src/postgresql-7.1.3/ $ su -c "cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql"
Example 2-19 arbitrarily re-names the new copy to postgresql; you may call it whatever you prefer, though it is typically named either postgresql, or postgres.
There are no additional requirements to use the SysV script with Red Hat, if you do not intend on using it to start PostgreSQL automatically (i.e., if you wish to use the script manually). However, if you do wish for the script to startup PostgreSQL automatically when the machine boots up (or changes runlevels), you will need to have the chkconfig program installed.
If chkconfig is installed, you will also need to add the following two lines, including the hash (#) symbol, at the beginning of the /etc/rc.d/init.d/postgresql file:
# chkconfig: 345 85 15 # description: PostgreSQL RDBMS
These example numbers should work on your system; however, it is good to know what purpose they each hold. The first group of numbers (345) represent which runlevels PostgreSQL should be started at. The second group of numbers (85) represents the order in which PostgreSQL should be started within that runlevel, relative to other programs. You should probably keep the second number high, to indicate that it should be started later in the runlevel. The third number (15) represents the order in which PostgreSQL should be shutdown. It is a good idea to keep this number low, representing a shutdown order that is inverse from the startup order. As previously mentioned, the script should work on your system with the numbers provided, but you can change them if it is necessary.
You will need to make the script file executable after copying it. To do so, use the command shown in Example 2-20.
You can now use the commands shown in Example 2-21 on Red Hat and Mandrake Linux distributions to start the PostgreSQL database.
Once the PostgreSQL database is installed and running, you have the option of using the default database, template1. If you create a new database, and you would like all of your consecutive databases to have the same system-wide options, then you should first configure the template1 database to have those options enabled.
For instance, if you plan to use the PL/pgSQL language to program, then you should install the PL/pgSQL language into template1 before using createdb. Then, when you use the createdb command, the database created will inherit template1's objects, and thus, inherit the PL/pgSQL language. For more information on installing the PL/pgSQL language into the database, refer to Chapter 11.
The next step will be to create a new database. This will be a test database. We do not recommend using the default template1 database for testing purposes. As we have not created any users with database-creation rights, you will want to make sure that you are logged in as the postgres user when adding a new database. You can also create users that are allowed to add databases, which is discussed later in Chapter 10. To create a new database named testdb, enter the command shown in Example 2-22.
You should receive a message that says CREATE DATABASE, indicating that creation of the database was successful. You can now use PostgreSQL's command line interface, psql, to access the newly created database. To do so, enter the command shown in Example 2-23.
You can now start entering SQL commands (e.g., such as SELECT) at the psql prompt. If you are unfamiliar with psql, please see Chapter 4 for an introduction.
To verify that the database is working correctly, you can issue the command shown in Example 2-24, which should give you a listing of the languages installed in the database.