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

• PostgreSQL home page
• Tuning Your PostgreSQL Server
• PostgreSQL - Wikipedia