PostgreSQL记录

目录

安装与配置

数据类型

数字类型

字符类型

时间/日期类型

数据类型转化

COLLATE

SQL高级特性

WITH查询

批量插入

聚合函数

窗口函数

索引

视图

函数

体系结构

逻辑和物理存储结构

进程结构

内存结构

Oracle To PostgreSQL

DBA

谷歌云 PostgreSQL

---

官方文档：https://www.postgresql.org/docs/

（也可下载PDF方便查阅）

https://www.postgresql.org/docs/current/sql-commands.html

Github：https://github.com/postgres/postgres

安装与配置

1. Linux:

在RHEL 8系统上安装PostgreSQL 11软件包，需要安装PostgreSQL RPM存储库，其中包含许多不同的软件包，如PostgreSQL服务器，客户端二进制文件和第三方加载项。

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

yum install -y postgresql11-server postgresql11-contrib

client(postgresql11)包会随它们一起被安装，-y可避免安装图中出现确认安装的提示。

官方yum源安装的位置在/usr/pgsql-10目录，可执行文件位于/usr/pgsql-10/bin目录，并且会自动创建一个postgres账户，它的home目录在/var/lib/pgsql。

ln -s /opt/pg10 /opt/pgsql 创建一个/opt/pgsql的软链接，即pgsql -> /opt/pg10

当进行版本变更之后，不需要调整大量脚本，只需要修改这个软链接。

初始化数据库：

/usr/bin/postgresql-setup --initdb

默认是/usr/lib/pgsql/data

Or initdb –D /usr/local/pgsql/data 初始化数据目录

启用它以在系统引导时自动启动并使用systemctl命令验证其状态。

systemctl start postgresql
systemctl enable postgresql
systemctl status postgresql

创建密码：

passwd postgres

su - postgres
psql -c "ALTER USER postgres WITH PASSWORD 'adminpasswdhere123';"

各种PostgreSQL配置文件可以在/var/lib/pgsql/data/目录中找到

yum install tree

tree -L 1 /var/lib/pgsql/data/

2. Windows请参考官网

下载和安装包

cd </path/to/postgresql>/pgsql/bin

官方Reference doc PDF手册中可以看到：

initdb

pg_ctl start

pg_ctl status

createdb audit

createdb runtime

createuser -P -S -e cxf_user 

SELECT * FROM pg_roles WHERE rolsuper='true';

ALTER USER "cxf_user" WITH SUPERUSER;

通过 pg_ctl register -N "pgsql_service" -D <data folder> -S a

然后在 Windows 的 Service 可以设置 “Automatic” 注册 Postgres 服务到 Windows 开机自启动。

3. Mac (Postgres.app – the easiest way to get started with PostgreSQL on the Mac)

全局配置文件

• postgresql.conf：文件位置、资源限制、集群复制等。

cd "/Users/cxf/Library/Application Support/Postgres/var-14"

• pg_hba.conf：客户端的连接和认证。

如果有改动配置，重新启动Postgres服务应用配置中的更改

systemctl reload postgresql

客户端工具： psql (自带的命令行客户端) 和pgAdmin (图形化客户端)

psql -h <host/ip> -p <port> <db> <user>

常用命令参考列表：

命令    描述

\d     List tables
\dd?object-name    Display comments for?object-name
\db    List all tablespaces
\dn    List all schemas
\d_\dt    List all tables
\di    List all indexes
\ds    List all sequences
\dv    List all views
\dS    List all PostgreSQL-defined tables
\d table-name    Show table definition
\d index-name    Show index definition
\d view-name    Show view definition
\d sequence-name    Show sequence definition
\dp    List all privileges
\dl    List all large objects
\da    List all aggregates
\df    List all functions
\dc    List all conversions
\dC    List all casts
\df function-name    List all functions with given name
\do    List all operators
\do operator-name    List all operators with given name
\dT    List all types
\dD    List all domains
\dg    List all groups
\du    List all users
\l    List all databases in this cluster

eg：

\l 查看所有数据库信息

pgAdmin

Connection信息包括hostname/address (eg: localhost)，port (eg: 5432)，database（eg: audit），username（eg: cxf_user)。

选择具体的数据库，右键选择”Query Tool"，执行SQL。

数据类型

包括常规数据类型中的数字类型、字符类型、日期/时间类型等，以及非常规数据类型中的布尔类型、网络地址类型、数组类型、范围类型、json/jsonb类型等。

数字类型

PostgreSQL支持的数字类型有整数类型、用户指定精度类型、浮点类型、serial类型。

​

