<?xml version='1.0' ?>
<!DOCTYPE chapter 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;
]>
<chapter id="database-management">
    <title>Database Management</title>

    <authorblurb>
      <para>By <ulink url="mailto:joel@aufrecht.org">Joel Aufrecht</ulink></para>
    </authorblurb>

    <sect1 id="remote-postgres">
      <title>Running a PostgreSQL database on another server</title>

      <para>To run a database on a different machine than the
      webserver requires changes to the database configuration file
      and access control file, and to the OpenACS service's
      configuration file.</para>
      <itemizedlist>
        <listitem>
          <para>Edit the database configuration file, which in a
      Reference install is located at <computeroutput>/usr/local/pgsql/data/postgresql.conf</computeroutput>
      and change</para>
      <programlisting>#tcpip_socket = false</programlisting>
<para>to</para>
<programlisting>tcpip_socket = true</programlisting>
        </listitem>
        <listitem>
          <para>Change the access control file for the database to
          permit specific remote clients to access.  Access can be
          controlled ... (add notes from forum post) </para>
        </listitem>
        <listitem>
          <para>Change the OpenACS service's configuration file to
          point to the remote database.  Edit
          <computeroutput>/var/lib/aolserver/<replaceable>$OPENACS_SERVICE_NAME</replaceable>/etc/config.tcl</computeroutput>
          and change</para>
          <programlisting></programlisting>
          <para>to </para>
          <programlisting></programlisting>
        </listitem>
      </itemizedlist>
    </sect1>
    
    <sect1 id="install-openacs-delete-tablespace">
      <title>Deleting a tablespace</title>
      
    <para>Skip down for instructions on <xref linkend="install-openacs-delete-postgres-tablespace"/>.
    </para>

    <sect2 id="install-openacs-delete-oracle-tablespace">
      <title>Deleting an Oracle tablespace</title>

      <para>
        Should it become necessary to rebuild a tablespace from scratch,
        you can use the <computeroutput>drop user</computeroutput> command
        in SVRMGRL with the <computeroutput>cascade</computeroutput>
        option. This command will drop the user and every database object
        the user owns.</para>

      <programlisting>SVRMGR&gt; <userinput>drop user <replaceable>$OPENACS_SERVICE_NAME</replaceable> cascade;</userinput></programlisting>

      <para>
        If this does not work because svrmgrl &quot;cannot drop a user that
        is currently connected&quot;, make sure to kill the AOLserver using
        this user. If it still does not work, do:</para>

      <programlisting>SVRMGR&gt; <userinput>select username, sid, serial# from v$session where lower(username)=&#39;<replaceable>$OPENACS_SERVICE_NAME</replaceable>&#39;;</userinput></programlisting>

      <para>and then</para>
      <programlisting>SVRMGR&gt; <userinput>alter system kill session &#39;<replaceable>sid, serial#</replaceable>&#39;;</userinput></programlisting>

      <para>
        where <emphasis>sid</emphasis> and <emphasis>serial#</emphasis> are
        replaced with the corresponding values for the open session.</para>

      <para><emphasis role="strong">Use with caution!</emphasis></para>

      <para>
        If you feel the need to delete <emphasis>everything</emphasis>
        related to the service, you can also issue the following:</para>
      <programlisting>SVRMGR&gt; <userinput>drop tablespace <replaceable>$OPENACS_SERVICE_NAME</replaceable> including contents cascade constraints;</userinput></programlisting>   
    </sect2>

    <sect2 id="install-openacs-delete-postgres-tablespace" xreflabel="Deleting a PostgreSQL tablespace">
      <title>Deleting a PostgreSQL tablespace</title>

      <para>
        Dropping a PostgreSQL tablespace is easy. You have to stop any
        AOLserver instances that are using the database that you wish to
        drop. If you're using daemontools, this is simple, just use the
        'down' flag (-d). If you're using inittab, you have to comment out
        your server in <computeroutput>/etc/inittab</computeroutput>,
        reread the inittab with <computeroutput>/sbin/init
        q</computeroutput>, and then <computeroutput>restart-aolserver
        <replaceable>$OPENACS_SERVICE_NAME</replaceable></computeroutput>.</para>
    
      <para>Then, to drop the db, just do:</para>
      <programlisting>
[$OPENACS_SERVICE_NAME ~]$ <userinput>dropdb <replaceable>$OPENACS_SERVICE_NAME</replaceable></userinput>
DROP DATABASE</programlisting>
    </sect2>
  </sect1>
  <sect1 id="install-next-nightly-vacuum">
    <title>Vacuum Postgres nightly</title>
    <para>
      The "vacuum" command must be run periodically to reclaim space
      in versions of PostgreSQL before 7.4. The "vacuum analyze" form
      additionally collects statistics on the 
      disbursion of columns in the database, which the optimizer uses when
      it calculates just how to execute queries. The availability of this
      data can make a tremendous difference in the execution speed of
      queries. This command can also be run from cron, but it probably makes
      more sense to run this command as part of your nightly backup
      procedure - if "vacuum" is going to screw up the database, you'd
      prefer it to happen immediately after (not before!) you've made a
      backup! The "vacuum" command is very reliable, but conservatism is
      the key to good system management. So, if you're using the export
      procedure described above, you don't need to do this extra
      step.
    </para>

    <para>Edit your crontab:</para>
    <programlisting>[joeuser ~]$ <userinput>crontab -e</userinput></programlisting>

    <para>We'll set vacuum up to run nightly at 1 AM. Add the following
    line:</para>
    <programlisting>
0 1 * * * /usr/local/pgsql/bin/vacuumdb <replaceable>$OPENACS_SERVICE_NAME</replaceable></programlisting>
    <para><phrase role="cvstag">($Id: database-maintenance.xml,v 1.8 2006/07/17 05:38:37 torbenb Exp $)</phrase></para>

  </sect1>

</chapter>
    
  <!--
  Local Variables:
   sgml-parent-document: ("../index.xml" "book" "chapter")
  End:
  -->
