Prev: Problem when adding special characters to an XML file
Next: set different .htaccess rules
From: "Michael A. Peters" on 15 Jul 2010 08:08
I need to switch from MySQL (where everything is peachy) to Postgresql -
reason I need to switch is I need to use PostGIS and I don't see a need
to run two databases.

I got Postgresql installed (stock CentOS / RHEL 5) and installed the
postgresql php module and mdb2 driver. Restarted the web server.

Did a MySQL dump, ran it through a perl script, and managed to get it
load into Postgresql without any apparent hickups. I can connect to
postgresql and issue queries and expected results are in fact returned.

Made a user called webuser (what, you expect the real name to be
disclosed here ??) in postgresql and gave it a password and access to
the database. Updated the data/pg_hba.conf to add the following line:

local mydbname webuser 127.0.0.1 password

restarted the postgresql server.

I *thought* that all I would then have to do is change my mdb2 dsn to
reflect the new driver to use and then find parts of my code that are
not standard enough SQL - but when I try to use anything, I get -

Fatal error: Call to undefined method MDB2_Error::execute() in
/path/to/blah on line 63

I'm missing something here.

Here's my dsn -

$dsn = array(
'phptype' => 'pgsql',
'username' => 'webuser',
'password' => 'secret',
'hostspec' => 'localhost',
'database' => 'mydbname');

What am I doing wrong?
I get the same error no matter what I set
username/password/hostspec/database to - so I think it is a basic
connection error.

I'm hoping to be able to get the switch to postgresql finished soon so
that I can start playing with using the PostGIS extension.

I'm running php 5.2.12 (my own build, based on Fedora src.rpm) w/ pear
1.8.1:

pear list
Installed packages, channel pear.php.net:
=========================================
Package Version State
Archive_Tar 1.3.3 stable
Console_Getopt 1.2.3 stable
Date 1.4.7 stable
MDB2 2.4.1 stable
MDB2_Driver_mysql 1.4.1 stable
MDB2_Driver_pgsql 1.4.1 stable
PEAR 1.8.1 stable
Structures_Graph 1.0.2 stable
Text_Highlighter 0.7.1 beta
XML_Parser 1.3.2 stable
XML_RPC 1.5.1 stable
XML_Util 1.2.1 stable

Thanks for suggestions.
From: "Michael A. Peters" on 15 Jul 2010 09:57
Michael A. Peters wrote:
> I need to switch from MySQL (where everything is peachy) to Postgresql -
> reason I need to switch is I need to use PostGIS and I don't see a need
> to run two databases.
>
> I got Postgresql installed (stock CentOS / RHEL 5) and installed the
> postgresql php module and mdb2 driver. Restarted the web server.
>
> Did a MySQL dump, ran it through a perl script, and managed to get it
> load into Postgresql without any apparent hickups. I can connect to
> postgresql and issue queries and expected results are in fact returned.
>
> Made a user called webuser (what, you expect the real name to be
> disclosed here ??) in postgresql and gave it a password and access to
> the database. Updated the data/pg_hba.conf to add the following line:
>
> local mydbname webuser 127.0.0.1 password
>
> restarted the postgresql server.
>
> I *thought* that all I would then have to do is change my mdb2 dsn to
> reflect the new driver to use and then find parts of my code that are
> not standard enough SQL - but when I try to use anything, I get -
>
> Fatal error: Call to undefined method MDB2_Error::execute() in
> /path/to/blah on line 63
>
> I'm missing something here.
>
> Here's my dsn -
>
> $dsn = array(
> 'phptype' => 'pgsql',
> 'username' => 'webuser',
> 'password' => 'secret',
> 'hostspec' => 'localhost',
> 'database' => 'mydbname');
>
> What am I doing wrong?
> I get the same error no matter what I set
> username/password/hostspec/database to - so I think it is a basic
> connection error.

Yes, I put some error catching in -

MDB2 Error: connect failed, _doConnect: [Error message: unable to
establish a connection]

So it looks like pg_hba.conf file either isn't allowing it or something
else is blocking it.

*sigh* - guess back to trying to find document on setting up
authentication for an account for a pgsql web app user because the docs
I did find don't seem to be accurate.
From: "Michael A. Peters" on 15 Jul 2010 11:14
Michael A. Peters wrote:
> Michael A. Peters wrote:
>> I need to switch from MySQL (where everything is peachy) to Postgresql
>> - reason I need to switch is I need to use PostGIS and I don't see a
>> need to run two databases.
>>
>> I got Postgresql installed (stock CentOS / RHEL 5) and installed the
>> postgresql php module and mdb2 driver. Restarted the web server.
>>
>> Did a MySQL dump, ran it through a perl script, and managed to get it
>> load into Postgresql without any apparent hickups. I can connect to
>> postgresql and issue queries and expected results are in fact returned.
>>
>> Made a user called webuser (what, you expect the real name to be
>> disclosed here ??) in postgresql and gave it a password and access to
>> the database. Updated the data/pg_hba.conf to add the following line:
>>
>> local mydbname webuser 127.0.0.1 password
>>
>> restarted the postgresql server.
>>
>> I *thought* that all I would then have to do is change my mdb2 dsn to
>> reflect the new driver to use and then find parts of my code that are
>> not standard enough SQL - but when I try to use anything, I get -
>>
>> Fatal error: Call to undefined method MDB2_Error::execute() in
>> /path/to/blah on line 63
>>
>> I'm missing something here.
>>
>> Here's my dsn -
>>
>> $dsn = array(
>> 'phptype' => 'pgsql',
>> 'username' => 'webuser',
>> 'password' => 'secret',
>> 'hostspec' => 'localhost',
>> 'database' => 'mydbname');
>>
>> What am I doing wrong?
>> I get the same error no matter what I set
>> username/password/hostspec/database to - so I think it is a basic
>> connection error.
>
> Yes, I put some error catching in -
>
> MDB2 Error: connect failed, _doConnect: [Error message: unable to
> establish a connection]
>
> So it looks like pg_hba.conf file either isn't allowing it or something
> else is blocking it.

Possibly solved.
This line works -

host mydbname webuser 127.0.0.1/32 password

provided I removed the previous host line that specified authentication
by ident for all users connection via tcp from localhost.

Still getting errors, but now its a db grant issue and not a connection
issue, so i think I'm good to go.
 | 
Pages: 1
Prev: Problem when adding special characters to an XML file
Next: set different .htaccess rules