Composr Tutorial: Using PostgreSQL with Composr

Written by Chris Graham
This tutorial provides some advice targeted towards PostgreSQL (also known simply as 'Postgres').

Composr has internal 'support' for many different databases backends and is intentionally written with a combination of simple common-denominator SQL and use of an abstraction API. However officially only MySQL is supported, due to the significant effort testing across new versions of Composr and the database backends involved. Different database systems vary in all kinds of surprising and subtle ways. Those wanting to do extra testing and bug fixing can put fixes put back into the mainline version of Composr if they choose to.
Composr functionality that only works on MySQL can be identified by looking on the Composr feature tracker.

Postgres has been tested for Composr v10.0.x for a commercial user of Composr CMS (starting with Composr version 10.0.5). Testing was done on Postgres 9.6.3 across our whole test set and all screens and blocks, with both multi-lang-content on and off. We expect in practice Postgres 8.4+ is supported (we use subquery string joining [via arrays] in 8.4 as an alternative to MySQL's GROUP_CONCAT function).

Postgres is a very high quality Open Source database backend developed by developers working for a number of different companies. It is much more sophisticated than MySQL, although it also tends to be stricter in a number of ways, more technical, and not as available on shared hosting. Usage is likely of most interest to technical organisations that are standardising on Postgres.

This tutorial broadly assumes you are responsible for configuring and managing the server that is running Postgres. It is not written for shared hosting customers, who would typically configure a database using a proprietary control panel of some kind.

Table of contents

  1. Composr Tutorial: Using PostgreSQL with Composr
    1. Installing Postgres
    2. After installing Postgres
    3. Migration to Postgres
      1. Whoops?
    4. Full-text search (advanced)
    5. Technical information for developers (advanced)
    6. See also

Installing Postgres

You can usually install Postgres using your normal package manager. On Mac I installed using HomeBrew. On Linux, yum, apt, etc, should all have it.
On Windows there are downloadable packages.

You will need the PHP postgresql extension to be installed.

After installing Postgres

Postgres ties its own user acccounts to system accounts. On my Mac it tied it to my normal login account automatically, with a blank password. On other systems it may create a new postgres user.

To get a Postgres console if you are already logged into the same system account as the Postgres user:

Code (Bash)

psql postgres
 
or, if not you can either su/sudo into that user (Linux or Mac OS), or be explicit:

Code (Bash)

psql postgres -U <user>
 
postgres here refers to the system database not the user.

Postgres has system commands that you execute with a leading \. To see a list of users you can do:

Code (Bash)

\du
 

From now on the tutorial we'll assume you want to be using a database called "cms" and a database user "cms".

You can create a database using SQL:

Code (Bash)

CREATE DATABASE cms;
 

You can create a user like:

Code (Bash)

CREATE USER cms WITH password 'examplePassword';
 
Note that Postgres will not actually use this password for authentication of 'peer' or 'ident' authentication is on, it will authenticate using Unix user account. This was the case for me by default when testing on Fedora Linux, but not Mac OS. I had to edit /var/lib/pgsql/data/pg_hba.conf:

Code (Text)

# "local" is for Unix domain socket connections only
local   all             postgres                                     peer
local   all             all                                          md5
# IPv4 local connections:
host    all             postgres        127.0.0.1/32                 ident
host    all             all             127.0.0.1/32                 md5
# IPv6 local connections:
host    all             postgres        ::1/128                      ident
host    all             all             ::1/128                      md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                    peer
#host    replication     postgres        127.0.0.1/32                ident
#host    replication     postgres        ::1/128                     ident
 
This configuration allows 'md5' (i.e. password-based) authentication for all users except the postgres user.

You can grant access like:

Code (Bash)

GRANT ALL PRIVILEGES ON DATABASE cms TO cms;
\connect cms;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO cms;
ALTER DATABASE cms OWNER TO cms;
 

Migration to Postgres

Migrate via this process:
  1. Commandr can export an SQL dump compatible with any database that Composr supports, assuming you have the PHP extensions needed installed:

    Code

    sql_dump postgresql
    (yes, you can be running MySQL and export a Postgres SQL dump!)
  2. You then setup your database, user, and password, in Postgres.
  3. You then import via:

    Code (Bash)

    psql cms -f <file.sql> -Ucms -W
     
  4. Generate SQL for updating sequence counters:

    Code (PostgreSQL)

    SELECT 'SELECT setval(pg_get_serial_sequence(''' || tablename || ''', ''id''), coalesce(max(id),0) + 1, false) FROM ' || tablename || ';'
    FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
    ORDER BY schemaname, tablename;
     
  5. Run the above generated SQL (ignoring any errors).
  6. You then edit Composr's _config.php to have the correct database details.

Whoops?

You may need to reassign ownership of the tables you imported if you accidentally did it with he wrong user. This will generate some SQL you can run to do that:

Code (PostgreSQL)

SELECT 'ALTER TABLE ' || tablename || ' OWNER TO cms;'
FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;
 

Full-text search (advanced)

Full-text is fully supported. However, ideally (not importantly) Composr would know the correct stop-word list for Postgres so it knows whether to direct searches only for a stopword to a non-full-text search. This is controlled in a dictionary (unlike MySQL, where it's hard-coded, and hence we were able to hard-code in our code also). To provide Composr an accurate full-text search word list you need to override the get_stopwords_list() function.

If you don't want Postgres's default English configuration for word tokenisation there's a hidden postgres_fulltext_language option you can use to set a different Postgres configuration:

Code

:set_value('postgres_fulltext_language', 'spanish');
Existing indices would need hand-editing to the new value too.

Technical information for developers (advanced)

The main complexities of Postgres support, for MySQL developers are:
  1. No support for prefix indexes, which is a feature only of MySQL. This means you cannot easily add an index for a text field without imposing a length limit on the data within the actual field. Postgres has excellent support for "calculated indexes", which MySQL does not, and which could work for a substitute for prefix indexes except you would need to code specifically to this feature when writing read queries. Therefore if you are making a Postgres site with many millions of records and are relying on querying based on the contents or prefixes of long text fields, you need to give some special consideration. This is unlikely in practice as search would be either via shorter fields or via full-text search.
  2. You can not query an integer field using a string representation of an integer, or vice-versa.
  3. You can not directly join an integer and string field, unless you use casting. Composr's db_cast function will help with this in a portable way.
  4. The MySQL LIMIT max,start syntax is not supported. You can do LIMIT max OFFSET start though. The Composr query* methods abstract this for 99.9% of cases you may have.
  5. You can only use particular functions. Composr's db_function function will help with this in a portable way.
  6. You need to use Postgres string escaping, not MySQL string escaping.
  7. You have to be much more careful about aggregate functions like COUNT, or GROUP BY, or DISTINCT. If you do a COUNT then you are not allowed an ORDER BY clause (you don't need one, but it's easy to add one by mistake when you are deriving a count query from your pagination query and this causes the count query to fail). If you do a GROUP BY then you may not select anything that is not covered by the GROUP BY clause or is itself an aggregate like COUNT or SUM or MAX or MIN – this is because Postgres won't allow selecting an arbitrary value out of a result set for you like MySQL will. If doing a DISTINCT query then you can only do an ORDER BY with something that is also being SELECTed (because Postgres reserves the right to apply the ordering after it has whittled down the result set).
  8. Stay away from hand-coding anything sophisticated like DDL or Information Schema. I haven't covered any of the cases of obvious MySQL-specific syntax, syntax which is very poorly standardised, or case sensitivity differences. The SQL standards only really cover some very core things, they don't even properly standardise basic data types, or moderately sophisticated use of indexes, or pagination, let alone complicated expressions, further let alone triggers or stored procedures (!).


See also


Feedback

Please rate this tutorial:

Have a suggestion? Report an issue on the tracker.