PostgreSql

发表于 更新于 分类于

基本概念

关系（Relation）

TABLE

元组（Tuple）

RECORD、ROW

字段 Field

Column

表空间（Tablespace）

是一个存储位置，可以在其中保存底层数据库对象的实际数据，定义一个文件系统位置，代表数据库对象（表、索引等）的文件可以存储在该文件目录下

数据段（Segment）和数据页（Page）

每个表和索引都保存在一个单独的文件中，每个文件就是一个数据段（Segment）。在默认情况下，当一个表或索引的大小超过1GB时，它会被拆分出另一个数据段。第1个数据段以PostgreSQL内部定义的filenode命名，第2个数据段则命名为filenode.1，以此类推。一个数据段内部是以数据页的形式来组织的，数据页表示硬盘中的数据块，默认大小为8KB，最大为32KB，数据页的大小是在编译时确定的。行就存储在数据页中，由于每个数据页是等价的，因此一个特定的行可以存储在任意一个数据页中

存储结构

数据库族 -> 数据库 -> schema -> 表 -> 行 -> 字段

安装

安装脚本

管理

1
2
3
4
5
pg_ctl start -D /opt/postgresql/data 
pg_ctl stop -D /opt/postgresql/data -m smart/fast/immediate 
pg_ctl restart -D /opt/postgresql/data 
pg_ctl reload -D /opt/postgresql/data 
pg_ctl status -D /opt/postgresql/data

sql

sql分类

DQL、DML、DDL

控制台客户端

psql –help

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
General options:
  -c, --command=COMMAND    run only single command (SQL or internal) and exit
  -d, --dbname=DBNAME      database name to connect to (default: "postgres")
  -f, --file=FILENAME      execute commands from file, then exit
  -l, --list               list available databases, then exit
  -v, --set=, --variable=NAME=VALUE
                           set psql variable NAME to VALUE
                           (e.g., -v ON_ERROR_STOP=1)
  -V, --version            output version information, then exit
  -X, --no-psqlrc          do not read startup file (~/.psqlrc)
  -1 ("one"), --single-transaction
                           execute as a single transaction (if non-interactive)
  -?, --help[=options]     show this help, then exit
      --help=commands      list backslash commands, then exit
      --help=variables     list special variables, then exit

Input and output options:
  -a, --echo-all           echo all input from script
  -b, --echo-errors        echo failed commands
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -L, --log-file=FILENAME  send session log to file
  -n, --no-readline        disable enhanced command line editing (readline)
  -o, --output=FILENAME    send query results to file (or |pipe)
  -q, --quiet              run quietly (no messages, only query output)
  -s, --single-step        single-step mode (confirm each query)
  -S, --single-line        single-line mode (end of line terminates SQL command)

Output format options:
  -A, --no-align           unaligned table output mode
      --csv                CSV (Comma-Separated Values) table output mode
  -F, --field-separator=STRING
                           field separator for unaligned output (default: "|")
  -H, --html               HTML table output mode
  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)
  -R, --record-separator=STRING
                           record separator for unaligned output (default: newline)
  -t, --tuples-only        print rows only
  -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)
  -x, --expanded           turn on expanded table output
  -z, --field-separator-zero
                           set field separator for unaligned output to zero byte
  -0, --record-separator-zero
                           set record separator for unaligned output to zero byte

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "postgres")
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)

常用命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 列举数据库
\l
# 选择数据库
\c databasename
# 查看该某个库中的所有表
\d
# 查看某个库中的某个表结构
\d tablename
# 显示字符集
\encoding
# 退出psgl
\q
# 显示帮助
\?

表空间

1
2
3
4
5
6
7
mkdir -p /opt/postgresql/tablespace/testspace && chown -R postgres:postgres  /opt/postgresql
CREATE TABLESPACE testspace OWNER postgres LOCATION '/opt/postgresql/tablespace/testspace';
\db;
create TABLE test(a int) tablespace testspace;
select oid,datname from pg_database where datname = 'postgres';
select relname,relfilenode from pg_class where relname='test';
drop TABLESPACE testspace;

