Index: openacs-4/packages/acs-core-docs/www/postgres.html =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-core-docs/www/postgres.html,v diff -u -r1.23 -r1.24 --- openacs-4/packages/acs-core-docs/www/postgres.html 11 Dec 2003 23:08:46 -0000 1.23 +++ openacs-4/packages/acs-core-docs/www/postgres.html 4 Feb 2004 16:47:33 -0000 1.24 @@ -1,8 +1,7 @@ -Install PostgreSQL
Alex logo
Prev Chapter�3.�Complete Installation Next

Install PostgreSQL

by Vinod Kurup

+Install PostgreSQL
Alex logo
Prev Chapter�3.�Complete Installation Next

Install PostgreSQL

by Vinod Kurup

OpenACS docs are written by the named authors, and may be edited by OpenACS documentation staff. -

Skip this section if you will run only Oracle.

OpenACS 5.0.0b4 will run with PostgreSQL 7.2.x, 7.3.2, - 7.3.3, and 7.3.4. 7.3.4 is the recommended version of PostgreSQL.

  • Debian.�

    Debian users can install the package and add some backwards-compatibility links:

    apt-get install postgresql postgresql-dev postgresql-doc
+

Skip this section if you will run only Oracle.

OpenACS will run with PostgreSQL 7.2.x, 7.3.2, 7.3.3, and 7.3.4. 7.3.4 is the recommended version of PostgreSQL. PostgreSQL 7.4 has been verified.

  • Mac OS X.�If you are running Mac OS X prior to 10.3, you should be able to install and use PostGreSQL 7.2.x or 7.3.x. Mac OS X 10.3 requires PostGreSQL 7.4.

  • Debian.�

    Debian users, especially Debian stable users, should install PostGreSQL from source as detailed below. Debian unstable users: the following process has been known to work:

    apt-get install postgresql postgresql-dev postgresql-doc
 ln -s /usr/include/postgresql/ /usr/include/pgsql
 ln -s /var/lib/postgres /usr/local/pgsql
 ln -s /usr/include/pgsql /usr/local/pgsql/include
@@ -11,78 +10,85 @@
   can skip a few steps.  These shell commands add some links for compatibility with the directories from a source-based install; start the service; create a new group for web service
   users, and modify the postgres user's
   environment (more
-  information):
    [root@yourserver root]# ln -s /usr/lib/pgsql/ /var/lib/pgsql/lib
-[root@yourserver root]# ln -s /var/lib/pgsql /usr/local/pgsql
-[root@yourserver root]# ln -s /etc/init.d/postgresql /etc/init.d/postgres
-[root@yourserver root]# service postgresql start
+  information):
    [root root]# ln -s /usr/lib/pgsql/ /var/lib/pgsql/lib
+[root root]# ln -s /var/lib/pgsql /usr/local/pgsql
+[root root]# ln -s /etc/init.d/postgresql /etc/init.d/postgres
+[root root]# ln -s /usr/bin /usr/local/pgsql/bin
+[root root]# service postgresql start
 Initializing database:
                                                            [  OK  ]
 Starting postgresql service:                               [  OK  ]
-[root@yourserver root]# echo "export LD_LIBRARY_PATH=/usr/local/pgsql/lib" >> ~postgres/.bash_profile
-[root@yourserver root]# echo "export PATH=$PATH:/usr/local/pgsql/bin" >> ~postgres/.bash_profile
-[root@yourserver root]# groupadd web
-[root@yourserver root]# su - postgres
+[root root]# echo "export LD_LIBRARY_PATH=/usr/local/pgsql/lib" >> ~postgres/.bash_profile
+[root root]# echo "export PATH=$PATH:/usr/local/pgsql/bin" >> ~postgres/.bash_profile
+[root root]# groupadd web
+[root root]# su - postgres
 -bash-2.05b$
 
 ln -s /usr/lib/pgsql/ /var/lib/pgsql/lib
 ln -s /var/lib/pgsql /usr/local/pgsql
