10 Steps to PostgreSQL Installation
PostgreSQL is included on the CD but you may want to visit the PostgreSQL site at http://www.postgresql.org to see if there is a newer version available. The source files for PostgreSQL are available for download at, among other mirrors: http://www.ca.postgresql.org/ftpsite/
Step 1: Installing the PostgreSQL Source Package.
When you have acquired the source for PostgreSQL you are going to compile, you will wish to copy the PostgreSQL source package to a temporary folder. This folder will be the path where you will install and configure PostgreSQL. Within this path you will extract the contents from the tar.gz file and proceed with the installation.
Bear in mind that this will not be the installation location. This is a temporary location for configuration and compilation of the source package itself. If you have downloaded the PostgreSQL package from the web, it is most likely not saved in the correct path (unless you explicitly chose to save there). A common convention for building source on UNIX and Linux machines is to build in the /usr/local/src path. You will most likely need root access in order to access this path. As such, the remaining examples in this chapter will involve the root user unless otherwise specified.
 | Linux Users |
|---|---|
If you are a user of a commercial Linux distribution, we strongly suggest you verify whether or not you have PostgreSQL already installed. On a 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 pre-installed, there is a good chance that it is outdated. You will want to install at least PostgreSQL 7.1.2. You can remove rpm packages using the rpm -e command. |
Here is an example of unpacking the source on your system:
Example 2-5. Unpacking the PostgreSQL Source Package
[root@host root]# mv postgresql-7.1.2.tar.gz /usr/local/src [root@host root]# cd /usr/local/src [root@host src]# tar xzvf postgresql-7.1.2.tar.gz postgresql-7.1.2/ postgresql-7.1.2/ChangeLogs/ postgresql-7.1.2/ChangeLogs/ChangeLog-7.1-7.1.1 postgresql-7.1.2/ChangeLogs/ChangeLog-7.1RC1-to-7.1RC2 postgresql-7.1.2/ChangeLogs/ChangeLog-7.1RC2-to-7.1RC3 postgresql-7.1.2/ChangeLogs/ChangeLog-7.1RC3-to-7.1rc4 postgresql-7.1.2/ChangeLogs/ChangeLog-7.1beta1-to-7.1beta3 postgresql-7.1.2/ChangeLogs/ChangeLog-7.1beta3-to-7.1beta4 postgresql-7.1.2/ChangeLogs/ChangeLog-7.1beta4-to-7.1beta5 postgresql-7.1.2/ChangeLogs/ChangeLog-7.1beta5-to-7.1beta6 postgresql-7.1.2/ChangeLogs/ChangeLog-7.1beta6-7.1RC1 postgresql-7.1.2/ChangeLogs/ChangeLog-7.1rc4-7.1 postgresql-7.1.2/ChangeLogs/ChangeLog-7.1.1-7.1.2 postgresql-7.1.2/COPYRIGHT [...]
For references purposes, the following list is a description of the tar options we are using to extract the PostgreSQL source distribution.
x (extract) - tar will extract from the passed filename (as opposed to creating a new file). The "v" and "z" parameters to the tar command mean:
v (verbose) - 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.
z (zipped) - tar will use gunzip to decompress the source (this assumes that you are using the GNU tools; other versions of tar may not support the "z" flag, which requires that the file be unzipped manually with gunzip, or a compatible unzip application, which creates a ".tar" file to be unpacked by tar without the "z" flag).
Non-Gnu tar The use of the z flag within tar may not work if you are not using the GNU version of tar. In this case you would have to use use gzip -d to decompress the tgz file before using tar to extract them.
f (file) - , tar will using the filename following the "f" parameter to determine which file to extract. In our case, postgresql-7.1.2.tar.gz.
After you have completed the extraction of the file files, you will need to change into the newly created path (e.g., /usr/local/src/postgres-7.1.2), as the remaining installation steps will take place in the directory where you extracted the PostgreSQL source.
Step 2: Configuring the Source Tree
This is step where you will configure the source tree and specify installation options specific to your needs. To use the default installation script, use the following command:
./configure [options]
If you are curious about all of the possible configure options, you can use the --help option with configure.
./configure --help
The configure script is 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 make command.
There is a good chance that the default source configuration that configure uses will not be the setup you require. Therefore, in order to customize the default set up, you can enable certain options with configure. Table 2-1 contains a summarized list of available configuration options.
Table 2-1. Configuration Options
Option | Description |
|---|---|
--prefix=PREFIX | The default install directory is /usr/local/pgsql. You can use this option to change the default installation directory. For example, /usr/pgsql. |
--exec-prefix=EXEC-PREFIX | This is used for architecture dependent files. For PostgreSQL purposes it is the same as --prefix= files between different hosts. |
--bindir= DIRECTORY | The directory you would like the executable programs such as psql and postmaster into. The default is EXEC-PREFIX/bin. |
--datadir= DIRECTORY | The directory that contains read-only data files. This directory also includes sample configuration files such as the pg_hba.conf. The default is EXEC-PREFIX/share |
--sysconfdir= DIRECTORY | The directory that contains certain configuration files. Typically the ODBC configuration files are held here. The default is PREFIX/etc |
--libdir= DIRECTORY | The directory that contains the required PostgreSQL libs. You will want to make sure this directory is placed in your ld.so.conf if you are running Linux. The default is EXEC-PREFIX/lib. |
--includedir= DIRECTORY | The directory that contains the C and C++ header files. The default is PREFIX/include. |
--docdir= DIRECTORY | The directory that contains the documentation files. This excludes the man pages which are installed in EXEC-PREFIX/man. The default is PREFIX/doc. |
--mandir= DIRECTORY | Sets this directory to hold PostgreSQL man pages. The default is EXEC-PREFIX/man. |
--with-includes= DIRECTORIES | A colon separated list directories to be searched for additional header files. For example --with-includes=/usr/include:/usr/local/include. This should only be required for non-standard installation locations. |
--with-libraries= DIRECTORIES | The DIRECTORIES A colon separated list of directories to be searched for additional libraries. The syntax is the same as --with-includes=. |
--enable-locale | This will enable locale support. The use of the locale support will incur a performance penalty and should only be activated if you will need to support non-English environments. |
--enable-recode | This will enable the recode translation library. |
--enable-multibyte | This will enable the use of multibyte character encodings. Multibyte is traditionally used with languages such as Japanese, Korean, and Chinese. |
--with-pgport= NUMBER | The will allow the setting of an alternative default port for PostgreSQL. The normal default is 5432. You can change this with run time parameters. |
--with-CXX | Turns on the compilation of the C++ interface library. If you are going to be developing in C++ for PostgreSQL you will need this. |
--with-perl | This will compile the PostgreSQL Perl interface. You will likely need to be a privileged user (such as root) to compile with this option. |
--with-python | This will compile the PostgreSQL Python interface. You will likely need to be a privileged user (such as root) to compile with this option. |
--with-tcl | This will compile the PostgreSQL TCL interface. This is option is recommended if you would like to use PgAccess or pl/TCL. |
--without-tk | If you would like to compile support for TCL but you do not need the graphical (TK) applications enable this option. If this option is specified with the --with-tcl option, then programs that require Tk (such as pgtksh and pgaccess) will not be included. |
--with-tclconfig= DIRECTORY , --with-tkconfig= DIRECTORY | Sets this directory to hold tcl or tk Config.sh files. These files are automatically installed by Tcl/Tk and it contains configuration information needed to build interface modules. |
--enable-odbc | This will enable support for ODBC. |
--with-odbcinst= DIRECTORY | This will set the directory to hold the odbcinst.ini configuration file. The default is /usr/local/pgsql/etc. This location can be overridden by using the --sysconfdir option. |
--with-krb4= DIRECTORY , --with-krb5= DIRECTORY | This will enable support for the Kerberos authentication system. The use of Kerberos is not covered within this book. |
--with-krb-srvnam= NAME | This will set the name of the Kerberos service principal. The name 'postgres' is the default. |
--with-openssl= DIRECTORY | This will enable the use of SSL for encrypted connections. You will need to have OpenSSL installed to use this feature. The default OpenSSL location is /usr/local/ssl. It is suggested that you enable this option if you wish to utilize Stunnel. |
--with-java | This will enable Java/JDBC support. The Ant and JDK packages are required for this to compile correctly. We suggest you enable this option if you wish to utilize the JDBC chapter in this book. Information on ANT, and the Java JDK can be found respectively at: http://jakarta.apache.org/ant/index.html and http://java.sun.com/j2se/1.3/ |
--enable-syslog | If you are running the syslog daemon, you can enable this to allow PostgreSQL to log to your syslog. This is also commonly known as the messages file on Linux. |
--enable-debug | This will compile PostgreSQL with the debugging options turned on. This will make PostgreSQL more resource intensive and should only be used if you are developing the PostgreSQL database itself. |
--enable-cassert | This will enable assertion checking on the PostgreSQL server. This feature should only be used if you are developing the PostgreSQL database itself. |
| PgAccess and TCL |
|---|---|
If you are interested in using PgAccess to interface with your PostgreSQL database, be sure to pass the --with-tcl option to the configure script. |
You can change the C or C++ compiler chosen by the configure command. To do so, set the environment variables CC and CXX to the program that you want to use instead of gcc.
| Oops! |
|---|---|
If you compile PostgreSQL and find that you are missing a feature, you can reconfigure and then continue with the subsequent steps to build and install PostgreSQL. If you choose to reconfigure the PostgreSQL source before you install, make sure you use the gmake clean command within the source tree. This will delete all object files and partially compiled files. |
Step 3: Compiling the Source
After using the configure command, you may begin compiling the PostgreSQL source by entering the gmake command.
Example 2-6. Compiling the Source with GNU Make
[root@host postgresql-7.1.2]# gmake gmake -C doc all gmake[1]: Entering directory `/usr/local/src/postgresql-7.1.2/doc' gmake[1]: Nothing to be done for `all'. gmake[1]: Leaving directory `/usr/local/src/postgresql-7.1.2/doc' gmake -C src all gmake[1]: Entering directory `/usr/local/src/postgresql-7.1.2/src' gmake -C backend all gmake[2]: Entering directory `/usr/local/src/postgresql-7.1.2/src/backend' prereqdir=`cd parser/ >/dev/null && pwd` && \ cd ../../src/include/parser/ && rm -f parse.h && \ ln -s $prereqdir/parse.h . gmake -C utils fmgroids.h gmake[3]: Entering directory `/usr/local/src/postgresql-7.1.2/src/backend/utils' [...]
| Make on Linux-Based Systems |
|---|---|
Remember that, on Linux-based systems, you can usually just type make in place of gmake. |
| BSD users |
|---|---|
On BSD Systems, GNU Make is called gnumake rather than gmake. |
At this point, depending on the speed of your machine, you may want to get some coffee, because the PostgreSQL compilation could take from 10 minutes, an hour, or more. After the compilation has finished, the following message should appear:
All of PostgreSQL is successfully made. Ready to install.
Step 4: Regression Testing
Regression tests are an optional but recommended group of tests. 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 and the extended capabilities of PostgreSQL. The execution of the regression tests is not a requirement. However, the regression tests can help point out any possible (but not probable) problems with the PostgreSQL compilation.
If you decide you would like to run the regression tests you can do so by using the following command command gmake check.
 | Test Failure |