创建数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 获取帮助
\h create database
# 用法
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

URL: https://www.postgresql.org/docs/12/sql-createdatabase.html
# 创建数据库
CREATE database dennis WITH ENCODING='utf8' TABLESPACE=testspace OWNER=postgres;

字段说明

  • name：要创建的数据库的名称
  • user_name：要创建的数据库所属的用户如果没有指定，则默认属于执行该命令的用户
  • template：要创建的数据库所用的模板库，默认的模板库是template1
  • encoding：要创建的数据库所使用的字符集。如果没有指定，则默认使用其模板库的字符集
  • lc_collate：要创建的数据库所使用的collation顺序。这会影响在ORDER BY语法中字符串类型列的顺序，也会影响text类型列的索引顺序。如果没有指定，则默认使用其模板库的collation顺序
  • lc_ctype：要创建的数据库所使用的字符分类。这会影响字符的分类，如大小写和数字。如果没有指定，则默认使用其模板库的字符分类
  • tablespace_name：要创建的数据库所关联的表空间。默认使用模板库对应的表空间
  • allowconn：是否可以连接该数据库，默认设置为true。如果设置为false，则任何用户都不能连接该数据库
  • connlimit：允许并发连接该数据库的个数。默认设置为-1，即没有限制
  • istemplate：是否是模板库，默认设置为false。如果设置为true，则任何具有创建数据库权限的用户均可以用其复制新的数据库；如果设置为false，则只有超级用户和该数据库的用户可以用其复制新的数据库

创建数据表

官方文档

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
drop TABLE IF EXISTS company;
CREATE TABLE IF NOT EXISTS company (
    id serial PRIMARY KEY,
    title CHAR(50) UNIQUE NOT NULL,
    subtitle varchar(500) UNIQUE NOT NULL,
    age INT NOT NULL DEFAULT 0,
    address TEXT,
    salary REAL,
    money decimal,
    status double precision,
    guid uuid
);
-- \d+ company
insert into company(name,address,age,salary) values('dennis','成都','28',3.14);
insert into company(name,address,age,salary) values('dennis1','成都','28',3.14);
insert into company(name,address,age,salary) values('dennis2','成都','28',3.14);
insert into company(name,address,age,salary) values('dennis3','成都','28',3.14);
insert into company(name,address,age,salary) values('dennis4','成都','28',3.14);
insert into company(name,address,age,salary) values('dennis5','成都','28',3.14);
delete from company where age=28;

postgresql、mysql数据类型比较

postgresql mysql
无 TINYINT
smallint(2字节) SMALLINT(2字节)
无 MEDIUMINT(3字节)
integer(4字节) INT或INTEGER(4字节)
bigint(8字节) BIGINT(8字节)
decimal(可变长) DECIMAL
numeric(可变长) 无
real(4字节) FLOAT(4字节)
double(8字节) DOUBLE(8字节)
character varying(n), varchar(n) CHAR(0-255 bytes)
character(n), char(n) VARCHAR(0-65535 byte)
无 TINYTEXT
text(无长度限制) TEXT(0-65 535 bytes)
无 MEDIUMTEXT(0-16 777 215 bytes)
无 LONGTEXT(0-4 294 967 295 bytes)
timestamp（8字节） TIMESTAMP(4字节)
date(4字节) DATE(3字节)
time（8字节） TIME(0-4 294 967 295 bytes)
interval（12字节） 无
smallserial(2字节) 无
serial(4字节) 无
bigserial(8字节) 无

json、jsonb数据类型

二者的区别在于json写入快，读取慢，jsonb写入慢

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
drop table if exists json_test;
CREATE TABLE json_test (
  id serial,
  board_id float NOT NULL,
  data jsonb
);
INSERT INTO json_test VALUES (1, 1, '{"name": "Paint house", "tags": ["Improvements", "Office"], "finished": true}');
INSERT INTO json_test VALUES (2, 1, '{"name": "Wash dishes", "tags": ["Clean", "Kitchen"], "finished": false}');
INSERT INTO json_test VALUES (3, 1, '{"name": "Cook lunch", "tags": ["Cook", "Kitchen", "Tacos"], "ingredients": ["Tortillas", "Guacamole"], "finished": false}');
INSERT INTO json_test VALUES (4, 1, '{"name": "Vacuum", "tags": ["Clean", "Bedroom", "Office"], "finished": false}');
INSERT INTO json_test VALUES (5, 1, '{"name": "Hang paintings", "tags": ["Improvements", "Office"], "finished": false}');
SELECT data->>'name' AS name FROM json_test;
SELECT * FROM json_test WHERE data->>'finished' = 'true';

