<?xml version='1.0' ?>
<!DOCTYPE sect1 PUBLIC "-//OASIS//DTD DocBook XML V4.4//EN"
               "http://www.oasis-open.org/docbook/xml/4.4/docbookx.dtd" [
<!ENTITY % myvars SYSTEM "../variables.ent">
%myvars;
]>

<sect1 id="postgres" xreflabel="Install PostgreSQL">
  <title>Install PostgreSQL</title>

  <authorblurb>
    <para>by <ulink url="mailto:vinod@kurup.com">Vinod Kurup</ulink></para>
  </authorblurb>
  
  <para>Skip this section if you will run only Oracle.</para>

  <para>OpenACS &version; will run with <link linkend="source-postgresql">PostgreSQL</link> 7.3.2, 7.3.3, and 7.3.4 and 7.4.x.  7.4.7 is the recommended version of PostgreSQL.</para>

  <itemizedlist>
    <listitem>
      <formalpara id="install-postgres-mac">
        <title>Special notes for Mac OS X</title>
        <para>If you are running Mac OS X prior to 10.3, you should be
        able to install and use PostGreSQL 7.3.x.  Mac OS X 10.3
        requires PostGreSQL 7.4. </para>
      </formalpara>
    </listitem>
    <listitem>
      <formalpara id="install-postgres-debian">
        <title>Special Notes for Debian</title>
        <para></para>
      </formalpara>
      
      <para>Debian stable user should install PostGreSQL from source
      as detailed below, or they should use the www.backports.org
      backport for Postgres to get a more current version. Debian
      unstable users: the following process has been known to work
      (but you should double-check that the version of PostGreSQL is
      7.3 or above):</para>
      
      <para>For Debian stable users, you can use backports, by adding
      this line to the /etc/apt/sources.list</para>

      <screen>
        <userinput>deb http://www.backports.org/debian stable bison postgresql openssl openssh tcl8.4 courier debconf spamassassin tla diff patch neon chkrootkit
        </userinput>
      </screen>

      <screen><userinput>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
su postgres -c "/usr/lib/postgresql/bin/createlang plpgsql template1"</userinput></screen>
      <para>and proceed to <xref linkend="postgres-tune"/> or to the
      next section.</para>
    </listitem>
    <listitem>
        <formalpara id="install-postgres-rpm">
        <title>Special Notes for Red Hat</title>
       <para>Red Hat users: If you install PostgreSQL 7.3.2 from the Red Hat 9 RPM, you
  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 (<link linkend="install-postgres-env">more
  information</link>):</para>
      </formalpara>
  <screen>[root root]# <userinput>ln -s /usr/lib/pgsql/ /var/lib/pgsql/lib</userinput>
[root root]# <userinput>ln -s /var/lib/pgsql /usr/local/pgsql</userinput>
[root root]# <userinput>ln -s /etc/init.d/postgresql /etc/init.d/postgres</userinput>
[root root]# <userinput>ln -s /usr/bin /usr/local/pgsql/bin</userinput>
[root root]# <userinput>service postgresql start</userinput>
Initializing database:
                                                           [  OK  ]
Starting postgresql service:                               [  OK  ]
[root root]# <userinput>echo "export LD_LIBRARY_PATH=/usr/local/pgsql/lib" >> ~postgres/.bash_profile</userinput>
[root root]# <userinput>echo "export PATH=$PATH:/usr/local/pgsql/bin" >> ~postgres/.bash_profile</userinput>
[root root]# <userinput>groupadd web</userinput>
[root root]# <userinput>su - postgres</userinput>
-bash-2.05b$
<action>
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</action></screen>
  <para>... and then skip to <xref linkend="install-plpgsql"/>.  Something similar may work for other binary packages as well.</para>
    </listitem>
    <listitem>
      <para>Safe approach: install from source</para>
  <orderedlist>
    <listitem>
      <formalpara>
        <title>Unpack PostgreSQL 7.4.7.</title>
        <para>If you have not downloaded the postgresql tarball to
        <computeroutput>/var/tmp/postgresql-7.4.7.tar.gz</computeroutput>,
        <link linkend="source-postgresql">get it</link>.</para>
      </formalpara>
      <screen>[root root]# <userinput>cd /usr/local/src</userinput>
