Bug 297475: Add docs about how to install and configure Bugzilla using PostgreSQL

Patch by Colin Ogilvie <colin.ogilvie@gmail.com> (with info from mkanat) r=mkanat

1 files changed, 205 insertions, 131 deletions

diff --git a/docs/en/xml/installation.xml b/docs/en/xml/installation.xml
index 6c511ad31..851cf11b7 100644
--- a/docs/en/xml/installation.xml
+++ b/docs/en/xml/installation.xml
@@ -1,5 +1,5 @@
 <!-- <!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.1.2//EN"> -->
-<!-- $Id: installation.xml,v 1.101 2008/04/04 06:47:31 mozilla%colinogilvie.co.uk Exp $ -->
+<!-- $Id: installation.xml,v 1.102 2008/04/04 06:47:32 mozilla%colinogilvie.co.uk Exp $ -->
 <chapter id="installing-bugzilla">
   <title>Installing Bugzilla</title>
 
@@ -95,7 +95,7 @@
       If you don't have it and your OS doesn't provide official packages, 
       visit <ulink url="http://www.perl.com"/>.
       Although Bugzilla runs with Perl &min-perl-ver;,
-      it's a good idea to be using the latest stable version. 
+      it's a good idea to be using the latest stable version.
       </para>
     </section>
 
@@ -611,174 +611,248 @@
       </para>
     </section>
     
-    <section id="mysql">
-      <title>MySQL</title>
+    <section id="database-engine">
+      <title>Database Server</title>
+      <para>This section deals with configuring your database server for use
+      with Bugzilla. Currently <xref linkend="mysql"/> and
+      <xref linkend="postgresql"/> are available.</para>
+      
+      <section id="mysql">
+        <title>MySQL</title>
 
-      <caution>
-        <para>
-          MySQL's default configuration is very insecure.
-          <xref linkend="security-mysql"/> has some good information for
-          improving your installation's security.
-        </para>
-      </caution>
-     
-      <section id="install-setupdatabase">
-        <title>Allow large attachments</title>
+        <caution>
+          <para>
+            MySQL's default configuration is very insecure.
+            <xref linkend="security-mysql"/> has some good information for
+            improving your installation's security.
+          </para>
+        </caution>
         
-        <para>
-          By default, MySQL will only accept packets up to 64Kb in size.
-          If you want to have attachments larger than this, you will need
-          to modify your <filename>/etc/my.cnf</filename> as below.
-        </para>
+        <section id="install-setupdatabase">
+          <title>Allow large attachments</title>
+        
+          <para>
+            By default, MySQL will only accept packets up to 64Kb in size.
+            If you want to have attachments larger than this, you will need
+            to modify your <filename>/etc/my.cnf</filename> as below.
+          </para>
 
-        <para>
-          If you are using MySQL 4.0 or newer, enter:
-        </para>
-        <screen>  [mysqld]
+          <para>
+            If you are using MySQL 4.0 or newer, enter:
+          </para>
+          <screen>  [mysqld]
   # Allow packets up to 1M
   max_allowed_packet=1M</screen>
 
-        <para>
-          If you are using an older version of MySQL, enter:
-        </para>
-        <screen>  [mysqld]
+          <para>
+            If you are using an older version of MySQL, enter:
+          </para>
+          <screen>  [mysqld]
   # Allow packets up to 1M
   set-variable = max_allowed_packet=1M</screen>
 
-        <para>
-          There is also a parameter in Bugzilla called 'maxattachmentsize'
-          (default = 1000 Kb) that controls the maximum allowable attachment
-          size. Attachments larger than <emphasis>either</emphasis> the 
-          'max_allowed_packet' or 'maxattachmentsize' value will not be
-          accepted by Bugzilla.
-        </para>
-
-        <note>
           <para>