insert into json_test select * from json_test;

vacuum full json_test

->将以JSON对象的形式返回该属性，而->>将以整数或文本

数组类型

在存储一些数据时能更节省空间

1
2
3
4
5
6
7
8
9
10
11
drop table if exists array_test;
CREATE TABLE array_test (
id serial primary key,
data int[]
);
INSERT INTO array_test VALUES (1,'{3,5}');
select * from array_test;
update array_test set data[0]=3434 where id=1;
select * from array_test;
update array_test set data[7]=3434 where id=1;
select * from array_test;

postgresql还支持几何类型、uuid类型、xml类型、数组类型、复合类型、范围类型、布尔类型等

uuid采用16位存储,更节省空间

1
2
3
4
5
6
7
8
select * from pg_database;
select * from pg_class;
COPY json_test TO '/opt/postgresql/test.copy';
select * from json_test;
delete from json_test;
COPY json_test FROM '/opt/postgresql/test.copy';
COPY json_test(id, name) TO '/opt/postgresql/test.copy' DELIMITER ' ';

查询正在执行的sql

1
SELECT procpid,START,now() - START AS lap,current_query FROM(SELECT backendid,pg_stat_get_backend_pid(S.backendid) AS procpid,pg_stat_get_backend_activity_start (S.backendid) AS START,pg_stat_get_backend_activity (S.backendid) AS current_query FROM(SELECT pg_stat_get_backend_idset () AS backendid) AS S) AS S WHERE current_query <> '<IDLE>' ORDER BY lap DESC;

配置相关接口

1
2
3
4
5
select name,setting from pg_settings where category='File Locations';
select name,setting from pg_settings;
alter system set log_statement='none';
SELECT pg_reload_conf();
show log_statement;

upsert

1
insert into test values (1,'test',now()) on conflict(id) do update set info=excluded.info,crt_time=excluded.crt_time;

其他

  • postgre 事务号32位，约49亿
  • vacuum 不会锁表，只是单纯的回收空间以被重用，被回收的空间一般情况不会被返还给操作系统，仅仅被保留在同一个表中以备重用
  • vacuum full会锁表，会将表的整个内容重写到一个新的磁盘文件中，并且不包含额外的空间，这使得没有被 使用的空间被还给操作系统

索引组织表和堆表

  • 堆表：存储存储，插入速度块，查询速度慢(postgresql)，索引的二级索引为堆表上面的绝对位置(页号，页内偏移)，所以不一定要有主键，索引一般用B树
  • 索引组织表：有序存储，插入速度慢，查询速度快，写入时需要在特定的位置写入（mysql innodb），索引的二级索引为主键，所以是需要有主键的，索引一般用B+树

cpu 选取方法

最好的方法是通过类似top工具对当前数据库所在服务器进行监测。如果每个CPU运行的进程很少，则速度更快的CPU方案会更加适合这种类型的工作负载,单个查询一般只能使用一个核心（9.6引入了并行查询机制）

内存选取方式

  • 数据量相对于系统RAM来说非常小，那么加大内存也不会提升性能。此时，用户需要使用速度更快的处理器
  • 表的数据量远远大于可以为数据库分配的内存时，如数据仓库系统，这时用户需要选取更快的硬盘，而不是增加内存
  • 用户频繁访问的数据量相对于内存较大时，加大内存往往能起到显著效果，一般尽可能把热数据放在内存中