[root src]# <userinput>tar xzf /var/tmp/postgresql-7.4.7.tar.gz</userinput>
[root src]# 
<action>cd /usr/local/src
tar xzf /var/tmp/postgresql-7.4.7.tar.gz</action></screen>
    </listitem>
    <listitem>
      <formalpara>
        <title>ALTERNATIVE: Unpack PostgreSQL 7.4.7</title>
        <para>If you have not downloaded the postgresql tarball to
        <computeroutput>/var/tmp/postgresql-7.4.7.tar.bz2</computeroutput>,
        <link linkend="source-postgresql">get it</link>.</para>
      </formalpara>
      <screen>[root root]# <userinput>cd /usr/local/src</userinput>
[root src]# <userinput>tar xfj /var/tmp/postgresql-7.4.7.tar.bz2</userinput>
[root src]# 
<action>cd /usr/local/src
tar xfj /var/tmp/postgresql-7.4.7.tar.bz2</action></screen>
    </listitem>
    <listitem>
      <formalpara>
        <title>Install Bison</title>
        <para>Only do this if <userinput>bison --version</userinput> is smaller than 1.875 and you install PostgreSQL 7.4 from cvs instead of tarball.</para> 
      </formalpara>
      <screen>[root root]# <userinput>cd /usr/local/src</userinput>
[root src]# <userinput>wget http://ftp.gnu.org/gnu/bison/bison-1.875.tar.gz</userinput>
[root src]# <userinput>tar xfz bison-1.875.tar.gz</userinput>
[root src]# <userinput>cd bison-1.875</userinput>
[root src]# <userinput>./configure</userinput>
[root src]# <userinput>make install</userinput>
      </screen>
    </listitem>
    <listitem>
      <formalpara>
        <title>Create the Postgres user</title>
        <para>
	  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.
	</para>
      </formalpara>
        <para>
	  Debian users should probably use adduser instead of
	  useradd. Type <computeroutput>man adduser</computeroutput>
	</para>
      <screen>[root src]# <userinput>groupadd web</userinput>
[root src]# <userinput>useradd -g web -d /usr/local/pgsql postgres</userinput>
[root src]# <userinput>mkdir -p /usr/local/pgsql</userinput>
[root src]# <userinput>chown -R postgres.web /usr/local/pgsql /usr/local/src/postgresql-7.4.7</userinput>
[root src]# <userinput>chmod 750 /usr/local/pgsql</userinput>
[root src]#
<action>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.4.7
chmod 750 /usr/local/pgsql</action></screen>
      <itemizedlist>
        <listitem>
          <formalpara>
            <title>Mac OS X: Do instead:</title>
          <para>First make sure the gids and uids below are available (change them if 
they  are not).To list taken uids and gids:</para>
          </formalpara>
          <screen><userinput>nireport / /groups name gid | grep "[0123456789][0123456789]"
nireport / /users name uid | grep "[0123456789][0123456789]"</userinput>
          </screen>
      <para>Now you can install the users</para>
      <screen><userinput>sudo niutil -create / /groups/web
sudo niutil -createprop / /groups/web gid <replaceable>201</replaceable>
sudo niutil -create / /users/postgres
sudo niutil -createprop / /users/postgres gid <replaceable>201</replaceable>
sudo niutil -createprop / /users/postgres uid <replaceable>502</replaceable>
sudo niutil -createprop / /users/postgres home /usr/local/pgsql
sudo niutil -create / /users/<replaceable>$OPENACS_SERVICE_NAME</replaceable>
sudo niutil -createprop / /users/<replaceable>$OPENACS_SERVICE_NAME</replaceable> gid  <replaceable>201</replaceable>
sudo niutil -createprop / /users/<replaceable>$OPENACS_SERVICE_NAME</replaceable> uid <replaceable>201</replaceable>
mkdir -p /usr/local/pgsql
chown -R postgres:web /usr/local/pgsql /usr/local/src/postgresql-7.4.7
chmod 750 /usr/local/pgsql</userinput></screen>
        </listitem>
        <listitem>
          <formalpara>
            <title>FreeBSD users:</title>
          <para> need to add more parameters.
          </para>
          </formalpara>
      <screen>[root src]# <userinput>mkdir -p /usr/local/pgsql</userinput>
