33

I'm having a terribly difficult time getting the command "pg_connect()" to work properly on my Mac. I'm currently writing a PHP script (to be executed from console) to read a PostgreSQL database and email a report.

I've gone into my php.ini file and added 

extension=pgsql.so

But, I'm met with the following error.

PHP Warning: PHP Startup: Unable to load dynamic library '/usr/lib/php/extensions/no-debug-non-zts-20090626/php_pgsql.so' - dlopen(/usr/lib/php/extensions/no-debug-non-zts-20090626/php_pgsql.so, 9): image not found in Unknown on line 0
PHP Fatal error: Call to undefined function pg_connect() in... (blah file here)

When running phpinfo(), I see nothing about PostgreSQL, so what is my issue here?

potashin
  • 44,205
  • 11
  • 83
  • 107
Jordan Scales
  • 2,687
  • 4
  • 28
  • 37

10 Answers10

72

The PHP version that comes bundled with OS X doesn't include PostgreSQL. You'll have to compile the extension yourself. Here are some instructions:

  1. Find your version of PHP: php -v.
  2. Download the version of PHP that matches yours: curl -O http://us.php.net/distributions/php-5.3.3.tar.gz. (This example downloads PHP 5.3.3 but this must match your version)
  3. Extract the archive you downloaded: tar -xzvf php-5.3.3.tar.gz
  4. Change to the PostgreSQL's extension directory: cd php-5.3.3/ext/pgsql/
  5. Type phpize.
  6. Type ./configure.
  7. Type make.
  8. Type sudo make install.
  9. Add the extension to you php.ini file by adding extension=pgsql.so. (You may already have done this)
  10. Restart Apache.

Update for OS X Mountain Lion Apple has removed autoconf from the newer versions of XCode so the procedure above will fail at #5. To solve that problem:

  1. Type /usr/bin/ruby -e "$(/usr/bin/curl -fksSL https://raw.github.com/mxcl/homebrew/master/Library/Contributions/install_homebrew.rb)".
  2. Type sudo chown -R $USER /usr/local/Cellar.
  3. Type brew update.
  4. Type brew install autoconf.

That should install autoconf and allow you to install the module using the instructions above.

Francois Deschenes
  • 24,816
  • 4
  • 64
  • 61
  • 5
    Thanks for the steps, but unfortunately I'm getting stuck at step 6. With the following error. `configure: error: Cannot find libpq-fe.h. Please specify correct PostgreSQL installation path` – Jordan Scales Jul 05 '11 at 20:17
  • 7
    Fixed: Just had to specify --with-pgsql=/opt/local/lib/postgresql83/ at the end of the configure statement. And also, I added pgsql.so, not pcntl.so. Thanks for your help! – Jordan Scales Jul 05 '11 at 20:23
  • 5
    @Jordan Scales - It's looking for the files in "/usr", "/usr/local", and "/usr/local/pgsql". If PostgreSQL is installed somewhere else on your system, you'll have to change `./configure` to `./configure --with-pgsql=/path/to/pgsql`. – Francois Deschenes Jul 05 '11 at 20:25
  • @Jordan Scales - No problem. You solved the problem before I had a chance to finish writing my answer! – Francois Deschenes Jul 05 '11 at 20:25
  • Looks like they moved the location for older versions. Try http://us.php.net/releases/ to find the URL for the version you're looking for. – Josh Sep 11 '13 at 08:49
  • 3
    I am running Mavericks, but this is probably applicable to other versions. My php.ini was not present, and instead a php.ini.default was in place. This file isn't automatically picked up by php, so I did a 'sudo cp /etc/php.ini.default /etc/php.ini' then restarted apache which is done using 'sudo /usr/sbin/apachectl restart'. – CenterOrbit Nov 19 '13 at 15:27
  • 1
    I am using yosemite... big trouble over here!!! This worked perfectly, but only after i have installed XCODE command line tools though terminal command **xcode-select --install**. So, first reinstall -select, then follow the instructions of this post. – Hiro Ferreira Oct 23 '14 at 17:12
  • If you get an error about "error: use of undeclared identifier 'HASH_KEY_NON_EXISTENT'", edit the pgsql.c file, and change the spelling of EXISTENT to EXISTANT - with an "A" in all places HASH_KEY_NON_EXISTENT exists. Then run make again. – davidhaskins Nov 26 '14 at 16:17
  • I was not able to get past @JordanScales error. Installing postgres with brew worked.. The app did not do it. – Union find Jan 02 '16 at 23:48
  • In El Capitan works, but first i needed do this: http://stackoverflow.com/questions/32659348/operation-not-permitted-when-on-root-el-capitan-rootless-disabled – Tom Feb 05 '16 at 23:12
36

If you use home brew, you can solve this with a command as simple as:

brew install php55-pdo-pgsql

for other php version, search with:

brew search pgsql