pg_buffercache

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 搜索contrib
yum search contrib
-- 安装 contrib
yum install postgresql12-contrib.x86_64
-- 查看已安装扩展 \dx
-- 查看pg_buffercache插件是否存在
select * from pg_available_extensions where name='pg_buffercache';
-- 安装插件
create extension pg_buffercache;
-- 查询
select * from pg_buffercache;
-- 查看buffer配置
SELECT name,setting,unit,current_setting(name) FROM pg_settings WHERE name='shared_buffers';
-- 查看当前buffer使用情况
select d.datname, c.relname, c.relkind, count(*) as buffers from pg_class c inner join pg_buffercache b on b.relfilenode=c.relfilenode inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database()) where 1=1 group by d.datname, c.relname, c.relkind order by d.datname,4 desc ;
-- 删除buffer
drop extension pg_buffercache;

wal和lsm

PostgreSQL实现MVCC方式

PostgreSQL如何实现MVCC (基于xmin、xmax、cmin、cmax)

常用运维sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 查看数据库
select * from pg_database;
-- 查看表空间
select * from pg_tablespace;
-- 查看用户
select * from pg_user;
select * from pg_shadow;
select * from pg_roles;
-- 查看表
select * from pg_tables;
-- 查看索引
select * from pg_index;
-- 查看锁
select * from pg_locks;
-- 查看表页数,表存储空间大小为 8k*relpages
select relname,relpages,reltuples,relallvisible,reltuples/relpages as avg_record_per_page from pg_class where relpages>0 order by relpages desc;
-- 查看会话进程
select usename,application_name,client_addr,query_start,query,backend_type from pg_stat_activity;
-- 查看表大小
select pg_relation_size('json_test')/1024 as table_size;
-- 查看数据库统计信息
select * from pg_stat_database;
-- 查看表io信息 
select * from pg_statio_user_tables;
-- 查看报表统计  vacuum
select * from pg_stat_all_tables;
-- 查看索引使用情况
select * from pg_stat_all_indexes;
select * from pg_statio_user_indexes;

pg_statio_user_indexes、pg_statio_all_indexes

返回字段含义

  • idx_blks_read:从磁盘中读取索引的次数
  • idx_blks_hit：从内存中读取索引次数
1
2
3
select * from pg_statio_user_indexes;
-- 查询索引内存命中率
select relname,relname,idx_blks_read,idx_blks_hit,idx_blks_hit*100/(idx_blks_read+idx_blks_hit) as idx_hit_rate from pg_statio_user_indexes;

pg_statio_user_tables、pg_statio_all_tables

heap_blks_read：读取的磁盘块数
heap_blks_hit：缓冲区命中数
idx_blks_read：所有索引读取的磁盘块数
idx_blks_hit：所有索引缓冲区命中数
toast_blks_read：TOAST表读取的磁盘块数（如果存在）
toast_blks_hit：TOAST表命中缓冲区数（如果存在）
tidx_blks_read：TOAST表索引读取的磁盘块数（如果存在）
tidx_blks_hit：TOAST表索引命中缓冲区数（如果存在）

1
2
3
select * from pg_statio_user_tables;
-- 查询索引内存命中率
select heap_blks_read,heap_blks_hit,idx_blks_read,idx_blks_hit,heap_blks_hit*100/(heap_blks_hit+heap_blks_read) as heap_hit_rate,idx_blks_hit*100/(idx_blks_read+idx_blks_hit) as idx_hit_rate from pg_statio_user_tables;

pg_stat_user_tables、pg_stat_all_tables

seq_scan：顺序扫描次数（全表扫描）
seq_tup_read：顺序扫描抓取的有live数据行的数目
idx_scan:索引扫描的次数
idx_tup_fetch:索引扫描抓取的有live数据行的数目
n_tup_ins:插入的行数
n_tup_upd:更新的行数
n_tup_del:删除的行数
n_tup_hot_upd:HOT更新的行数（即不需要单独的索引更新
n_live_tup:live行估计数
n_dead_tup:dead行估计数
last_vacuum:最后一次手动vacuum时间（不计算VACUUM FULL）
last_autovacuum:最后一次autovacuum时间
last_analyze:最后一次analyze时间
last_autoanalyze:最后一次autoanalyze时间
vacuum_count:vacuum的次数（不计算VACUUM FULL）
autovacuum_count:autovacuum的次数
analyze_count:analyze的次数
autoanalyze_count:autoanalyze的次数