[root src]# <userinput>pw groupadd -n web</userinput>
[root src]# <userinput>pw useradd -n postgres -g web -d /usr/local/pgsql -s /bin/bash</userinput>
[root src]# <userinput>chown -R postgres:web /usr/local/pgsql /usr/local/src/postgresql-7.4.7</userinput>
[root src]# <userinput>chmod -R 750 /usr/local/pgsql</userinput>
[root src]#
<action>mkdir -p /usr/local/pgsql
pw groupadd -n web
pw useradd -n postgres -g web -d /usr/local/pgsql -s /bin/bash
chown -R postgres:web /usr/local/pgsql /usr/local/src/postgresql-7.4.7
chmod -R 750 /usr/local/pgsql</action></screen>
        </listitem>
</itemizedlist>
    </listitem>
    <listitem id="install-postgres-env">
      <formalpara>
        <title>Set up postgres's environment variables</title>
      <para>They are necessary for the executable to find its supporting
	libraries.  Put the following lines into the postgres user's environment.</para>
      </formalpara>
      <screen>[root src]# <userinput>su - postgres</userinput>
[postgres ~] <userinput>emacs ~postgres/.bashrc</userinput></screen>
      <para>Paste this line into <computeroutput>.bash_profile</computeroutput>:</para>
<programlisting>source $HOME/.bashrc</programlisting>
      <para>Paste these lines into <computeroutput>.bashrc</computeroutput>:</para>
<programlisting>export PATH=/usr/local/bin/:$PATH:/usr/local/pgsql/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib</programlisting>
      <para>Test this by logging in as
	<computeroutput>postgres</computeroutput> and checking the
	paths; you should see <computeroutput>/usr/local/pgsql/bin</computeroutput> somewhere in the output (the total output is system-dependent so yours may vary)</para>
      <screen>[root src]# <userinput>su - postgres</userinput>
[postgres pgsql]$ <userinput>env | grep PATH</userinput>
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 pgsql]$ <userinput>exit</userinput>
</screen>

      <para>Don't continue unless you see correct output from
      <computeroutput>env | grep PATH</computeroutput></para>

    </listitem>
    <listitem id="install-postgres-compile">
      <formalpara>
	<title>Compile and install PostgreSQL</title>
	<para>
	  Change to the postgres user and run <computeroutput>./configure</computeroutput> to set the compilation options automatically. This is the point at which you can
	  configure PostgreSQL in various ways. For example, if you are installing on "OS X" add the flags <computeroutput> --with-includes=/sw/include/ --with-libraries=/sw/lib</computeroutput>. If you want to see what the other possibilities are, run <computeroutput>./configure --help</computeroutput>.
	</para>
      </formalpara>
          <para>On debian woody (stable, 3.0), do <computeroutput>./configure --without-readline --without-zlib</computeroutput>.</para>
      <screen>[root src]# <userinput>su - postgres</userinput>
