ubuntu 安装 RPostgreSQL 库
其实大家在Linux 的R 中安装其他库，完全可以使用R 自带的安装方式，只是这个 RPostgreSQL 库需要用到 postgresql 的lib 库与include 头文件，所以才有若干个步骤去准备。
我自己的做法是这样的，首先在网上下载一个 RPostgreSQL 的源码包，源码下载地址：https://cran.r-project.org/src/contrib/RPostgreSQL_0.4-1.tar.gz ，解压源码包后，里面会有一个 configure 的文件，我们可以通过这个configure 文件来检测我们的环境是否已经可以安装 RPostgreSQL 库了。
例如我自己在检测时，发现我的环境了没有 postgresql 的lib 和include 相关文件，开始时的错误信息
checking for "/libpq-fe.h"... no
其实我自己的环境中，是有postgresql 的相关文件的，只是我不是通过 apt-get 或者是源码安装的方式来部署我自己的 postgresql 环境而已。
我的postgresql HOME 目录在 /home/sdbadmin/ssql，而且也能在ssql 的目录中看到 lib 和 include 目录，所以我自己就直接将文件拷贝到 /usr 路径下了（需要root 权限）
cp -rf /home/sdbadmin/ssql/include/* /usr/include/ cp -rf /home/sdbadmin/ssql/lib/* /usr/lib/
再来执行configure 文件，就能够通过了
root@chen:~/tmp/RPostgreSQL# ./configure checking for gcc... gcc checking for C compiler default output file name... a.out checking whether the C compiler works... yes checking whether we are cross compiling... no checking for suffix of executables... checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking build system type... x86_64-unknown-linux-gnu checking host system type... x86_64-unknown-linux-gnu checking target system type... x86_64-unknown-linux-gnu checking for pg_config... no configure: checking for PostgreSQL header files configure: Checking include /usr/include. configure: Checking lib /usr/lib. checking for "/usr/include/libpq-fe.h"... yes configure: creating ./config.status config.status: creating src/Makevars
然后我们就能放心在R 中安装 RPostgreSQL 库了
直接通过 Shell 进入R 环境，进入方式 就是大写 的R
执行安装命令
install.packages('RPostgreSQL')
它会让你选在一个下载方式，我自己随便选了个 2
--- Please select a CRAN mirror for use in this session --- HTTPS CRAN mirror 1: 0-Cloud [https] 2: Algeria [https] 3: Austria [https] 4: Belgium (Ghent) [https] 5: Brazil (SP 1) [https] 6: Canada (MB) [https] 7: Chile [https] 8: China (Beijing 4) [https] 9: Colombia (Cali) [https] 10: France (Lyon 1) [https] 11: France (Lyon 2) [https] 12: France (Paris 2) [https] 13: Germany (Münster) [https] 14: Iceland [https] 15: Italy (Padua) [https] 16: Japan (Tokyo) [https] 17: Malaysia [https] 18: Mexico (Mexico City) [https] 19: New Zealand [https] 20: Russia (Moscow) [https] 21: Serbia [https] 22: Spain (A Coruña) [https] 23: Spain (Madrid) [https] 24: Switzerland [https] 25: UK (Bristol) [https] 26: UK (Cambridge) [https] 27: USA (CA 1) [https] 28: USA (KS) [https] 29: USA (MI 1) [https] 30: USA (TN) [https] 31: USA (TX) [https] 32: USA (WA) [https] 33: (HTTP mirrors)
Selection: 2
它就开始扒拉扒拉 开始下载了
also installing the dependency ‘DBI’ trying URL 'https://cran.usthb.dz/src/contrib/DBI_0.4-1.tar.gz' Content type 'application/x-gzip' length 141644 bytes (138 KB) ================================================== downloaded 138 KB trying URL 'https://cran.usthb.dz/src/contrib/RPostgreSQL_0.4-1.tar.gz' Content type 'application/x-gzip' length 476204 bytes (465 KB) ================================================== downloaded 465 KB * installing *source* package ‘DBI’ ... ** package ‘DBI’ successfully unpacked and MD5 sums checked ** R ** inst ** preparing package for lazy loading ** help *** installing help indices ** building package indices ** installing vignettes ** testing if installed package can be loaded * DONE (DBI) * installing *source* package ‘RPostgreSQL’ ... ** package ‘RPostgreSQL’ successfully unpacked and MD5 sums checked checking for gcc... gcc checking for C compiler default output file name... a.out checking whether the C compiler works... yes checking whether we are cross compiling... no checking for suffix of executables... checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking build system type... x86_64-unknown-linux-gnu checking host system type... x86_64-unknown-linux-gnu checking target system type... x86_64-unknown-linux-gnu checking for pg_config... no configure: checking for PostgreSQL header files configure: Checking include /usr/include. configure: Checking lib /usr/lib. checking for "/usr/include/libpq-fe.h"... yes configure: creating ./config.status config.status: creating src/Makevars ** libs gcc -std=gnu99 -I/usr/local/lib/R/include -DNDEBUG -I/usr/include -I/usr/local/include -fpic -g -O2 -c RS-DBI.c -o RS-DBI.o gcc -std=gnu99 -I/usr/local/lib/R/include -DNDEBUG -I/usr/include -I/usr/local/include -fpic -g -O2 -c RS-PQescape.c -o RS-PQescape.o gcc -std=gnu99 -I/usr/local/lib/R/include -DNDEBUG -I/usr/include -I/usr/local/include -fpic -g -O2 -c RS-PostgreSQL.c -o RS-PostgreSQL.o RS-PostgreSQL.c: In function ‘RS_PostgreSQL_createDataMappings’: RS-PostgreSQL.c:446:5: warning: passing argument 1 of ‘Rf_protect’ from incompatible pointer type [enabled by default] /usr/local/lib/R/include/Rinternals.h:1348:6: note: expected ‘SEXP’ but argument is of type ‘struct RS_DBI_fields *’ gcc -std=gnu99 -I/usr/local/lib/R/include -DNDEBUG -I/usr/include -I/usr/local/include -fpic -g -O2 -c RS-pgsql-copy.c -o RS-pgsql-copy.o gcc -std=gnu99 -I/usr/local/lib/R/include -DNDEBUG -I/usr/include -I/usr/local/include -fpic -g -O2 -c RS-pgsql-getResult.c -o RS-pgsql-getResult.o gcc -std=gnu99 -I/usr/local/lib/R/include -DNDEBUG -I/usr/include -I/usr/local/include -fpic -g -O2 -c RS-pgsql-pqexec.c -o RS-pgsql-pqexec.o gcc -std=gnu99 -I/usr/local/lib/R/include -DNDEBUG -I/usr/include -I/usr/local/include -fpic -g -O2 -c RS-pgsql-pqexecparams.c -o RS-pgsql-pqexecparams.o gcc -std=gnu99 -shared -L/usr/local/lib -o RPostgreSQL.so RS-DBI.o RS-PQescape.o RS-PostgreSQL.o RS-pgsql-copy.o RS-pgsql-getResult.o RS-pgsql-pqexec.o RS-pgsql-pqexecparams.o -L/usr/lib -lpq installing to /usr/local/lib/R/library/RPostgreSQL/libs ** R ** inst ** preparing package for lazy loading Creating a generic function for ‘format’ from package ‘base’ in package ‘RPostgreSQL’ Creating a generic function for ‘print’ from package ‘base’ in package ‘RPostgreSQL’ Creating a generic function for ‘summary’ from package ‘base’ in package ‘RPostgreSQL’ ** help *** installing help indices ** building package indices ** testing if installed package can be loaded * DONE (RPostgreSQL) The downloaded source packages are in ‘/tmp/Rtmp4P3XQb/downloaded_packages’ Updating HTML index of packages in '.Library' Making 'packages.html' ... done
RPostgreSQL 安装好了之后，当然是先试试能不能工作了
执行如下命令
require(RPostgreSQL) drv = dbDriver("PostgreSQL") pgdb = dbConnect(drv, user="pgadmin", password="pgadmin",host="192.168.57.132", dbname="foo")
执行到 dbConnect 时，报错
Error in postgresqlNewConnection(drv, ...) : RS-DBI driver: (could not connect pgadmin@192.168.57.132 on dbname "foo"
细想，应该是postgresql 的访问权限问题，导致外来的客户端无法直接连接数据库
手工对postgresql 停服务，修改 postgresql 的配置参数，我自己的postgresql 环境，所有配置文件都在 pg_data 目录下，执行以下命令时，是在 /home/sdbadmin/ssql 目录下
sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '0.0.0.0'/g" pg_data/postgresql.conf ; linenum=$(cat -n pg_data/pg_hba.conf | grep "# IPv4 local connections:" | awk '{print $1}'); let "linenum=linenum+1";varStr="host all all 0.0.0.0/0 trust"; sed -i "${linenum} a${varStr}" pg_data/pg_hba.conf;
启动postgresql 服务
bin/postgres -D pg_data/ >> logfile 2>&1 &
再回过头来测试 RPostgreSQL 库
pgdb = dbConnect(drv, user="pgadmin", password="pgadmin",host="192.168.57.132", dbname="foo")
通过了，然后在里面查找数据
dbGetQuery(pgdb, "select * from t2")
成功返回记录
name 1 ttt
到这里就完全介绍完 RPostgreSQL 库如何安装和与 postgresql 进行对接，希望能够对大家有所帮助
参考博客：
http://blog.chinaunix.net/uid-14874549-id-3457872.html
http://www.01yun.com/dotnet/20130711/571315.html
http://stackoverflow.com/questions/22202141/installing-rpostgresql-on-linux
http://www.sequoiadb.com/cn/index.php?a=index&m=Files&cat_id=1432190716&edition_id=0
再进击
