PostgreSql数据库

• 层级结构
  • 从上到下为：数据库（Database） - 模式（Schema） - Table（表）
  • properties文件中配的地址是到数据库一级：spring.datasource.url=jdbc:postgresql://127.0.0.1:5432/postgres
  • 代码中连接成功后，如果需要使用jdbc或mybatis等方式使用sql，操作表时需要指定模式（Schema）：SELECT id, name, password FROM test.user
• 用户/权限管理
  • 在PostgreSql数据库中叫做Login Role，可以自行增删改并设置合适的权限。
  • Role
    • 两类role
      • Login role
        • 相当于其他数据库的用户/user？
        • 创建时在Privileges中选Ccan login?权限
        • 可以在Definition中设置密码/password用于登陆
      • Group role
        • 相当于一个用户组/user group
        • 创建时在privilege中没有选Can login?权限
    • 选中role后，在pgadmin4右侧的面板中可以看到
      • Dashboard
      • Dependant
        • 该user创建的table等元素？
      • Dependency
    • public是一个不可见的内置的role，代表所有role和人，所有role和人默认都隐含的继承自它
  • 权限/privilege
    • 创建role时，可以在properties窗口的Privileges面板中勾选可以有哪些权限
    • 在database或table的properties菜单的security窗口，可以看有哪些具体的权限/privilege设置（哪些role有哪些权限）
    • owner
      • 默认哪个role创建的database或者table，其owner就是这个role
      • 在pgadmin中手动通过UI创建database或table时，可以手动指定owner，也可以在security标签页中设置详细的Privileges（比如某个login role或group role，可以有哪些具体的权限）
      • 只有owner才能删除database或者table
      • 其他role都可以操作table中的数据
    • grant all on all tables in schema public to public;
      • 需要当前账号有当前想要开放权限的元素的owner或更细的相关权限如all with grant option？（如果是all tables就需要有所有table的权限了）
        • 否则会报错(SQL执行失败了，是报错，打断脚本)：permission denied for relation table_nam
      • 把public这个schema下面的所有表的所有权限授予给public这个role
      • 如果该元素（某个table或all table）已经授予过权限给public等role了，那么就报一个提示(SQL还是执行成功了，不是报错，不打断脚本)：no privileges were granted for “table_name”
      • 注意要在创建完表之后再执行这个命令，不然后面再创建的表，别的用户还是会没有权限。那么如果有多个微服务都有SQL脚本来处理该数据库，就要注意并行串行问题了？为什么执行有时会报permission denied for relation xxx，因为并行多个用户在执行它且有的表在grant之后才创建，导致有时后面的用户再执行grant会没有权限处理这个前面的用户在grant后才创建的表？
• Schema
  • 默认是一个public，也可以自己创建自定义的
  • Schema下可以有各种元素
    • Table
    • View
    • Domain
      • 可以用来自定义数据类型，类似于一个别名。
        • CREATE DOMAIN xxx AS character varying(36) NULL;
        • create table yyy { id xxx NOT NULL PRIMARY KEY }
    • Collation
    • Foreign Table
    • Function
    • Trigger Function
    • Type
• TABLESPACE(表空间)
  • PostgreSQL中的表空间允许在文件系统中定义用来存放表示数据库对象的文件的位置。在PostgreSQL中表空间实际上就是给表指定一个存储目录。
  • 在PostgreSQL集群中；一个表空间可以让多个数据库使用；而一个数据库可以使用多个表空间。属于"多对多"的关系。
  • 建表时可以指定
  • 作用
    • 一句话来说，就是能合理利用磁盘性能和空间,制定最优的物理存储方式来管理数据库表和索引。
    • 如果初始化集簇所在的分区或者卷用光了空间，而又不能在逻辑上扩展或者做别的什么操作，那么表空间可以被创建在一个不同的分区上，直到系统可以被重新配置。
    • 表空间允许管理员根据数据库对象的使用模式来优化性能。例如，一个很频繁使用的索引可以被放在非常快并且非常可靠的磁盘上，如一种非常贵的固态设备。同时，一个很少使用的或者对性能要求不高的存储归档数据的表可以存储在一个便宜但比较慢的磁盘系统上。
  • 系统自带的表空间
    • 表空间pg_default是用来存储系统目录对象、用户表、用户表index、和临时表、临时表index、内部临时表的默认空间。对应存储目录$PADATA/base/
    • 表空间pg_global用来存放系统字典表；对应存储目录$PADATA/global/
• OID
  • 行对象标识符（对象ID），这个字段只有在创建表时使用了“with oids”或配置参数“default_with_oids”的值为真时才出现，这个字段的类型是oid（类型名与字段名同名）
  • PostgreSQL在内部使用对象标识符（oid）作为系统表的主键。系统不会给用户创建的表增加一个oid字段。oid类型用一个四字节的无符号整数实现，不能提供大数据范围内的唯一性保证，甚至在单个大表中也不行。因此PostgreSQL官方不鼓励在用户创建的表中使用oid字段。
  • 可以在建表时指定
