Configuration - Rather noobish question

[grouchotron]

Well, I have a computer running Ubuntu server edition, and I'm trying to access a Postgres database with a php file.

 

Everything I've seen tells me I have to enable postgres support in a php.ini file, by uncommenting a line about extensions.

 

Can someone give me some guidance as to what exactly I am supposed to be changing in the .ini file? There is a section for postgresql settings in it, but none of them would lead me to believe they effect whether or not its enabled. I'd copy and paste it into here... but I have no idea how to do that with vim, which is what I have on the server.

[roopurt18]

Are you accessing the server with putty?  If so you can just highlight the said lines; they will be automatically copied to the clipboard.  At least that is the typical behavior.

 

How did you install PHP on the server?  Did you pre-select it as a package?  Or did you run a command:

apt-get php5

 

How did you install the PHP Postgres extension?  Was it pre-installed?  Or did you run a command:

apt-get php5-pgsql

 

If you are using apt-get or aptitude, the server might have created the following:

/etc/php.ini    # config file
/etc/php.d      # conf directory

 

Once the extension is installed (with apt-get php5-pgsql or the equivalent), you want to make sure there is a file:

/etc/php.d/pgsql.ini

 

It should have the line that enables the extension:

extension=php_pgsql.dso ; or whatever it's actually called

 

Then from the command line you should be able to do the following:

php -m | less

Look that the pgsql extension is loaded.

 

php -i | less

Look at the configuration values for pgsql.

 

Start and stop apache

/etc/init.d/apache2 stop
/etc/init.d/apache2 start

 

Find your htdocs or webroot and create a file:

<?php
// i.php
phpinfo();
?>

 

Load the file in your browser and check that PHP running under Apache has pgsql enabled.

 

Lastly, I'll throw a little tidbit your way.  If using PDO / Postgres there seems to be a bug when connecting to the database that causes seg faults.

 

I use the following to get around them until a fix is available:

 

