PostgreSQL PG数据库 相关

PostgreSQL PG

2019/09/12 Chenxin

简介

PostgreSQL是一个功能非常强大的、源代码开放的客户/服务器关系型数据库管理系统（RDBMS）。PostgreSQL最初设想于1986年，当时被叫做Berkley Postgres Project。该项目一直到1994年都处于演进和修改中，直到开发人员Andrew Yu和Jolly Chen在Postgres中添加了一个SQL（Structured Query Language，结构化查询语言）翻译程序，该版本叫做Postgres95，在开放源代码社区发放。

安装与部署

加入系统镜像

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

安装客户端

yum install postgresql11

安装服务端

yum install postgresql11-server

安装生成文件

[root@localhost ~]# tree /var/lib/pgsql/  这里存放数据文件
/var/lib/pgsql/
└── 11
    ├── backups
    └── data    当前为空
[root@localhost pgsql-11]# pwd      这里存放二进制和说明手册
/usr/pgsql-11
[root@localhost pgsql-11]# ls
bin  lib  share
           

初始化数据库

/usr/pgsql-11/bin/postgresql-11-setup initdb

[root@localhost 11]# ls /var/lib/pgsql/11/data/ 原先为空,生成很多数据文件
base    log           pg_dynshmem  pg_ident.conf  ...
           

设置开机启动

systemctl enable postgresql-11

systemctl start postgresql-11

配置

允许外部连接,包含端口: 5432配置
vim /var/lib/pgsql/11/data/postgresql.conf
listen_addresses = \'*\'  或 listen_addresses = \'you_ip\'

修改认证方式为md5(不适合sonar,sonar调用JDBC用的是明文密码)
vim /var/lib/pgsql/11/data/pg_hba.conf 
# TYPE  DATABASE  USER     ADDRESS    METHOD
host    all       all     0.0.0.0/0 md5

适合sonar方式
...
# IPv4 local connections:
host    all   all    127.0.0.1/32    password #默认的ident修改为password.
...

具体的认证方式说明,请参考本文"权限"章节.
           

重启

systemctl restart postgresql-11

修改默认用户密码

su postgres
$ psql
postgres=# \password postgres
或
sudo -u postgres psql postgres
# \password postgres
Enter new password: 这里暂时配置为 PGsqlLG2019
           

其他

暂时无需执行

yum install postgresql11-devel postgresql11-llvmjit

登陆与操作指令

• ssh登陆机器后,su postgres

  $ psql 进入sql命令行模式,如下

postgres=# 在此提示符后输入指令
           

再比如

bash-4.2$ psql -U postgres -d postgres
psql (11.5)
输入 "help" 来获取帮助信息.
postgres=# 
           

• pg的sql指令

默认的用户和数据库名称都是postgres
psql -U user -d dbname

切换数据库,相当于mysql的use dbname
\c dbname

列举数据库，相当于mysql的show databases
\l

列举表，相当于mysql的show tables
\dt

查看表结构，相当于desc tblname,show columns from tbname
\d tblname

\di 查看索引 

创建数据库： 
create database [数据库名]; 

删除数据库： 
drop database [数据库名];  

重命名一个表： 
alter table [表名A] rename to [表名B]; 

删除一个表： 
drop table [表名]; 

在已有的表里添加字段： 
alter table [表名] add column [字段名] [类型]; 

删除表中的字段： 
alter table [表名] drop column [字段名]; 

修改数据库列属性
alter table 表名 alter 列名 type 类型名(350)

重命名一个字段：  
alter table [表名] rename column [字段名A] to [字段名B]; 

给一个字段设置缺省值：  
alter table [表名] alter column [字段名] set default [新的默认值];

去除缺省值：  
alter table [表名] alter column [字段名] drop default; 

在表中插入数据： 
insert into 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......); 

修改表中的某行某列的数据： 
update [表名] set [目标字段名]=[目标值] where [该行特征]; 

删除表中某行数据： 
delete from [表名] where [该行特征]; 
delete from [表名];--删空整个表 