Daniel Cukier
  • 11,502
  • 15
  • 68
  • 123
  • 2
    This is the better answer (+1). The currently accepted answer suggests to use `brew` to install `autoconf` and then to compile PHP from source code. Which is unnecessary work, because you can directly install PostgreSQL, PHP and the `phpXX-pdo-pgsql` packages - all using [brew](http://brew.sh) for Mac OSX. – Alexander Farber Mar 12 '16 at 13:36
  • I tried this and the install is successful (I see pdo_pgsql in `php -i`) but I still get `pg_connect() not found` errors.. – Nic Cottrell May 30 '16 at 08:45
  • 1
    Actually, this is a better answer. Because almost all developers use some kind of packet manager these days. – Turdaliev Nursultan Nov 23 '16 at 12:38
8

This worked for me with OSX 10.9.4 «Mavericks»

Install sources

Download the PHP source code. Unlike on Mountain Lion, you don’t get any headers preinstalled to link against so need to put it in /usr/include/php. Mavericks ships with PHP 5.4.17, but the latest 5.4.x source from php.net should do:

tar -jxvf php-5.4.20.tar.bz2
sudo mkdir -p /usr/include
sudo mv php-5.4.20 /usr/include/php

Configure PHP

cd /usr/include/php
./configure --without-iconv
sudo cp /etc/php.ini.default /etc/php.ini

Building a module

I needed the pdo_pgsql module - the same pattern should apply to just about any module assuming you have the necessary dependencies installed:

cd ext/pdo_pgsql

In my case I had the following error:

Cannot find autoconf. Please check your autoconf installation and the $PHP_AUTOCONF environment variable. Then, rerun this script. ERROR: `phpize' failed

So I had to use this command:

brew install autoconf

Then:

phpize

After that I tried to do: ./configure

but I had the next problem:

checking for pg_config... not found configure: error: Cannot find libpq-fe.h. Please specify correct PostgreSQL installation path

So the solution was to specify correct PostgreSQL installation path:

./configure --with-pdo-pgsql=/Library/PostgreSQL/9.3/
make
sudo make install

That copies pdo_pgsql.so to /usr/lib/php/extensions/no-debug-non-zts-20100525.

Then simply add 

extension=pdo_pgsql.so to /etc/php.ini

Run php -m to confirm everything went to plan.

Jorge Casariego
  • 21,948
  • 6
  • 90
  • 97
  • 2
    Your approach also works for Yosemite. The only caveats are: to use `./configure --with-pgsql=/usr/local/Cellar/postgresql/9.4.1/` in case of PHP 5.5.20; add `extension=pgsql.so` to /etc/php.ini (originally php.ini.default). – Igor de Lorenzi Jun 09 '15 at 17:53
7

For those who installed php7/ngix/postgres with homebrew

You can install the PostgreSQL module with:

brew install php70-pdo-pgsql

After that, you have to restart the php service:

brew services restart php70
ricardo
  • 1,221
  • 2
  • 21
  • 39
3

OS X El Capitan users can simply upgrade their version of PHP 5.6. This is a one liner that will do that.

curl -s http://php-osx.liip.ch/install.sh | bash -s 5.6

Christopher Charles Gorman
  • 49
  • 1
3

For php56 via brew:

brew install php56-pdo-pgsql
ThangTD
  • 1,586
  • 17
  • 16
  • 1
    I'm on Sierra, and this didn't work for me. Install completed, but no `pdo_pgsql` appears in in phpinfo() – Black Nov 28 '16 at 04:08
  • Hi @Francis, did you check by `php -i | grep pdo_pgsql` in terminal ? Or restart your webserver to see in browser. Refer to my screen shot: http://prnt.sc/dcmkcp – ThangTD Nov 28 '16 at 04:11
  • Hi - found the issue. Sierra Server of course has at least two PHP instances with their own php.ini files; one in `/private/etc` and the other in `/Applications/Server.app/Contents/ServerRoot/private/etc/php.ini`. Thanks – Black Nov 28 '16 at 04:44
  • 1
    Yeah, I intent to tell you to make sure whether php version and its location are correct. Better you should use php with `homebrew`, not the default one. Because its config will lose when you upgrade your MacOS – ThangTD Nov 28 '16 at 04:47
2

PostgreSQL by default is installed in a unusual place on MAC OS X:

/Library/PostgreSQL/9.3

Given the location above you can type this:

./configure --with-pgsql=/Library/PostgreSQL/9.3
Brad Koch
  • 19,267
  • 19
  • 110
  • 137
YP Leung
  • 169
  • 1
  • 3
2

I killed the whole day trying to make it work on El Capitan after I made an upgrade yesterday and it turned out that I forgot to modify httpd.conf and change the path from the default php module (version 5.5.27) to the one I installed (version 5.6.14). This should be done in httpd.conf by modifying your default LoadModule php5_module path to LoadModule php5_module /usr/local/opt/php56/libexec/apache2/libphp5.so. Just decided to leave it here as the potential solution for those who upgrade their OS or just the PHP version and face the same problem.

ievgenii
  • 333
  • 5
  • 13
0

For those of you having openssl error while make here is the solution

OSX uses openssl 0.98 while installer is searching for 1.0.0

refer this link for instructions

psycopg2 installation error - Library not loaded: libssl.dylib

Community
  • 1
  • 1
Majky
  • 1,943
  • 1
  • 18
  • 30
0

I downloaded PostgreSQL for Mac, and used the stack builder after installation to standup the entire EnterpriseDB Apache/PHP stack end-to-end. I mention this as a possible time saving option, probably not ideal for all situations. Should work OK if the apache and postgres shipped with Mac OS X were never started.

To keep existing apache hosted applications (i.e. pre-PostgreSQL install legacy) stable, I would just install the newer EnterpriseDB apache on port 81 (stackbuilder will prompt for new port if legacy apache instance is already running). Then, use mod_proxy in httpd.conf for the apache running on port 80 to provide seamless user experience to applications hosted on PostgreSQL.

Net Dawg
  • 518
  • 1
  • 6
  • 10