Use a postgres database with symfony3

Question

We are retro-engineering a web application using a postgres database. We want to refactor the code using the symfony framework but we are facing a problem to use the database with it.

For the moment our project is working with a MySQL database just using the same structure for the used tables. The problem is that, now, we need to use the postgresql database because there is a lot of data and it's not really adapted to MySQL as far as we know.

We made a lot of research but we didn't succeed to create a postgresql database in the symfony project.
First, we tried to apply this tutorial : http://www.tutodidacte.com/symfony2-utiliser-une-base-de-donnee-postgresql we adapted it as much as possible for our project. Here is our config.yml

imports:
    - { resource: parameters.yml }
    - { resource: security.yml }
    - { resource: services.yml }

# Put parameters here that don't need to change on each machine where the app is deployed
#     http://symfony.com/doc/current/best_practices/configuration.html#application-related-configuration
parameters:
    locale: en

framework:
    #esi:             ~
    #translator:      { fallbacks: ["%locale%"] }
    secret:          "%secret%"
    router:
        resource: "%kernel.root_dir%/config/routing.yml"
        strict_requirements: ~
    form:            ~
    csrf_protection: ~
    validation:      { enable_annotations: true }
    #serializer:      { enable_annotations: true }
    templating:
        engines: ['twig']
    default_locale:  "%locale%"
    trusted_hosts:   ~
    trusted_proxies: ~
    session:
         #      http://symfony.com/doc/current/reference/configuration/framework.html#handler-id
        handler_id:  session.handler.native_file
        save_path:       "%kernel.root_dir%/../var/sessions/%kernel.environment%"
    fragments:       ~
    http_method_override: true
    assets: ~

# Twig Configuration
twig:
     debug:            "%kernel.debug%"
     strict_variables: "%kernel.debug%"

# Doctrine Configuration
doctrine:
    dbal:
        default_connection: default
        connections:
           #Mysql
           default:
                driver:   pdo_mysql
                host:     "%database_host%"
                port:     "%database_port%"
                dbname:   "%database_name%"
                user:     "%database_user%"
                password: "%database_password%"
                charset:  UTF8
           #Postgresql
           pgsql:
                driver:   pdo_pgsql
                host:     localhost
                port:     5432
                dbname:   "%psql_database_name%"
                user:     root
                password: "%psql_database_password%"
                charset:  UTF8

         #mapping_types:
            #geometry: string

    orm:
        default_entity_manager: default
        auto_generate_proxy_classes: "%kernel.debug%"
        #naming_strategy: doctrine.orm.naming_strategy.underscore
        #auto_mapping: true

        entity_managers:
            default:
                connection: default

                 # lister les Bundles utilisant la connexion par defaut

                 #mappings:
                    #monprojetmysqlBundle:  ~
                    #tutoUserBundle:  ~

            pgsql:
                connection: pgsql    # connection name for your additional DB

                # bundles utilisant la connexion Postgresql
                #mappings:
                    # PostgresqlBundle: ~

# Swiftmailer Configuration
swiftmailer:
    transport: "%mailer_transport%"
    host:      "%mailer_host%"
    username:  "%mailer_user%"
    password:  "%mailer_password%"
    spool:     { type: memory }

but when we launched this command : php bin/console doctrine:database:create --connection=pgsql we had this answer :

  [Doctrine\DBAL\Exception\DriverException]              
    An exception occured in driver: could not find driver  



  [Doctrine\DBAL\Driver\PDOException]  
   could not find driver                



  [PDOException]         
   could not find driver  


doctrine:database:create [--connection [CONNECTION]] [--if-not-exists] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command>

It seems that we don't have the module pdo_pgsql so we searched how to install it.
To do it, we applied the script proposed on this github page : https://gist.github.com/doole/8651341#file-install_psql_php-sh
We changed the version of postgres.app to 9.5. After a few tries, we finally succeeded to have pdo_pgsql on the result of php -m.
But know, we have this answer when we launch the command : php bin/console doctrine:database:create --connection=pgsql

