PostgreSQL Database Setup Guide
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.
Bookmarks to the headings on this page:
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';