+ln -s /usr/bin /usr/local/pgsql/bin
 service postgresql start
 echo "export LD_LIBRARY_PATH=/usr/local/pgsql/lib" >> ~postgres/.bash_profile
 echo "export PATH=$PATH:/usr/local/pgsql/bin" >> ~postgres/.bash_profile
 groupadd web
 su - postgres
    ... and then skip to 6.  Something similar may work for other binary packages as well.

  1. Unpack PostgreSQL.�If you have not downloaded the postgresql tarball to /tmp/postgresql-7.3.4.tar.gz, - get it.

    [root@yourserver root]# cd /usr/local/src
-[root@yourserver src]# tar xzf /tmp/postgresql-7.3.4.tar.gz
-[root@yourserver src]# 
+        get it.
    [root root]# cd /usr/local/src
+[root src]# tar xzf /tmp/postgresql-7.3.4.tar.gz
+[root src]# 
 cd /usr/local/src
 tar xzf /tmp/postgresql-7.3.4.tar.gz

  2. Create the Postgres user.� Create a user and group (if you haven't done so before) for PostgreSQL. This is the account that PostgreSQL will run as since it will not run as root. Since nobody will log in directly as that user, we'll leave the password blank. - 

    [root@yourserver src]# groupadd web
-[root@yourserver src]# useradd -g web -d /usr/local/pgsql postgres
-[root@yourserver src]# mkdir -p /usr/local/pgsql
-[root@yourserver src]# chown -R postgres.web /usr/local/pgsql /usr/local/src/postgresql-7.3.4
-[root@yourserver src]# chmod 750 /usr/local/pgsql
-[root@yourserver src]#
+	
    [root src]# groupadd web
+[root src]# useradd -g web -d /usr/local/pgsql postgres
+[root src]# mkdir -p /usr/local/pgsql
+[root src]# chown -R postgres.web /usr/local/pgsql /usr/local/src/postgresql-7.3.4
+[root src]# chmod 750 /usr/local/pgsql
+[root src]#
 groupadd web
 useradd -g web -d /usr/local/pgsql postgres
 mkdir -p /usr/local/pgsql
 chown -R postgres.web /usr/local/pgsql /usr/local/src/postgresql-7.3.4
-chmod 750 /usr/local/pgsql

  3. Set up postgres's environment variables.�They are necessary for the executable to find its supporting - libraries. For convenience, we'll simply append the necessary - lines to the postgres shell config file.

    [root@yourserver src]# echo "export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib" >> ~postgres/.bashrc
-[root@yourserver src]# echo "export PATH=$PATH:/usr/local/pgsql/bin" >> ~postgres/.bashrc
-echo "export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib" >> ~postgres/.bashrc
-echo "export PATH=$PATH:/usr/local/pgsql/bin" >> ~postgres/.bashrc

    Test this by logging in as +chmod 750 /usr/local/pgsql

    • Mac OS X: Do instead:

      sudo niutil -create / /groups/web
+sudo niutil - list / /groups

      The second command returns a list of groups. The last entry should include the gid of the group web, which you will need in the next sequence:

      sudo niutil -create / /users/postgres
+sudo niutil -createprop / /users/postgres gid web's gid_number
+sudo niutil -createprop / /users/postgres home /usr/local/pgsql
+sudo niutil -createprop / /users/postgres shell /bin/bash
+mkdir -p /usr/local/pgsql
+chown -R postgres:web /usr/local/pgsql /usr/local/src/postgresql-7.4
+chmod 750 /usr/local/pgsql

  4. Set up postgres's environment variables.�They are necessary for the executable to find its supporting + libraries. Put the following lines into the postgres user's environment.

    [root src]# su - postgres
+[postgres ~] emacs ~postgres/.bashrc

    Paste these lines into .bashrc:

    export PATH=$PATH:/usr/local/pgsql/bin
+export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib

    Test this by logging in as postgres and checking the - paths; you should see /usr/local/pgsql/bin

    [root@yourserver src]# su - postgres
-[postgres@yourserver pgsql]$ env | grep PATH
+	paths; you should see /usr/local/pgsql/bin
    [root src]# su - postgres
+[postgres pgsql]$ env | grep PATH
 LD_LIBRARY_PATH=:/usr/local/pgsql/lib
 PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/usr/bin/X11:/usr/X11R6/bin:/root/bin:/usr/local/pgsql/bin:/usr/local/pgsql/bin
-[postgres@yourserver pgsql]$ exit
+[postgres pgsql]$ exit

  5. Compile and install PostgreSQL.� Change to the postgres user and run ./configure to set the compilation options automatically. This is the point at which you can configure PostgreSQL in various ways. For example, if you want to enable - Unicode support, add the flags --enable-locale and --enable-multibyte. If you want to see what the other possibilities are, run ./configure --help. - 

    [root@yourserver src]# su - postgres
-[postgres@yourserver pgsql]$ cd /usr/local/src/postgresql-7.3.4
-[postgres@yourserver postgresql-7.3.4]$ ./configure
+	  Unicode support, add the flags --enable-locale and --enable-multibyte. If you want to see what the other possibilities are, run ./configure --help.
+	
    [root src]# su - postgres
+[postgres pgsql]$ cd /usr/local/src/postgresql-7.3.4
+[postgres postgresql-7.3.4]$ ./configure
 creating cache ./config.cache
 checking host system type... i686-pc-linux-gnu
 (many lines omitted>
 linking ./src/makefiles/Makefile.linux to src/Makefile.port
 linking ./src/backend/port/tas/dummy.s to src/backend/port/tas.s
-[postgres@yourserver postgresql-7.3.4]$ make all
+[postgres postgresql-7.3.4]$ make all
 make -C doc all
 make[1]: Entering directory `/usr/local/src/postgresql-7.3.4/doc'
 (many lines omitted)
 make[1]: Leaving directory `/usr/local/src/postgresql-7.3.4/src'
 All of PostgreSQL successfully made. Ready to install.
-[postgres@yourserver postgresql-7.3.4]$ make install
+[postgres postgresql-7.3.4]$ make install
 make -C doc install
 make[1]: Entering directory `/usr/local/src/postgresql-7.3.4/doc'
 (many lines omitted)
@@ -96,15 +102,15 @@
 	  The initdb command initializes the
 	  database. pg_ctl is used to start up
 	  PostgreSQL.
-	
    [postgres@yourserver tsearch]$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
+	
    [postgres tsearch]$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
 The files belonging to this database system will be owned by user "postgres".
 This user must also own the server process.
 (17 lines omitted)
 or
     /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
-[postgres@yourserver tsearch]$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/data/server.log start
+[postgres tsearch]$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/data/server.log start
 postmaster successfully started
-[postgres@yourserver tsearch]$
+[postgres tsearch]$
 /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
 /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/data/server.log start
    
 	  PostgreSQL errors will be logged in
@@ -113,20 +119,20 @@
 	  access. Plpgsql is a PL/SQL-like language.  We add it to
 	  template1, which is the template from which all new
 	  databases are created.  We can verify that it was created
-	  with the createlang command in list mode.
    [postgres@yourserver pgsql]$ createlang plpgsql template1
-[postgres@yourserver pgsql]$ createlang -l template1
+	  with the createlang command in list mode.
    [postgres pgsql]$ createlang plpgsql template1
+[postgres pgsql]$ createlang -l template1
 Procedural languages
   Name   | Trusted?
 ---------+----------
  plpgsql | t
 (1 row)
 
-[postgres@yourserver pgsql]$
+[postgres pgsql]$
 createlang plpgsql template1
 createlang -l template1

  6. Test PostgreSQL (OPTIONAL).�Create a database and try some simple commands. The output should be as shown. - 

    [postgres@yourserver pgsql]$ createdb mytestdb
+	
    [postgres pgsql]$ createdb mytestdb
 CREATE DATABASE
-[postgres@yourserver pgsql]$ psql mytestdb
+[postgres pgsql]$ psql mytestdb
 Welcome to psql, the PostgreSQL interactive terminal.
 
 Type:  \copyright for distribution terms
@@ -150,55 +156,55 @@
 (1 row)
 
 mytestdb=# \q
-[postgres@yourserver pgsql]$ dropdb mytestdb
+[postgres pgsql]$ dropdb mytestdb
 DROP DATABASE
-[postgres@yourserver pgsql]$ exit
+[postgres pgsql]$ exit
 logout
 
-[root@yourserver src]#

  7. Set PostgreSQL to start on boot. First, we copy the +[root src]#

  8. Set PostgreSQL to start on boot. First, we copy the postgresql.txt init script, which automates startup and shutdown, to the distribution-specific init.d directory. Then we verify that it works. Then we automate it by setting up a bunch of symlinks that ensure that, when the operating system changes runlevels, postgresql goes to the appropriate state. Red Hat and Debian and SuSE each work a little differently. -

    • Red Hat RPM:

      The init script is already installed; just turn it on for the appropriate run levels.

      [root@yourserver root]# chkconfig --level 345 postgresql on
-[root@yourserver root]#

    • Red Hat from source:

      [root@yourserver src]# cp /tmp/openacs-5.0.0b4/packages/acs-core-docs/www/files/postgresql.txt /etc/init.d/postgresql
-[root@yourserver src]# chown root.root /etc/rc.d/init.d/postgresql
-[root@yourserver src]# chmod 755 /etc/rc.d/init.d/postgresql
-[root@yourserver src]# 
-cp /tmp/openacs-5.0.0b4/packages/acs-core-docs/www/files/postgresql.txt /etc/init.d/postgresql
+	
      • Red Hat RPM:
        The init script is already installed; just turn it on for the appropriate run levels.
        [root root]# chkconfig --level 345 postgresql on
+[root root]# 
      • Red Hat from source:
        [root src]# cp /tmp//packages/acs-core-docs/www/files/postgresql.txt /etc/init.d/postgresql
+[root src]# chown root.root /etc/rc.d/init.d/postgresql
+[root src]# chmod 755 /etc/rc.d/init.d/postgresql
+[root src]# 
+cp /tmp//packages/acs-core-docs/www/files/postgresql.txt /etc/init.d/postgresql
 chown root.root /etc/rc.d/init.d/postgresql
-chmod 755 /etc/rc.d/init.d/postgresql
        Test the script.
        [root@yourserver root]# service postgresql stop
+chmod 755 /etc/rc.d/init.d/postgresql
        Test the script.
        [root root]# service postgresql stop
 Stopping PostgreSQL: ok
-[root@yourserver root]# 
        If PostgreSQL successfully stopped, then use the following
+[root root]#

      If PostgreSQL successfully stopped, then use the following command to make sure that the script is run appropriately at boot and shutdown. And turn it back on because we'll use it later. - 

      [root@yourserver root]# chkconfig --add postgresql
-[root@yourserver root]# chkconfig --level 345 postgresql on
-[root@yourserver root]# chkconfig --list postgresql
+		
      [root root]# chkconfig --add postgresql
+[root root]# chkconfig --level 345 postgresql on
+[root root]# chkconfig --list postgresql
 postgresql      0:off   1:off   2:on    3:on    4:on    5:on    6:off
-[root@yourserver root]# service postgresql start
+[root root]# service postgresql start
 Starting PostgreSQL: ok
-[root@yourserver root]#
+[root root]#
 chkconfig --add postgresql
 chkconfig --level 345 postgresql on
 chkconfig --list postgresql
-service postgresql start

    • Debian:

      root:~# cp /tmp/openacs-5.0.0b4/packages/acs-core-docs/www/files/postgresql.txt /etc/init.d/postgresql
-root:~# chown root.root /etc/init.d/postgresql
-root:~# chmod 755 /etc/init.d/postgresql
-root:~# 
-cp /tmp/openacs-5.0.0b4/packages/acs-core-docs/www/files/postgresql.txt /etc/init.d/postgresql
+service postgresql start

    • Debian:

      [root ~]# cp /tmp//packages/acs-core-docs/www/files/postgresql.txt /etc/init.d/postgresql
+[root ~]# chown root.root /etc/init.d/postgresql
+[root ~]# chmod 755 /etc/init.d/postgresql
+[root ~]# 
+cp /tmp//packages/acs-core-docs/www/files/postgresql.txt /etc/init.d/postgresql
 chown root.root /etc/init.d/postgresql
-chmod 755 /etc/init.d/postgresql

      Test the script

      root:~# /etc/init.d/postgresql stop
+chmod 755 /etc/init.d/postgresql

      Test the script

      [root ~]# /etc/init.d/postgresql stop
 Stopping PostgreSQL: ok
-root:~#

      If PostgreSQL successfully stopped, then use the following +[root ~]#

      If PostgreSQL successfully stopped, then use the following command to make sure that the script is run appropriately at boot and shutdown.

      -root:~# update-rc.d postgresql defaults
+[root ~]# update-rc.d postgresql defaults
  Adding system startup for /etc/init.d/postgresql ...
    /etc/rc0.d/K20postgresql -> ../init.d/postgresql
    /etc/rc1.d/K20postgresql -> ../init.d/postgresql
@@ -207,9 +213,9 @@
    /etc/rc3.d/S20postgresql -> ../init.d/postgresql
    /etc/rc4.d/S20postgresql -> ../init.d/postgresql
    /etc/rc5.d/S20postgresql -> ../init.d/postgresql
-root:~# /etc/init.d/postgresql start
+[root ~]# /etc/init.d/postgresql start
 Starting PostgreSQL: ok
-root:~#

    • SuSE:

      Note

      +[root ~]#

    • SuSE:

      Note

      I have received reports that SuSE 8.0 is different from previous versions. Instead of installing the boot scripts in @@ -219,20 +225,20 @@ rc.d/ part in each of the following commands. - 

      root:~# cp /tmp/openacs-5.0.0b4/packages/acs-core-docs/www/files/postgresql.txt /etc/rc.d/init.d/postgresql
-root:~# chown root.root /etc/rc.d/init.d/postgresql
-root:~# chmod 755 /etc/rc.d/init.d/postgresql

      + 

    [root ~]# cp /tmp//packages/acs-core-docs/www/files/postgresql.txt /etc/rc.d/init.d/postgresql
+[root ~]# chown root.root /etc/rc.d/init.d/postgresql
+[root ~]# chmod 755 /etc/rc.d/init.d/postgresql

    Test the script. - 

    root:~# /etc/rc.d/init.d/postgresql stop
+        
    [root ~]# /etc/rc.d/init.d/postgresql stop
 Stopping PostgreSQL: ok
    
 
           If PostgreSQL successfully stopped, then use the following
           command to make sure that the script is run appropriately at boot
           and shutdown.
 
-        
    root:~# cd /etc/rc.d/init.d
+        
    [root ~]# cd /etc/rc.d/init.d
 root:/etc/rc.d/init.d# ln -s /etc/rc.d/init.d/postgresql K20postgresql
 root:/etc/rc.d/init.d# ln -s /etc/rc.d/init.d/postgresql S20postgresql  
 root:/etc/rc.d/init.d# cp K20postgresql rc2.d
@@ -252,7 +258,9 @@
         
    root:/etc/rc.d/init.d # cd
 root:~ # /etc/rc.d/init.d/rc2.d/S20postgresql start
 Starting PostgreSQL: ok
-root:~ #

+root:~ #

  • Mac OS X:

    1. Install the startup script:

      cd /Library/StartupItems/
+tar xfz /var/lib/aolserver/service0/packages/acs-core-docs/www/files/osx-postgres-startup-item.tgz
+

    • From now on, PostgreSQL should start automatically each time you boot up and it should shutdown gracefully each time you shut down. (Note: @@ -262,8 +270,8 @@ little. This usually isn't a problem as Red Hat defaults to runlevel 3)

  • Tune postgres. (OPTIONAL).�The default values for PostgreSQL are very conservative; we can safely change some of them and improve performance.

    1. Change the kernel parameter for maximum shared memory - segment size to 128Mb:

      [root@yourserver root]# echo 134217728 >/proc/sys/kernel/shmmax
-[root@yourserver root]#

      Make that change permanent by editing + segment size to 128Mb:

      [root root]# echo 134217728 >/proc/sys/kernel/shmmax
+[root root]#

      Make that change permanent by editing /etc/sysctl.conf to add these lines at the end:

      # increase shared memory limit for postgres
 kernel.shmmax = 134217728

    2. Edit the PostgreSQL config file, /usr/local/pgsql/data/postgresql.conf, to use more memory. These values should improve performance in most cases. (more information)

      #       Shared Memory Size