PostgreSQL Installation Guide by The PostgreSQL Development Team PostgreSQL is © 1998-9 by the Postgres Global Development Group. Please consult the file README first! This installation procedure makes some assumptions about the desired configuration and runtime environment for your system. This may be adequate for many installations, and is almost certainly adequate for a first installation. But you may want to do an initial installation up to the point of unpacking the source tree and installing documentation, and then print or browse the Administrator's Guide. Before installing Postgres, you may wish to visit www.postgresql.org (http://www.postgresql.org) for up to date information, patches, etc. These installation instructions assume: o You already have downloaded the sources. (If not, see web site.) o Commands are Unix-compatible. See note below. o Defaults are used except where noted. o User 'postgres' is the Postgres superuser. o The source path is /usr/src/pgsql (other paths are possible). o The runtime path is /usr/local/pgsql (other paths are possible). Commands were tested on RedHat Linux version 5.2. Except where noted, they will probably work on most systems. Commands like ps and tar may vary wildly between platforms on what options you should use. Use common sense before typing in these commands. Our Makefiles require GNU make. They will not work with non-GNU make programs. If you do not have GNU make (check make --version), get it from www.gnu.org. Requirements to Run Postgres In general, most Unix-compatible platforms with modern libraries should be able to run Postgres. Although the minimum required memory for running Postgres is as little as 8MB, there are noticable improvements in runtimes for the regression tests when expanding memory up to 96MB on a relatively fast dual-processor system running X-Windows. The rule is you can never have too much memory. Check that you have sufficient disk space. You will need about 30 Mbytes for /usr/src/pgsql, about 5 Mbytes for /usr/local/pgsql (excluding your database) and 1 Mbyte for an empty database. The database will temporarily grow to about 20 Mbytes during the regression tests. You will also need about 3 Mbytes for the distribution tar file. We therefore recommend that during installation and testing you have well over 20 Mbytes free under /usr/local and another 25 Mbytes free on the disk partition containing your database. Once you delete the source files, tar file and regression database, you will need 2 Mbytes for /usr/local/pgsql, 1 Mbyte for the empty database, plus about five times the space you would require to store your database data in a flat file. To check for disk space, use $ df -k Installation Procedure 1. Read any last minute information and platform specific porting notes. There are some platform specific notes at the end of this file for Ultrix4.x, Linux, BSD/OS and NeXT. There are other files in directory /usr/src/pgsql/doc, including files FAQ-Irix and FAQ-Linux. 2. Some platforms use flex. If your system uses flex then make sure you have a good version. To check, type $ flex --version If the flex command is not found then you probably do not need it. If the version is 2.5.2 or 2.5.4 or greater then you are okay. If it is 2.5.3 or before 2.5.2 then you will have to upgrade flex. Please read the file README.flex for details. *** If you are not upgrading an existing system then skip to step 6. *** 3. If you are upgrading an existing system then back up your database. For alpha- and beta-level releases, the database format is liable to change, often every few weeks, with no notice besides a quick comment in the HACKERS mailing list. Full releases always require a dump/reload from previous releases. It is therefore a bad idea to skip this step. Tip: Do not use the pg_dumpall script from v6.0 or everything will be owned by the Postgres super user. To dump your fairly recent post-v6.0 database installation, type $ pg_dumpall > db.out (To use the latest pg_dumpall script on your existing older database before upgrading Postgres, pull the most recent version of pg_dumpall from the new distribution: $ gunzip -c postgresql-v6.5.tar.gz | tar xvf - src/bin/pg_dump/pg_dumpall $ chmod a+x src/bin/pg_dump/pg_dumpall $ src/bin/pg_dump/pg_dumpall > db.out $ rm -rf src ) If you wish to preserve object id's (oids), then use the -o option when running pg_dumpall. However, unless you have a special reason for doing this (such as using OIDs as keys in tables), don't do it. CAUTION: You must make sure that your database is not updated in the middle of your backup. If necessary, bring down postmaster, edit the permissions in file /usr/local/pgsql/data/pg_hba.conf to allow only you on, then bring postmaster back up. 4. Kill the postmaster. You might find that $ killall postmaster will do the job. Otherwise type $ ps -ax | grep postmaster and you might get an output like this: 2565 5 S 0:00 grep postmaster 285 ? SW 0:00 (postmaster) Then type $ kill 285 (substitute your number here). On systems which have Postgres started at boot time, there is probably a startup file which will accomplish the same thing. For example, on my Linux system I can type $ /etc/rc.d/init.d/postgres.init stop to halt Postgres. 5. Move the old directories out of the way. If you are short of disk space then you may have to back up and delete the directories instead. If you do this, save the old database in the /usr/local/pgsql/data directory tree or the database dump you made, respectively. At a minimum, save file /usr/local/pgsql/data/pg_hba.conf. Type the following: $ su - $ cd /usr/src $ mv pgsql pgsql_6_0 $ cd /usr/local $ mv pgsql pgsql_6_0 $ exit If you are not using /usr/local/pgsql/data as your data directory (check to see if environment variable PGDATA is set to something else) then you will also want to move this directory in the same manner. *** Continue here if you are installing a new system. *** 6. Make a directory for the source code and unpack the source tarball there: $ gunzip -c ~/postgresql-v6.5.tar.gz | tar xvf - $ mv postgresql-6.5 /usr/src/pgsql 7. Configure the source code for your system. It is this step at which you can specify your actual installation path for the build process (see the --prefix option below). Type $ cd /usr/src/pgsql/src $ ./configure [ options ] The configure script selects a system-specific "template" file from the files provided in the template subdirectory. If it cannot guess which one to use for your system, it will say so and exit. In that case you'll need to figure out which one to use and run configure again, this time giving the --with-template=TEMPLATE option to make the right file be chosen. Please Report Problems: If your system is not automatically recognized by configure and you have to do this, please send email to scrappy@postgresql.org with the output of the program ./config.guess. Indicate what the template file should be. Choose configuration options. Check Configuration Options for details. However, for a first installation with no extra options like multi-byte character support or locale collation support it may be adequate to just chose the installation areas and run configure without extra options specified. The configure script accepts many additional options that you can use if you don't like the default configuration. To see them all, type $ ./configure --help Some of the more commonly used ones are: --prefix=BASEDIR Selects a different base directory for the installation of the Postgres configuration. The default is /usr/local/pgsql. --with-template=TEMPLATE Use template file TEMPLATE - the template files are assumed to be in the directory src/template, so look there for proper values. --with-tcl Build interface libraries and programs requiring Tcl/Tk, including libpgtcl, pgtclsh, and pgtksh. --with-perl Build the Perl interface library. --with-odbc Build the ODBC driver package. --enable-locale Enables locale support. See the file README.locale for details. 8. Compile the program. Type $ cd src $ make all The last line displayed will hopefully be "All of PostgreSQL is successfully made. Ready to install." If the compiler fails with a message stating that the flex command cannot be found then install flex as described earlier. Next, change directory back to this directory, type $ make clean then recompile again. Compiler options, such as optimization and debugging, may be specified on the command line using the COPT variable. For example, typing $ make COPT="-g" all would invoke your compiler's -g option in all steps of the build. See src/Makefile.global.in for further details. 9. Install the program. Type $ make install The last line displayed will be make[1]: Leaving directory `/usr/src/pgsql/src/man' 10. If necessary, tell your system how to find the new shared libraries. You can do one of the following, preferably the first: a. As root, edit file /etc/ld.so.conf. Add a line /usr/local/pgsql/lib to the file. Then run command /sbin/ldconfig. b. In a bash shell, type $ export LD_LIBRARY_PATH=/usr/local/pgsql/lib c. In a csh shell, type $ setenv LD_LIBRARY_PATH /usr/local/pgsql/lib Please note that the above commands may vary wildly for different operating systems. Check the platform specific notes, such as those for Ultrix4.x or and for non-ELF Linux. If, when you create the database, you get the message pg_id: can't load library 'libpq.so' then the above step was necessary. Simply do this step, then try to create the database again. 11. If you used the --with-perl option to configure and you were an unprivileged user during the installation process, then the Perl module won't have been installed, for lack of write privileges on the Perl library directories. You can complete its installation, either now or later, by doing $ su - $ cd /usr/src/pgsql/src/interfaces/perl5 $ make install 12. Prepare the Postgres superuser account. Often the username "postgres" is used for this but any unprivileged user will suffice. It must *not* be root or any similarly privileged user (bin, kmem, ...) for that would be a security risk. $ useradd postgres (The above command may differ from system to system.) Make the postgres installation owner by the Postgres superuser: $ chown -R postgres /usr/local/postgres Add the following lines to your ~/.bash_profile or equivalent: PATH=$PATH:/usr/local/pgsql/bin MANPATH=$MANPATH:/usr/local/pgsql/man PGLIB=/usr/local/pgsql/lib PGDATA=/usr/local/pgsql/data export PATH MANPATH PGLIB PGDATA Any ordinary user that wishes to use the database must do the same. Several regression tests could fail if the user's locale collation scheme is different from that of standard C locale. If you configure and compile Postgres with the --enable-locale option then set locale environment to C (or unset all LC_* variables) by putting these additional lines to your login environment before starting the database server: LC_COLLATE=C LC_CTYPE=C LC_COLLATE=C export LC_COLLATE LC_CTYPE LC_COLLATE Log into your postgres superuser account: $ su - postgres 13. Create the database installation from your Postgres superuser account (typically account postgres). Do not do the following as root! This would be a major security hole. Type $ initdb 14. Briefly test that the backend will start and run by running it from the command line. Start the postmaster daemon running in the background by typing $ postmaster -i Connect to the always existing template database: $ psql template1 And run a sample query: postgres=> SELECT datetime 'now'; Exit postgres=> \q 15. Run postmaster in the background from your Postgres superuser account (typically account postgres). Do not run postmaster from the root account! Usually, you will want to modify your computer so that it will automatically start postmaster whenever it boots. It is not required; the Postgres server can be run successfully from non-privileged accounts without root intervention. Here are some suggestions on how to do this, contributed by various users. Whatever you do, postmaster must be run by the Postgres superuser and not by root. This is why all of the examples below start by switching user (su) to postgres. These commands also take into account the fact that environment variables like PATH and PGDATA may not be set properly. The examples are as follows. Use them with extreme caution. o If you are installing from a non-privileged account and have no root access, then start the postmaster and send it to the background: $ cd $ nohup postmaster > regress.log 2>&1 & o Edit file rc.local on NetBSD or file rc2.d on SPARC Solaris 2.5.1 to contain the following single line: su postgres -c "/usr/local/pgsql/bin/postmaster -S -D /usr/local/pgsql/data" o In FreeBSD 2.2-RELEASE edit /usr/local/etc/rc.d/pgsql.sh to contain the following lines and make it chmod 755 and chown root:bin. #!/bin/sh [ -x /usr/local/pgsql/bin/postmaster ] && { su -l pgsql -c 'exec /usr/local/pgsql/bin/postmaster -D/usr/local/pgsql/data -S -o -F > /usr/local/pgsql/errlog' & echo -n ' pgsql' } o In RedHat Linux add a file /etc/rc.d/init.d/postgres.init which is based on the example in contrib/linux/. Then type $ chkconfig --add postgres 16. Run the regression tests. The file /usr/src/pgsql/src/test/regress/README has detailed instructions for running and interpreting the regression tests. While it is not mandatory to run the regression test, it is probably good to know whether or not your database server functions like the developers had it in mind before running anything important on it. 17. If you haven't already done so, this would be a good time to modify your computer to do regular maintainence. The following should be done at regular intervals: Minimal Backup Procedure 1. Run the SQL command VACUUM. This will clean up your database. 2. Back up your system. (You should probably keep the last few backups on hand.) Preferably, no one else should be using the system at the time. 18. If you are upgrading an existing system then reinstall your old database. Type $ psql -e template1 < db.out If your pre-v6.2 database uses either path or polygon geometric data types, then you will need to upgrade any columns containing those types. To do so, type (from within psql) => UPDATE FirstTable SET PathCol = UpgradePath(PathCol); => UPDATE SecondTable SET PathCol = UpgradePath(PathCol); ... => VACUUM; UpgradePath() checks to see that a path value is consistant with the old syntax, and will not update a column which fails that examination. UpgradePoly() cannot verify that a polygon is in fact from an old syntax, but RevertPoly() is provided to reverse the effects of a mis-applied upgrade. 19. Clean up after yourself. Type $ rm -rf /usr/src/pgsql_6_5 $ rm -rf /usr/local/pgsql_6_5 # Also delete old database directory tree if it is not in # /usr/local/pgsql_6_5/data $ rm ~/postgresql-v6.5.tar.gz 20. Now create, access and manipulate databases as desired. Write client programs to access the database server. In other words, enjoy! Questions? Bugs? Feedback? First, read the files in directory /usr/src/pgsql/doc/. The FAQ in this directory may be particularly useful. If Postgres failed to compile on your computer then fill out the form in file /usr/src/pgsql/doc/bug.template and mail it to the location indicated at the top of the form. Check on the web site at http://www.postgresql.org For more information on the various support mailing lists. Appendix I: Playing with Postgres After Postgres is installed, a database system is created, a postmaster daemon is running, and the regression tests have passed, you'll want to see Postgres do something. That's easy. Invoke the interactive interface to Postgres, psql: $ psql template1 (psql has to open a particular database, but at this point the only one that exists is the template1 database, which always exists. We will connect to it only long enough to create another one and switch to it.) The response from psql is: Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL [PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by egcs ] type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: template1 template1=> Create the database foo: template1=> create database foo; CREATEDB (Get in the habit of including those SQL semicolons. Psql won't execute anything until it sees the semicolon or a "\g" and the semicolon is required to delimit multiple statements.) Now connect to the new database: template1=> \c foo connecting to new database: foo ("slash" commands aren't SQL, so no semicolon. Use \? to see all the slash commands.) And create a table: foo=> create table bar (i int4, c char(16)); CREATE Then inspect the new table: foo=> \d bar Table = bar +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | i | int4 | 4 | | c | char() | 16 | +----------------------------------+----------------------------------+-------+ And so on. You get the idea.