How to Install & Configure TYPO3 with PostgreSQL

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.

Quick Intro to PostgreSQL

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.

MySQL vs PostgreSQL

What is Doctrine DBAL (Database Abstraction Layer)?

Source

 

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 :)

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

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:

 

createuser --interactive

Create TYPO3 PostgreSQL Database

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

 

createdb test1

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:

 

# psql

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:

 

// LocalConfiguration.php
// [...]
'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

Wrapping-up!

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!

Post Comment

Your email address will not be published. Required fields are marked *

×

Name is required!

Enter valid name

Valid email is required!

Enter valid email address

Comment is required!

Google Captcha Is Required!

Comments :

  • Monika Schumacher September 9, 2021 At 1:19 pm
    I had a problem using multiple databases with PostgreSQL, and the sys_log table mapping is definitely not a cakewalk for me, the example for the same was good, It got my personal project started. Thanks for sharing Sanjay.
  • Karl Krüger September 8, 2021 At 11:28 am
    Thanks for the article on this one, the process started as a bit of confusion for me at first but now I have got the hang of it. And all the examples were accurate and on-point along with the representations. It helped me complete my tutorial on PostgreSQL, thanks again.
  • Felix Werner September 7, 2021 At 12:28 pm
    Indeed Sanjay, MySQL is the most popular but Postgre is the most advanced. I have been using it for quite a while now and it gets the job done very well.

    Thanks for the article, very informative.

7 Key Changes of TYPO3 v11.5

TYPO3 version11.5 brings even more improvements to the vivid list of functionality and user interface. Let’s hit the highlight…

Automise Your TYPO3 Deployment (2020)

How to Automise Your TYPO3 Deployment (with PHP Deployer)

Are you looking for automatic & modern TYPO3 Deployment? Then, you are in the correct place. In this article, You will learn the…

Improve TYPO3 Development with Composer 2.0

Improve TYPO3 Development with Composer 2.0

Woohoo! TYPO3 (aka PHP) people’s long-awaited Composer 2.0 has been launched with lightning-fast speed. Read this article for…

Stay up to date with our recent TYPO3 Blogs, news, & updates