[postgres pgsql]$<userinput> cd /usr/local/src/postgresql-7.4.7</userinput>
[postgres postgresql-7.4.7]$ <userinput>./configure</userinput>
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 postgresql-7.4.7]$ <userinput>make all</userinput>
make -C doc all
make[1]: Entering directory `/usr/local/src/postgresql-7.4.7/doc'
(many lines omitted)
make[1]: Leaving directory `/usr/local/src/postgresql-7.4.7/src'
All of PostgreSQL successfully made. Ready to install.
[postgres postgresql-7.4.7]$ <userinput>make install</userinput>
make -C doc install
make[1]: Entering directory `/usr/local/src/postgresql-7.4.7/doc'
(many lines omitted)
Thank you for choosing PostgreSQL, the most advanced open source database
engine.
<action>su - postgres
cd /usr/local/src/postgresql-7.4.7
./configure 
make all
make install</action></screen>
    </listitem>
    <listitem id="install-postgres-startup">
      <formalpara>
	<title>Start PostgreSQL</title>
	<para>
	  The <computeroutput>initdb</computeroutput> command initializes the
	  database. <computeroutput>pg_ctl</computeroutput> is used to start up
	  PostgreSQL. If PostgreSQL is unable to allocate enough memory, see section 11
          Tuning PostgreSQL (below).
	</para>
      </formalpara>
      <screen>[postgres postgresql-7.4.7]$ <userinput>/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data</userinput>
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 postgresql-7.4.7]$ <userinput>/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/data/server.log start</userinput>
postmaster successfully started
[postgres postgresql-7.4.7]$
<action>/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</action></screen>
	<para>
	  PostgreSQL errors will be logged in
	  <computeroutput>/usr/local/pgsql/data/server.log</computeroutput>
	</para>
    </listitem>
    <listitem id="install-plpgsql">
      <formalpara>
        <title>Install Pl/pgSQL</title>
      <para>Set up plpgsq and allow your user to have
	  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.</para>
      </formalpara>
      <screen>[postgres postgresql-7.4.7]$ <userinput>createlang plpgsql template1</userinput>
[postgres pgsql]$ <userinput>createlang -l template1</userinput>
Procedural languages
  Name   | Trusted?
---------+----------
 plpgsql | t
(1 row)

[postgres pgsql-7.4.7]$
<action>createlang plpgsql template1
createlang -l template1</action></screen>
    </listitem>
    <listitem id="install-postgres-test">
      <formalpara>
        <title>Test PostgreSQL (OPTIONAL).</title>
        <para>Create a database and try some simple commands. The output should be as shown.
	</para>
      </formalpara>
      <screen>[postgres pgsql]$ <userinput>createdb mytestdb</userinput>
CREATE DATABASE
[postgres pgsql]$ <userinput>psql mytestdb</userinput>
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

mytestdb=# <userinput>select current_timestamp;</userinput>
          timestamptz
-------------------------------
 2003-03-07 22:18:29.185413-08
(1 row)

mytestdb=# <userinput>create function test1() returns integer as 'begin return 1; end;' language 'plpgsql';</userinput>
CREATE
mytestdb=#<userinput> select test1();</userinput>
 test1
-------
     1
(1 row)

mytestdb=# <userinput>\q</userinput>
[postgres pgsql]$<userinput> dropdb mytestdb</userinput>
DROP DATABASE
[postgres pgsql]$ <userinput>exit</userinput>
logout

[root src]#</screen>
  </listitem>
  <listitem id="install-postgres-startonboot">
    <para>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.  If you haven't <link linkend="install-from-tarball"> untarred the OpenACS tarball</link>, you will need to do so now to access the postgresql.txt file.
	</para>
      <itemizedlist>
        <listitem><para>Red Hat RPM:</para>
          <para>The init script is already installed; just turn it on for the appropriate run levels.</para>
          <screen>[root root]# <userinput>chkconfig --level 345 postgresql on</userinput>
[root root]# </screen>
        </listitem>
        <listitem><para>Red Hat from source:</para>
          
          <screen>[root src]# <userinput>cp /var/tmp/&tarballpath;/packages/acs-core-docs/www/files/postgresql.txt /etc/init.d/postgresql</userinput>
[root src]# <userinput>chown root.root /etc/rc.d/init.d/postgresql</userinput>
[root src]# <userinput>chmod 755 /etc/rc.d/init.d/postgresql</userinput>
[root src]# 
<action>cp /var/tmp/&tarballpath;/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</action></screen>
          
          <para>Test the script.</para>
          
          <screen>[root root]# <userinput>service postgresql stop</userinput>
Stopping PostgreSQL: ok
[root root]# </screen>

          <para>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.

		</para>
          
		<screen>[root root]# <userinput>chkconfig --add postgresql</userinput>
[root root]# <userinput>chkconfig --level 345 postgresql on</userinput>
[root root]# <userinput>chkconfig --list postgresql</userinput>
postgresql      0:off   1:off   2:on    3:on    4:on    5:on    6:off
[root root]# <userinput>service postgresql start</userinput>
Starting PostgreSQL: ok
[root root]#
<action>chkconfig --add postgresql
chkconfig --level 345 postgresql on
chkconfig --list postgresql
service postgresql start</action></screen>
        </listitem>
        <listitem><para>Debian:</para>
          <screen>[root ~]# <userinput>cp /var/tmp/packages/acs-core-docs/www/files/postgresql.txt /etc/init.d/postgresql</userinput>
[root ~]# <userinput>chown root.root /etc/init.d/postgresql</userinput>
[root ~]# <userinput>chmod 755 /etc/init.d/postgresql</userinput>
[root ~]# <action>
cp /var/tmp/&tarballpath;/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</action></screen>
          <para>Test the script</para>
          
          <screen>[root ~]# <userinput>/etc/init.d/postgresql stop</userinput>
Stopping PostgreSQL: ok
[root ~]# </screen>
          
          <para>If PostgreSQL successfully stopped, then use the following
		  command to make sure that the script is run
		  appropriately at boot and shutdown.</para>

		<screen>
[root ~]# <userinput>update-rc.d postgresql defaults</userinput>
 Adding system startup for /etc/init.d/postgresql ...
   /etc/rc0.d/K20postgresql -> ../init.d/postgresql
   /etc/rc1.d/K20postgresql -> ../init.d/postgresql
   /etc/rc6.d/K20postgresql -> ../init.d/postgresql
   /etc/rc2.d/S20postgresql -> ../init.d/postgresql
   /etc/rc3.d/S20postgresql -> ../init.d/postgresql
   /etc/rc4.d/S20postgresql -> ../init.d/postgresql
   /etc/rc5.d/S20postgresql -> ../init.d/postgresql
[root ~]# <userinput>/etc/init.d/postgresql start</userinput>
Starting PostgreSQL: ok
[root ~]#</screen>
        </listitem>

        <listitem><para>FreeBSD:</para>
          <screen>[root ~]# <userinput>cp /tmp/&tarballpath;/packages/acs-core-docs/www/files/postgresql.txt /usr/local/etc/rc.d/postgresql.sh</userinput>
[root ~]# <userinput>chown root:wheel /usr/local/etc/rc.d/postgresql.sh</userinput>
[root ~]# <userinput>chmod 755 /usr/local/etc/rc.d/postgresql.sh</userinput>
[root ~]# <action>
cp /tmp/&tarballpath;/packages/acs-core-docs/www/files/postgresql.txt /usr/local/etc/rc.d/postgresql.sh
chown root:wheel /usr/local/etc/rc.d/postgresql.sh
chmod 755 /usr/local/etc/rc.d/postgresql.sh</action></screen>
          <para>Test the script</para>
          
          <screen>[root ~]# <userinput>/usr/local/etc/rc.d/postgresql.sh stop</userinput>
Stopping PostgreSQL: ok
[root ~]# </screen>
          <para>If PostgreSQL successfully stopped, then turn it back on because we'll use
		  it later.</para>
          
		<screen>[root root]# <userinput>/usr/local/etc/rc.d/postgresql.sh start</userinput>
Starting PostgreSQL: ok
[root root]#
<action>/usr/local/etc/rc.d/postgresql.sh start</action></screen>
        </listitem>
        <listitem><para>SuSE:</para>
          
          <note>
            <para>

			I have received reports that SuSE 8.0 is different from
            previous versions. Instead of installing the boot scripts in
            <computeroutput>/etc/rc.d/init.d/</computeroutput>, they should
            be placed in <computeroutput>/etc/init.d/</computeroutput>. If
            you're using SuSE 8.0, delete the
            <computeroutput>rc.d/</computeroutput> part in each of the
            following commands.

          </para>
          </note>
          
          <screen>[root ~]# <userinput>cp /var/tmp/&tarballpath;/packages/acs-core-docs/www/files/postgresql.txt /etc/rc.d/init.d/postgresql</userinput>
[root ~]# <userinput>chown root.root /etc/rc.d/init.d/postgresql</userinput>
[root ~]# <userinput>chmod 755 /etc/rc.d/init.d/postgresql</userinput></screen>
          <para>

          Test the script.

        </para>

        <screen>[root ~]# <userinput>/etc/rc.d/init.d/postgresql stop</userinput>
Stopping PostgreSQL: ok</screen>
        
        <para>

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

        </para>
          
          <screen>[root ~]# <userinput>cd /etc/rc.d/init.d</userinput>
root:/etc/rc.d/init.d# <userinput>ln -s /etc/rc.d/init.d/postgresql K20postgresql</userinput>
root:/etc/rc.d/init.d# <userinput>ln -s /etc/rc.d/init.d/postgresql S20postgresql  </userinput>
root:/etc/rc.d/init.d# <userinput>cp K20postgresql rc2.d</userinput>
root:/etc/rc.d/init.d# <userinput>cp S20postgresql rc2.d</userinput>
root:/etc/rc.d/init.d# <userinput>cp K20postgresql rc3.d</userinput>
root:/etc/rc.d/init.d# <userinput>cp S20postgresql rc3.d</userinput>
root:/etc/rc.d/init.d# <userinput>cp K20postgresql rc4.d</userinput>
root:/etc/rc.d/init.d# <userinput>cp S20postgresql rc4.d </userinput>
root:/etc/rc.d/init.d# <userinput>cp K20postgresql rc5.d</userinput>
root:/etc/rc.d/init.d# <userinput>cp S20postgresql rc5.d</userinput>
root:/etc/rc.d/init.d# <userinput>rm K20postgresql</userinput>
root:/etc/rc.d/init.d# <userinput>rm S20postgresql</userinput>
root:/etc/rc.d/init.d# </screen>
          
          <para>

          Test configuration.

        </para>
          
          <screen>root:/etc/rc.d/init.d # <userinput>cd</userinput>
root:~ # <userinput>/etc/rc.d/init.d/rc2.d/S20postgresql start</userinput>
Starting PostgreSQL: ok
root:~ # </screen>
        </listitem>
        <listitem>
          <para>Mac OS X 10.3:</para>
          <orderedlist>
            <listitem>
              <para>Install the startup script:</para>
              <screen><userinput>cd /System/Library/StartupItems/</userinput>
<userinput>tar xfz /var/lib/aolserver/<replaceable>$OPENACS_SERVICE_NAME</replaceable>/packages/acs-core-docs/www/files/osx-postgres-startup-item.tgz</userinput>
</screen>
            </listitem>
          </orderedlist>
        </listitem>
        <listitem>
          <para>Mac OS X 10.4 can use Launchd:</para>
          <orderedlist>
            <listitem>
              <para>Install the startup script:</para>
              <screen><userinput>cd /Library/LaunchDaemons</userinput>
<userinput>cp
/var/lib/aolserver/<replaceable>$OPENACS_SERVICE_NAME</replaceable>/packages/acs-core-docs/www/files/osx-postgres-launchd-item.txt
org.postgresql.PostgreSQL.plist</userinput>
</screen>
    <para>If postgres does not start automatically on reboot, see what
    error you get when manually starting it with:</para>
  <screen>
$ <userinput>sudo launchctl load /Library/LaunchDaemons/org.postgresql.PostgreSQL.plist</userinput>
$ <userinput>sudo launchctl start org.postgresql.PostgreSQL</userinput></screen>
            </listitem>
          </orderedlist>
        </listitem>

      </itemizedlist>
      
      <para>

      From now on, PostgreSQL should start automatically each time you boot
      up and it should shutdown gracefully each time you shut down. (Note:
      Debian defaults to starting all services on runlevels 2-5. Red Hat
      defaults to starting services on 3-5. So, on Red Hat, PostgreSQL won't
      start on runlevel 2 unless you alter the above commands a
      little. This usually isn't a problem as Red Hat defaults to runlevel 3)

    </para>
    </listitem>
    <listitem>
      <formalpara id="postgres-tune">
        <title>Tune postgres.  (OPTIONAL)</title>
        <para>The default values for PostgreSQL are very conservative; we can safely change some of them and improve performance.</para>  
      </formalpara>
      <orderedlist>
        <listitem>
          <para>Change the kernel parameter for maximum shared memory
          segment size to 128Mb:</para>
          <screen>[root root]# <userinput>echo 134217728 &gt;/proc/sys/kernel/shmmax</userinput>
[root root]#</screen>
          <para>Make that change permanent by editing
          <computeroutput>/etc/sysctl.conf</computeroutput> to
          add these lines at the end:</para>
<programlisting># increase shared memory limit for postgres
kernel.shmmax = 134217728</programlisting>
        </listitem>
        <listitem>
          <para>Edit the PostgreSQL config file, <computeroutput>/usr/local/pgsql/data/postgresql.conf</computeroutput>, to use more memory.  These values should improve performance in most cases.  (<ulink url="http://openacs.org/forums/message-view?message_id=94071">more information</ulink>)</para>
          <programlisting>#       Shared Memory Size
#
shared_buffers = 15200      # 2*max_connections, min 16

#       Non-shared Memory Sizes
#
sort_mem = 32168            # min 32


#       Write-ahead log (WAL)
#
checkpoint_segments = 3     # in logfile segments (16MB each), min 1
</programlisting>
          <para>Restart postgres (<computeroutput>service postgresql
          restart</computeroutput>) or
          (<computeroutput>/etc/init.d/postgres
          restart</computeroutput>) so that the changes take effect.</para>
        </listitem>
      </orderedlist>

      <para>FreeBSD users: See <userinput>man syctl</userinput>, <userinput>man 5 sysctl</userinput>
      and <userinput>man 5 loader.conf</userinput>.</para>

      <para>Performance tuning resources:</para>

    <itemizedlist>
      <listitem><para>
        <ulink url="http://www.postgresql.org/docs/7.4/interactive/kernel-resources.html">Managing Kernel Resources</ulink>
        about PostgreSQL shared memory and semaphores with specific operating system notes.
        </para></listitem>
      <listitem><para>
        <ulink url="http://developer.postgresql.org/docs/postgres/kernel-resources.html">Managing Kernel Resources (development version)</ulink>
          This information may be experimental.
        </para></listitem>
        <listitem><para>
        <ulink url="http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html">Tuning PostgreSQL for performance</ulink></para>
        </listitem>
      </itemizedlist>
    </listitem>
  </orderedlist>
</listitem>
</itemizedlist>

<sect2 id="install-postgres-moreinfo">
    <title>more information about PostgreSQL</title>
    
    <itemizedlist>
      <listitem><para>

          <ulink url="http://www.postgresql.org/idocs/">Official PostgreSQL
          Docs</ulink>

        </para></listitem>

      <listitem><para>


          <ulink
          url="http://pascal.scheffers.net/openacs/pgupdate/">Migrating
          from 7.0 to 7.1</ulink>

        </para></listitem>

      <listitem><para>

          <ulink
            url="http://techdocs.postgresql.org">techdocs.postgresql.org</ulink>

        </para></listitem>

      <listitem><para>
          
          <ulink
        url="http://www.linuxjournal.com/article.php?sid=4791">PostgreSQL
        Performance Tuning</ulink>

        </para></listitem>

    </itemizedlist>
  <para><phrase role="cvstag">($Id: postgres.xml,v 1.35 2006/07/17 05:38:38 torbenb Exp $)</phrase></para>
  </sect2>
</sect1>