|---|---|
The regression tests will not always pick up on every error message. This can be due to time zone support and floating point results. As with any application, you are going to want to make sure that you perform your own testing while developing with PostgreSQL. |
Step 5: Installing Compiled Programs and Libraries
| Upgrading |
|---|---|
If you are going to upgrade PostgreSQL, instead of installing from scratch. Make sure you back up your old database. Information on performing backups on PostgreSQL can be found in the Administrating PostgreSQL chapters of this book. |
Use the following command to install PostgreSQL:
gmake install
The gmake install command will install the freshly compiled source into the directory structure you chose in step 2. Before using the gmake install command, make sure you have the correct access permissions to your installation directories. On most machines, this will probably require access to the root user. However, it is possible to build PostgreSQL without root permission. For example, you could build PostgreSQL underneath your own home directory.
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. In order to install the interfaces manually you can use the following commands.
gmake -C src/interfaces/perl5 install gmake -C src/interfaces/python install
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 database in C you will need this functionality. To install the all the required header files perform the following command.
gmake install-all-headers
| Keep in mind |
|---|---|
The header files are only required for advanced PostgreSQL server-side development. |
Step 6: Creating the "postgres" user
A Unix user account needs to be created to manage the PostgreSQL database. Typically this user is called postgres, but can be anything that you choose. For consistency through out the book, the user postgres is considered the PostgreSQL root or super-user.
In order to create the PostgreSQL super-user, you will need to have root privileges. On a Linux machine you can use the following commands to add the postgres user.
su -
useradd postgres
 | Don't Use the root User |
