Querying Databases From R on a Mac
I use a mac, currently running OS 10.6 / Snow Leopard, and I’d like to query our greenplum / postgres database from R. This used to work with R 2.9, but I unfortunately had to upgrade R, and R 2.10 on the mac is a 64 bit app. So, I want to use either RODBC or RPostgreSQL packages under 64 bit R on a mac to query postgres / greenplum.
First, I tried just installing RPostgreSQL as before. Unfortunately, I started getting weird errors when I attempted to load the package:
>library('RPostgreSQL')
Loading required package: DBI
Error in dyn.load(file, DLLpath = DLLpath, ...) :
unable to load shared library '/Library/Frameworks/R.framework/Resources/library/RPostgreSQL/libs/x86_64/RPostgreSQL.so':
dlopen(/Library/Frameworks/R.framework/Resources/library/RPostgreSQL/libs/x86_64/RPostgreSQL.so, 6): Symbol not found: _PQbackendPID
Referenced from: /Library/Frameworks/R.framework/Resources/library/RPostgreSQL/libs/x86_64/RPostgreSQL.so
Expected in: flat namespace
in /Library/Frameworks/R.framework/Resources/library/RPostgreSQL/libs/x86_64/RPostgreSQL.so
Error: package/namespace load failed for 'RPostgreSQL'
The key bit of the error message is the missing symbol: _PQbackendPID. Some googling suggested this could be caused by mixing 32 and 64 bit libs. I used file to check and yes, indeed, I had a 32 bit version of Postgres that was refusing to talk to a 64 bit version on R. Suck.
In brief, the solution is to use ports to install postgres — in this case, postgres 8.4 as such:
sudo port install postgres84
you can use the file command to see what architecture your installed postgres is configured as:
laptop:src earl$ file `echo $PG_CONFIG`
/opt/local/lib/postgresql84/bin/pg_config: Mach-O 64-bit executable x86_64
checking, my previous postgres 8.4 install, from the Postgres Plus prebuild package, produces
file /Library/PostgresPlus/8.4SS/bin/pg_config
/Library/PostgresPlus/8.4SS/bin/pg_config: Mach-O universal binary with 2 architectures
/Library/PostgresPlus/8.4SS/bin/pg_config (for architecture ppc): Mach-O executable ppc
/Library/PostgresPlus/8.4SS/bin/pg_config (for architecture i386): Mach-O executable i386
Notice the lack of any 64bit support.
Then open a terminal, set the PG_CONFIG environmental variable to point to the right location, then run R from the terminal and install the package.
laptop: work earl$ export PG_CONFIG=/opt/local/lib/postgresql84/bin/pg_config
laptop: work earl$ R64
install.packages('RPostgreSQL', type='source')
If you have misconfigured the pg_config, this is the relevant bit of the compilation error message you will receive:
checking for "/libpq-fe.h"... no
configure: error: File libpq-fe.h not in ; installation may be broken.
ERROR: configuration failed for package ‘RPostgreSQL’
* removing ‘/Library/Frameworks/R.framework/Versions/2.10/Resources/library/RPostgreSQL’
Otherwise, RPostgreSQL will compile and install. Seriously, though, there *must* be a better way of distributing software on macs.
