How to Install & Configure TYPO3 with PostgreSQL

Are you looking for a quick handbook guide for TYPO3 PostgreSQL? You landed at the right place; In this article, you will find the installation and configuration of the PostgreSQL server and set up TYPO3 CMS using the PostgreSQL database.

Most of our TYPO3 customers prefer to use the MySQL/MariaDB database. But, sometimes, we get requests to install & configure the TYPO3 PostgreSQL Database. Fortunately, TYPO3 supports multiple database servers like MySQL, PostgreSQL, Oracle, etc. I think TYPO3 is one of the only Opensource CMS which supports such a wide range of database servers - that’s the power of TYPO3 :)

My dear TYPO3 reader, as you know my ritual, Let’s remember those working very hard to support Multiple databases to the TYPO3 CMS. I wanted to dedicate this blog to them; #T3Kudos to everyone who contributed to this project.

Relational databases are the backbone of data organization for a wide range of applications. They power everything from internet shopping to rocket launching. PostgreSQL is a database that is both venerable and still relevant today. PostgreSQL adheres to most SQL standards, supports ACID transactions, foreign keys, and views, and is still under active development.

In TYPO3, database queries are performed via a Doctrine DBAL-based API. The system extension core, which is always loaded and hence always available, provides the API. This low-level API allows extension authors to manage query operations directly on the chosen DBMS. Doctrine DBAL was introduced with TYPO3 CMS version 8 and replaced the traditional $GLOBALS['TYPO3 DB']-based API.

Doctrine is a two-part project, with Doctrine DBAL providing a low-level database abstraction and query building interface to certain database engines and Doctrine ORM providing a high-level object-relational mapping on top of Doctrine DBAL.

The DBAL component is solely implemented by the TYPO3 Core. Doctrine ORM is not necessary, executed, or used.

That's the beauty of feature-rich TYPO3 CMS; it supports the following database within TYPO3 core :)

• MySQL/MariaDB
• Microsoft SQL Server
• PostgreSQL
• Oracle
• Firebird

According to the official documentation of TYPO3 CMS, it does support PostgreSQL version 7.x and 8.x. These are the versions well-tested by the TYPO3 community. I’m sure; the other latest version should work well - just take a test drive ;)

Before seeing the configuration of PostgreSQL in TYPO3; Let’s install it.

Below installation and configuration of PostgreSQL are well tested with Debian 8. Based on your

UNIX flavor, you may need to figure out commands.

# sudo apt-get update
Reading package lists... Done.
# sudo apt-get install postgresql-9.4 postgresql-client-9.4

Test-drive your PostgreSQL installation using the grep command.

# ps -ef | grep postgre

postgres 32164     1  0 21:58 ?        00:00:00 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/   postgresql/9.4/main -c config_file=/etc/postgresql/9.4/main/postgresql.conf
postgres 32166 32164  0 21:58 ?        00:00:00 postgres: checkpointer process
postgres 32167 32164  0 21:58 ?        00:00:00 postgres: writer process
postgres 32168 32164  0 21:58 ?        00:00:00 postgres: wal writer process
postgres 32169 32164  0 21:58 ?        00:00:00 postgres: autovacuum launcher process
postgres 32170 32164  0 21:58 ?        00:00:00 postgres: stats collector process

Do you know how to login access to the PostgreSQL server? It’s very similar to the MySQL/MariaDB database server.

# su - postgres
You now should be logged in as postgres. To start the PostgreSQL console, type psql:

# psql
psql (9.4.2)
Type "help" for help.

postgres=#

To create additional roles, we can use the createuser command. Mind that this command should be issued as the user postgres, not inside the PostgreSQL console:

createuser --interactive

You can create the appropriate database by simply calling this command as the postgres user:

createdb test1

Let’s assume that you have a Linux account named test1, created a PostgreSQL test1 role to match it, and created the database test1. To change the user account in Linux to test1:

su - test1

Then, connect to the test1 database as the test1 PostgreSQL role using the command:

# psql

pgAdmin is the most popular and feature-rich Open Source administration and development platform for PostgreSQL, the world’s most advanced Open Source database. Explore at https://www.pgadmin.org/

As with other central configuration options, the database endpoint and mapping configuration happens within typo3conf/LocalConfiguration.php and ends in $GLOBALS['TYPO3_CONF_VARS'] after the Core bootstrap. The specific sub-array is $GLOBALS['TYPO3_CONF_VARS']['DB'].

A typical, basic example using only the Default connection with a single database endpoint:

// LocalConfiguration.php
// [...]
'DB' => [
   'Connections' => [
      'Default' => [
         'charset' => 'utf8',
         'dbname' => 'postgreDatabaseName',
         'driver' => 'postgres',
         'host' => 'postgreHost',
         'password' => 'postgrePassword',
         'port' => 5432,
         'user' => 'postgreUser',
      ],
   ],
],
// [...]

A slightly more complex example with two connections, mapping the sys_log table to a different endpoint:

// LocalConfiguration.php
// [...]
'DB' => [
   'Connections' => [
      'Default' => [
         'charset' => 'utf8',
         'dbname' => 'default_dbname',
         'driver' => 'mysqli',
         'host' => 'default_host',
         'password' => '***',
         'port' => 3306,
         'user' => 'default_user',
      ],
      'Syslog' => [
         'charset' => 'utf8',
         'dbname' => 'postgreDatabaseName',
         'driver' => 'postgres',
         'host' => 'postgreHost',
         'password' => 'postgrePassword',
         'port' => 5432,
         'user' => 'postgreUser',
      ],
   ],
   'TableMapping' => [
      'sys_log' => 'Syslog'
   ]
],
// [...]

TYPO3 community loves DDEV TYPO3 Docker solution for their local development environment.

For test-drive TYPO3 PostgreSQL with DDEV Docker; Please read official step-by-step instructions at https://github.com/drud/ddev-contrib/blob/master/docker-compose-services/postgres/README.md

Thanks for reading my blog. I hope you learned the basics of TYPO3 PostgreSQL.

Most of the TYPO3 projects are developing on environments like LAMP with MySQL/MariaDB database. But many guys are trying to create projects with popular PostgreSQL.  Do you have any experience with TYPO3 PostgreSQL? What’s your experience with TYPO3 PostgreSQL? Of course, feel free to write to me with any questions or support in the comment box.

Have a Happy TYPO3 PostgreSQL!