Note: You are currently viewing documentation for Moodle 3.8. Up-to-date documentation for the latest stable version of Moodle may be available here: PostgreSQL.

PostgreSQL: Difference between revisions

From MoodleDocs
No edit summary
(→‎See also: MDL-55124, removing old discussion link)
 
(13 intermediate revisions by 10 users not shown)
Line 1: Line 1:
PostgreSQL is one of the two databases that is fully supported by Moodle. A database is a required component of any Moodle installation.
{{Installing Moodle}}
PostgreSQL is one of the five databases that is fully supported by Moodle. A database is a required component of any Moodle installation.


PostgreSQL describes itself as "an object-relational database management system (ORDBMS) based on POSTGRES, Version 4.2, developed at the University of California at Berkeley Computer Science Department. POSTGRES pioneered many concepts that only became available in some commercial database systems much later.
== Creating Moodle Database ==


PostgreSQL is an open-source descendant of this original Berkeley code. It supports a large part of the SQL standard and offers many modern features:
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.


* complex queries
* Log into the PostgreSQL command line client. The exact form depends on how your PostgreSQL is configured but will be something like
* foreign keys
* triggers
* views
* transactional integrity
* multiversion concurrency control
Also, PostgreSQL can be extended by the user in many ways, for example by adding new


* data types
    $ psql -U postgres
* functions
    Password for user postgres:
* operators
* aggregate functions
* index methods
* procedural languages


And because of the liberal license, PostgreSQL can be used, modified, and distributed by everyone free of charge for any purpose, be it private, commercial, or academic."
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 [http://www.postgresql.org/docs/9.1/static/multibyte.html 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 ==
== See also ==


* [http://www.postgresql.org PostgreSQL home page]
* [http://www.postgresql.org PostgreSQL home page]
* [http://moodle.org/mod/forum/view.php?id=45 Using Moodle databases forum]
* [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


[[Category:Administrator]]
[[Category:Developer]]
[[Category:SQL databases]]
[[Category:SQL databases]]


{{stub}}
[[es:PostgreSQL]]
[[ja:PostgreSQL]]

Latest revision as of 07:48, 18 August 2017

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