Use postgres database with symfony3

Question

We are a retro-engineering web application using the postgres database. We want to reorganize the code using the symfony structure, but we are faced with the problem of using the database.

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

We did a lot of research, but we were unable to create the postgresql database in the symfony project.
Firstly, we tried to apply this lesson: 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 ran 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 do not have the pdo_pgsql module, so we were looking for how to install it.
To do this, we used 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 several attempts, we finally got pdo_pgsql the result of php -m .
But you know, we have this answer when we run 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 from Postgresql to MAC using homebrew but that didn’t change anything. Now we have 5 PHP WARNING for pdo_pgsql and pgsql when we run the php bin / console doctrine command : database: create --connection = pgsql

We also saw this: How to change the database on postgresql using Symfony 2.0? and as follows: How to create 2 connections (mysql and postgresql) using Doctrine2 in Symfony2 , but that didn’t help, because the first one is about debian and we are working on OS X El Capitan, and the second one is not talking more than the previous tutorial.

Finally, our only hope is that someone can help us ... Thank you in advance.

Answer 1

Finally, I fixed it by deleting all the files that control php and reinstalling php using homebrew.

---- Remove php ----
First, I used the following root ( cd / ) cd / to find all files starting with "php"

 find . -name "php*" 

Depending on the results (you may have a lot), delete all the files that you need to delete (at the moment this is your opinion matters). For example, I deleted 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 may have a "permission denied" error even when using sudo, but in the end it did not cause problems.

---- Reinstall php ----

Once everything related to php has been 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 run the following command:

 xcode-select --install 

and restart the installation with

 brew reinstall php56 --with-postgresql 

If all goes well, you will need to define the date.timezone field in php.ini and you will have a new php system. You can verify that the pdo_pgsql module is installed using this command line: php -m .

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

First, you need to modify the app / config / parameters.yml file 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 host and port fields may be different, but these are two default values ​​for symfony and the postgres database.

Then you will need to modify the app / config / config.yml file at the Doctrine configuration level as follows:

 # 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 that you can adapt as you wish.

Now you can connect your database to your project using 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 

Should be fine. Hope this helps someone else who is facing such issues.