PHP Warning:  PHP Startup: pdo_pgsql: Unable to initialize module
Module compiled with module API=20131226
PHP    compiled with module API=20121212
These options need to match
in Unknown on line 0
PHP Warning:  PHP Startup: pgsql: Unable to initialize module
Module compiled with module API=20131226
PHP    compiled with module API=20121212
These options need to match
 in Unknown on line 0


  [Doctrine\DBAL\Exception\DriverException]              
  An exception occured in driver: could not find driver  



  [Doctrine\DBAL\Driver\PDOException]  
  could not find driver                



  [PDOException]         
   could not find driver  


doctrine:database:create [--connection [CONNECTION]] [--if-not-exists] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command>

We tried to do this : Install PHP with Postgresql on MAC using homebrew but it didn't change anything. Now we have 5 PHP WARNING for pdo_pgsql and pgsql when we launch the command php bin/console doctrine:database:create --connection=pgsql

We have also seen this : How to change a database to postgresql with Symfony 2.0? and this : How to create 2 connections (mysql and postgresql) with Doctrine2 in Symfony2 but it didn't really help because the first one concern debian and we are working on OS X El Capitan and the second one don't tell more than the previous tutorial.

Finally, the only hope we have is that someone can help us... Thank you in advance.

Answer 1

Finally, I fixed it by removing all the files that manage php and reinstalling php with homebrew.

---- Removing php ----
First, I used the following command from root (cd /) to find all the files starting by "php"

find . -name "php*"

Depending on the results (you might have a lot), remove all the files that need to be removed (at this point it's your judgement that matter). For example, I removed files in /usr/local and /usr/bin but not in /Applications or /Homebrew.
Examples :

rm -Rf /usr/bin/php*
rm -Rf /usr/local/php*

Sometimes, you can have a "permission denied" error even with sudo but it didn't make problem at the end.

---- Reinstalling php ----

Once everything concerning php is removed, you can reinstall it using the following command line :

brew install php56 --with-postgresql

If you have a "libz not found" error, you will need to launch the following command :

xcode-select --install

and relaunch the installation with :

brew reinstall php56 --with-postgresql

If everything went well, you will only have to define the field date.timezone in php.ini and you will have new php system. You can check that you have the pdo_pgsql module installed using this commande line : php -m.

---- Connect your database to your symfony project ----

First, you need to modify the file app/config/parameters.yml in your project by adding the following code :

# Postgresl
    psql_database_driver: pdo_pgsql
    psql_database_host: 127.0.0.1
    psql_database_port: 5432
    psql_database_name: your_database_name
    psql_database_user: your_user_name
    psql_database_password: your_password

The fields host and port can be different but this two are the default values for symfony and a postgres database.

Then, you will have to modify the file app/config/config.yml at the Doctrine Configuration level this way :

# Doctrine Configuration
doctrine:
    dbal:
        default_connection: pgsql
        connections:
           #Mysql
           default:
                driver:   pdo_mysql
                host:     "%database_host%"
                port:     "%database_port%"
                dbname:   "%database_name%"
                user:     "%database_user%"
                password: "%database_password%"
                charset:  UTF8
           #Postgresql
           pgsql:
                driver:   pdo_pgsql
                host:     "%psql_database_host%"
                port:     "%psql_database_port%"
                dbname:   "%psql_database_name%"
                user:     "%psql_database_user%"
                password: "%psql_database_password%"
                charset:  UTF8

        #mapping_types:
            #geometry: string

    orm:
        auto_generate_proxy_classes: "%kernel.debug%"
        naming_strategy: doctrine.orm.naming_strategy.underscore
        auto_mapping: true

This is an example you can adapt as you wish.

Now, you can connect your database to your project with this command line :

php bin/console doctrine:database:create --connection=pgsql

If you already have entities in your src/AppBundle/Entity you can create your tables with :

php bin/console doctrine:schema:update --force

Everything must be alright now. I hope, it will help someone else who faces this kind of problems.