📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

前言

PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统,本文总结了日常的运维经验

---

⛳️ 1. 何为postgresql

PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS），是以加州大学计算机系开发的POSTGRES，4.2版本为基础的对象关系型数据库管理系统。POSTGRES的许多领先概念只是在比较迟的时候才出现在商业网站数据库中。PostgreSQL支持大部分的SQL标准并且提供了很多其他现代特性，如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。同样，PostgreSQL也可以用许多方法扩展，例如通过增加新的数据类型、函数、操作符、聚集函数、索引方法、过程语言等。另外，因为许可证的灵活，任何人都可以以任何目的免费使用、修改和分发PostgreSQL。

⛳️ 2. 参数文件

✨ 2.1 黑名单参数文件

相当于mysql数据库中的mysql.user表。
该文件位于初始化安装的数据库目录下，例如：/var/lib/pgsql/11/data/pg_hba.conf。
视图select * from pg_hba_file_rules; 可以查询pg_hba.conf文件的内容。

cat  << EOF > /var/lib/pgsql/11/data/pg_hba.conf
# TYPE  DATABASE    USER    ADDRESS       METHOD
local     all       all                    trust
host      all       all    ::1/128         trust
host      all       all   127.0.0.1/32     trust
host      all       all    0.0.0.0/0        md5
host   replication  all    0.0.0.0/0        md5
EOF
1
2
3
4
5
6
7
8

✨ 2.2 运行参数文件

cat >> /postgresql/pgdata/postgresql.conf <<"EOF"
listen_addresses = '*'
port=5433
unix_socket_directories='/postgresql/pgdata'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on   #日志循环
EOF
1
2
3
4
5
6
7
8
9

📢📢📢 配置示例

64G内存，16核，HUGE PAGE=on，SSD机器
port = 5432# 监听端口
max_connections=1700# 规格内存(GB)*1000*(1/4)/10+superuser_reserved_connections  
shared_buffers=16GB# IF use hugepage: 规格内存*(1/4)ELSE: min(32GB, 规格内存*(1/4))
max_prepared_transactions=1700# max_prepared_transactions=max_connections
work_mem =16MB# max(min(规格内存/4096, 64MB), 4MB)
maintenance_work_mem = 1GB# min( 8G, (主机内存*1/8)/max_parallel_maintenance_workers )
autovacuum_work_mem  = 1GB# min( 8G, (规格内存*1/8)/autovacuum_max_workers )
max_parallel_maintenance_workers = 8# min( max(2, CPU核数/2) , 16 )
max_parallel_workers_per_gather = 12# min( max(2, CPU核数-4) , 24 )
max_parallel_workers =12# min(max(2, CPU核数-4) ,32)
max_wal_size = 32GB# min(shared_buffers*2 ,用户存储空间/10)
min_wal_size  =8GB# min(shared_buffers/2  , 用户存储空间/10)  
max_sync_workers_per_subscription = 12  # min ( 32 , max(2, CPU核数-4) )
effective_cache_size  = 48GB# 规格内存*0.75  
autovacuum_max_workers = 8# max(min( 8 , CPU核数/2 ) , 5)  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

✨ 2.3 动态参数文件

ostgresql.auto.conf文件
postgres=# alter system set port=5434;
postgres=# select pg_reload_conf(); --生效

⛳️ 3、 常用psql

psql 是 PostgreSQL 中的一个命令行交互式客户端工具，它允许你交互地键入 SQL 命令，然后把它们发送给 PostgreSQL 服务器，再显示 SQL 或命令的结果。
输入的内容允许来自一个文件，此外它还提供了一些元命令和多种类似 shell 的特性来实现书写脚本，以及对大量任务的自动化工作。
特性：方便快捷、没有图形化工具使用上的一些限制。
C:\Users\wangd>psql --help

✨ 3.1 远程登陆

psql -U postgres -h 192.168.66.35 -d postgres -p 15432 -E
-U为用户
-h为主机
-d为数据库
-p为端口号
-E为后台SQL显示，可以省略
如果想要免密登陆，需要设置环境变量

✨ 3.2 常用选项

postgres=# \db+

\d [ table ] 
列出数据库中的表，或（如果声明了）表 table 的列/字段．如果表名是用统配符（“*”）声明的，列出所有表和表的列/字段信息．
\da 
列出所有可用聚集．
\dd object 
列出 pg_description 里对声明的对象的描述，对象可以是一个表，表中的列/字段，类型，操作符或聚集．

