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

Creating LMS 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 LMS database and assign a password:

   postgres=# CREATE USER lmsuser 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 lms WITH OWNER lmsuser;

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 LMS database. The following example creates new database called ‘lms’ optimised for a Czech LMS site:

   postgres=# CREATE DATABASE lms WITH OWNER lmsuser 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   
   -----------+------------+----------+-------------+-------------+-----------------------
    lms    | lmsuser | 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 LMS 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 ‘lmsuser’@’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 LMS 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        lms      lmsuser      127.0.0.1/32        password

If your database lives on a separate server from your LMS 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
• Arguments in favour of PostgreSQL
• PostgreSQL – Wikipedia
• MDL-55124 – Support for connection pooler (pgbouncer) in PostgreSQL connection in LMS 3.2 onwards