-            This does not affect Big Files, attachments that are stored directly
-            on disk instead of in the database.  Their maximum size is
-            controlled using the 'maxlocalattachment' parameter.
+            There is also a parameter in Bugzilla called 'maxattachmentsize'
+            (default = 1000 Kb) that controls the maximum allowable attachment
+            size. Attachments larger than <emphasis>either</emphasis> the 
+            'max_allowed_packet' or 'maxattachmentsize' value will not be
+            accepted by Bugzilla.
           </para>
-        </note>
-      </section>
 
+          <note>
+            <para>
+              This does not affect Big Files, attachments that are stored directly
+              on disk instead of in the database.  Their maximum size is
+              controlled using the 'maxlocalattachment' parameter.
+            </para>
+          </note>
+        </section>
+        
+        <section>
+          <title>Allow small words in full-text indexes</title>
 
+          <para>By default, words must be at least four characters in length
+          in order to be indexed by MySQL's full-text indexes. This causes
+          a lot of Bugzilla specific words to be missed, including "cc",
+          "ftp" and "uri".</para>
 
-      <section>
-        <title>Allow small words in full-text indexes</title>
-
-        <para>By default, words must be at least four characters in length
-        in order to be indexed by MySQL's full-text indexes. This causes
-        a lot of Bugzilla specific words to be missed, including "cc",
-        "ftp" and "uri".</para>
-
-        <para>MySQL can be configured to index those words by setting the
-        ft_min_word_len param to the minimum size of the words to index.
-        This can be done by modifying the <filename>/etc/my.cnf</filename>
-        according to the example below:</para>
+          <para>MySQL can be configured to index those words by setting the
+          ft_min_word_len param to the minimum size of the words to index.
+          This can be done by modifying the <filename>/etc/my.cnf</filename>
+          according to the example below:</para>
 
-        <screen>  [mysqld]
+          <screen>  [mysqld]
   # Allow small words in full-text indexes
   ft_min_word_len=2</screen>
 
-        <para>Rebuilding the indexes can be done based on documentation found at
-        <ulink url="http://www.mysql.com/doc/en/Fulltext_Fine-tuning.html"/>.
-        </para>
+          <para>Rebuilding the indexes can be done based on documentation found at
+          <ulink url="http://www.mysql.com/doc/en/Fulltext_Fine-tuning.html"/>.
+          </para>
+
+          <note>
+            <para>
+              The ft_min_word_len parameter is only suported in MySQL v4 or higher.
+            </para>
+          </note>
+        </section>
+        
+        <section>
+          <title>Permit attachments table to grow beyond 4GB</title>
 
-        <note>
           <para>
-            The ft_min_word_len parameter is only suported in MySQL v4 or higher.
+            By default, MySQL will limit the size of a table to 4GB.
+            This limit is present even if the underlying filesystem
+            has no such limit.  To set a higher limit, follow these
+            instructions.
           </para>
-        </note>
-      </section>
 
-      <section>
-        <title>Permit attachments table to grow beyond 4GB</title>
+          <para>
+            Run the <filename>MySQL</filename> command-line client and
+            enter:
+          </para>
 
-        <para>
-          By default, MySQL will limit the size of a table to 4GB.
-          This limit is present even if the underlying filesystem
-          has no such limit.  To set a higher limit, follow these
-          instructions.
-        </para>
+          <screen>  <prompt>mysql&gt;</prompt> ALTER TABLE attachments 
+            AVG_ROW_LENGTH=1000000, MAX_ROWS=20000;
+          </screen>
 
-        <para>
-          Run the <filename>MySQL</filename> command-line client and
-          enter:
-        </para>
+          <para>
+            The above command will change the limit to 20GB. Mysql will have 
+            to make a temporary copy of your entire table to do this. Ideally, 
+            you should do this when your attachments table is still small.
+          </para>
 
-        <screen>  <prompt>mysql&gt;</prompt> ALTER TABLE attachments 
-          AVG_ROW_LENGTH=1000000, MAX_ROWS=20000;
-        </screen>
+          <note>
+            <para>
+              This does not affect Big Files, attachments that are stored directly
+              on disk instead of in the database.
+            </para>
+          </note>
+        </section>
+        
+        <section id="install-setupdatabase-adduser">
+          <title>Add a user to MySQL</title>
 
