How to connect to POSTGRESQL in Codeigniter 3?

Question

I am trying to connect to PostgreSQL using Codeigniter framework. Now in my database.php

I have the following code :

$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
    'dsn'   => '',
    'hostname' => 'localhost',
    'username' => 'postgres',
    'password' => '',
    'database' => 'fmsdb',
    'dbdriver' => 'postgre',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

But When I run my site in localhost, I get following database error :

A PHP Error was encountered

Severity: Warning

Message: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: Permission denied Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Permission denied Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?

Filename: postgre/postgre_driver.php

Line Number: 154

I tried putting this in my PostgreSQL.conf file :

listen_addresses = '*'

Where am I going wrong?

Solution 1

First enable Postgresql extension in php.ini

extension=php_pgsql.dll

You also can enable Postgresql extension for PDO as well.

extension=php_pdo_pgsql.dll

---

$db['default'] = array(
    'port'   => 5432, # Add 
);

OR

$db['default'] = array(
    'dsn'   => 'pgsql:host=localhost;port=5432;dbname=database_name', 
    'dbdriver' => 'pdo',
);

---

Database-configuration in codeigniter.com

Solution 2

Tested with Codeigniter 4, PHP 7.3 and PostgreSQL 9.3.5:

1) Enable in your php.ini

extension=php_pgsql.dll

2) In app/Config/Database class override your $default property as follows:

/**
 * The default database connection.
 *
 * @var array
 */

public $default = [
    'DSN'      => '',
    'hostname' => 'your_host',
    'username' => 'your-user',
    'password' => 'your-password',
    'database' => 'your-database',
    'DBDriver' => 'postgre',
    'DBPrefix' => '',
    'pConnect' => false,
    'DBDebug'  => (ENVIRONMENT !== 'production'),
    'cacheOn'  => false,
    'cacheDir' => '',
    'charset'  => 'utf8',
    'DBCollat' => 'utf8_general_ci',
    'swapPre'  => '',
    'encrypt'  => false,
    'compress' => false,
    'strictOn' => false,
    'failover' => [],
    'port'     => 5432, //the default port 
];

Solution 3

1. First enable these two extensions

   extension=php_pgsql.dll
   extension=php_pdo_pgsql.dll
   

2. Then restart apache
3. Add $db['default']['port'] = 5432 in database.php file with all other codes.