smallint存储2字节整数，字段定义时也可以写成int2，同理，integer也可写成int4，是最常用的整数类型。

字符类型

​

时间/日期类型

​

 其他还有布尔boolean、数组、网络地址、范围、json/jsonb类型等。

数据类型转化

主要有三种方式：通过格式化函数、CAST函数、::操作符。

（1）数据类型转换函数

​

（2）通过CAST函数进行转换

​

 （3）通过 :: 操作符进行转换

 ​

COLLATE

The collation feature allows specifying the sort order and character classification

PostgreSQL: Documentation: 14: 24.2. Collation Support

设置PostgreSQL排序规则（Collation） - 云数据库 RDS - 阿里云

​

COLLATE pg_catalog."default" just tell that using default lc_collate for this column.

​

SQL高级特性

WITH查询

这一特性常称为Common Table Expressions (CTE)，可简化SQL且减少嵌套，常用于复杂查询或递归查询。在复杂查询中定义辅助语句，可理解成在一个查询中定义的临时表。对于复杂查询，如果不使用CTE，可通过创建视图方式简化SQL。

这个例子首先定义了regional_sales和top_regions两个辅助语句，regional_sales算出每个区域的总销售量，top_regions算出销售量占总销售量10%以上的所有区域，主查询语句通过辅助语句与orders表关联，算出了顶级区域每件商品的销售量和销售额。 

批量插入

方式一：INSERT INTO...SELECT...

方式二：INSERT INTO VALUES (), (), …()

方式三：COPY或\COPY元命令

聚合函数

聚合函数将结果集进行计算并且通常返回一行。

sum(), avg(), count(), min(), max()

string_agg()

array_agg() 跟string_agg函数类似，最主要的区别为返回的类型为数组，数组数据类型同输入参数数据类型一致，array_agg函数输入参数支持非数组类型和数组类型。

窗口函数

窗口函数也是基于结果集进行计算，与聚合函数不同的是窗口函数不会将结果集进行分组计算并输出一行，而是将计算出的结果合并到输出的结果集上，并返回多行。

row_number(), rank(), dense_rank(), lag()等，除了内置的窗口函数外，聚合函数、自定义函数后接OVER属性也可作为窗口函数。聚合函数后接OVER属性的窗口函数表示在一个查询结果集上应用聚合函数。

1. row_number()

2. rank()

3. dense_rank()

4. lag()

以上演示了lag()窗口函数取向上偏移记录的字段值，将offset设置成负整数可以取向下偏移记录的字段值。

5. first_value(), last_value(), nth_value()

first_value()、last_value()、nth_value()窗口函数分别用来取结果集每一个分组的第一行、最后一行、指定行数据的字段值。

6. avg()聚合函数后接OVER属性的窗口函数，此窗口函数用来计算分组后数据的平均值。 

使用窗口函数很容易实现以上需求：

窗口函数别名的使用：

索引

支持在同一张表中混合搭配不同的索引类型，且预计规划器将综合考虑所有的索引。

B-树索引

B-树是一种关系型数据库中常见的通用索引类型。如果你对别的索引类型不感兴趣，那么一般使用 B-树索引就可以了。有的场景下 PostgreSQL 会自动创建索引（比如创建主键约束或者唯一性约束时），那么创建出来的索引就是 B-树类型的；如果你自己创建索引时未指定索引类型，那么默认也会创建 B-树类型的索引。主键约束和唯一性约束唯一支持的后台索引就是 B-树索引。
      

视图

• 单表视图
• 使用触发器更新视图
• 物化视图

函数

CREATE [OR REPLACE] FUNCTION function_name (p1 type, p2 type)   
RETURNS return_datatype AS $variable_name$  
DECLARE  
  <declaration>
BEGIN  
  < function_body -- 函数逻辑 >  
  RETURN { variable_name | value }  
END; 
LANGUAGE <language_name>;

将代码放在 BEGIN 和 END 块内，该函数始终以分号（;）结尾，函数的过程语言比如 plpgsql 表示 PL/pgSQL。

体系结构

逻辑和物理存储结构

1. Database Cluster逻辑存储结构

 database默认schema是public。

分区表

分区表主要有以下优势：

• 当查询或更新一个分区上的大部分数据时，对分区进行索引扫描代价很大，然而，在分区上使用顺序扫描能提升性能。
• 当需要删除一个分区数据时，通过DROP TABLE删除一个分区，远比DELETE删除数据高效，特别适用于日志数据场景。
• 由于一个表只能存储在一个表空间上，使用分区表后，可以将分区放到不同的表空间上，例如可以将系统很少访问的分区放到廉价的存储设备上，也可以将系统常访问的分区存储在高速存储上。

