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

Status: RESOLVED FIXED

(Bugzilla :: Documentation, defect)

Description

[Frédéric Buclin]

I installed the PostgreSQL RPM packages (server and client) as well as perl-Pg.
But now, I have no idea what to do!! (don't tell me this is not the right place
to get support!!! :-p )

Colin pointed me to bug 98304 comment 133. Great! Here is was I read:

"The steps for setting up a Bugzilla installation on PostgreSQL are very
simple: [this is mkanat's humor!]

(0) Connect to the template1 database in PostgreSQL as a PostgreSQL
superuser. (That's usually the "postgres" user.) [...]"

Great, and how do I connect to the DB?? I mean, with which program? I tried
'psql' and 'pg_ctl' unsuccessfully. If it's really 'psql', what arguments should
I pass to the program??


mkanat, you are the only one among us to know Pg pretty well. Could you *please*
take a few minutes to write a (small) docs section about "How to install and
configure Bugzilla using PostgreSQL"? I'm sure Colin would be happy to help you
if you are not familiar on how to write docs.

And this way, I could then test my patches myself without asking you everytime
if I can write such or such SQL request. See, by writing this docs section now,
you will save much more time later, because I won't bother you that much
anymore. :-p

Comment 1

[Max Kanat-Alexander]

Yes, I was definitely planning to do this before the release of 2.20. Probably
after 2.20rc1, but before the final release.

Comment 2

[Max Kanat-Alexander]

Attachment: Basic Text Outline of what needs to be done

OK, here's basically what needs to be done. Now this just needs to be turned
into docbook, and perhaps polished up a bit.

Comment 3

[Colin Ogilvie [:cso]]

Attachment: Docbook Patch v1

Max's attachment converted to Docbook.

Note that this patch is generated with -w to ensure that only changes not
including the re-indentation of the MySQL section are included.

Comment 4

[Frédéric Buclin]

Comment on attachment 188923 [details] [diff] [review]
Docbook Patch v1

>+            <para>After editing the <filename>pg_hba.conf</filename>, you will also need
>+            to edit <filename>postgresql.conf</filename>, also usually found in the
>+            <filename>/var/lib/pgsql/data/</filename> folder. In this file, you will need
>+            to make a single line change, changing</para>
>+            <computeroutput># tcpip_socket = false</computeroutput>
>+            <para>to</para>
>+            <computeroutput>tcpip_socket = true</computeroutput>

Adding "tcpip_socket = true" into /var/lib/pgsql/data/postgresql.conf makes
postgresql to stop running. Without this line, this service starts as expected.


Even without this line added, I get the following error when runnning
checksetup.pl:

Checking for	     DBD::Pg (v1.31)   ok: found v1.40
Checking for	  PostgreSQL (v7.03.0000) ok: found v08.00.1000
Creating database bugs_pg ...

The 'bugs_pg' database could not be created.  The error returned was:

ERREUR:  droit refusé pour créer une base de données (translation: access
rights denied to create a database)

Comment 5

[Max Kanat-Alexander]

(In reply to comment #4)
> Adding "tcpip_socket = true" into /var/lib/pgsql/data/postgresql.conf makes
> postgresql to stop running. Without this line, this service starts as 
> expected.

  Maybe Pg 8 does it differently. These instructions are for Pg 7.

> ERREUR:  droit refusé pour créer une base de données (translation: access
> rights denied to create a database)

  That either means that you didn't follow the "createuser" instructions
correctly, or createuser works differently in Pg 8 (which I've never used).

Comment 6

[Emmanuel Seyman]

(In reply to comment #5)
> 
>   Maybe Pg 8 does it differently. These instructions are for Pg 7.

From the Pg 8 Release Notes:

"Server configuration parameters virtual_host and tcpip_socket have been
replaced with a more general parameter listen_addresses. Also, the server now
listens on localhost by default, which eliminates the need for the -i postmaster
switch in many scenarios."
http://traduc.postgresqlfr.org/pgsql-8.0.3-fr/release-8-0.html

Comment 7

[Max Kanat-Alexander]

(In reply to comment #6)
> Also, the server now
> listens on localhost by default, which eliminates the need for the -i postmaster
> switch in many scenarios."
> http://traduc.postgresqlfr.org/pgsql-8.0.3-fr/release-8-0.html

  Ahh, thank you, Emmanuel! So the whole step about "tcpip_socket" is only for Pg 7.

Comment 8

[Colin Ogilvie [:cso]]

Emmanuel,

Is that the only difference between Max's instructions for Pg 7.3 and Pg 8?

Comment 9

[Emmanuel Seyman]

(In reply to comment #8)
> 
> Is that the only difference between Max's instructions for Pg 7.3 and Pg 8?

Yup. With Pg 8, Step 5 in mkanat's explanations is no longer necessary.

Comment 10

[Frédéric Buclin]

I tried again, using slightly different command lines for the user and DB
creation, and here is what I get now:


Checking for         DBD::Pg (v1.31)   ok: found v1.40
Checking for      PostgreSQL (v7.03.0000) ok: found v08.00.1000

Adding new table bz_schema ...
Initializing the new Schema storage...
DBD::Pg::st execute failed: ERREUR:  La colonne «version» est de type numeric
mais l'expression est de type character varying
ASTUCE : Vous devez réécrire l'expression ou lui appliquer une transformation de
type.
 [for Statement "INSERT INTO bz_schema  (schema_data, version) VALUES (?,?)"
with ParamValues: 2='1.00', 1='...1234.....'] at Bugzilla/DB.pm line 808
       
Bugzilla::DB::_bz_init_schema_storage('Bugzilla::DB::Pg=HASH(0x8f04a34)') called
at Bugzilla/DB.pm line 323
        Bugzilla::DB::bz_setup_database('Bugzilla::DB::Pg=HASH(0x8f04a34)')
called at Bugzilla/DB/Pg.pm line 209
        Bugzilla::DB::Pg::bz_setup_database('Bugzilla::DB::Pg=HASH(0x8f04a34)')
called at ./checksetup.pl line 1585

Comment 11

[Colin Ogilvie [:cso]]

Attachment: Docbook Patch v2

v2 with changes as above.

Comment 12

[Max Kanat-Alexander]

Obviously a blocker. :-)

Comment 13

[Frédéric Buclin]

Comment on attachment 189318 [details] [diff] [review]
Docbook Patch v2

>>             <screen> <prompt>bash$</prompt> createuser -P bugs</screen>

To have my installation working, I needed to write: createuser -h localhost -d
-A -P bugs

The -d and -A options are only here to avoid to answer the questions later (why
not including them if these options are the same for both Pg 7 and 8 ?). But
the important part is '-h localhost' which was required for my installation to
work correctly. Maybe should we add a note in case other users have the same
problem as me.

Moreover, we should definitely mention that perl-DBD-Pg 1.41 or higher is
required when using PostgreSQL 8.0.1 or higher. perl-DBD-Pg 1.40 definitely
doesn't work.

Comment 14

[Max Kanat-Alexander]

Instead of the createuser instructions, we should just give people this SQL,
which I'm fairly sure works on all versions of Pg:

CREATE USER bugs PASSWORD 'mypassword' CREATEDB;

They can run that if they connect as the "postgres" user to the "template1"
database. Usually you can do that by doing "su - postgres" as root, and then doing:

psql template1

Comment 15

[Frédéric Buclin]

(In reply to comment #14)
> Instead of the createuser instructions, we should just give people this SQL,
> which I'm fairly sure works on all versions of Pg:
> 
> CREATE USER bugs PASSWORD 'mypassword' CREATEDB;

Please no! It's even worse. You have to 1) connect to template1 as postgres and
then use psql and 2) write this instruction, and assuming there is any problem
at this level, the user cannot do anything. Please keep the createuser
instruction. It's much easier... believe me! ;)

Comment 16

[Colin Ogilvie [:cso]]

Attachment: Patch v3

I agree with not doing the SQL statements, incidently.

Comment 17

[Frédéric Buclin]

Comment on attachment 190766 [details] [diff] [review]
Patch v3

>       </para>
>+    <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>
>     
I'm a bit confused: you add a <section>, but there is no corresponding
</section> ?? Or is this one intentionally added at the end of the patch?


>+            <para>You need to add a new user to PostgreSQL for the Bugzilla
>+            application to use to access the database. The following instructions

to use to access ???


>+            assume the defaults in <filename>localconfig</filename>; if you
>+            changed those, you need to modify the commands appropriately.You will

appropriately.You will <-- missing space.


>+            <para>After editing the <filename>pg_hba.conf</filename>, you may also need
>+            to edit <filename>postgresql.conf</filename>, also usually found in the
>+            <filename>/var/lib/pgsql/data/</filename> folder. <emphasis role="bold">This
>+            file should only be edited if you are using versions of PostgreSQL before
>+            version 8.</emphasis> If this is the case, you will need to make a single
>+            line change, changing</para>

Why not:
"<para>If you are using <emphasis role="bold">versions of PostgreSQL before
version 8</emphasis>, you may also need to edit postgresql.conf, also usually
found in the /var/lib/pgsql/data/ folder. You will need to make a single line
change, changing</para>"


All these are minor points. The description itself looks good to me.

Comment 18

[Colin Ogilvie [:cso]]

Attachment: Patch v4

Note this patch probably has a bit of whitespace change in it - output HTML is
at http://landfill.mozilla.org/csodocs/html/configuration.html

(In reply to comment #17)
> (From update of attachment 190766 [details] [diff] [review] [edit])
> >	  </para>
> >+	<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>
> >	
> I'm a bit confused: you add a <section>, but there is no corresponding
> </section> ?? Or is this one intentionally added at the end of the patch?

I think that was late night coding... for some reason the XML was still valid
though.

> >+		<para>You need to add a new user to PostgreSQL for the Bugzilla

> >+		application to use to access the database. The following
instructions
> 
> to use to access ???

Makes sense to me, but reworded any way.

 
> >+		assume the defaults in <filename>localconfig</filename>; if you

> >+		changed those, you need to modify the commands
appropriately.You will
> 
> appropriately.You will <-- missing space.

Fixed

> >+		<para>After editing the <filename>pg_hba.conf</filename>, you
may also need
> >+		to edit <filename>postgresql.conf</filename>, also usually
found in the
> >+		<filename>/var/lib/pgsql/data/</filename> folder. <emphasis
role="bold">This
> >+		file should only be edited if you are using versions of
PostgreSQL before
> >+		version 8.</emphasis> If this is the case, you will need to
make a single
> >+		line change, changing</para>
> 
> Why not:
> "<para>If you are using <emphasis role="bold">versions of PostgreSQL before
> version 8</emphasis>, you may also need to edit postgresql.conf, also usually

> found in the /var/lib/pgsql/data/ folder. You will need to make a single line

> change, changing</para>"

Changed

Comment 19

[Max Kanat-Alexander]

Comment on attachment 190862 [details] [diff] [review]
Patch v4

>+          <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 -h localhost -P bugs</screen>

  No, that won't work. You told it to use a TCP/IP connection, before
PostgreSQL is even accepting TCP/IP connections. :-)

  And as far as I know, most postgresql installations aren't configured to
allow users to *log in* (pg_hba.conf) through TCP/IP before you actually
configure that.

Comment 20

[Max Kanat-Alexander]

The correct createuser command line is:

createuser -U postgres -dAP bugs

That should work on almost every Pg installation.

Comment 21

[Emmanuel Seyman]

It might be worth noting that it's possible to, as the postgres user, create a
'bugs' user that doesn't have the rights to create a database and create an
empty database with the option '--owner bugs'.

This avoids giving db-creating right to the 'bugs' user.

Comment 22

[Max Kanat-Alexander]

(In reply to comment #21)
> This avoids giving db-creating right to the 'bugs' user.

  Yes, but that won't work with how checksetup works. It's simplest just to give
the user CREATEDB.

Comment 23

[Emmanuel Seyman]

(In reply to comment #22)
>
>   Yes, but that won't work with how checksetup works.

It doesn't?
That's how I created the database I'm using now.

Comment 24

[Max Kanat-Alexander]

(In reply to comment #23)
> It doesn't?
> That's how I created the database I'm using now.

  Yeah, what you did is fine. I didn't fully explain this.

  Imagine that one day we need to actually add options to the CREATE DATABASE
statement, or that we need to do something fancy inside of the area where the
database is created, in checksetup.pl. In that case, we need checksetup.pl to
create the database. Also, having checksetup.pl create the database is much
easier than asking the user to do it with createdb.

  However, a createdb --owner=bugs is fine, at the moment, yes.

Comment 25

[Colin Ogilvie [:cso]]

So what should the docs say? Should it mention the createdb thing or just give
the command Max gave?

Comment 26

[Max Kanat-Alexander]

Yeah, don't mention the createdb thing just now. Just use the createuser command
that I gave.

Comment 27

[Colin Ogilvie [:cso]]

Attachment: Patch v5

Comment 28

[Max Kanat-Alexander]

Comment on attachment 191125 [details] [diff] [review]
Patch v5

Yep, those look correct! The language "setting it to be" in the last step is a
bit awkward. Otherwise, that all looks quite nice, and should work on both Pg7
and Pg 8.

Comment 29

[Colin Ogilvie [:cso]]

2.20:
Checking in docs/xml/installation.xml;
/cvsroot/mozilla/webtools/bugzilla/docs/xml/installation.xml,v  <-- 
installation.xml
new revision: 1.98.2.4; previous revision: 1.98.2.3
done

Trunk:
Checking in docs/xml/installation.xml;
/cvsroot/mozilla/webtools/bugzilla/docs/xml/installation.xml,v  <-- 
installation.xml
new revision: 1.102; previous revision: 1.101
done