Php – Yii2 impossible to connect to postgresql

databasepdoPHPpostgresqlyii2

I get this error when i'am using yii2 with a postgresql database.

SQLSTATE[HY000] [2002] No such file or directory

Caused by: PDOException

SQLSTATE[HY000] [2002] No such file or directory

I configured the file main-local.php like this : 

<?php
return [
    'components' => [
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'pgsql:host=127.0.0.1;port=5432;dbname=dbname',
            'username' => 'user',
            'password' => 'pass',
            'charset' => 'utf8',
        ],
        'mailer' => [
            'class' => 'yii\swiftmailer\Mailer',
            'viewPath' => '@common/mail',
            // send all mails to a file by default. You have to set
            // 'useFileTransport' to false and configure a transport
            // for the mailer to send real emails.
            'useFileTransport' => true,
        ],
    ],
];

By the way when i use mysql it's working .

Best Answer

This is obviously a configuration problem.

  1. Call the requirements page provided by yii2 and check if the PDO Postgresql extension is installed.
  2. Get your config right. PostgreSQL is different to MySQL. Every Database Cluster contains at least one named database. A database contains at least one named schema which in turn contain tables.

With that knowledge, your main-local.php should look like this:

<?php
return [
    'components' => [
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'pgsql:host=localhost;dbname=YOURDATABASE',
            'username' => 'YOURPOSTGRESUSERNAME',
            'password' => 'YOURPOSTGRESPASSWORD',
            'charset' => 'utf8',
            'schemaMap' => [
                'pgsql' => [
                  'class' => 'yii\db\pgsql\Schema',
                  'defaultSchema' => 'public' //specify your schema here, public is the default schema
                ]
            ], // PostgreSQL
        ],
        'mailer' => [
            'class' => 'yii\swiftmailer\Mailer',
            'viewPath' => '@common/mail',
            // send all mails to a file by default. You have to set
            // 'useFileTransport' to false and configure a transport
            // for the mailer to send real emails.
            'useFileTransport' => true,
        ],
    ],
];
Related Topic