-        <para>
-          The above command will change the limit to 20GB. Mysql will have 
-          to make a temporary copy of your entire table to do this. Ideally, 
-          you should do this when your attachments table is still small.
-        </para>
+          <para>
+            You need to add a new MySQL user for Bugzilla to use.
+            (It's not safe to have Bugzilla use the MySQL root account.)
+            The following instructions assume the defaults in
+            <filename>localconfig</filename>; if you changed those,
+            you need to modify the SQL command appropriately. You will
+            need the <replaceable>$db_pass</replaceable> password you
+            set in <filename>localconfig</filename> in 
+            <xref linkend="localconfig"/>.
+          </para>
 
-        <note>
           <para>
-            This does not affect Big Files, attachments that are stored directly
-            on disk instead of in the database.
+            We use an SQL <command>GRANT</command> command to create
+            a <quote>bugs</quote> user. This also restricts the 
+            <quote>bugs</quote>user to operations within a database
+            called <quote>bugs</quote>, and only allows the account
+            to connect from <quote>localhost</quote>. Modify it to
+            reflect your setup if you will be connecting from another
+            machine or as a different user.
           </para>
-        </note>
+        
+          <para>
+            Run the <filename>mysql</filename> command-line client.
+          </para>
+
+          <para>
+            If you are using MySQL 4.0 or newer, enter:
+          </para>
+
+          <screen>  <prompt>mysql&gt;</prompt> GRANT SELECT, INSERT,
+           UPDATE, DELETE, INDEX, ALTER, CREATE, LOCK TABLES,
+           CREATE TEMPORARY TABLES, DROP, REFERENCES ON bugs.*
+           TO bugs@localhost IDENTIFIED BY '<replaceable>$db_pass</replaceable>';
+           <prompt>mysql&gt;</prompt> FLUSH PRIVILEGES;</screen>
+
+          <para>
+            If you are using an older version of MySQL,the
+            <computeroutput>LOCK TABLES</computeroutput> and 
+            <computeroutput>CREATE TEMPORARY TABLES</computeroutput>
+            permissions will be unavailable and should be removed from
+            the permissions list. In this case, the following command
+            line can be used:
+          </para>
+
+          <screen>  <prompt>mysql&gt;</prompt> GRANT SELECT, INSERT,
+           UPDATE, DELETE, INDEX, ALTER, CREATE, DROP,
+           REFERENCES ON bugs.* TO bugs@localhost IDENTIFIED BY
+           '<replaceable>$db_pass</replaceable>';
+           <prompt>mysql&gt;</prompt> FLUSH PRIVILEGES;</screen>
+        </section>      
       </section>
-            
-      <section id="install-setupdatabase-adduser">
-        <title>Add a user to MySQL</title>
+      
+      <section id="postgresql">
+        <title>PostgreSQL</title>
+        <note>
+          <para>Note if you are using PostgreSQL 8.0.1 or higher, then you
+          will require to use a version of DBD::Pg which is equal to or
+          greater than version 1.41
+          </para>
+        </note>
+        
+        <section>
+          <title>Add a User to PostgreSQL</title>
 
-        <para>
-          You need to add a new MySQL user for Bugzilla to use.
-          (It's not safe to have Bugzilla use the MySQL root account.)
-          The following instructions assume the defaults in
-          <filename>localconfig</filename>; if you changed those,
-          you need to modify the SQL command appropriately. You will
+          <para>You need to add a new user to PostgreSQL for the Bugzilla
+          application to use when accessing the database. The following instructions
+          assume the defaults in <filename>localconfig</filename>; if you
+          changed those, you need to modify the commands appropriately. You will
           need the <replaceable>$db_pass</replaceable> password you
           set in <filename>localconfig</filename> in 
-          <xref linkend="localconfig"/>.
-        </para>
+          <xref linkend="localconfig"/>.</para>
 
-        <para>
-          We use an SQL <command>GRANT</command> command to create
-          a <quote>bugs</quote> user. This also restricts the 
-          <quote>bugs</quote>user to operations within a database
-          called <quote>bugs</quote>, and only allows the account
-          to connect from <quote>localhost</quote>. Modify it to
-          reflect your setup if you will be connecting from another
-          machine or as a different user.
-        </para>
+          <para>On most systems, to create the user in PostgreSQL, you will need to
+          login as the root user, and then</para>
+
+          <screen> <prompt>bash#</prompt> su - postgres</screen>
+
+          <para>As the postgres user, you then need to create a new user: </para>
+            
+          <screen> <prompt>bash$</prompt> createuser -U postgres -dAP bugs</screen>
+ 
+          <para>When asked for a password, provide the password which will be set as
+          <replaceable>$db_pass</replaceable> in <filename>localconfig</filename>.
+          The created user will have the ability to create databases and will not be
+          able to create new users.</para>
+        </section>
         
-        <para>
-          Run the <filename>mysql</filename> command-line client.
-        </para>
+        <section>
+          <title>Configure PostgreSQL</title>
 
-        <para>
-          If you are using MySQL 4.0 or newer, enter:
-        </para>
+          <para>Now, you will need to edit <filename>pg_hba.conf</filename> which is
+          usually located in <filename>/var/lib/pgsql/data/</filename>. In this file,
+          you will need to add a new line to it as follows:</para>
 
-        <screen>  <prompt>mysql&gt;</prompt> GRANT SELECT, INSERT,
-         UPDATE, DELETE, INDEX, ALTER, CREATE, LOCK TABLES,
-         CREATE TEMPORARY TABLES, DROP, REFERENCES ON bugs.*
-         TO bugs@localhost IDENTIFIED BY '<replaceable>$db_pass</replaceable>';
-  <prompt>mysql&gt;</prompt> FLUSH PRIVILEGES;</screen>
+          <computeroutput>host   all    bugs   127.0.0.1    255.255.255.255  md5</computeroutput>
 
-        <para>
-          If you are using an older version of MySQL,the
-          <computeroutput>LOCK TABLES</computeroutput> and 
-          <computeroutput>CREATE TEMPORARY TABLES</computeroutput>
-          permissions will be unavailable and should be removed from
-          the permissions list. In this case, the following command
-          line can be used:
-        </para>
+          <para>This means that for TCP/IP (host) connections, allow connections from
+          '127.0.0.1' to 'all' databases on this server from the 'bugs' user, and use
+          password authentication (md5) for that user.</para>
 
-        <screen>  <prompt>mysql&gt;</prompt> GRANT SELECT, INSERT,
-         UPDATE, DELETE, INDEX, ALTER, CREATE, DROP,
-         REFERENCES ON bugs.* TO bugs@localhost IDENTIFIED BY
-         '<replaceable>$db_pass</replaceable>';
-  <prompt>mysql&gt;</prompt> FLUSH PRIVILEGES;</screen>
-      </section>      
-    </section>
+          <para>If you are using <emphasis role="bold">versions of PostgreSQL
+          before version 8</emphasis>, you may also need to edit <filename>postgresql.conf</filename>
+          , also usually found in the <filename>/var/lib/pgsql/data/</filename> folder.
+          You will need to make a single line change, changing</para>
+
+          <computeroutput># tcpip_socket = false</computeroutput>
+
+          <para>to</para>
+            
+          <computeroutput>tcpip_socket = true</computeroutput>
+            
+          <para>Now, you will need to restart PostgreSQL, but you will need to fully
+          stop and start the server rather than just restarting due to the possibility
+          of a change to <filename>postgresql.conf</filename>. After the server has
+          restarted, you will need to edit <filename>localconfig</filename>, finding
+          the <literal>$db_driver</literal> variable and setting it to
+          <literal>Pg</literal> and changing the password in <literal>$db_pass</literal>
+          to the one you picked previously, while setting up the account.</para> 
+        </section>
+      </section>
+    </section>  
 
     <section>
       <title>checksetup.pl</title>