小技巧：并非所有对象在 pg_description 里有描述．此后期命令在快速获取 Postgres 内部特性时很有用．
\df 
列出函数．
\di 
只列出索引．
\do 
只列出操作符．
\ds 
只列出序列．
\dS 
列出系统表和索引．
\dt 
只列出非系统表．
\dT 
列出类型．
\e [ filename ] 
编辑当前查询缓冲或文件 filename 的内容．
\E [ filename ] 
编辑当前查询缓冲或文件 filename 的内容并且在编辑结束后执行之．
\f [ separator ] 
设置域分隔符．缺省是单个空白．
\g [ { filename | |command } ] 
将当前查询输入缓冲送给后端并且（可选的）将输出放到 filename 或通过管道将输出送给一个分离的Unix shell 用以执行 command．
\h [ command ] 
给出声明的 SQL 命令的语法帮助．如果 command 不是一个定义的 SQL 命令（或在 psql 里没有文档），或没有声明 command ，这时 psql将列出可获得帮助的所有命令的列表．如果命令 command 是一个通配符（“*”），则给出所有 SQL 命令的语法帮助．
\H 
切换 HTML3 输出．等效于 -H 命令行选项．
\i filename 
从文件 filename 中读取查询到输入缓冲．
\l 
列出服务器上所有数据库．
\m 
切换老式监视器样的表输出，这时表周围有边界字符包围着．这是标准 SQL 输出．缺省时，psql 只包括列/字段间的分隔符．
\o [ { filename | |command } ] 
将后面的查询结果输出到文件 filename 或通过管道将后面结果输出到一个独立的Unix shell 里执行 command．如果没有声明参数，将查询结果输出到 stdout．
\p 
打印当前查询缓冲区．
\q 
退出 psql 程序．
\r 
重置（清空）查询缓冲区．
\s [ filename ] 
将命令行历史打印出或是存放到 filename．如果省略 filename ，将不会把后继的命令存放到历史文件中．此选项只有在 psql 配置成使用输入行时才有效．
\t 
切换输出的列/字段名的信息头和行记数脚注（缺省是开）．
\T table_options 
允许你在使用HTML 3.0 格式输出时声明放在表 table ... 中的标记选项．例如，border 将给你的表以边框．这必须和 H 后期命令一起使用．
\x 
切换扩展行格式．当打开时，每一行将在左边打印列/字段名而在右边打印列/字段值．这对于那些不能在一行输出的超长行是很有用的．HTML 行输出模式也支持这个标记．
\w filename 
将当前查询缓冲区输出到文件 filename．
\z 
生成一个带有正确 ACL（赋予/禁止权限）的数据库中所有表的输出列表．
! [ command ] 
回到一个独立的Unix shell或执行一个Unix 命令 command．
\? 
获得关于反斜杠 (“”) 命令的帮助．
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
52
53
54
55
56
57
58
59
60
61
62
63
64

⛳️ 4、 常用psql启动和关闭PG

启动和停止 PostgreSQL 数据库服务器，通常使用pg_ctl。通常在我们的生产环境中，如果数据库主机发生意外停机或者由于计划内的硬件配置等操作停止了主机后，PostgreSQL 服务也将会停止，需要手动重启。因此，在生产环境中，采用编译安装 PostgreSQL 数据库后，建议配置系统 postgresql.service 服务，通过 systemctl 系统命令设置开机自动启动。

⛳️ 5、数据字典

✨ 5.1 查询数据库大小

SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges",
       CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
            THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
            ELSE 'No Access'
       END as "Size",
       t.spcname as "Tablespace",
       pg_catalog.shobj_description(d.oid, 'pg_database') as "Description"
FROM pg_catalog.pg_database d
  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid
ORDER BY 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

✨ 5.2 查询前几张大表（表和索引大小）

SELECT
t.table_catalog as db,
n.nspname AS schemaname,
c.relname,
c.reltuples::numeric as rowcount,
pg_size_pretty(pg_table_size ( '"' || nspname || '"."' || relname || '"' )) AS table_size,
pg_size_pretty(pg_indexes_size ( '"' || nspname || '"."' || relname || '"' )) AS indexes_size,
pg_size_pretty (pg_total_relation_size ( '"' || nspname || '"."' || relname || '"' )) AS total_size --,pg_relation_filepath(table_name) filepath
FROM pg_class C 
LEFT JOIN pg_namespace N ON ( N.oid = C.relnamespace ) 
left join information_schema.tables t on (n.nspname= t.table_schema and c.relname=t."table_name" )
WHERE
nspname NOT IN ( 'pg_catalog', 'information_schema' ) 
AND relkind in ('r','p')  
ORDER BY
reltuples DESC 
LIMIT 20;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17