Postgresql食用教程

##postgresql笔记

0、前言

因为要对接一个项目，对方用的Postgresql，因此这里记录一下从安装部署到配置运行的过程。

本笔记参考文章如下：

Centos 7 安装 PostgreSQL

CentOS7安装并配置PostgreSQL

基本安装和配置步骤类似，简要讲讲然后重点记录下遇到的坑。

1、安装

安装环境：centos7

首先到 Postgresql的下载文件目录页面找到需要的版本，安装yum源，本文以Postgresql11为例

yum install https://download.postgresql.org/pub/repos/yum/testing/12/redhat/rhel-7-x86_64/pgdg-centos12-12-1.noarch.rpm

然后安装客户端

yum install postgresql11-contrib

安装服务端

yum install postgresql11-server

验证安装是否成功

rpm -aq|grep postgres

输出如下

postgresql11-server-11.1-1PGDG.rhel7.x86_64
postgresql11-libs-11.1-1PGDG.rhel7.x86_64
postgresql11-11.1-1PGDG.rhel7.x86_64

至此安装完成

2、初始化及启动

安装完成后进入Postgresql的安装目录 /usr/pgsql-11/

初始化数据库命令

/bin/postgresql-11-setup initdb

启动Postgresql服务并设置开机自启动

systemctl start postgresql-11
systemctl enable postgresql-11

3、修改Postgresql用户密码

使用初始化命令后，Postgresql会创建一个名为postgres的用户

输入su postgres切换用户，此时命令行提示符会变为类似bash-4.2$的字样

输入psql -U postgres登录数据库，然后修改密码，注意SQL语句的分号不要漏掉，敲回车执行

ALTER USER  postgres with encrypted password 'password123';

输入\q退出Postgresql数据库（类似MySQL或MongoDB的exit命令）

输入exit退出postgres用户

4、开启并配置远程访问

Postgresql默认只信任本地连接，进入Postgresql的配置目录/var/lib/pgsql/11/data

打开配置文件

vim postgresql.conf

修改listen_addresses = 'localhost'为 listen_addresses = '*'

如有需要，修改端口 port = xxx

vim pg_hba.conf

添加需要连接的ip，并设置为trust（信任）

 # IPv4 local connections:
    host    all            all      127.0.0.1/32    trust
    host    all            all      需要连接的IP     trust

配置完毕后，重启服务生效

systemctl restart postgresql-11

5、Navicat的坑

Navicat操作Postgresql有点不好用，尽管升级到了12.1版本，但是偶尔还是会出现断连的问题。如果是12.1以下版本则更难受，建议不要用。不过Navicat12.1支持MongoDB了，还是很爽。

6、Postgresql的JDBC

简单提一下如何使用java连接Postgresql

首先是下载PostgresqlJDBC的jar包，前往 https://jdbc.postgresql.org/download.html
连接数据库

/**
* 测试连接数据库代码
* dbIP:你的数据库ip
* dbPort:你的数据库端口
* dbName:你的数据库名
* dbUsername:你的用户名
* dbPassword:你的密码
*/
public boolean test(String dbIp, int dbPort, String dbName, 
                    String dbUsername, String dbPassword) {
    Connection connection = null;
    boolean result = false;
    String addr = "jdbc:postgresql://" + dbIP + ":" + dbPort + "/" + dbName;
    try {
        Class.forName("org.postgresql.Driver");
        connection = DriverManager.getConnection(addr, dbUsername, dbPassword);
        result = true;
        connection.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return result;
}

执行SQL语句（增删改等）部分代码

/*
* sql为执行语句 INSERT、DELETE、UPDATE 等
* objs为参数，用到了PreparedStatement
* 可以在sql中用'?'占位，防止字符串拼接导致的sql注入风险
*/
Connection connection = null;
PreparedStatement stmt = null;
try {
    Class.forName("org.postgresql.Driver");
    connection = DriverManager.getConnection("jdbc:postgresql://"
                 + dbIP + ":" + dbPort + "/" + dbName,
                 dbUsername, dbPassword);
    connection.setAutoCommit(false);
    stmt = connection.prepareStatement(sql);
    for (int i = 0; i < objs.length; i++) {
        stmt.setObject(i + 1, objs[i]);
    }
    stmt.executeUpdate();
    stmt.close();
    connection.commit();
    connection.close();
} catch (Exception e) {
    e.printStackTrace();
}

查询SQL语句（SELECT）部分代码

Connection connection = null;
PreparedStatement stmt = null;
ResultSet resultSet = null;
try {
    Class.forName("org.postgresql.Driver");
    connection = DriverManager.getConnection("jdbc:postgresql://" 
                 + dbIP + ":" + dbPort + "/" + dbName,
                 dbUsername, dbPassword);
    connection.setAutoCommit(false);
    stmt = connection.prepareStatement(sql);
    for (int i = 0; i < objs.length; i++) {
        stmt.setObject(i + 1, objs[i]);
    }
    resultSet = stmt.executeQuery();
    int col = resultSet.getMetaData().getColumnCount();
    System.out.println("============================");
    while (resultSet.next()) {
        for (int i = 1; i <= col; i++) {
            System.out.print(resultSet.getString(i) + "\t");
            if ((i == 2) && (resultSet.getString(i).length() < 8)) {
                System.out.print("\t");
            }
        }
        System.out.println("");
    }
    System.out.println("============================");
    stmt.close();
    connection.commit();
    connection.close();
} catch (Exception e) {
    e.printStackTrace();
}

7、Postgresql的SQL操作

插入存在主键的栏则进行更改
如下SQL语句，表T1的A字段为主键（PK），若插入时发生冲突，则直接修改B和C两个字段

INSERT INTO T1 (A, B, C) VALUES(?,?,?) 
ON conflict(A)
DO UPDATE SET B=?,C=?;

时间日期
如下SQL语句，表T2的B、C字段为timestamp类型，参数x1、x2为long类型

SELECT A,B,C
FROM T2
WHERE A = ?
AND "B" > to_timestamp(X1)
AND "C" <= to_timestamp(x2) + '1 DAY';

    to_timestamp(x2) + '1 DAY'表示x2的后一天，与此类似的还有

-   '1 MONTH'

-   '2 YEAR'

-   '3 WEEK'

-   '4 MIN'

    to_timestamp()函数是long转timestamp，long是java的Date.getTime()获取到的Unix时间戳