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.
What is Doctrine DBAL (Database Abstraction Layer)?
The data in TYPO3 CMS is stored in a relational database management system (RDBMS). Connecting to different database management systems is made possible via the Doctrine DBAL component. The most popular database is still MySQL/MariaDB. However, thanks to Doctrine, additional databases such as PostgreSQL and SQLServer are also available.
How Technically TYPO3 Database Connection?
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.
TYPO3 Doctrine DBAL and Doctrine ORM
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.
Which Are Supported Databases in TYPO3 CMS?
That's the beauty of feature-rich TYPO3 CMS; it supports the following database within TYPO3 core :)
- Microsoft SQL Server
Which PostgreSQL versions does TYPO3 support?
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 ;)
Install PostgreSQL Server for TYPO3
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
Let's Check PostgreSQL Installation
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
Login Access to PostgreSQL Database Server
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=#
How to Add a New Role in PostgreSQL?
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:
Create TYPO3 PostgreSQL Database
You can create the appropriate database by simply calling this command as the postgres user:
Connecting to PostgreSQL with the New User
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:
pgAdmin - PostgreSQL Database Tool
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/
Installation of TYPO3 PostgreSQL Database
While installing TYPO3 CMS, If you have installed the PostgreSQL server, choose the “Manually configured PostgreSQL connection” option at selecting the database.
Configuration of TYPO3 PostgreSQL Database
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:
'DB' => [
'Connections' => [
'Default' => [
'charset' => 'utf8',
'dbname' => 'postgreDatabaseName',
'driver' => 'postgres',
'host' => 'postgreHost',
'password' => 'postgrePassword',
'port' => 5432,
'user' => 'postgreUser',
Multiple Database Use
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 PostgreSQL @ DDEV Docker
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!
Thanks for the article, very informative.