创建表： 
create table ([字段名1] [类型1] ;,[字段名2] [类型2],......<,primary key (字段名m,字段名n,...)>;); 

\copyright     显示 PostgreSQL 的使用和发行条款
\encoding [字元编码名称] 显示或设定用户端字元编码
\h [名称]      SQL 命令语法上的说明，用 * 显示全部命令
\prompt [文本] 名称 提示用户设定内部变数
\password [USERNAME] securely change the password for a user
\q             退出 psql

           

权限

从pg_hba.conf文件谈postgresql的连接认证

1./var/lib/pgsql/11/data/pg_hba.conf 配置文件示例

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
#local   all             all                                     trust
# IPv4 local connections:
#host    all             all             127.0.0.1/32            ident
#host    all             all             127.0.0.1/32            trust
host    all             all             127.0.0.1/32            password
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

# Add this line by handful.
# host    all             all             0.0.0.0/0               md5
           

有以下几个类型

TYPE DATABASE USER ADDRESS METHOD

2.TYPE连接方式

local: Unix 域套接字

host: TCP/IP网络进行的联接.匹配通过 ssl/非ssl 方式的连接。要使用该选项你要在postgresql.conf文件里设置listen_address选项.默认的行为是只在localhost上监听本地连接.

hostssl: 这条记录匹配通过在TCP/IP上进行的SSL联接企图。要使用该选项，服务器编译时必须使用--with-openssl选项，并且在服务器启动时ssl设置是打开的.

hostnossl: 这个和上面的hostssl相反，只匹配通过在TCP/IP上进行的非SSL联接企图。

3.数据库(database)

声明记录所匹配的数据库。

值 all 表明该记录匹配所有数据库;

值 sameuser表示如果被请求的数据库和请求的用户同名，则匹配;

值samegroup 表示请求的用户必须是一个与数据库同名的组中的成员;

值 replication 表示匹配一条replication连接，它不指定一个特定的数据库，一般在流复制中使用;

在其他情况里，这就是一个特定的 PostgreSQL 数据库的名字。 我们可以通过用逗号分隔的方法声明多个数据库。 一个包含数据库名的文件可以通过对该文件前缀 @ 来声明．该文件必需和 pg_hba.conf 在同一个目录。

4.用户名(user)

为这条记录声明所匹配的 PostgreSQL 用户，值 all 表明它匹配 于所有用户。否则，它就是特定 PostgreSQL 用户的名字，多个用户名可以通过用逗号分隔的方法声明，在名字前面加上+代表匹配该用户组的所有用户。一个包含用户名的文件可以 通过在文件名前面前缀 @ 来声明，该文件必需和 pg_hba.conf 在同一个目录。

5.主机地址(address)

指定匹配的客户端的地址，它可以是一个主机名，一个IP地址范围，或者下面提到的这些选项。

一个IP地址范围是一个标准的点分十进制表示的 IP地址/掩码值。注意， 在\'IP地址\',\'/\'和\'掩码值\'之间不要有任何的空白字符。

比如对于IPv4地址来说， 172.20.143.89/32指定单个主机的IP，172.20.143.0/24代表一个小的子网。对于IPv6地址来说，::1/128指定单个主机(这里是本机环回地址)，fe80::7a31:c1ff:0000:0000/96 指定一个IPv6的子网。0.0.0.0/0代表所有IPv4地址，::0/0代表所有IPv6地址。

一个IPv4地址选项只能匹配IPv4地址，一个IPv6地址选项只能匹配IPv6地址，即使给出的地址选项在IPV4和IPv6中同时存在。

当然你可以使用 all 选项来匹配所有的IP地址，使用 samehost 匹配服务器自己所有的IP地址，samenet来匹配服务器直接接入的子网。