分区表的优势主要体现在降低大表管理成本和某些场景的性能提升

2. 物理存储结构

数据库的文件默认保存在initdb时创建的数据目录中。在数据目录中有很多类型、功能不同的目录和文件，除了数据文件之外，还有参数文件、控制文件、数据库运行日志及预写日志等。

数据目录中子目录和文件的用途：

cd "/Users/cxf/Library/Application Support/Postgres/var-14"

 base子目录是数据文件默认保存的位置。

基础的数据库对象：

（1）OID

 （2）表空间

进程结构

PostgreSQL是一用户一进程的客户端/服务器的应用程序。数据库启动时会启动若干个进程，其中有postmaster（守护进程）、postgres（服务进程）、syslogger、checkpointer、bgwriter、walwriter等辅助进程。

内存结构

PostgreSQL的内存分为两大类：本地内存和共享内存，另外还有一些为辅助进程分配的内存等。

Oracle To PostgreSQL

推荐参考阅读: Oracle to Postgres Conversion - PostgreSQL wiki

Oracle与PostgreSQL常见数据类型适配表：

​

​

PostgreSQL的 character varying 相当于 varchar，character 也可以写成 char；

值得一提的是，Oracle将对象名称默认转换成大写，而PostgreSQL将对象名称转换成小写；

PostgreSQL建表时表名不要用双引号，否则将带来使用、维护上的复杂度。

存储过程代码差异

有些应用系统会将部分业务用数据库的存储过程实现，尤其是大型数据库系统使用的存储过程可能多达上百个，大型系统迁移将涉及大量的改造工作。PostgreSQL没有存储过程的概念，可以用函数来实现存储过程中的逻辑，PostgreSQL函数的语法和Oracle有一定的差异，因此，Oracle的存储过程迁移到PostgreSQL中需要重写存储过程代码，所涉及的工作量还是相当大的。

典型的SQL语法差异

• Oracle ROWNUM => Postgres LIMIT

Oracle数据库中可以使用ROWNUM虚拟列限制返回的结果集记录数，例如限制仅返回一条记录，如下所示：

​

PostgreSQL可以使用LIMIT关键字限制返回的记录数，如下所示：

​

Oracle中的ROWNUM和PostgreSQL的LIMIT语法虽然在功能上都可以限制返回的结果集，但两者原理不同，ROWNUM是一个虚拟列，而LIMIT不是虚拟列。Oracle中的ROWNUM和PostgreSQL的LIMIT常用于分页查询的场景。 

• 序列 Oracle seq.CURRVAL => Postgres CURRVAL('seq')

​

 

• Oracle SYSDATE => Postgres CURRENT_DATE

•  Oracle TO_DATE & ADD_MONTHS => Postgres ::date & plus

eg: Oracle "timestamp" data type: DATE

Postgres "timestamp" data type: "timestamp without time zone"

WHERE timestamp < TO_DATE ( ADD_MONTHS (SYSDATE, -12),  'DD-MON-RR'))

=> WHERE timestamp::date + 12 < CURRENT_DATE

•  Oracle ROUND & minus => Postgres extract (day from ...)

eg: Oracle "last_logon_dt" data type: DATE

Postgres "last_logon_dt" data type: "timestamp without time zone"

ROUND(SYSDATE - last_logon_dt) AS noOfInactiveDay =>

extract ( day from CURRENT_DATE - last_logon_dt) AS noOfInactiveDay

•  Oracle NVL => Postgres COALESCE

eg: NVL(PASSWD_HISTORY, ‘NULL') <> 'IS A ROLE' =>

COALESCE(PASSWD_HISTORY, ‘NULL') <> 'IS A ROLE'

•  子查询 Oracle subquery alias is optional => Postgres subquery alias is required

​

• 递归查询等

DBA

1. SQL:

查找最耗费资源的 SQL（Top SQL） - 云数据库 RDS - 阿里云

2. 执行计划：EXPLAIN (ANALYZE)

3. 基准测试：吞吐量（Throughout）、响应时间（RT）或延迟（Latency）和并发量

4. 备份与恢复等

谷歌云 PostgreSQL

gcloud beta sql instances create <instance> --project <project> --network <network> --no-assign-ip --zone <zone> --cpu 1 --memory 3840Mib --database-version POSTGRES_11 --disk-encryption-key <cryptoKeys>

目前已经是在物理机上的PostgreSQL，之前工作中的谷歌云的PostgreSQL实践有时间再看哪些可以分享到博客，觉得还蛮有意思:)