MySource Matrix Resources

Content Tools

Main Content

PostgreSQL Database Setup Guide

Introduction

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

Creating a User

MySource 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 -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 MySource Matrix and grant ownership to the matrix user: 

$ createdb -U postgres -O matrix -E SQL_ASCII mysource_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 mysource_matrix
If creating the PLPGSQL language displays the error createlang: language "plpgsql" is already installed in database "mysource_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)

Version 3.18.x and later

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 (db, db2, db3 and dbcache).

In this format, only the database name and host needs to be placed in the DSN entry of each database, as shown below (please note: they are of a different format to earlier versions). The user name and password are placed in separate fields.

        'db' => array (
                'DSN' => 'pgsql:dbname=mysource_matrix',
                'user' => 'matrix',
                'password' => '',
                'type' => 'pgsql',
               ),
        'db2' => array (
                'DSN' => 'pgsql:dbname=mysource_matrix',
                'user' => 'matrix',
                'password' => '',
                'type' => 'pgsql',
               ),
        'db3' => array (
                'DSN' => 'pgsql:dbname=mysource_matrix',
                'user' => 'matrix_secondary',
                'password' => '',
                'type' => 'pgsql',
               ),
        'dbcache' => array (
                'DSN' => 'pgsql:dbname=mysource_matrix',
                'user' => 'matrix',
                'password' => '',
                'type' => 'pgsql',
               ),
If you are using TCP/IP to connect to another database server, you should add a "host" section to the DSN: 
dbname=mysource_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.
Version 3.16.x and earlier

DSNs for each database are stored in main.inc, which is created following the running of the step_01.php installation script. Four variables are used (SQ_CONF_DB_DSN, SQ_CONF_DB2_DSN, SQ_CONF_DB3_DSN and SQ_CONF_DBCACHE_DSN respectively).

The extension for PostgreSQL DSNs is pgsql. Using the credentials and the database name in this example, your four DSNs should look like:

pgsql://matrix@unix()/mysource_matrix
pgsql://matrix@unix()/mysource_matrix
pgsql://matrix_secondary@unix()/mysource_matrix
pgsql://matrix@unix()/mysource_matrix
If you are using UNIX sockets to connect to a database on the same server, you should use unix() in the host section, as above. If you are using TCP/IP to connect to a server, place the host name there.
The entry for the "dbcache" variable (SQ_CONF_DBCACHE_DSN) can be replaced with an empty string ('') if desired. This will make it use the default settings for the cache connection, which is db2 (SQ_CONF_DB2_DSN).