This guide will demonstrate how to create two users and a database for use with Squiz Matrix. It will also define the format of the DSN connection strings used to connect to the database.

Creating a User

Squiz Matrix requires two PostgreSQL users for connecting to the database. One for general purposes and one for batch processing jobs. We will now create these two users with appropriate names:

$ createuser -SRDU postgres matrix
$ createuser -SRDU postgres matrix_secondary

This will create new postgres users that are not superusers,
can't create new databases and can't create new roles.

Alternatively you can get createuser to prompt you for the information:
$ createuser -U postgres matrix
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE

$ createuser -U postgres matrix_secondary
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE 

If you have another superuser that you use to create accounts, then replace postgres with that user in the createuser command above.

Creating a Database

Now, create a database for Squiz Matrix and grant ownership to the matrix user:

createdb -U postgres -O matrix -E UTF8 squiz_matrix
CREATE DATABASE 

Creating the PLPGSQL Language

The PLPGSQL language is required for the database that you have just created, so lets add support for it now:

$ createlang -U postgres plpgsql squiz_matrix 

If creating the PLPGSQL language displays the error createlang: language "plpgsql" is already installed in database "squiz_matrix", this can safely be ignored. It's most likely that the language was installed on the template database that is used to create other databases.

Creating a Data Source Name (DSN)

DSNs for each database are stored in db.inc (inside the data/private/conf directory), which is created following the running of the step_01.php installation script. This file stores database information in one large array, with information for each database.

In this format, only the database name and host needs to be placed in the DSN entry of each database, as shown below. The user name and password are placed in separate fields.

        'db' => array (
                'DSN' => 'pgsql:dbname=squiz_matrix',
                'user' => 'matrix',
                'password' => '',
                'type' => 'pgsql',
               ),
        'db2' => array (
                'DSN' => 'pgsql:dbname=squiz_matrix',
                'user' => 'matrix',
                'password' => '',
                'type' => 'pgsql',
               ),
        'db3' => array (
                'DSN' => 'pgsql:dbname=squiz_matrix',
                'user' => 'matrix_secondary',
                'password' => '',
                'type' => 'pgsql',
               ),
        'dbcache' => array (
                'DSN' => 'pgsql:dbname=squiz_matrix',
                'user' => 'matrix',
                'password' => '',
                'type' => 'pgsql',
               ),
        'dbsearch' => NULL,

The "dbsearch" database parameter, if appropriate to your configuration, should be populated with the database details of a replicated Squiz Matrix database - in DSN / user / password / type array format as shown above - to offload search index requests from the main DB server.

If you are using TCP/IP to connect to another database server, you should add a "host" section to the DSN:

dbname=squiz_matrix;host=db.example.com 

If you are using UNIX sockets to connect to a database on the same server, you should omit the host from the DSN entirely, as above.

The whole of the "dbcache" array entry can be replaced with NULL if desired. This will make it use to the default settings for the cache connection, which is db2.

PostgreSQL 9.0 and Higher

When creating a database using PostgreSQL 9.0 or higher, the output format for bytea values must be configured to the escape format, as follows:

alter database squiz_matrix SET bytea_output TO 'escape';