Difference between revisions of "PostgreSQL"

Jump to: navigation, search
(copying recent changes from 21 wiki)
m (Wiki syntax improved and added information about charsets and collations)
Line 6: Line 6:
 
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.  
 
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:
+
* Log into the PostgreSQL command line client. The exact form depends on how your PostgreSQL is configured but will be something like
<pre>
+
 
$ psql -U postgres
+
    $ psql -U postgres
Password for user postgres:
+
    Password for user postgres:
</pre>
+
 
(enter the password for your 'postgres' user set during installation). After some preamble you should see the prompt ''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:
 
* Create the user for the Moodle database and assign a password:
<pre>
+
 
postgres=# create user moodleuser with password 'yourpassword';
+
    postgres=# CREATE USER moodleuser WITH PASSWORD 'yourpassword';
</pre>
+
 
...substituting a suitably strong password.  
+
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.
 +
 
 
* Create the database:
 
* Create the database:
<pre>
+
 
postgres=# create database moodle owner moodleuser;
+
    postgres=# CREATE DATABASE moodle WITH OWNER moodleuser;
</pre>
+
 
 +
== 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
  
 
== See also ==
 
== See also ==

Revision as of 13:45, 11 July 2012

PostgreSQL is one of the two 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.

  • 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

See also