• 粉丝日志首页

2020微软Virtual Azure Community Day-异常检测算法

跨界知识聚会系列文章,“知识是用来分享和传承的”,各种会议、论坛、沙龙都是分享知识的绝佳场所。我也有幸作为演讲嘉宾参加了一些国内的大型会议,向大家展示我所做的一些成果。从听众到演讲感觉是不一样的,把知识分享出来,你才能收获更多。

关于作者

  • 张丹,分析师/程序员/Quant: R,Java,Nodejs
  • blog: http://fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/meeting-ms-virtual-community-day-20201203

前言

由于疫情在全球肆虐,原来线下的各种会议都改成了线上的模式。微软及时做了线上的分享的调整,这次有幸参加微软的Virtual Community Day,做一次数据分析的主题演讲。除了我的分享,其他人都是.Net和Azure的主题,希望听众能接受这种跨领域的内容。

目录

  1. 我分享的主题:异常检测算法-自动发现数据中的异常值
  2. 会议体验和照片分享

1. 我分享的主题:异常检测算法-自动发现数据中的异常值

在处理时间序列数据时,经常会观测数据中有一个或几个数值与其他数值相比差异较大,或者在周期型的数据中出现了与周期性不相符的数据分布。通过异常检测算法,可以对不匹配预期的模式或异常数据进行识别,自动发现数据中的离群值、噪声值、偏差值等。

我分享主题:数据分析领域正在发生的变革

数据分析,作为大数据和人工智能的一个分支,正在各领域中发挥着作用。异常检测就是一种常见的,而且落地的一个AI的应用场景。本次我的分享也是从4个方面进行介绍,本次分享的PPT下载

我主要为分四个部分进行介绍:

  • 什么是异常检测?
  • 异常检测算法介绍
  • R语言算法实现
  • 现实场景应用

异常检测(Anomaly detection)是目前时序数据分析最成熟的应用之一,从正常的时间序列中识别不正常的事件或行为的过程。

常见的应用场景包括

  • 金融领域:从金融数据中识别”欺诈案例“,如识别信用卡申请欺诈、虚假信贷等;
  • 网络安全:从流量数据中找出”入侵者“,并识别新的网络入侵模式;
  • 电商领域:从交易数据中识别”恶意买家“,如羊毛党、恶意刷屏团伙;
  • 生态灾难预警:基于对风速、降雨量、气温等指标的预测,判断未来可能出现的极端天气;
  • 工业界:可通过异常检测手段进行工业产品的瑕疵检测,代替人眼进行测量和判断。

举例说明一下,下图就是一组时间序列数据,这些数据有趋势型的、周期型的、平稳型型的,蓝色的线是正常的数据,红色的点代表异常的数据。

  • 左上1图是趋势型的,红色的点,在数据趋势变动的过程中,出现了一种凸起。
  • 左下1图是平稳型的,红色的点,在数据点突然的变大,导致数据不平稳了。
  • 左下2图是周期型的,红色的点,出现的位置都是反周期的,导致数据局部反周期的异常。

这些数据异常的情况,在我们的现实生活中会经常的发生,通过算法来自动识别这样的异常,就可以大大解放人的工作,从而实现AI驱动。

2. 会议体验和照片分享

Virtual Azure Community Day全球直播又来啦,本次大会的官方页面:https://azureday.community/, 微信公众号地址:https://mp.weixin.qq.com/s/L2xDf1JIZsHYwJyEZ0wejA

2.1 会议主题

会议主题:从10:00开始 到 17:15,连续不间断。

2.2 相关照片

我在进行分享时候的屏幕截图,CSDN直播 热度1w 不知道是一个什么水平。

最后,整个分享结束,各位嘉宾都辛苦啦。

微软在越来越放开,融合各种技术,并且自己也在支持多种技术的融合和创新。同时,R语言做为数据分析的主要语言,一定会在各个领域中大有可为。

最后打个小广告:公司招聘!

转载请注明出处:
http://blog.fens.me/meeting-ms-virtual-community-day-20201203

打赏作者

打通Linux上的ODBC

R的极客理想系列文章,涵盖了R的思想,使用,工具,创新等的一系列要点,以我个人的学习和体验去诠释R的强大。

R语言作为统计学一门语言,一直在小众领域闪耀着光芒。直到大数据的爆发,R语言变成了一门炙手可热的数据分析的利器。随着越来越多的工程背景的人的加入,R语言的社区在迅速扩大成长。现在已不仅仅是统  计领域,教育,银行,电商,互联网….都在使用R语言。

要成为有理想的极客,我们不能停留在语法上,要掌握牢固的数学,概率,统计知识,同时还要有创新精神,把R语言发挥到各个领域。让我们一起动起来吧,开始R的极客理想。

关于作者:

  • 张丹,分析师/程序员/Quant: R,Java,Nodejs
  • blog: http://fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/r-rodbc-linux/

前言

ODBC是由微软公司开发的,用于数据库连接的统一连接方案,在基于Windows的操作系统上被广泛地使用。在Linux上,其实也有ODBC的解决方案。今天我就来试试,在Linux上配置ODBC进行数据库的连接和交互。关于Window的ODBC解决方案,请参考文章用RODBC连接数据库

目录

  1. 在Linux在安装ODBC
  2. 通过RODBC连接PostgreSQL数据库
  3. 通过RODBC连接MySQL数据库

1. 在Linux在安装ODBC

我们先来整理一下ODBC在 Linux 环境中系统架构,我们需要找到Linux的ODBC软件程序 unixodbc ,unixodbc 就是解决方案核心的连接器。通过 unixodbc 一端绑定数据库,另一端绑定编程语言的API。

我在测试的过程中,ODBC只能是绑定本地的数据库,不能连接远程数据库,所以需要在本地计算机提前装好数据库。MySQL的数据库安装请参考文章在Ubuntu上安装MySQL,PostgreSQL的数据库安装请参考文章在Ubuntu上安装PostgreSQL

我们具体的操作步骤:

  • 第一步,在Linux中安装unixodbc驱动程序
  • 第二步,绑定MySQL/PostgreSQL数据库到unixodbc。
  • 第三步,用RDOBC与unixodbc建立连接

我使用Linux Ubuntu 操作系统,可以通过apt 来直接安装 unixodbc 软件包。

我的操作系统环境:

  • Linux Ubuntu 20.04.1 LTS 64bit
  • R version 3.6.3 64bit

在Linux中,安装unixodbc 软件,就是一条命令。


~ sudo apt install unixodbc

在odbc软件安装好后,在 /etc/目录下面,就可以看到2个文件,odbc.ini和odbcinst.ini文件。

  • odbc.ini,文件中主要是配置数据库连接,要手动配置
  • odbcinst.ini,配置数据库驱动文件,各数据库驱动安装时,自动配置。

通过odbcinst 的工具,来查看一下odbc程序的基本情况。


~ odbcinst -j
unixODBC 2.3.6
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/conan/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

另外,可以通过isql的命令行工具,来测试一下odbc的数据库连接,后面配置好数据源后就可以进行测试。

打开odbc客户端 isql 。


~ isql
**********************************************
* unixODBC - isql                            *
**********************************************
* Syntax                                     *
*                                            *
*      isql DSN [UID [PWD]] [options]        *
*                                            *
* Options                                    *
*                                            *
* -b         batch.(no prompting etc)        *
* -dx        delimit columns with x          *
* -x0xXX     delimit columns with XX, where  *
*            x is in hex, ie 0x09 is tab     *
* -w         wrap results in an HTML table   *
* -c         column names on first row.      *
*            (only used when -d)             *
* -mn        limit column display width to n *
* -v         verbose.                        *
* -lx        set locale to x                 *
* -q         wrap char fields in dquotes     *
* -3         Use ODBC 3 calls                *
* -n         Use new line processing         *
* -e         Use SQLExecDirect not Prepare   *
* -k         Use SQLDriverConnect            *
* -L         Length of col display (def:300) *
* --version  version                         *
*                                            *
* Commands                                   *
*                                            *
* help - list tables                         *
* help table - list columns in table         *
* help help - list all help options          *
*                                            *
* Examples                                   *
*                                            *
*      isql WebDB MyID MyPWD -w < My.sql     *
*                                            *
*      Each line in My.sql must contain      *
*      exactly 1 SQL command except for the  *
*      last line which must be blank (unless *
*      -n option specified).                 *
*                                            *
* Please visit;                              *
*                                            *
*      http://www.unixodbc.org               *
*      nick@lurcher.org                      *
*      pharvey@codebydesign.com              *
**********************************************

装完ODBC后,我们就可以尝试把不同的数据库和ODBC进行绑定,再通过程序来连接ODBC,从而实现对数据库的访问。

2. 通过RODBC连接PostgreSQL数据库

我们开始配置PostgreSQL数据库,进行ODBC数据源的绑定。

