postgresql 在 symfony3 中使用 postgres 数据库

Use a postgres database with symfony3

提问by MarcoD

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.

我们正在使用 postgres 数据库对 Web 应用程序进行逆向工程。我们想使用 symfony 框架重构代码，但我们面临着使用数据库的问题。

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.

目前我们的项目正在使用 MySQL 数据库，只是对使用过的表使用相同的结构。问题是，现在我们需要使用 postgresql 数据库，因为有很多数据，据我们所知，它并没有真正适应 MySQL。

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-postgresqlwe adapted it as much as possible for our project. Here is our config.yml

我们做了很多研究，但没有成功在symfony项目中创建postgresql数据库。
首先，我们尝试应用本教程：http: //www.tutodidacte.com/symfony2-utiliser-une-base-de-donnee-postgresql我们尽可能地为我们的项目修改了它。这是我们的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=pgsqlwe had this answer :

但是当我们启动这个命令时：php bin/console algorithm:database:create --connection=pgsql我们得到了这个答案：

  [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_pgsqlso 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_pgsqlon the result of php -m.
But know, we have this answer when we launch the command : php bin/console doctrine:database:create --connection=pgsql

似乎我们没有模块pdo_pgsql所以我们搜索了如何安装它。
为此，我们应用了这个 github 页面上提出的脚本：https: //gist.github.com/doole/8651341#file-install_psql_php-sh
我们将 postgres.app 的版本更改为 9.5。经过几次尝试，我们终于成功地 对php -m的结果进行了pdo_pgsql。 但是要知道，当我们启动命令时，我们有这个答案：php bin/console police: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 homebrewbut 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

我们尝试这样做：使用自制软件在 MAC 上安装带有 Postgresql 的 PHP，但它没有改变任何东西。现在，当我们启动命令php bin/console police:database:create --connection=pgsql时，我们有 5 个针对 pdo_pgsql 和 pgsql 的 PHP 警告

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 Symfony2but 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.

我们也看到了这一点： 如何使用 Symfony 2.0 将数据库更改为 postgresql？和这个：如何在 Symfony2 中使用 Doctrine2 创建 2 个连接（mysql 和 postgresql），但它并没有真正帮助，因为第一个与 debian 相关，我们正在开发 OS X El Capitan，而第二个并没有告诉更多之前的教程。

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

最后，我们唯一的希望是有人可以帮助我们...在此先感谢您。

回答by MarcoD

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

最后，我通过删除所有管理 php 的文件并使用自制软件重新安装 php 来修复它。

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

---- 删除 php ----
首先，我从 root ( cd /)使用以下命令查找所有以“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 :

根据结果​​（您可能有很多），删除所有需要删除的文件（此时重要的是您的判断）。例如，我删除了 /usr/local 和 /usr/bin 中的文件，但没有删除 /Applications 或 /Homebrew 中的文件。
例子 ：

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.

有时，即使使用 sudo 也可能出现“权限被拒绝”错误，但最终没有出现问题。

---- Reinstalling php ----

---- 重新安装php ----

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

删除有关 php 的所有内容后，您可以使用以下命令行重新安装它：

brew install php56 --with-postgresql

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

如果出现“找不到 libz”错误，则需要启动以下命令：

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.timezonein 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.

如果一切顺利，您只需date.timezone在 php.ini 中定义该字段，您将拥有新的 php 系统。您可以检查您在使用本COMMANDE线安装pdo_pgsql模块：php -m。

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

---- 将您的数据库连接到您的 symfony 项目 ----

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

首先，您需要通过添加以下代码来修改项目中的文件 app/config/parameters.yml：

# 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.

字段主机和端口可以不同，但​​这两个是 symfony 和 postgres 数据库的默认值。

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

然后，您必须以这种方式在 Doctrine 配置级别修改文件 app/config/config.yml：

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

如果您的 src/AppBundle/Entity 中已经有实体，您可以使用以下命令创建表：

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.

现在一切都应该好了。我希望，它会帮助面临此类问题的其他人。