1
2
3
4
5
6
7
select * from pg_stat_user_tables;
-- 查看全表扫描与索引扫描，seq_scan最好能为0
select relname,seq_scan,seq_tup_read,idx_scan,idx_tup_fetch from pg_stat_user_tables;
-- 查看操作行数
select relname,n_tup_ins,n_tup_upd,n_tup_del,n_tup_hot_upd,n_live_tup,n_dead_tup from pg_stat_user_tables;
-- 查看vacuum和analyze
select relname,last_vacuum,last_autovacuum,vacuum_count,autovacuum_count,last_analyze,last_autoanalyze,analyze_count,autoanalyze_count from pg_stat_user_tables;

PG_STAT_REPLICATION

PG_THREAD_WAIT_STATUS

https://support.huaweicloud.com/devg-dws/dws_04_0517.html

数据写入流程

  • change发生时：先将变更后内容写入 wal buffer，再将数据写入 data buffer
  • commit发生时：wal buffer中数据内容刷新到磁盘
  • checkpoint发生时：将所有data buffer刷新到磁盘

触发checkpoint时机

  • 手动执行checkpoint命令
  • 执行需要检查点的命令（pg_stat_backup、pg_ctl stop restart 等）
  • 达到检查点配置的时间（checkpoint_timeout）
  • max_wal_size 满了
1
2
show checkpoint_timeout
show max_wal_size

复制

日志复制

对wal日志进行拷贝，因此从库始终落后主库一个日志文件，并且使用rsync工具同步data目录

流复制

PostgreSQL通过wal日志来传送的方式有两种：基于文件的日志传送和流复制

流复制过程
$PGDATA/pg_wal(pg10之前叫pg_xlog)

procs ———–memory———- —swap– —–io—- -system– ——cpu—–
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 0 0 26416 25936 281156 0 0 0 0 521 1258 0 0 100 0 0
0 0 0 26528 25936 281156 0 0 0 0 508 1223 1 1 98 0 0
0 0 0 26540 25936 281156 0 0 0 0 513 1220 1 0 99 0 0
0 0 0 26544 25944 281152 0 0 0 20 517 1226 0 1 99 0 0

in：每秒的中断数，包括时钟中断。
cs：每秒上下文切换的次数
r：等待运行的进程数
b：阻塞中的进程

默认监控: sar 1 1 // CPU和IOWAIT统计状态
(1) sar -b 1 1 // IO传送速率
(2) sar -B 1 1 // 页交换速率
(3) sar -c 1 1 // 进程创建的速率
(4) sar -d 1 1 // 块设备的活跃信息
(5) sar -n DEV 1 1 // 网路设备的状态信息
(6) sar -n SOCK 1 1 // SOCK的使用情况
(7) sar -n ALL 1 1 // 所有的网络状态信息
(8) sar -P ALL 1 1 // 每颗CPU的使用状态信息和IOWAIT统计状态
(9) sar -q 1 1 // 队列的长度（等待运行的进程数）和负载的状态
(10) sar -r 1 1 // 内存和swap空间使用情况
(11) sar -R 1 1 // 内存的统计信息（内存页的分配和释放、系统每秒作为BUFFER使用内存页、每秒被cache到的内存页）
(12) sar -u 1 1 // CPU的使用情况和IOWAIT信息（同默认监控）
(13) sar -v 1 1 // inode, file and other kernel tablesd的状态信息
(14) sar -w 1 1 // 每秒上下文交换的数目
(15) sar -W 1 1 // SWAP交换的统计信息(监控状态同iostat 的si so)
(16) sar -x 2906 1 1 // 显示指定进程(2906)的统计信息，信息包括：进程造成的错误、用户级和系统级用户CPU的占用情况、运行在哪颗CPU上
(17) sar -y 1 1 // TTY设备的活动状态

————————————————
版权声明：本文为CSDN博主「liyongbing1122」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/liyongbing1122/article/details/89517282