Composr Supplementary: Using PostgreSQL with Composr

Written by Chris Graham (ocProducts)
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 outside of a commercial relationship, 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. Inside a commercial relationship whatever extra testing and bug fixing is required will be done under that relationship, and fixes put back into the mainline version of Composr.

Postgres has been tested for Composr v10.0.x under a commercial relationship (starting with 10.0.5). We tested 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.

What is and is not supported

The following parts of our ecosystem are MySQL-specific:
  • Automatic database creation when installing, if installing using a MySQL admin account
  • Some non-bundled addons marked as requiring MySQL (at the time of writing nothing significant)
  • Certain esoteric performance optimisations for certain cases of large amounts of data are only for MySQL (the MySQL support is heavily optimised for a wide range of high load scenarios; similar Postgres optimisations will be made as client requirements come up)
  • Commandr-fs feature for listing when tables were last updated is only for MySQL (other database backends tend to not provide last-updated timestamps for tables)
  • Documentation is written with MySQL in mind, particularly tutorials relating to installation, performance, and maintenance (however experienced system/database administrators will be able to adapt instructions to the systems they use without too much trouble)
  • The database repair cleanup tool is only for MySQL (we use this to help test our upgrade code works perfectly, or to make it easier for 3rd-party developers who don't know how to correctly code to our database meta-system; it is not required and SQL structure dump comparison will work as a substitute in most cases)
  • We only do minimum version check for MySQL (MySQL is the only serious database backend that tends to not support basic stuff until recent versions!)
  • The bundled rootkit_detector addon is hard-coded to MySQL (this is developed for experts and bakes in assumption of mysqli due to running outside of Composr; the code can be customised to other backends as required)
  • The MySQL optimiser cleanup tool is MySQL only (other database backends are better at doing automatic cleanup)
  • Upgrade code is only tested for MySQL (upgrades for professional sites should be done by a developer, who should be able to patch around any issues in the upgrade code and pass fixes back to mainline Composr)
  • Extra development mode security checks designed to help ensure amateur Composr developers write secure code
  • Laxness modes designed to help ensure amateur Composr developers write or port code without knowing too much about database portability (MySQL is the only database backend that has non-strictness, and in other cases we are just doing some niceties to smooth things over when developers assume MySQL)
Nothing in the above list is likely to be of concern for the vast majority of Composr users who also who would want to use Postgres.

The following parts of our ecosystem are supported by MySQL and Postgres (and possibly some other database drivers, but not all):
  • Showing database server version on the PHP-Info page
  • Commandr db_table_sizes command
  • Full-text search
  • Proper Unicode support (so full-text search will search using knowledge of Unicode characters, so sorting will, and maximum column lengths will be based on character length)

Third-parties may write code that assumes (intentionally or not) MySQL, so don't assume any third party code is going to work without putting it through testing.

Installing Postgres

You can usually install Postgres using your normal package manager. On Mac I installed using HomeBrew. On Linux, yum, apt-get, 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 MacOS), 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 MacOS. 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 (SQL)

    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 (SQL)

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.