<?php
class LibPDO {

/**
 * Creates a PDO object to be used with PostgreSQL and returns it.
 *
 * @param string $host
 * @param string $user
 * @param string $pass
 * @param string $dbname
     * @param [boolean] $throw_exceptions
     * @param [array] $addl_dns
 * @return PDO $pdo
 */
public static function CreateForPostgre( $host, $user, $pass, $dbname, $errors_as_exception = false, $addl_dns = null ) {
        if( strlen( $host ) === 0 ) {
            throw new Exception( "Host not provided." );
        }
        if( strlen( $user ) === 0 ) {
            throw new Exception( "User not provided." );
        }
        if( strlen( $pass ) === 0 ) {
            throw new Exception( "Password not provided." );
        }
        if( strlen( $dbname ) === 0 ) {
            throw new Exception( "Database not provided." );
        }
	$arg = "pgsql:host={$host} user={$user} password={$pass} dbname={$dbname}";
        if( is_array( $addl_dns ) && count( $addl_dns ) ) {
            foreach( $addl_dns as $k => $v ) {
                $addl_dns[$k] = "{$k}={$v}";
            }
            $arg .= " " . implode( ' ', $addl_dns );
        }
        $driver_opts = array();
        if( $errors_as_exception === true ) {
            $driver_opts[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
        }
        if( empty( $driver_opts ) ) {
            return new PDO( $arg );
        }else{
            return new PDO( $arg, null, null, $driver_opts );
        }
}
}
?>

 

And I call it like so:

<?php
LibPDO::CreateForPostgre( $host, $user, $password, $database, 
    true, /* causes pdo to throw exceptions */
    array( 'sslmode' => 'disable' )  /* required to get around segfaults */
);
?>

[grouchotron]

Hey, much thanks for the detailed response!

 

So, yes I am using putty, and thanks for telling me how to copy! That'll definately be useful, haha. Below is the relevent section of the php.ini file in /etc/php5/apache2

 

[PostgresSQL]
; Allow or prevent persistent links.
pgsql.allow_persistent = On

; Detect broken persistent links always with pg_pconnect().
; Auto reset feature requires a little overheads.
pgsql.auto_reset_persistent = Off

; Maximum number of persistent links.  -1 means no limit.
pgsql.max_persistent = -1

; Maximum number of links (persistent+non persistent).  -1 means no limit.
pgsql.max_links = -1

; Ignore PostgreSQL backends Notice message or not.
; Notice message logging require a little overheads.
pgsql.ignore_notice = 0

; Log PostgreSQL backends Notice message or not.
; Unless pgsql.ignore_notice=0, module cannot log notice message.
pgsql.log_notice = 0

 

 

To answer your questions, I used apt-get for both php5 and php5-pgsql.

 

I didn't find either of the folders you suggested, but I did find a pgsql.ini file in both /etc/php5/apache2 and /etc/php5/conf.d both contained these lines:

 

# configuration for php PostgreSQL module
extension=pgsql.so

 

Both of the other commands you suggested resulted in a message saying I don't have program php installed, which is strange because I know I do, I see php files parsed and functioning correctly from my browser.

 

I've read something about having to put postgresql in a path, or having to start it? Do you know how I can tell if either of these things need to be done?

 

Any other tips?

 

Thanks again

 

 

 

 

[roopurt18]

From your command prompt, what is the output of:

type php

 

It will tell you where the PHP executable is located.

 

If it's not there, you might have to do something like:

apt-get php5-cli

 

However, you said PHP is running Apache, so are you able to create a file i.php with the following: <?php phpinfo(); ?>

 

When you load that page in your browser does it say pgsql is enabled for PHP?

[grouchotron]

Type php was getting me a message telling me to install php5-cli, so I did. However my php was working before this, and I was  (still am) able to view a file with <?php php.info() ?>. 

 

The info page does not say that postgres is enabled for php.

 

Now when typing php -m | less it works, and I do see pgsql on the list of modules.

 

When typing php -i | less I see that postgresql has been enabled.

 

However after restarting my server I see that the php info() file still doesnt display anything about postgres. What is different about the php5 I originally apt-get installed and the php5-cli? Is it a different version or something now installed on my server? Is apache2 using the wrong one?

[grouchotron]

Sorry for the double post, but it wont let me modify again?

 

I see something called PDO, with enabled = no value

 

I looked it up and it seems to have something to do with databases. Is this related to my problem perhaps?

[roopurt18]

Apache is probably using a php5-cgi.  This installs a mod_php5.so in the Apache modules directory and is normal.

 

php5-cli gives you access to PHP from the command line.  It installs a compiled PHP executable and places it in /usr/bin (or another location along your path).

 

Everything is normal as far as I can tell with your installation.

 

Is this a managed or dedicated server / VPS with cPanel, WHM, or plesk installed on it?  Those management software packages typically "break" the normal system behavior and configuration as far as Apache, PHP, databases, and modules and extensions go.

 

I don't know the apt-get command to remove packages, so you'll have to look it up.  But you might try removing php5-pgsql, then adding it back, and restarting Apache and seeing if pgsql shows up in the phpinfo() web page.

 

In the phpinfo() web page, take not of the following settings which will be near the top:

loaded configuration file: this gives you the path to the php.ini file to edit to effect changes in php running under apache

additional ini files (or similar name): this should list each file in the php5.d or php5.conf directory, so you'd have a list like: pdo.ini, mysql.ini, mcrypt.ini, etc

extension directory (or similar name): this will tell you where the *.so files are for php extensions, check this directory for a pgsql.so or similar

 

Then from the command line run:

php -i | less

and take not of the same settings.  If you could copy and paste the values here that may be helpful in assisting you.

 

I see something called PDO, with enabled = no value

It might but I'm not expecting it to.  Each database extension you install in PHP will enable a group of functions prefixed with the database they're used with.  For example, enabling the MySQL extension will add functions mysql_connect(), mysql_query(), mysql_fetch(), and other functions beginning with mysql_.  Adding Postgres extension will enable functions pgsql_connect(), pgsql_query(), etc (or maybe they're just psql_).

 

PDO is a more abstract database layer where you just tell it what database to connect to (host, user, password, database_name) and what database it is (mysql, pgsql, sqlite).  Then you use the same function calls regardless of the database type.  In addition PDO has more simple mechanisms of adding parameters to queries and escapes them properly.  The code example I gave you earlier instantiates a PDO object for a pgsql database.

 

If you are starting from scratch in your endeavors, then I highly recommend you learn how to use PDO to interact with Postgres.  It will make your life much easier.

 

As far as your inquiry I'll reiterate.  PDO is a more abstract database layer than "plain ol' pgsql."  PDO is dependent on pgsql being installed to function correctly with pgsql, not vice versa.  Again, I could be mistaken.

 

On the latest Ubuntu box that I configured, PDO was already installed and enabled when I installed php5.  You may need to apt-get php5-pdo (or the equivalent).

 

Once php5-cgi and php5-cli and PDO were all installed, when I ran apt-get php5-pgsql it correctly installed and enabled the pgsql_ functions (i.e. the "regular" stuff) and it enabled pgsql for PDO as well.

 

Finally, just to remind you, I'd like to see those config settings I mentioned earlier.  I also need to know if you have WHM, cPanel, or plesk installed.