|---|---|
Do not try to use the user root as the PostgreSQL super-user. It presents a large security hole, and the system will not allow it. |
As the installation of PostgreSQL was done as the root user you will have to change the ownership of the PostgreSQL data directory before use. On Linux/Unix you can use the following command. Using the -R will cause a recursive change of ownership.
chown -R postgres /usr/local/pgsql/data
Step 7: Setting Environment Variables.
The use of the environment variables is not required. However, it is helpful when performing tasks within PostgreSQL including starting/shutting down the postmaster processes. The environment variables that should be set are for the shared library, 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 LD_LIBRARY_PATH PATH MANPATH
Depending on the type of Unix system you are running, you will want to add the PostgreSQL lib directory to the system linker path. On Linux systems this can be done by adding the following line to the /etc/ld.so.conf.
/usr/local/pgsql/lib
Step 8: Initializing and Starting PostgreSQL
If have a SysV style Unix system (such as RedHat, Linux, Mandrake Linux, Solaris, or UnixWare, then you can skip this step and go on to step 9. Otherwise, to prepare the database for startup, you will need to login as the postgres user you added in step 6. Once you are logged in as the postgres user, issue the following commands at the prompt:
su - postgres /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
The -D option in the above command is the location where the data will be stored. This location is also recognized by the $PGDATA environment variable. If you would like to use a different directory to hold these data files, then make sure the server account can write to that directory.
When the initdb command has completed, initdb will provide you with information on starting the PostgreSQL server. The first command displayed will start the postmaster process in the foreground. That command is:
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data
The second command displayed will start the postmaster processes in the background. It uses pg_ctl to start the postmaster service:
nohup /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data </dev/null >>server.log 2>&1 >/dev/null &
The major difference between the first command and the second command is that the second runs the postgres process in the background as well as prints the DEBUG information into /dev/null. It prints the DEBUG information to /dev/null by redirecting standard error (stderr) to standard out (stdout) and then redirecting that result to /dev/null. Once the second command is executed, it will display a process id (pid). For normal operation, it is better to run the postmaster process in the background. However there are times when running postmaster in the foreground (debugging) can be useful. pg_ctl.
Step 9: Configuring the PostgreSQL SysV Script
The SysV script will allow the graceful control of the PostgreSQL database through the use of the SysV runlevel system. This includes, starting, stopping and status checking of PostgreSQL. If you would like to use the SysV start up script, then you will need to copy the linux script to the init.d directory. The following operations may require root access. First, change directories to the path where you unpacked the PostgreSQL 7.1.2 source. In our case, the path is /usr/local/src/postgresql-7.1.2/. In this directory you will want to change into the containing the SysV script under the contrib directory. This is located in src/contrib/start-scripts Then copy the SysV script to the init.d directory of your Linux installation.
cd /usr/local/src/postgresql-7.1.2/ cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql
| SysV start up script |
|---|---|
This script is known to work with most Red Hat based versions of Linux, including Mandrake. However, it should work with other SysV systems (UnixWare, Solaris etc..) with little modification. |
After having copied the file, you will probably need to change the access permission. You can do this by using the following command:
chmod a+x /etc/rc.d/init.d/postgresql
You can now use the following commands on Red Hat and Mandrake linux distributions to start the PostgreSQL database.
service postgresql start Starting PostgreSQL: ok
Step 10: Creating a Database
Now that the PostgreSQL database is running, you have the option of using the default database named 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 plpgsql language to program, then you should install the plpgsql language into template1 before using createdb. Then when you use the createdb command, the database created will inherit template1's options and thus, inherit the plpgsql language. For more information on installing the plpgsql language into the database, refer to the section on adding pl/pgSQL to your database.
PostgreSQL should now be up and running. The first step will be to create a new database. This will be our 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 postgres when adding a new database. You can create users that are allowed to add databases but that is discussed later on in the book. To create a new database you can enter a the following command at command line:
createdb testdb
You should receive a message which says CREATE DATABASE. PostgreSQL provides an interface called psql. You can use psql to access the newly created database by entering the command:
psql testdb
You can now start entering SQL commands at the psql prompt. If you are unfamiliar with psql, please see the Basic Psql chapter.