2.1 配置PostgreSQL的ODBC数据源

安装PostgresSQL在Ubuntu上的odbc驱动


~ sudo apt install odbc-postgresql

安装后驱动程序后,我们会发现在 /etc/odbcinst.ini 文件中,已经配置了好PostgreSQL 驱动程序信息。

查看文件 /etc/odbcinst.ini


~ cat /etc/odbcinst.ini
[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

之后,我们需要修改 /etc/odbc.ini,把PostgreSQL数据库绑定到ODBC数据源中,就可以使用ODBC了。

编辑文件 /etc/odbc.ini,


~ vi /etc/odbc.ini

[postgres01]
Description = post01
Driver      = PostgreSQL Unicode
Database = testdb
Servername = 127.0.0.1
UserName = test
Password = test
Port = 5432
ReadOnly = 0
ConnSettings = set client_encoding to UTF8

参数解释

  • [postgres01]:为用户DNS,这个名称是用于配置到编程语言程序中的。
  • Driver: PostgreSQL Unicode,驱动名词,这个名词为 /etc/odbcinst.ini 所对应的驱动名词。
  • Database :testdb,PostgreSQL的数据库名称
  • Servername : 127.0.0.1,PostgreSQL的数据库服务器IP
  • UserName: test,PostgreSQL的数据库用户名
  • Password: test,PostgreSQL的数据库用户密码
  • Port: 5432,PostgreSQL的数据库端口
  • ReadOnly: 0,是否是只读,0不是,1是
  • ConnSettings :客户端的连接字符编码

用命令检测数据源是否配置成功


~ isql -v postgres01
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> 

2.2 用RODBC进行数据库连接

RODBC的安装,比较简单,在R语言环境中用install.packages(“RODBC”)就能成功安装了。也可以参考Window的ODBC解决方案,请参考文章用RODBC连接数据库

下面我们在Ubuntu的R语言环境中调用ODBC。


# 安装RODBC
> install.packages("RODBC")
> library(RODBC)

建立ODBC的数据库连接。


# 建立连接
> o1<-odbcConnect("postgres01")

# 查看连接对象
> o1
RODBC Connection 6
Details:
  case=tolower
  DSN=postgres01
  DATABASE=testdb
  SERVER=127.0.0.1
  PORT=5432
  UID=test
  PWD=******
  SSLmode=disable
  ReadOnly=0
  Protocol=7.4
  FakeOidIndex=0
  ShowOidColumn=0
  RowVersioning=0
  ShowSystemTables=0
  Fetch=100
  UnknownSizes=0
  MaxVarcharSize=255
  MaxLongVarcharSize=8190
  Debug=0
  CommLog=0
  UseDeclareFetch=0
  TextAsLongVarchar=1
  UnknownsAsLongVarchar=0
  BoolsAsChar=1
  Parse=0
  ExtraSysTablePrefixes=
  LFConversion=0
  UpdatableCursors=1
  TrueIsMinus1=0
  BI=0
  ByteaAsLongVarBinary=1
  UseServerSidePrepare=1
  LowerCaseIdentifier=0
  D6=-101

# 查看连接信息
> odbcGetInfo(o1)
       DBMS_Name         DBMS_Ver  Driver_ODBC_Ver Data_Source_Name
    "PostgreSQL"         "12.0.4"          "03.51"     "postgres01"
     Driver_Name       Driver_Ver         ODBC_Ver      Server_Name
  "psqlodbcw.so"     "12.01.0000"          "03.52"      "127.0.0.1"

接下来,做一下数据库的操作。


# 查看数据库中的所有表的列表
> sqlTables(o1)
  TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
1    testdb      public        acc      TABLE
2    testdb      public    account      TABLE
3    testdb      public       iris      TABLE

# 读取iris表
> idat<-sqlFetch(o1, "iris");idat
  sepallength sepalwidth petallength petalwidth species
1         5.1        3.5         1.4        0.2  setosa
2         4.9        3.0         1.4        0.2  setosa
3         4.7        3.2         1.3        0.2  setosa
4         4.6        3.1         1.5        0.2  setosa
5         5.0        3.6         1.4        0.2  setosa

# 增加插入数据
> iris[6:7,]
  Sepal.Length Sepal.Width Petal.Length Petal.Width Species
6          5.4         3.9          1.7         0.4  setosa
7          4.6         3.4          1.4         0.3  setosa

# 执行插入命令
> sqlSave(o1, iris[6:7,] ,"iris",append=TRUE)
> sqlFetch(o1, "iris")
  sepallength sepalwidth petallength petalwidth species
1         5.1        3.5         1.4        0.2  setosa
2         4.9        3.0         1.4        0.2  setosa
3         4.7        3.2         1.3        0.2  setosa
4         4.6        3.1         1.5        0.2  setosa
5         5.0        3.6         1.4        0.2  setosa
6         5.4        3.9         1.7        0.4  setosa
7         4.6        3.4         1.4        0.3  setosa

2.3 错误排查

用RODBC进行连接时,可能会报这个错误,主要是 /etc/odbc.ini 文件中的Driver没有写对造成的,详细核对一下配置就行了。


> o1<-odbcConnect("postgres01")
Warning messages:
1: In RODBC::odbcDriverConnect("DSN=postgres01") :
  [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specified
2: In RODBC::odbcDriverConnect("DSN=postgres01") : ODBC connection failed

3. 通过RODBC连接MySQL数据库

同样的步骤,我们先安装Linux ubuntu中MySQL的ODBC驱动程序,

3.1 配置MySQL的ODBC数据源

首先,安装MySQL的ODBC恭驱动。在apt源中,找到mysql-connector-odbc的包尝试安装,但会出现错误。


# 从apt源中安装mysql odbc
~ sudo apt install mysql-connector-odbc
Reading package lists... Done
Building dependency tree
Reading state information... Done
Package mysql-connector-odbc is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source

E: Package 'mysql-connector-odbc' has no installation candidate

安装失败,我们就需要换一种方式进行安装。从MySQL官网手动下载MySQL驱动 https://dev.mysql.com/downloads/connector/odbc/ 上传到服务器/home/conan/soft 目录,我下载的包为 mysql-connector-odbc-8.0.22-linux-glibc2.12-x86-64bit.tar.gz

上传到/home/conan/soft 目录,进行解压。


~ cd /home/conan/soft
~ tar xvf mysql-connector-odbc-8.0.22-linux-glibc2.12-x86-64bit.tar.gz
~ cd  mysql-connector-odbc-8.0.22-linux-glibc2.12-x86-64bit

在 /usr/lib 下面新建mysql-odbc目录,把mysql-odbc类库复制过来。


~ sudo mkdir /usr/lib/mysql-odbc
~ sudo cp -r /home/conan/soft/mysql-connector-odbc-8.0.22-linux-glibc2.12-x86-64bit/lib/* /usr/lib/mysql-odbc

通过myodbc-installer命令,手动安装mysql-odbc的驱动,指定libmyodbc8w.so库对应的位置。


 ~ sudo bin/myodbc-installer -d -a -n "MySQL ODBC 8.0 Unicode Driver" -t "DRIVER=/usr/lib/mysql-odbc/libmyodbc8w.so"
Success: Usage count is 1

安装完成后,在 odbcinst.ini 文件中,会自动创建MySQL的驱动,建立和ODBC的连接。

查看odbcinst.ini文件,增加了 [MySQL ODBC 8.0 Unicode Driver] 的驱动声明。


~ cat /etc/odbcinst.ini

[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[MySQL ODBC 8.0 Unicode Driver]
Driver=/usr/lib/mysql-odbc/libmyodbc8w.so
UsageCount=1

接下来,我们配置MySQL数据库,与ODBC进行连接。编辑odbc.ini文件,增加[mysql01]的DNS命名。


[mysql01]
Description = post01
Driver      = MySQL ODBC 8.0 Unicode Driver
Database = world
Servername = 192.168.1.6
UserName = root
Password = bsspirit
Port = 3306
ReadOnly = 0
ConnSettings = set client_encoding to UTF8

用isql检查一下,ODBC是否配置成功。


~ sudo isql -v mysql01
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from aa;
+-----------+
| id        |
+-----------+
| 1         |
| 2         |
+-----------+
SQLRowCount returns 2
2 rows fetched
SQL>

3.2 用RODBC进行数据库连接


# 加载程序包
> library(RODBC)

# 连接ODBC
> o2<-odbcConnect("mysql01")

# 查看连接信息
> o2
RODBC Connection 1
Details:
  case=nochange
  DSN=mysql01
> odbcGetInfo(o2)
                DBMS_Name                  DBMS_Ver           Driver_ODBC_Ver
                  "MySQL" "8.0.22-0ubuntu0.20.04.2"                   "03.80"
         Data_Source_Name               Driver_Name                Driver_Ver
                "mysql01"          "libmyodbc8w.so"              "08.00.0022"
                 ODBC_Ver               Server_Name
                  "03.52"    "localhost via TCP/IP"

最后,我们进行一些数据库操作,来验证一下数据库连接成功。


# 查询数据表
> sqlFetch(o2, "aa")
  id
1  1
2  2

# 查入数据表
> sqlQuery(o2,"insert into aa values(3)")
character(0)

# 查询数据表
> sqlFetch(o2, "aa")
  id
1  1
2  2
3  3

经过测试我们就完成了RODBC在Linux上的数据连接操作,这样就可以方便地解决数据库多样性的问题,不仅能让代码保持一致的风格,而且对于复杂的配置过程也可以一次性的解决,是一个不错的方案。

转载请注明出处:
http://blog.fens.me/r-rodbc-linux/

打赏作者

用RODBC连接数据库

R的极客理想系列文章,涵盖了R的思想,使用,工具,创新等的一系列要点,以我个人的学习和体验去诠释R的强大。

R语言作为统计学一门语言,一直在小众领域闪耀着光芒。直到大数据的爆发,R语言变成了一门炙手可热的数据分析的利器。随着越来越多的工程背景的人的加入,R语言的社区在迅速扩大成长。现在已不仅仅是统  计领域,教育,银行,电商,互联网….都在使用R语言。

要成为有理想的极客,我们不能停留在语法上,要掌握牢固的数学,概率,统计知识,同时还要有创新精神,把R语言发挥到各个领域。让我们一起动起来吧,开始R的极客理想。

关于作者:

  • 张丹,分析师/程序员/Quant: R,Java,Nodejs
  • blog: http://fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/r-rodbc/

前言

在进行数据开发的时候,数据源经常是多样,有时候是Oracle,有时候是MySQL,有时候是PostgreSQL等,不同的数据库有不同的特征和不同的API。很难保证,开发人员对每种数据库都熟悉,而且一个项目如果使用超过3个数据库,程序复杂度会骤然升高。

微软提供了一个基于Window的数据开放接口统一方案就是ODBC,让所有的数据库都通过ODBC进行注册统一管理,我们只需要基于ODBC进行编程,就可以连接所有数据库了。RODBC包,则作为R语言连接ODBC的数据访问接口程序。

目录

  1. ODBC和RODBC包介绍
  2. 通过RODBC连接MySQL数据库
  3. 通过RODBC连接PostgreSQL数据库

1. ODBC和RODBC包介绍

ODBC(Open Database Connectivity,开放数据库互连),是微软公司开放服务结构(WOSA,Windows Open Services Architecture)中有关数据库的一个组成部分,它建立了一组规范,并提供了一组对数据库访问的标准API(应用程序编程接口)。这些API利用SQL来完成其大部分任务。ODBC本身也提供了对SQL语言的支持,用户可以直接将SQL语句送给ODBC。一组数据的位置,可以使用 ODBC驱动程序访问该位置。

一个基于ODBC的应用程序对数据库的操作不依赖任何DBMS,不直接与DBMS打交道,所有的数据库操作由对应的DBMS的ODBC驱动程序完成。也就是说,不论是MySQL 还是 Oracle数据库,均可用ODBC API进行访问。由此可见,ODBC的最大优点是能以统一的方式处理所有的数据库。

RODBC包,为R语言实现了ODBC数据库连接。在RODBC中提供了两种主要功能函数,主要是内部 odbc* 开头的函数,命令使用相似的名称实现对C语言级别低级访问。 sql*开头函数,在更高级别上运行以读取,保存,复制和操作数据之间的数据框架和SQL表。

2. 通过RODBC连接MySQL数据库

我们尝试在Window操作系统进行ODBC的连接。

Window系统的环境:

  • win10 64bit professional
  • R version 3.6.1 64bit

2.1 配置ODBC数据源

首先,需要安装MySQL,具体安装过程请参考文章在Ubuntu中安装MySQL

我们需要在win10操作中先配置MySQL到ODBC连接,所以需要安装ODBC的驱动程序,请下载Connector/ODBC,然后安装。

打开 “ODBC数据源管理器” 界面,配置MySQL到ODBC连接。

选 用户DSN ,然后 添加 一个配置,会弹出选择数据源驱动程序,这个驱动是需要安装的,用来让数据库可以和ODBC进行连接。Window系统中,通常会默认支持MS SQL Server数据库。

选择 MySQL ODBC 后,输入MySQL连接的配置信息。

这样,MySQL和ODBC的连接就算是建立好了,我们就可以用R语言通过RODBC的通道和MySQL数据库进行通信了。DSN就是外部调用的名字。

2.2 安装RODBC包

接下来,我们安装RODBC,非常简单,一条语句就行了。

安装和加载RPostgreSQL包

> install.packages("RODBC")
> library(RODBC)

2.3 RODBC包API操作

基本的ODBC的API操作。

  • odbcConnect(“dsn”, uid=”username”, pwd=”password”),建立ODBC数据库连接
  • sqlTables(channel),查看数据库中的表
  • sqlFetch(channel, sqtable), 读取数据表并返回一个数据框
  • sqlQuery(channel, query), 向数据库提交一个查询,并返回结果
  • sqlSave(channel, mydf, tablename = sqtable, append = FALSE), 将一个数据框写入或更新(append=True)到数据库
  • sqlColumns(channel, sqtable) , 返回数据库表sqtable列的信息
  • sqlDrop(channel, sqtable), 从数据库删除一个表
  • sqlClear(channel, sqtable), 删除表中的内容
  • close(channel), 关闭连接

2.4 建立ODBC数据库连接

接下来,我们就可以使用RODBC进行ODBC的数据源连接了。


# 查看ODBC支持的数据源
> odbcDataSources()
                          mysql 
"MySQL ODBC 8.0 Unicode Driver" 

# 建立ODBC连接,设置DSN名字
> ocon1<-odbcConnect("mysql")

# 查看连接信息
> ocon1
RODBC Connection 4
Details:
  case=tolower
  DSN=mysql

# 查看连接信息
> odbcGetInfo(ocon1)
             DBMS_Name               DBMS_Ver        Driver_ODBC_Ver       Data_Source_Name            Driver_Name 
               "MySQL"               "8.0.19"                "03.80"                "mysql"         "myodbc8w.dll" 
            Driver_Ver               ODBC_Ver            Server_Name 
          "08.00.0019"           "03.80.0000" "localhost via TCP/IP" 

2.5 查看数据库基本信息

查看数据库中所有表


# 使用SQL,查看数据库中所有的表
> sqlQuery(ocon1, "SHOW TABLES")
  Tables_in_world
1            city
2           city2
3         country
4 countrylanguage

# 通过函数,查看数据库有所有的表
> sqlTables(ocon1)
  TABLE_CAT TABLE_SCHEM      TABLE_NAME TABLE_TYPE REMARKS
1     world                        city      TABLE        
2     world                       city2      TABLE        
3     world                     country      TABLE        
4     world             countrylanguage      TABLE        

2.5 查看数据表的结构基本信息

查看数据表的结构


# 用SQL查看city表数据结构
> sqlQuery(ocon1, "DESCRIBE city")
        Field     Type Null Key Default          Extra
1          ID      int   NO PRI      NA auto_increment
2        Name char(35)   NO          NA               
3 CountryCode  char(3)   NO MUL      NA               
4    District char(20)   NO          NA               
5  Population      int   NO           0    

# 用函数,查看city表数据结构
> sqlColumns(ocon1, "city")
  TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS NUM_PREC_RADIX NULLABLE
1                  <na>       city          ID         4   integer          10             4              0             10        1
2                  <na>       city        Name         1      char          35            35             NA             NA        0
3                  <na>       city CountryCode         1      char           3             3             NA             NA        0
4                  <na>       city    District         1      char          20            20             NA             NA        0
5                  <na>       city  Population         4   integer          10             4              0             10        0
  REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE
1                  0             4               NA                NA                1         YES
2                 ''             1               NA                35                2          NO
3                 ''             1               NA                 3                3          NO
4                 ''             1               NA                20                4          NO
5                  0             4               NA                NA                5          NO

2.6 用SQL执行查询

通常的数据库操作,我们都是基于SQL语句的,可以使用sqlQuery操作进行查询。


> sqlQuery(ocon1, paste("SELECT Code,Name,Continent,Region,GNP FROM country WHERE GNP > 500000 ORDER BY GNP DESC"))
   Code           Name     Continent          Region     GNP
1   USA  United States North America   North America 8510700
2   JPN          Japan          Asia    Eastern Asia 3787042
3   DEU        Germany        Europe  Western Europe 2133367
4   FRA         France        Europe  Western Europe 1424285
5   GBR United Kingdom        Europe British Islands 1378330
6   ITA          Italy        Europe Southern Europe 1161755
7   CHN          China          Asia    Eastern Asia  982268
8   BRA         Brazil South America   South America  776739
9   CAN         Canada North America   North America  598862
10  ESP          Spain        Europe Southern Europe  553233

2.7 批量数据操作

R语言中,经常会有对数据批量处理,ODBC的API也提供了,批量进行增、删、改、查操作的API函数。


# 增加,将一个数据框写入或更新
> sqlSave(ocon1, iris[1:5,] ,"iris")

# 查询
> sqlFetch(ocon1, "iris")
  sepallength sepalwidth petallength petalwidth species
1         5.1        3.5         1.4        0.2  setosa
2         4.9        3.0         1.4        0.2  setosa
3         4.7        3.2         1.3        0.2  setosa
4         4.6        3.1         1.5        0.2  setosa
5         5.0        3.6         1.4        0.2  setosa

# 增量写入
> sqlSave(ocon1, iris[6:10,] ,"iris", append=TRUE)

# 查询
> df1<-sqlFetch(ocon1, "iris");df1
   sepallength sepalwidth petallength petalwidth species
1          5.1        3.5         1.4        0.2  setosa
2          4.9        3.0         1.4        0.2  setosa
3          4.7        3.2         1.3        0.2  setosa
4          4.6        3.1         1.5        0.2  setosa
5          5.0        3.6         1.4        0.2  setosa
6          5.4        3.9         1.7        0.4  setosa
7          4.6        3.4         1.4        0.3  setosa
8          5.0        3.4         1.5        0.2  setosa
9          4.4        2.9         1.4        0.2  setosa
10         4.9        3.1         1.5        0.1  setosa

# 修改
> df1[,1]<-rnorm(1:10)
> df2<-df1[2:4,];df2
  sepallength sepalwidth petallength petalwidth species
2  -0.1721062        3.0         1.4        0.2  setosa
3   1.2726538        3.2         1.3        0.2  setosa
4   0.4437929        3.1         1.5        0.2  setosa
> sqlUpdate(ocon1, df2, "iris")

# 查看2-4行的修改结果
> sqlFetch(ocon1, "iris")
   sepallength sepalwidth petallength petalwidth species
1    5.1000000        3.5         1.4        0.2  setosa
2   -0.1721062        3.0         1.4        0.2  setosa
3    1.2726538        3.2         1.3        0.2  setosa
4    0.4437929        3.1         1.5        0.2  setosa
5    5.0000000        3.6         1.4        0.2  setosa
6    5.4000000        3.9         1.7        0.4  setosa
7    4.6000000        3.4         1.4        0.3  setosa
8    5.0000000        3.4         1.5        0.2  setosa
9    4.4000000        2.9         1.4        0.2  setosa
10   4.9000000        3.1         1.5        0.1  setosa

# 清空内数据
> sqlClear(ocon1, "iris")
> sqlFetch(ocon1, "iris")
[1] sepallength sepalwidth  petallength petalwidth  species    
<0 行> (或0-长度的row.names)

# 删除表
> sqlDrop(ocon1, "iris", errors = FALSE)
> sqlFetch(ocon1, "iris")
Error in odbcTableExists(channel, sqtable) : 
  ‘iris’: table not found on channel

2.8 事务处理

事务操作,默认的ODBC数据库连接的自动提交模式,必须手动设置了autoCommit = FALSE 后在odbcSetAutoCommit()函数中,才会进行手动管理。 一个事务完成后,还需要再次调用odbcSetAutoCommit()函数。进行下一条语句的事务管理。


# 设置
> odbcSetAutoCommit(ocon1,autoCommit = FALSE)
[1] 0

# 创建表
> sqlSave(ocon1, iris[1:5,] ,"iris")

# 执行查询,表示已经成功
> sqlFetch(ocon1, "iris")
  sepallength sepalwidth petallength petalwidth species
1         5.1        3.5         1.4        0.2  setosa
2         4.9        3.0         1.4        0.2  setosa
3         4.7        3.2         1.3        0.2  setosa
4         4.6        3.1         1.5        0.2  setosa
5         5.0        3.6         1.4        0.2  setosa

# 事务不提交,则进行回滚
> odbcEndTran(ocon1,commit = FALSE)
[1] 0

# 再次查询时,表中没有数据。
> sqlFetch(ocon1, "iris")
[1] sepallength sepalwidth  petallength petalwidth  species    
<0 行> (或0-长度的row.names)

从上面的例子中,我们可以看到事务可以控制数据的插入(DML)操作,但不能控制表的创建(DDL)。

最后,关闭数据库连接。


# 关闭单个数据库连接
> odbcClose(ocon1)

# 关闭所有数据库连接
> odbcCloseAll()

# 查看连接信息
> odbcGetInfo(ocon1)
Error in odbcGetInfo(ocon1) : argument is not an open RODBC channel

3. 通过RODBC连接PostgreSQL数据库

整个过程与上文中MySQL的过程类似,简略介绍。首先,安装PostgreSQL数据库,我在远程服务器进行了安装,安装过程请参考文章在Ubuntu上安装PostgreSQL

下载window上的postgreSQL的ODBC数据源驱动psqlODBC ,https://odbc.postgresql.org/

下载后进行驱动程序的安装,然后在ODBC的驱动程序的管理控制台,可以查看刚安装的PostgreSQL ODBC 驱动程序。

为postgreSQL数据库,添加 用户DSN 。

PostgreSQL数据库的配置信息。

接下为,用R语言连接 配置好的ODBC 的数据源。


# 加载RODBC包
> library(RODBC)

# 查看odbc数据源支持,这时又多了一个postgresql的数据源
> odbcDataSources()
                          mysql                      postgresql 
"MySQL ODBC 8.0 Unicode Driver"       "PostgreSQL Unicode(x64)" 

# 建立数据连接
> ocon2<-odbcConnect("postgresql")

# 查看连接信息
> odbcGetInfo(ocon2)
        DBMS_Name          DBMS_Ver   Driver_ODBC_Ver  Data_Source_Name       Driver_Name        Driver_Ver          ODBC_Ver 
     "PostgreSQL"          "12.0.4"           "03.51"      "postgresql" "PSQLODBC35W.DLL"      "12.02.0000"      "03.80.0000" 
      Server_Name 
    "192.168.1.5" 

# 查看数据库中,所有数所表
> sqlTables(ocon2)
  TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
1    testdb      public        acc      TABLE        
2    testdb      public    account      TABLE        
3    testdb      public       iris      TABLE        

# 读取account表的数据
> sqlFetch(ocon2, "account")
  user_id username password
1       3       a3     密码
2       5       fd   mddddd

# 关闭数据连接
> odbcClose(ocon1)

在我们配置好了 ODBC 数据源后,从R的代码的角度,对MySQL和PostgreSQL的数据库操作API都是一致的,都可以基本RODBC包提供的API来完成。ODBC很好的解决了,不同数据库之间数据一致性访问的问题。

转载请注明出处:
http://blog.fens.me/r-rodbc/

打赏作者

RPostgreSQL数据库编程指南

R的极客理想系列文章,涵盖了R的思想,使用,工具,创新等的一系列要点,以我个人的学习和体验去诠释R的强大。

R语言作为统计学一门语言,一直在小众领域闪耀着光芒。直到大数据的爆发,R语言变成了一门炙手可热的数据分析的利器。随着越来越多的工程背景的人的加入,R语言的社区在迅速扩大成长。现在已不仅仅是统计领域,教育,银行,电商,互联网….都在使用R语言。

要成为有理想的极客,我们不能停留在语法上,要掌握牢固的数学,概率,统计知识,同时还要有创新精神,把R语言发挥到各个领域。让我们一起动起来吧,开始R的极客理想。

关于作者:

  • 张丹,分析师/程序员/Quant: R,Java,Nodejs
  • blog: http://fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/r-rpostgressql/

前言

PostgreSQL和R通常可以一起用于数据分析,PostgreSQL作为数据库引擎,R作为统计工具。在R语言中,有2个包可以直接通过R连接PostgreSQL数据库,分别是RPostgreSQL包和RPostgres包,本文将先介绍用RPostgreSQL包进行直接连接PostgreSQL数据库。间接的连接方法还有RJDBC,RODBC等,以后再有专门的文章进行介绍。

PostgreSQL的数据库安装和基本信息,请参考文章在Ubuntu上安装PostgreSQL

目录

  1. RPostgreSQL包介绍
  2. 安装RPostgreSQL包
  3. RPostgreSQL包的API使用
  4. window客户端环境的中文乱码解决方案

1. RPostgreSQL包介绍

RPostgreSQL一个R语言程序包,由官方提供访问 PostgreSQL数据库的R语言接口程序,RPostgreSQL主要依赖于DBI项目,以访问 PostgreSQL 数据库系统。CRAN中的项目地址
https://cran.r-project.org/web/packages/RPostgreSQL/index.html

2. 安装RPostgreSQL包

RPostgreSQL包的安装过程非常简单,就是一条语句就能够搞定了,不管是在Window环境还是Linux环境。

在Linux Ubuntu中,需要先安装libpq-dev系统库,不然会出现libpq-fe.h错误


In file included from RS-PQescape.c:7:
RS-PostgreSQL.h:23:14: fatal error: libpq-fe.h: No such file or directory
   23 | #    include "libpq-fe.h"
      |              ^~~~~~~~~~~~

安装libpq-dev系统库


~ sudo apt install libpq-dev

安装和加载RPostgreSQL包


> install.packages("RPostgreSQL")
> library(RPostgreSQL)

3. RPostgreSQL包的API使用

RPostgreSQL包,提供了很多的API函数,API的使用过程,我们可以分成6个部分来介绍。

  • 3.1 数据库连接
  • 3.2 数据库状态查看
  • 3.3 数据直接查询
  • 3.4 数据交互查询
  • 3.5 数据批量插入
  • 3.6 事务处理

3.1 建立数据库链接


# 加载PostgreSQL驱动
> drv = dbDriver("PostgreSQL") 

# 建立数据库连接
> con1 = dbConnect(drv,  dbname="testdb",user = "test",  password = "test", host ="192.168.1.5")

3.2 数据库状态查看


# 再建立2个数据库连接
> con2 = dbConnect(drv,  dbname="testdb",user = "test",  password = "test", host ="192.168.1.5")
> con3 = dbConnect(drv,  dbname="testdb",user = "test",  password = "test", host ="192.168.1.5")

查看驱动状态


> dbGetInfo(drv)
$drvName
[1] "PostgreSQL"

$connectionIds                   # 连接1
$connectionIds[[1]]
<postgresqlconnection>

$connectionIds[[2]]              # 连接2
<postgresqlconnection>

$connectionIds[[3]]              # 连接3
<postgresqlconnection>

$fetch_default_rec
[1] 500

$managerId
<postgresqldriver>

$length
[1] 16

$num_con
[1] 3

$counter
[1] 9

查看con1数据连接状态


> dbGetInfo(con1)
$host
[1] "192.168.1.5"

$port
[1] "5432"

$user
[1] "test"

$dbname
[1] "testdb"

$serverVersion
[1] "12.0.4"

$protocolVersion
[1] 3

$backendPId
[1] 27263

$rsId
list()

关闭连接


# 关闭连接1
> dbDisconnect(con1)
[1] TRUE

# 查看con1状态
> isPostgresqlIdCurrent(con1)
[1] FALSE

# 查睦con1信息
> dbGetInfo(con1)
Error in postgresqlConnectionInfo(dbObj, ...) : 
  expired PostgreSQLConnection dbObj

关闭全部连接


# 通过循环关闭全部连接
> for(con in dbListConnections(drv)){
+   dbDisconnect(con)
+ }

# 查询驱动状态,没有活动连接
> dbGetInfo(drv)
$drvName
[1] "PostgreSQL"

$connectionIds   # 没有活动连接
list()

$fetch_default_rec
[1] 500

$managerId
<postgresqldriver>

$length
[1] 16

$num_con
[1] 0

$counter
[1] 9

3.3 数据直接查询

数据直接查询可以通过直接读取整个表数据dbReadTable()函数,或者执行查询语句dbGetQuery()函数来实现。

建立新连接


> con1 = dbConnect(drv,  dbname="testdb",user = "test",  password = "test", host ="192.168.1.5")

查看数据库中,所有的表。


# 所有表的列表
> dbListTables(con1)
[1] "account" "iris"  

# 确认表名是否存在
> dbExistsTable(con1,"iris")
[1] TRUE

通过表名,读取整个表的数据


> dbReadTable(con1,"iris")
  Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1          5.1         3.5          1.4         0.2  setosa
2          4.9         3.0          1.4         0.2  setosa
3          4.7         3.2          1.3         0.2  setosa
4          4.6         3.1          1.5         0.2  setosa
5          5.0         3.6          1.4         0.2  setosa

通过SQL语句,读取表中的数据


> dbGetQuery(con1,  "SELECT * FROM iris")
  row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa

3.4 数据交互查询

数据交互查询,可以通过发送一条sql请求dbSendQuery()函数来实现,返回数据访问句柄,再用dbFetch()函数来分批次获取数据。


# 发送SQL
> rs = dbSendQuery(con1,  statement  =  "SELECT * FROM iris")

# 判断rs句柄是否完成
> dbHasCompleted(rs)
[1] FALSE

# 取数据n=-1时,取全表数据
> dbFetch(rs,n=-1)
  row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa

# 判断rs句柄是否完成
> dbHasCompleted(rs)
[1] TRUE

# 取完数据后,句柄rs就失效了
> dbFetch(rs,n=1)
data frame with 0 columns and 0 rows

我们在获得句柄后,可以查到关于句柄的详细信息。


# 发送SQL,获得句柄rs
> rs = dbSendQuery(con1,  statement  =  "SELECT * FROM iris")

# 查看rs信息
> dbGetInfo(rs)
$statement
[1] "SELECT * FROM iris"

$isSelect
[1] 1

$rowsAffected
[1] -1

$rowCount
[1] 0

$completed
[1] 0

$fieldDescription
$fieldDescription[[1]]
$fieldDescription[[1]]$name
[1] "row.names"    "Sepal.Length" "Sepal.Width"  "Petal.Length" "Petal.Width"  "Species"     

$fieldDescription[[1]]$Sclass
[1] 16 14 14 14 14 16

$fieldDescription[[1]]$type
[1]  25 701 701 701 701  25

$fieldDescription[[1]]$len
[1] -1  8  8  8  8 -1

$fieldDescription[[1]]$precision
[1] -1 -1 -1 -1 -1 -1

$fieldDescription[[1]]$scale
[1] -1 -1 -1 -1 -1 -1

$fieldDescription[[1]]$nullOK
[1] TRUE TRUE TRUE TRUE TRUE TRUE

# 查看执行SQL语句
> dbGetStatement(rs)
[1] "SELECT * FROM iris"

# 查看数据结构
> dbColumnInfo(rs)
          name    Sclass   type len precision scale nullOK
1    row.names character   TEXT  -1        -1    -1   TRUE
2 Sepal.Length    double FLOAT8   8        -1    -1   TRUE
3  Sepal.Width    double FLOAT8   8        -1    -1   TRUE
4 Petal.Length    double FLOAT8   8        -1    -1   TRUE
5  Petal.Width    double FLOAT8   8        -1    -1   TRUE
6      Species character   TEXT  -1        -1    -1   TRUE

# 查看已获取的行
> dbGetRowCount(rs)
[1] 0

# 获取2行
> ret1 <- dbFetch(rs, 2)
> ret1
  row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa

# 查看已获取的行
> dbGetRowCount(rs)
[1] 2

# 查看句柄是否完成
> dbHasCompleted(rs)
[1] FALSE

# 获取剩余的行
> ret2 <- dbFetch(rs, -1)
> ret2
  row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa

# 查看句柄是否完成
> dbHasCompleted(rs)
[1] TRUE

# 清空rs句柄
> dbClearResult(rs)
[1] TRUE

# 拼接完整数据集
> rbind(ret1,ret2)
  row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa

3.5 数据批量插入
R语言中,数据组织方式是用data.frame的,data.frame可以批量地进行数据操作,在操作数据库时,我们也可以直接用data.frame与PostgreSQL数据库进行这种批量的操作。

当数据库没表时,dbWriteTable()函数会新建一个数据表。


# 新建数据表iris-table,插入5条数据
> dbWriteTable(con1,'iris-table',iris[1:5,])
[1] TRUE

# 读取数据表
> dbReadTable(con1,'iris-table')
  Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1          5.1         3.5          1.4         0.2  setosa
2          4.9         3.0          1.4         0.2  setosa
3          4.7         3.2          1.3         0.2  setosa
4          4.6         3.1          1.5         0.2  setosa
5          5.0         3.6          1.4         0.2  setosa

再进行数据表插入,由于表已存在,所以提示不能插入,如果需求追加插入时,可以增加append的合并参数。


# 插入数据失败
> dbWriteTable(con1,'iris-table',iris[6:10,])
[1] FALSE
Warning message:
In postgresqlWriteTable(conn, name, value, ...) :
  table iris-table exists in database: aborting assignTable

# 追加插入数据
> dbWriteTable(con1,'iris-table',iris[6:10,],append=TRUE)
[1] TRUE

# 查看数据表,变成了10行。
> dbReadTable(con1,'iris-table')
   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1           5.1         3.5          1.4         0.2  setosa
2           4.9         3.0          1.4         0.2  setosa
3           4.7         3.2          1.3         0.2  setosa
4           4.6         3.1          1.5         0.2  setosa
5           5.0         3.6          1.4         0.2  setosa
6           5.4         3.9          1.7         0.4  setosa
7           4.6         3.4          1.4         0.3  setosa
8           5.0         3.4          1.5         0.2  setosa
9           4.4         2.9          1.4         0.2  setosa
10          4.9         3.1          1.5         0.1  setosa

当表不需要后,可以直接删除数据表


# 删除数据表
> dbRemoveTable(con1,"iris-table")
[1] TRUE

# 删除后,数据表读取失败
> dbReadTable(con1,'iris-table')
Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  relation "iris-table" does not exist
LINE 1: SELECT * from "iris-table"
                      ^
)
Error in names(out) <- make.names(names(out), unique = TRUE) : 
  NULL是不能有属性的
此外: Warning message:
In postgresqlQuickSQL(conn, statement, ...) :
  Could not create execute: SELECT * from "iris-table"

3.6 事务提交和回滚
RPostgreSQL包的事务控制,是通过dbSendQuery()函数,配合事务控制dbBegin()函数,dbRollback()函数,dbCommit()函数来完成的。

我们模拟一个事务的场景,当删除一条数据时,判断这条数据影响多少行,如果删除超过1行则不执行删除进行回滚,如果正好为1行,则进行删除。


# 查看数据集,共5行
> df1<-dbGetQuery(con1, "select * from iris");df1
  row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa


# 开始事务
> dbBegin(con1)
[1] TRUE

# 进行删除
> rs <- dbSendQuery(con1, "Delete from iris where \"Species\" = 'setosa'")

# 删除判断,影响大于1行则回滚,
> if(dbGetInfo(rs, what = "rowsAffected") > 1){
+   warning("Rolling back transaction")
+   dbRollback(con1)
+ }else{
+   dbCommit(con1)
+ }
[1] TRUE
Warning message:
Rolling back transaction           # 进行回滚

# 再查询数据表结果,未发生删除
> dbGetQuery(con1, "select count(*) from iris")
  count
1     5

4. window客户端环境的中文乱码解决方案

执行查询,出现中文乱码。我们就需要逐步排查,到底是哪个环节产生了乱码,统一把编码以UTF-8做为编码标准。


# 创建中文data.frame
> acc<-data.frame(
+   user_id=1:3,
+   useranme=c("a1","小明","粉丝日志"),
+   password=c("678fdaiufda","jfdaked21+_~!","E!SM<I*")
+ )

# 建表,插入数据
> dbWriteTable(con1,"acc",acc)
[1] TRUE

# 读取数据
> df3<-dbReadTable(con1,"acc");df3
  user_id     useranme      password
1       1           a1   678fdaiufda
2       2       灏忔槑 jfdaked21+_~!
3       3 绮変笣鏃ュ織       E!SM<I*

我们插入的是正确,但读出来的数据是乱码。我们用pgadmin客户端,检查一下数据表,验证一下数据库中数据是否显示正常。

接下来,再检查一下PostgreSQL数据库中testdb库的编码为UTF8。

在服务器上,启动psql查询客户端。


# 启动psql客户端
~ sudo -u postgres psql

# 检查数据库
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | test=CTc/postgres
(4 rows)

再检查一下,客户端通信的字符编码,也是UTF8是正常的。


> dbGetQuery(con1, "SHOW CLIENT_ENCODING")
  client_encoding
1            UTF8

最后,考虑到Windows环境下,R的客户端默认编码为GBK,所以我们再试一下,把数据在R中进行编码转型。


# 字符编码转型
> df3a <- as.data.frame(apply(df3, 2, function(x){
+   iconv(x, 'UTF-8', 'GBK')
+ }))

# 查看输出结果
> df3a
  user_id useranme      password
1       1       a1   678fdaiufda
2       2     小明 jfdaked21+_~!
3       3 粉丝日志       E!SM<I*

这样就可以解决了,中文乱码的问题了。

我们已经完成,掌握了RPostgreSQL包的各种使用技巧,希望大家理解原理后,能少犯错误,提高工作效率!

转载请注明出处:
http://blog.fens.me/r-postgressql/

打赏作者

在Ubuntu上安装PostgreSQL

Ubuntu实用工具系列文章,将介绍基于Linux ubuntu的各种工具软件的配置和使用。有些工具大家早已耳熟能详,有些工具经常用到但确依然陌生。我将记录我在使用操作系统时,安装及配置工具上面的一些方法,把使用心得记录下来也便于自己的以后查找和回忆。

关于作者:

  • 张丹,分析师/程序员/Quant: R,Java,Nodejs
  • blog: http://fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/linux-postgresql-install/

前言

PostgreSQL是一个功能非常强大的、源代码开放关系型数据库,我之前主要在用MySQL,为了知识的完整性,也把PostgreSQL练一练。介绍一下PostgreSQL数据库在Ubuntu上的安装过程,和基本的使用操作。大家也可以对比MySQL的安装过程,请参考文章在Ubuntu中安装MySQL

希望本文能让所有的PostgreSQL新手快速上手,顺利完成PostgreSQL数据库的初探。

目录

  1. PostgreSQL在Ubuntu中安装
  2. 数据库操作命令
  3. 用pgadmin远程访问数据库

1. PostgreSQL在Ubuntu中安装

本文使用的Linux是Ubuntu 20.04.1 LTS Server 64bit的系统,安装PostgreSQL数据库软件包可以通过apt实现。

1.1 下载和安装PostgreSQL

在Linux Ubuntu中安装 PostgreSQL 数据库,可以先使用apt查看一下PostgreSQL的版本支持。apt支持的PostgreSQL数据库的最新版本为12,目前来说是主流的。PostgreSQL官方在 2020-09-24 发布了,最新的版本PostgreSQL 13

# 查看Postgres支持
~  apt show postgresql
Package: postgresql
Version: 12+214ubuntu0.1
Priority: optional
Section: database
Source: postgresql-common (214ubuntu0.1)
Origin: Ubuntu
Maintainer: Ubuntu Developers <ubuntu-devel-discuss@lists.ubuntu.com>
Original-Maintainer: Debian PostgreSQL Maintainers <team+postgresql@tracker.debian.org>
Bugs: https://bugs.launchpad.net/ubuntu/+filebug
Installed-Size: 67.6 kB
Depends: postgresql-12
Suggests: postgresql-doc
Task: postgresql-server
Download-Size: 3,924 B
APT-Sources: http://cn.archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages
Description: object-relational SQL database (supported version)
 This metapackage always depends on the currently supported PostgreSQL
 database server version.
 .
 PostgreSQL is a fully featured object-relational database management
 system.  It supports a large part of the SQL standard and is designed
 to be extensible by users in many aspects.  Some of the features are:
 ACID transactions, foreign keys, views, sequences, subqueries,
 triggers, user-defined types and functions, outer joins, multiversion
 concurrency control.  Graphical user interfaces and bindings for many
 programming languages are available as well.

N: There is 1 additional record. Please use the '-a' switch to see it

我们可以直接使用apt的源进行安装,一条命令就够了。


# 安装Postgres服务器端
~ sudo apt install postgresql

安装完成后,查看系统进程情况,和服务器端口占用情况

# 检查postgresql服务器系统进程
~ ps -aux|grep postgresql
postgres   18145  0.0  0.1 215652 29396 ?        Ss   10:30   0:00 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf

# 检查postgresql服务器占用端口,默认为5432
~  netstat -nlt|grep 5432
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN

通过系统服务命令检查 postgresql 服务器状态


~  service postgresql status
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Fri 2020-11-06 10:30:38 UTC; 12min ago
   Main PID: 17860 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 18660)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Nov 06 10:30:38 conan systemd[1]: Starting PostgreSQL RDBMS...
Nov 06 10:30:38 conan systemd[1]: Finished PostgreSQL RDBMS.

1.2 设置 postgres 账户密码

安装 PostgreSQL 数据库后,会自动创建 Ubuntu 的 postgres 用户和 PostgreSQL 的 postgres 用户。为了后续让测试更方便,我们可以设置这两个账户的密码保持一致,都为 postgres。

首先,修改Ubuntu系统中,postgres用户的密码为postgres。


# 清除原有 postgres账户密码
~ sudo passwd -d postgres
passwd: password expiry information changed.

# 设置新密码 postgres 
~ sudo -u postgres passwd
New password:
Retype new password:
passwd: password updated successfully

然后,登陆到PostgreSQL数据库,修改postgres用户的密码为postgres 。


#  登陆psql客户端
~ sudo -u postgres psql
psql (12.4 (Ubuntu 12.4-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD 'postgres'
postgres-# \q

完成数据库中 postgres 账户的密码修改。

1.3 初始化PostgreSQL数据库空间

我们自己需要创建数据库空间,这里与MySQL是不同的,MySQL没有这一步骤。PostgreSQL 官方文档推荐的位置是 /user/local/pgsql/data 或 /var/lib/pgsql/data。那么,我们也把数据库空间放到/user/local/pgsql/data 目录吧。

首先要创建目录,各种安装过程中的问题从此处开始。

# 创建目录,-p 表示创建多级目录
~ sudo mkdir -p /usr/local/pgsql/data

# 设置权限
~ sudo chown postgres -R /usr/local/pgsql

# 创建数据库空间
~ sudo -u postgres initdb -D /user/local/pgsql/data
sudo: initdb: command not found

问题一:没有 initdb 命令。

我们从根目录搜索一下,查找initdb的命令位置。

~ sudo find / -name initdb
/usr/lib/postgresql/12/bin/initdb

指定initdb的位置后,再重新创建数据库空间,创建成功。

~ sudo -u postgres /usr/lib/postgresql/12/bin/initdb -D /usr/local/pgsql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/lib/postgresql/12/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

1.4 启动数据库
启动数据库,执行上面最后一个提示的命令即可,pg_ctl是用于初始化PostgreSQL数据库集群。

~ sudo -u postgres /usr/lib/postgresql/12/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
waiting for server to start..../bin/sh: 1: cannot create logfile: Permission denied
 stopped waiting
pg_ctl: could not start server
Examine the log output.

问题二:pg_ctl权限问题

又出现了一个问题Permission denied,权限问题。增加输出目录/var/lib/postgresql/到logfile ,再次执行命令,程序正常启动!

~ sudo -u postgres /usr/lib/postgresql/12/bin/pg_ctl -D /usr/local/pgsql/data -l /var/lib/postgresql/logfile start
waiting for server to start.... done
server started

2. 数据库操作命令

安装postgresql服务器完成后,会自动地一起安装postgresql命令行客户端程序 psql。

在本机输入psql命令就可以启动,客户端程序访问postgresql服务器,直接输入 psql 命令时,会提示当前的用户没有授权。

~ psql
psql: error: could not connect to server: FATAL:  role "conan" does not exist

要使用postgres 用户,启动psql程序

~ sudo -u postgres psql
psql (12.4 (Ubuntu 12.4-0ubuntu0.20.04.1))
Type "help" for help.

postgres=#

如果一切正常,系统提示符会变为 postgres=# ,表示这时已经进入了数据库控制台。

2.1 控制台的命令使用

接下来的操作命令都在控制台内完成了,我们先要了解一下控制台,支持哪些操作。psql程序有一些不属于SQL命令的内部命令。它们以反斜线开头,“\”。

控制台命令列表:

  • \password 命令(设置密码)
  • \q 命令(退出)
  • \h:查看SQL命令的解释,比如\h select。
  • \?:查看psql命令列表。
  • \l:列出所有数据库。
  • \c [database_name]:连接其他数据库。
  • \d:列出当前数据库的所有表格。
  • \d [table_name]:列出某一张表格的结构。
  • \du:列出所有用户。
  • \e:打开文本编辑器。
  • \conninfo:列出当前数据库和连接的信息。

我们都分别试一下,这些命令。

查看当前数据库信息

postgres-# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

查看数据库和操作系统版本

postgres=# SELECT version();
                                                            version
-------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.4 (Ubuntu 12.4-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit
(1 row)

查看SQL命令解释


postgres=# \h
Available help:
  ABORT                            ALTER SYSTEM                     CREATE FOREIGN DATA WRAPPER      CREATE USER MAPPING              DROP ROUTINE                     PREPARE
  ALTER AGGREGATE                  ALTER TABLE                      CREATE FOREIGN TABLE             CREATE VIEW                      DROP RULE                        PREPARE TRANSACTION
  ALTER COLLATION                  ALTER TABLESPACE                 CREATE FUNCTION                  DEALLOCATE                       DROP SCHEMA                      REASSIGN OWNED
  ALTER CONVERSION                 ALTER TEXT SEARCH CONFIGURATION  CREATE GROUP                     DECLARE                          DROP SEQUENCE                    REFRESH MATERIALIZED VIEW
  ALTER DATABASE                   ALTER TEXT SEARCH DICTIONARY     CREATE INDEX                     DELETE                           DROP SERVER                      REINDEX
  ALTER DEFAULT PRIVILEGES         ALTER TEXT SEARCH PARSER         CREATE LANGUAGE                  DISCARD                          DROP STATISTICS                  RELEASE SAVEPOINT
  ALTER DOMAIN                     ALTER TEXT SEARCH TEMPLATE       CREATE MATERIALIZED VIEW         DO                               DROP SUBSCRIPTION                RESET
  ALTER EVENT TRIGGER              ALTER TRIGGER                    CREATE OPERATOR                  DROP ACCESS METHOD               DROP TABLE                       REVOKE
  ALTER EXTENSION                  ALTER TYPE                       CREATE OPERATOR CLASS            DROP AGGREGATE                   DROP TABLESPACE                  ROLLBACK
  ALTER FOREIGN DATA WRAPPER       ALTER USER                       CREATE OPERATOR FAMILY           DROP CAST                        DROP TEXT SEARCH CONFIGURATION   ROLLBACK PREPARED
  ALTER FOREIGN TABLE              ALTER USER MAPPING               CREATE POLICY                    DROP COLLATION                   DROP TEXT SEARCH DICTIONARY      ROLLBACK TO SAVEPOINT
  ALTER FUNCTION                   ALTER VIEW                       CREATE PROCEDURE                 DROP CONVERSION                  DROP TEXT SEARCH PARSER          SAVEPOINT
  ALTER GROUP                      ANALYZE                          CREATE PUBLICATION               DROP DATABASE                    DROP TEXT SEARCH TEMPLATE        SECURITY LABEL
  ALTER INDEX                      BEGIN                            CREATE ROLE                      DROP DOMAIN                      DROP TRANSFORM                   SELECT
  ALTER LANGUAGE                   CALL                             CREATE RULE                      DROP EVENT TRIGGER               DROP TRIGGER                     SELECT INTO
  ALTER LARGE OBJECT               CHECKPOINT                       CREATE SCHEMA                    DROP EXTENSION                   DROP TYPE                        SET
  ALTER MATERIALIZED VIEW          CLOSE                            CREATE SEQUENCE                  DROP FOREIGN DATA WRAPPER        DROP USER                        SET CONSTRAINTS
  ALTER OPERATOR                   CLUSTER                          CREATE SERVER                    DROP FOREIGN TABLE               DROP USER MAPPING                SET ROLE
  ALTER OPERATOR CLASS             COMMENT                          CREATE STATISTICS                DROP FUNCTION                    DROP VIEW                        SET SESSION AUTHORIZATION
  ALTER OPERATOR FAMILY            COMMIT                           CREATE SUBSCRIPTION              DROP GROUP                       END                              SET TRANSACTION
  ALTER POLICY                     COMMIT PREPARED                  CREATE TABLE                     DROP INDEX                       EXECUTE                          SHOW
  ALTER PROCEDURE                  COPY                             CREATE TABLE AS                  DROP LANGUAGE                    EXPLAIN                          START TRANSACTION
  ALTER PUBLICATION                CREATE ACCESS METHOD             CREATE TABLESPACE                DROP MATERIALIZED VIEW           FETCH                            TABLE
  ALTER ROLE                       CREATE AGGREGATE                 CREATE TEXT SEARCH CONFIGURATION DROP OPERATOR                    GRANT                            TRUNCATE
  ALTER ROUTINE                    CREATE CAST                      CREATE TEXT SEARCH DICTIONARY    DROP OPERATOR CLASS              IMPORT FOREIGN SCHEMA            UNLISTEN
  ALTER RULE                       CREATE COLLATION                 CREATE TEXT SEARCH PARSER        DROP OPERATOR FAMILY             INSERT                           UPDATE
  ALTER SCHEMA                     CREATE CONVERSION                CREATE TEXT SEARCH TEMPLATE      DROP OWNED                       LISTEN                           VACUUM
  ALTER SEQUENCE                   CREATE DATABASE                  CREATE TRANSFORM                 DROP POLICY                      LOAD                             VALUES
  ALTER SERVER                     CREATE DOMAIN                    CREATE TRIGGER                   DROP PROCEDURE                   LOCK                             WITH
  ALTER STATISTICS                 CREATE EVENT TRIGGER             CREATE TYPE                      DROP PUBLICATION                 MOVE
  ALTER SUBSCRIPTION               CREATE EXTENSION                 CREATE USER                      DROP ROLE                        NOTIFY

查看数据库列表

postgres-# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

查看所有用户角色

postgres-# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

查看当前的用户角色

postgres=# \c
You are now connected to database "postgres" as user "postgres".

要退出 psql 命令行

postgres=# \q

2.2 创建数据库testdb

创建一个用户数据库 testdb。

# 创建数据库
postgres=# create database testdb;
CREATE DATABASE

# 查看所有数据库
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

选择指定的数据库testdb。

# 选择数据库
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".

2.3 创建新用户

通常来说,不建议直接使用超级管理员账户来直接进行数据操作,我们可以创建一些用户来完成对数据库的使用,避免超级管理员权限过大的问题。

创建2个新用户 test 和 test_readonly,密码都为 test 。test用户可直接完全访问testdb数据库,test_readonly用户仅直接只读访问testdb数据库。


postgres=# create user test with password 'test';
CREATE ROLE
postgres=# create user test_readonly with password 'test';
CREATE ROLE

给 test 用户授权可以完成控制 testdb 。


postgres=# GRANT ALL PRIVILEGES ON DATABASE testdb TO test;
GRANT

给 test_readonly 用户授权可以只读访问 testdb 。


# 切换数据库
postgres=# \c testdb;
You are now connected to database "testdb" as user "postgres".

# 赋予只读权限
testdb=# GRANT SELECT ON ALL TABLES in schema public TO test_readonly ;
GRANT

2.4 建表
让我们切换一下用户,使用test用户,建表和插入数据。


testdb=# \c - test
You are now connected to database "testdb" as user "test".

在testdb数据库中,建表,主键自增


# 建表
testdb=# create table account(
testdb(#     user_id serial primary key,
testdb(#     username varchar(50) unique not null,
testdb(#     password varchar(50) not null
testdb(# );
CREATE TABLE

查看testdb库的所有表


testdb=> \d
                List of relations
 Schema |        Name         |   Type   | Owner
--------+---------------------+----------+-------
 public | account             | table    | test
 public | account_user_id_seq | sequence | test
(2 rows)

查看account表的数据结构


# 查看表结构
testdb=> \d  account
                                       Table "public.account"
  Column  |         Type          | Collation | Nullable |                 Default
----------+-----------------------+-----------+----------+------------------------------------------
 user_id  | integer               |           | not null | nextval('account_user_id_seq'::regclass)
 username | character varying(50) |           | not null |
 password | character varying(50) |           | not null |
Indexes:
    "account_pkey" PRIMARY KEY, btree (user_id)
    "account_username_key" UNIQUE CONSTRAINT, btree (username)

2.5 插入数据

插入3条数据,因为主键是自增的,所以我们不用插入主键


# 单条插入
testdb=> INSERT INTO account(username , password ) VALUES('a1', 'a1');
INSERT 0 1

# 多条插入
testdb=> INSERT INTO account(username , password ) VALUES('a2', 'a2'),('a3', 'a3');
INSERT 0 2

进行查询,共有3条记录。


testdb=> SELECT * FROM account;
 user_id | username | password
---------+----------+----------
       1 | a1       | a1
       2 | a2       | a2
       3 | a3       | a3
(3 rows)

2.5 修改数据
修改user_id = 1的数据,username 改为 aaa1


# 修改数据
testdb=> UPDATE account SET username = 'aaa1' where user_id = 1;
UPDATE 1

# 进行查询
testdb=>  SELECT * FROM account;
 user_id | username | password
---------+----------+----------
       2 | a2       | a2
       3 | a3       | a3
       1 | aaa1     | a1
(3 rows)

修改user_id = 2的数据


# 删除数据
testdb=> DELETE FROM account where user_id = 2;
DELETE 1

# 进行查询
testdb=> SELECT * FROM account;
 user_id | username | password
---------+----------+----------
       3 | a3       | a3
       1 | aaa1     | a1
(2 rows)

2.6 切换只读用户test_readonly

切换用户test_readonly


testdb=# \c - test_readonly;
You are now connected to database "testdb" as user "test_readonly".

# 进行查询
testdb=> SELECT * FROM account;
 user_id | username | password
---------+----------+----------
       3 | a3       | a3
       1 | aaa1     | a1
(2 rows)

进行插入、修改、删除操作。


# 插入拒绝
testdb=> INSERT INTO account(username , password ) VALUES('a4', 'a4');
ERROR:  permission denied for table account

# 修改拒绝
testdb=> UPDATE account SET username = 'aaa3' where user_id = 3;
ERROR:  permission denied for table account

# 删除拒绝
testdb=> DELETE FROM account where user_id = 1;
ERROR:  permission denied for table account

3. 用pgAdmin远程访问数据库

安装好PostgreSQL数据库后,我们就需要进行数据库访问操作了,通常会使用远程的客户端程序来执行SQL语句,使用官方的pgAdmin客户端软件。

3.1 pgadmin下载

我们使用官方的pgadmin 4的客户端来进行数据库操作,pgadmin 4的客户端下载地址为:https://www.pgadmin.org/download/

下载后,我在window 10上进行安装,打开是web版本的界面。

添加数据库服务器,并建立连接,发现数据库连接被拒绝。

默认情况下,postgreSQL数据库只允许本机连接,要在其他机器上远程连接到postgreSQL数据库还需要修改相应的配置。

3.2 修改远程连接配置

前方有坑!按照网上的大部分教程,我们要修改配置文件,/etc/postgresql/12/main/postgresql.conf , 找到”#listen_addresses = ‘localhost'”中的注释去掉并改为 listen_addresses = ‘*’,允许远程客户端连接

~ sudo vi /etc/postgresql/12/main/postgresql.conf

listen_addresses = '*'

PostgreSQL客户端身份验证在名为的配置文件中定义pg_hba.conf。缺省情况下,PostgreSQL使用对等身份验证方法进行本地连接。

接下来,打开允许访问的IP段,修改 /etc/postgresql/12/main/pg_hba.conf , 在文件末尾添加 host all all 0.0.0.0 0.0.0.0 md5 ,表示允许任何IP连接

~ sudo vi /etc/postgresql/12/main/pg_hba.conf

host all all 0.0.0.0 0.0.0.0 md5

最后,重启数据库,就生效了。

sudo /etc/init.d/postgresql restart

我们检查postgresql 的网络监听端口

~ netstat -nlt|grep 5432
  tcp        0      127.0.0.1:5432            0.0.0.0:*               LISTEN

怎么一直都是127.0.0.1:5432而没有编程0 0.0.0.0:5432。我确认过了好几次呢。

问题三:远程配置怎么不生效?
仔细研究后发现,我们前文中在 新创建了 /usr/local/pgsql/data 数据空间,initdb的过程,会把pg_hba.conf和postgresql.conf文件初始化,所以需要改动 /usr/local/pgsql/data 目录下的这2个配置文件才可以。

进入目录 /usr/local/pgsql/data,发现又没有权限。

~ cd /usr/local/pgsql/data
-bash: cd: /usr/local/pgsql/data: Permission denied

切换为postgres用户,再进入目录 /usr/local/pgsql/data

# 切换用户
~ su postgres
Password:

# 进入目录 
~  cd /usr/local/pgsql/data
 ls
base          pg_hba.conf    pg_notify     pg_stat      pg_twophase  postgresql.auto.conf
global        pg_ident.conf  pg_replslot   pg_stat_tmp  PG_VERSION   postgresql.conf
pg_commit_ts  pg_logical     pg_serial     pg_subtrans  pg_wal       postmaster.opts
pg_dynshmem   pg_multixact   pg_snapshots  pg_tblspc    pg_xact      postmaster.pid

修改pg_hba.conf,在最后一行加入host all all 0.0.0.0 0.0.0.0 md5。修改 postgresql.conf 文件,把 listen_addresses = ‘*’。

退出postgress用户

postgres@conan:/usr/local/pgsql/data$ exit
exit
conan@conan:/var/lib/postgresql

再用pg_ctl命令进行重启数据库服务。


# 停止数据库
~ sudo -u postgres /usr/lib/postgresql/12/bin/pg_ctl -D /usr/local/pgsql/data -l logfile stop
[sudo] password for conan:
waiting for server to shut down.... done
server stopped

# 启动数据库
~ sudo -u postgres /usr/lib/postgresql/12/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
waiting for server to start.... done
server started

查看服务器端口

# 检查PostgreSQL服务器占用端口
~ netstat -nlt|grep 5432
  tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN

终于,从之前的网络监听从 127.0.0.1:5432 变成 0 0.0.0.0:5432,表示PostgreSQL已经允许远程登陆访问。

3.3 使用pgadmin进行远程连接

使用pgadmin配置数据库的远程连接。

进入pgadmin的界面,功能很强大,查看testdb的数据库统计。

进行数据表的SQL查询。

通过上面的操作,我们就把PostgreSQL数据库服务器,在Linux Ubuntu中的系统安装完成,并完成了数据库的客户端远程连接访问!希望本文能让所有的PostgreSQL新手快速上手,顺利完成PostgreSQL数据库的初探。

转载请注明出处:
http://blog.fens.me/linux-postgresql-install/

打赏作者