• 表名字段名尽量要避免postgres的关键字，如user，会创建失败。！！！
  • 如timestamp这种关键字，如果想要当作字段名，那么sql语句中要加上双引号，如”timestamp”
• 定义表名、字段名时要小心双引号
  • 虽然管理端上看不出来，但是其实是不同的，会查不到
  • 标准的SQL是不区分大小写的，但PostgresSQL支持使用双引号来保持大小写。但是PostgreSQL对于数据库中对象的名字允许使用支持大小写区分的定义和引用方法。方式就是在DDL中用双引号把希望支持大小的对象名括起来。
    • 比如希望创建一个叫AAA的表。如果用CREATE TABLE AAA （...）;的话，创建出来的表实际上是aaa。如果希望创建大写的AAA表的话，就需要用CREATE TABLE "AAA" (...);这种双引号的方式定义对象名。
    • 这样写的缺点是查询语句必须也使用双引号的方式引用对象名。比如SELECT * FROM "AAA"；否则PostgreSQL缺省会去找aaa这个对象，然后返回aaa不存在的错误。需要注意的是不仅仅是表可以这样定义和引用，对PostgreSQL中的任意对象（比如列名，索引名等）都有效。
    • 总结
      • 不推荐使用pgAdmin III这个工具创建数据库对象。还是应该手工编写DDL语句。
        • 如果表是通过PostgreSQL的pgAdmin III 工具创建的话，缺省是按照有双引号的方式创建对象的，所以DLL里面必须也要按照有双引号的方式使用。但是这种写法不是标准的，如果是通过一些通用库函数访问数据库的话，会不被支持。
      • 不推荐在DDL里用双引号的方式创建区分大小写的对象。
      • PostgreSQL给出的建议是SQL的key word用大写，其他的名称全部使用小写。
• 状态监控和问题排查
  • 通过系统表pg_stat_activity可以查看当前的连接信息，如用户名、查询语句、状态等。
    • pg_stat_activity是一个非常有用的视图，可以帮助排查pg的一些问题（如连接数目过多问题）。pg_stat_activity每行展示的是一个“process”的相关信息，这里的“process”可以理解为一个DB连接。
    • 查询某个数据库的状态
      • select * from pg_stat_activity where datname='xxx'
      • state:运行状态，可以为几种值。active:正在执行查询；idle:等待新的命令；idle in transaction:后端是一个事务，但是尚未执行查询；idle in transaction(aborted):和idle in transaction类似，除了事务执行出错。
      • query_start:active状态的查询开始时间，如果状态不是active的，那么就是最后一次查询开始的时间
      • query:执行的查询文本（即SQL）。如果状态是active，那么就是正在执行的SQL；如果是其他状态，则展示最后一次执行的SQL。
    • 查询闲置连接数。如果数字过大，可以认为是有问题的（如连接忘记关闭）。
      • select count(*) from pg_stat_activity where state='idle';
    • 如果想进一步定位到有问题的SQL，可以如下查询：
      • select query,count(*) as num from pg_stat_activity where state='idle' group by query order by num desc;
• 时间戳和时间之间的转换
  • 如果timestamp是毫秒的，那么就要先除以1000，如to_char(to_timestamp(sent_date / 1000), 'yyyy-mm-dd hh24:mi:ss') as sent_date

SELECT *, to_char(to_timestamp(sent_date), 'yyyy-mm-dd hh24:mi:ss.us')
  FROM billing_report_sent_history order by usage_date desc limit 500;

• 自增列
  • 要不直接使用serial类型字段，要不先创建序列，然后设置字段的自增。

--方法一
create table test_a 
( 
  id serial, 
  name character varying(128), 
  constraint pk_test_a_id primary key( id) 
); 

--方法二
create table test_b 
( 
  id serial PRIMARY KEY, 
  name character varying(128) 
); 

--方法三
create table test_c 
( 
  id integer PRIMARY KEY, 
  name character varying(128) 
);  
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_c_pkey" for table "test_c" 
CREATE TABLE 

CREATE SEQUENCE test_c_id_seq 
    START WITH 1 
    INCREMENT BY 1 
    NO MINVALUE 
    NO MAXVALUE 
    CACHE 1; 

alter table test_c alter column id set default nextval('test_c_id_seq'); 

• 客户端
  • pgadmin3
  • pgadmin4
    • web版客户端，安装后直接通过浏览器访问。
    • 如果用docker安装，需要在run时指定邮箱地址和密码这两个环境变量，docker run -e “PGADMIN_DEFAULT_EMAIL=xxx.com” -e “PGADMIN_DEFAULT_PASSWORD=123456” -itd —name pgadmin4 dpage/pgadmin4 -p 80:80 /bin/bash
    • 细节
      • 可以在database上右击选择“Query Tool”，会打开一个新的脚本执行窗口，脚本会以当前login role的权限执行作用于该database，从查询窗口的名称上也能看出来是用哪个login role执行并作用于哪个database的。