如果指定的是主机名(既不是IP地址也不是上面提到的选项)，这个主机名将会和发起连接请求的客户端的IP地址的反向名称解析结果(即通过客户端的IP解析其主机名，比如使用反向DNS查找)进行比对，如果存在匹配，再使用正向名称解析(例如DNS查找)将主机名解析为IP地址(可能有多个IP地址)，再判断客户端的IP地址是否在这些IP地址中。如果正向和反向解析都成功匹配，那么就真正匹配这个地址(所以在pg_nba.conf文件里的主机地址必须是客户端IP的 address-to-name 解析返回的那个主机名。一些主机名数据库允许将一个IP地址和多个主机名绑定,但是在解析IP地址时，操作系统只会返回一个主机名)。

有些主机名以点(.)开头，匹配那些具有相同后缀的主机名，比如.example.com匹配foo.example.com(当然不仅仅只匹配foo.example.com)。

还有，在pg_hba.conf文件中使用主机名的时候，你最好能保证主机名的解析比较快，一个好的建议就是建立一个本地的域名解析缓存(比如nscd)。

本选项只能在连接方式是host,hostssl或者hostnossl的时候指定。

ip地址(ip-address)、子网掩码(ip-mask)

这两个字段包含可以看成是标准点分十进制表示的 IP地址/掩码值的一个替代。例如。使用255.255.255.0 代表一个24位的子网掩码。它们俩放在一起，声明了这条记录匹配的客户机的 IP 地址或者一个IP地址范围。本选项只能在连接方式是host,hostssl或者hostnossl的时候指定。

6.认证方法（authentication method）

trust: 无条件地允许联接，这个方法允许任何可以与PostgreSQL 数据库联接的用户以他们期望的任意 PostgreSQL 数据库用户身份进行联接，而不需要口令。

reject: 联接无条件拒绝，常用于从一个组中"过滤"某些主机。

md5: 要求客户端提供一个 MD5 加密的口令进行认证，这个方法是允许加密口令存储在pg_shadow里的唯一的一个方法。

password: 和"md5"一样，但是口令是以明文形式在网络上传递的，我们不应该在不安全的网络上使用这个方式。

gss: 使用GSSAPI认证用户，这只适用于 TCP/IP 连接。

sspi: 使用SSPI认证用户，这只适用于 Windows 连接。

peer: 获取客户端的操作系统的用户名并判断他是否匹配请求的数据库名，这只适用于本地连接。

ldap: 使用LDAP服务进行验证。

radius: 使用RADIUS服务进行验证。

cert: 使用SSL服务进行验证。

pam: 使用操作系统提供的可插入的认证模块服务 （Pluggable Authentication Modules）（PAM）来认证。

ident: 在PG11版本中默认有很多这个方式.

权限的其他建议

pg_hba.conf文件是如此重要，我们最好在建立数据库的时候就将它配置好，免得后来配置环境时出一些奇奇怪怪的错误。

在修改pg_hba.conf文件后一定要记得

pg_ctl reload 一下( systemctl reload postgresql-11.service )。
           

当然，还要做好备份。

配置尽量最小配置，比如指定用户名，数据库和可访问IP地址的时候，就只给最小权限，最小范围就好了，避免自己误操作是一回事。

对于访问认证的控制，除了在pg_hba.conf里面设置外，也应该在数据库里再进一步设置，比如给某个用户只授予所需的最低权限，比如对查询用户就只给所需的某几个数据库数据库的读权限，其他的只给数据库表的增删改查权限等，这里不赘述。

慎用trust认证方式，不要怕偷懒输密码.当然实在怕懒可以设置下.pgpass这个文件，懂得自然懂。

最后，对于系统数据库最好还是设置下reject的吧。

备份恢复

可以使用pg_dump和pg_dumpall来完成。比如备份sales数据库：

pg_dump drupal>/opt/Postgresql/backup/1.bak

其他说明

1.PostgreSQL对表名、字段名都是区分大小写的。

在图形化界面可以正常新建。用SQL语句的时候需要加双引号，如果jdbc查询等处，记得使用转义符号。

2.PostgreSQL在SQL语句中对大小写是不敏感的

例如 select ID from t_user 和 select id from t_user 都会从t_user这个表中查询id这个字段。如果要查询大写字母的字段，同样要加上双引号：select "ID" from t_user