File moodle3_8-README.postgresql of Package moodle3_8

= Installing Moodle with PostgreSQL = 

PostgreSQL is one of the five databases that is fully supported by Moodle. A
database is a required component of any Moodle installation.

== Creating Moodle Database ==

These instructions assume that the database server and web server are on the
same machine. If that is not the case you have some more work to do. See the
PostgreSQL documentation for further details. 

* Log into the PostgreSQL command line client. The exact form depends on how
  your PostgreSQL is configured but will be something like

    $ psql -U postgres
    Password for user postgres:

Enter the password for your 'postgres' user set during installation. After some
preamble you should see the prompt ''postgres=#''.

* Create the user for the Moodle database and assign a password:

    postgres=# CREATE USER moodleuser WITH PASSWORD 'yourpassword';

Provide a suitably strong password. Please note that the actual authentication
method depends on your PostgreSQL server's pg_hba.conf file. Some
authentication methods (like ident) do not require the password. See the
'Client Authentication' section below for further details.

* Create the database:

    postgres=# CREATE DATABASE moodle WITH OWNER moodleuser;

== Character set and collation ==

If the PostgreSQL server's default collation does not suit your needs, you can
provide explicit LC_CTYPE (character classification) and LC_COLLATE (string
sort order) setting for your Moodle database. The following example creates new
database called 'moodle' optimised for a Czech Moodle site:

    postgres=# CREATE DATABASE moodle WITH OWNER moodleuser ENCODING 'UTF8' LC_COLLATE='cs_CZ.utf8' LC_CTYPE='cs_CZ.utf8' TEMPLATE=template0;

For more details refer to Character Set Support chapter in PostgreSQL manual.

To make sure the database was created correctly, use the ''\l'' at the psql
console or execute ''psql -l'' shell command. You should get something like

    postgres=# \l
                                       List of databases
       Name    |  Owner     | Encoding |  Collation  |    Ctype    |   Access privileges   
    -----------+------------+----------+-------------+-------------+-----------------------
     moodle    | moodleuser | UTF8     | cs_CZ.utf8  | cs_CZ.utf8  | 
     postgres  | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
     template0 | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                                   : postgres=CTc/postgres
     template1 | postgres   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                                   : postgres=CTc/postgres

== Client Authentication ==

Client Authentication is how PostgreSQL decides which user accounts can connect
to which databases from which hosts. PostgreSQL handles this in a very
different way to MySQL, and this is a potential pitfall for system
administrators who are used to MySQL and now find themselves setting up a
PostgreSQL server.

If you've followed the instructions above, but find that Moodle just won't
connect to your database and gives an 'Ident authentication failed' error, you
should check your client authentication file.

In MySQL, access is controlled when user accounts are created or granted
privileges on databases, by using the 'moodleuser'@'servername' syntax.
PostgreSQL uses a client authentication file called 'pg_hba.conf' in
PostgreSQL's 'data' folder. In this file, you'll find a list of which users are
allowed to connect to which databases, the IP addresses they are allowed to
connect from, and the authentication methods they can use to connect.

In the client authentication file, you'll find the following line (starting
with a '#' sign), which tells you the order to add each piece of data. To grant
permission for Moodle to connect to a PostgreSQL server on the same machine,
just add the following line, changing the DATABASE and USER columns to your
actual database name and the username you set up above. The METHOD field should
say "password" - don't put your actual password here.

    # TYPE      DATABASE    USER            CIDR-ADDRESS        METHOD
    host        moodle      moodleuser      127.0.0.1/32        password

If your database lives on a separate server from your Moodle installation, you
can change the IP address above to match. In this case, it is recommended to
not use 'password' in the method field unless the connection between the two
servers is secure, as 'password' sends the password in plain text. In these
circumstances, you should consider changing METHOD to 'md5' instead.

Once the Client Authentication file has been changed, you will need to restart
the PostgreSQL service.

== See also ==

* [http://www.postgresql.org PostgreSQL home page]
* [http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server Tuning Your PostgreSQL Server]
* [[Arguments in favour of PostgreSQL]]
* [https://en.wikipedia.org/wiki/PostgreSQL PostgreSQL - Wikipedia]
* MDL-55124 - Support for connection pooler (pgbouncer) in PostgreSQL connection in Moodle 3.2 onwards