PostgreSQL之troubleshooting系列之一

前言

最近，看到灿灿老师分享了一本电子书：《Troubleshooting PostgreSQL》。书是针对的相对比较老的版本PG9.4.2。虽看起来有些老旧，但是里边的内容相当一部分仍然很实用。这本书内容不复杂，DBA和应用开发人员都可以简单的过一遍。不由得想把其中的一些相对重要的内容整理出来。（有些内容随着版本升级，也就不需要了，可能直接节略掉），有些内容可能不够全面，顺便也做些修补，成为真正的troubleshooting。

本文就涉及书中的如下内容：

• 安装过程的troubleshooting

• 创建数据结构

• 处理索引

1、安装过程的注意项

1.1 内存问题

主要提到了9.2版本以前经常出现的共享内存段的问题。现在基本上碰不到了。跳过

1.2 初始化数据库加上checksum

建议加上checksum选项，为了让数据库实例中的数据更加健康。在这一点上，性能上的损耗几乎可以忽略。这是书中的观点。这个选项决定于initdb初始化数据库实例的行为。

  -k, --data-checksums      use data page checksums

[17:07:28-postgres@centos1:/var/lib/pgsql/14]$ initdb -D data2/ -k
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are enabled.

creating directory data2 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option 

随着硬件的发展，似乎这个选项用的不太多了。作者的提议也无坏处，如果觉得性能上的损耗可以忽略，那么加上也无可厚非。

1.3 预防字符集编码相关问题

默认可能是en_US.UTF-8，但是你仍然可能对各个数据库本身的字符集进行定制。可以用 \l列出所有的相关信息。

postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 mydb      | postgres | UTF8     | C       | C     | =Tc/postgres         +
           |          |          |         |       | postgres=CTc/postgres+
           |          |          |         |       | mydb=CTc/postgres
 pgpool    | pgpool   | UTF8     | C       | C     | 
 postgres  | postgres | UTF8     | C       | C     | 
 repmgr    | repmgr   | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres

也可以自定义创建不同的字符集的数据库，如：

自9.5开始，

postgres=# create database tmpdb_zh_cn with template template0 lc_collate 'zh_CN.UTF8' lc_ctype 'zh_CN.UTF8' is_template=true;
CREATE DATABASE

这里我们就单独弄了个中文UTF8的模板数据库。

postgres=# create database test template tmpdb_zh_cn;
CREATE DATABASE
postgres=# \l
                                  List of databases
    Name     |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-------------+----------+----------+------------+------------+-----------------------
 template0   | postgres | UTF8     | C          | C          | =c/postgres          +
             |          |          |            |            | postgres=CTc/postgres
 template1   | postgres | UTF8     | C          | C          | =c/postgres          +
             |          |          |            |            | postgres=CTc/postgres
 test        | postgres | UTF8     | zh_CN.UTF8 | zh_CN.UTF8 | 
 tmpdb_zh_cn | postgres | UTF8     | zh_CN.UTF8 | zh_CN.UTF8 | 
(8 rows)

如我们所愿了。

关于字符集编码，加点料，推荐阅读：http://v0.pigsty.cc/zh/blog/2018/07/01/%E7%90%86%E8%A7%A3%E5%AD%97%E7%AC%A6%E7%BC%96%E7%A0%81/

1.4 避免template污染

上边刚提到使用了模板，他就提到了污染。意味着，我们最好自己创建独立的模板。而不是直接修改template1, template0.

1.5 postmaster进程的kill

有些管理员更残忍一些，他们使用kill -9来终止PostgreSQL。一般来说，这有害无益，因为它会引起一些讨厌的副作用。为什么会这样?

PostgreSQL架构是这样工作的: 当你启动PostgreSQL时，你是启动一个叫做postmaster的程序。每当有新的连接进来，这个postmaster分叉并创建一个所谓的后端进程(BE)。这个过程是只处理一个连接。在工作系统中，您可能会看到数百个进程为数百个用户服务。重要的是所有这些进程都通过一些共享内存块进行同步(传统上是共享内存，最近的版本是映射内存)，它们都可以访问这一块。如果一个数据库PostgreSQL基础架构中的连接或任何其他进程都会被kill终止9 ? 修改此共享内存块的进程可能会在创建时死掉了。被杀的进程无法抵御攻击，谁能保证共享内存不会因中断而损坏? 这正是postmaster介入的时候。它确保其中一个后端进程意外死亡。为了防止潜在的腐败蔓延，它杀死所有其他数据库连接，进入恢复模式，并修复数据库实例。然后再次允许新的数据库连接。

强烈建议不要使用kill -9。正常的杀掉就可以了。

2、创建（正确的）数据结构

涉及两方面内容：

• 合理的方式对列进行组合

• 确定正确的数据类型

这一部分建议开发人员可以好好读一读。

2.1、合理的列顺序

test=# CREATE TABLE t_test ( i1 int,
 i2 int,
 i3 int,
 v1 varchar(100),
 v2 varchar(100),
 v3 varchar(100)
);
CREATE TABLE
test=# INSERT INTO t_test SELECT 10, 20, 30,
 'abcd', 'abcd', 'abcd'
 FROM generate_series(1, 10000000);
INSERT 0 10000000

三个VARCHAR列放到三个int列之后，查看表的大小：

test=# SELECT pg_size_pretty(pg_relation_size('t_test'));
 pg_size_pretty
----------------
 574 MB
(1 row)

如果调整一下列的顺序：

test=# CREATE TABLE t_test ( v1 varchar(100),
 i1 int,
 v2 varchar(100),
 i2 int,
 v3 varchar(100),
 i3 int
);
CREATE TABLE
test=# INSERT INTO t_test SELECT 'abcd', 10, 'abcd',
 20, 'abcd', 30
 FROM generate_series(1, 10000000);
INSERT 0 10000000
test=# SELECT pg_size_pretty(pg_relation_size('t_test'));
 pg_size_pretty
----------------
 651 MB
(1 row)

尽管表内的数据完全相同，但表已经增长了很多。这个问题的原因被称为对齐，并且可以在代码的深处找到。基本理论如下: 如果一个字段不是从CPU字大小的乘数开始，CPU就会遇到困难。因此，PostgreSQL将相应地对数据进行物理对齐。

这里最重要的一点是，将具有相似数据类型的列彼此相邻分组是有意义的。当然，结果和潜在的大小差异很大程度上取决于内容。如果在本例中使用“abc”而不是“abcd”，则结果不会显示任何差异;  两个版本的表的大小都固定为498 MB。

补充：灿灿老师的公众号里有专门一篇文章介绍列类型对齐原则的。越简单越短的类型尽量靠前放置，对于表的总体大小以及整体性能都是有改善的。

2.2、确定正确的数据类型

作者特别提到了“正规化”（normalize)。最后结论应该是不能完全拘泥于理论，那样在实际 的生产环境中，往往不能充分利用PG的自身优势。

2.2.1 、varchar 和 text

这两类型，有啥分别呢？

postgres=# create table t1(col1 varchar, col2 text);
CREATE TABLE
postgres=# \d t1
                      Table "public.t1"
 Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
 col1   | character varying |           |          | 
 col2   | text              |           |          | 

test=# CREATE TABLE t_product (
 prodnr int,
 name varchar(20),
 description varchar(4096)
);
CREATE TABLE

我们就看看上边的例子。VARCHAR不指定长度时，它的长度应该是不限的。(If specified, the length n
must be greater than zero and cannot exceed 10,485,760. If character varying
(or varchar
) is used without length specifier, the type accepts strings of any length.)

关于性能上的考虑，我直接帖文档的原图，就不译了。char(N)的性能最差的。可以避免使用了。

就上边的例子而言，在有把握的前提下，比如限制name的长度确实不超过20个字符，那么，使用起来就是合理的。在知道有效范围，确实使用指定VARCHAR(n)更有意义。如果description是变长，且长度可能跨度很大，这时不妨可以考虑使用最长可达1G的text。

再补充点：在做类型转换至varchar(N)时，超长部分会自动截短，并且不报任何错误。这个在开发过程中可以充分利用这个特性。

postgres=# select 1234567890::varchar(5);
 varchar 
---------
 12345
(1 row)

2.2.2、numeric与float类型

这个相信大多比较清楚两者区别与使用场合。前者保留可控的精度，后者容易损失精度 。因此，只要是对精度要求高的应用，尽量定义成numberic, decimal类型。

test=# SELECT '1000.1'::float - '1000' AS float;
 float 
-------------------
 0.100000000000023
(1 row

test=# SELECT '1000.1'::numeric - '1000';
 ?column?
----------
 0.1
(1 row)

2.2.3、boolean与bit类型

PG数据库的表可以定义多达1600个列。有些使用场景可能要用高达100个boolean列。这个真的好吗？这个时候，可以考虑使用bit(N)来处理。

test=# CREATE TABLE t_test (x bit(10));
CREATE TABLE
test=# INSERT INTO t_test VALUES ('0110000101');
INSERT 0 1
test=# SELECT * FROM t_test;
 x 
------------
 0110000101
(1 row)

这里每一位的0或者1就可以用于描述T或者F。一下子能节省很多列。

test=# SELECT get_bit(x, 5), set_bit(x, 5, 1), x::int FROM t_test;
 get_bit | set_bit | x 
---------+------------+-----
 0 | 0110010101 | 389
(1 row

想得到某一项的值也非常简单。

如果不确定要多长，可以使用VARBIT：

test=# ALTER TABLE t_test ADD COLUMN y varbit(20);
ALTER TABLE

2.2.4、text与cidr/inet/circle/point类型

许多人常犯的一个错误是选择泛型数据类型，而不是特定的优化数据类型。当然，一切都可以以某种方式存储为文本，但在大多数情况下，使用适当的数据类型更有意义。

这就是文中的观点，尽量使用优化后的数据类型，这才是PostgreSQL支持那么多数据类型的威力。

直接使用起来还是比较方便的，可以充分利用相关的函数调用。

test=# SELECT '192.168.0.34/32'::cidr, '192.168.0.34/32'::inet;
 cidr | inet 
-----------------+--------------
 192.168.0.34/32 | 192.168.0.34
(1 row)
test=# SELECT '::1'::cidr, '::1'::inet;
 cidr | inet
---------+------
 ::1/128 | ::1
(1 row)
test=# SELECT '::1'::cidr + 43242342;
 ?column? 
-----------------
 ::2.147.211.103
(1 row)

错误的例子，如：

test=# CREATE TABLE t_location (
 loc text,
 x numeric,
 y numeric
);
CREATE TABLE

从不熟悉PG的人的角度来讲，用上述的定义来描述位置信息，似乎也没问题，只是这样，你就不能充分利用PG提供的相关函数。PG中本身就支持一些简单的几何类型。

test=# CREATE TABLE t_test (loc text, coord point);
CREATE TABLE

如点类型：

test=# SELECT '(10, 30)'::point;
 point 
---------
 (10,30)
(1 row)

计算两点距离 ：

test=# SELECT '(10, 30)'::point <-> '(20, 20)'::point;
 ?column? 
-----------------
 14.142135623731
(1 row)

2.2.5 第7范式

正如在许多情况下提到的，规范化很容易被夸大。在这种情况下，性能将受到影响，事情将变得非常复杂。这里有一个正常化走得太远的例子: 几年前，当我(作者）与学生交谈时，一位当地大学的教授向我提出了一个有趣的例子。他告诉我，他用下面的例子向学生们解释，标准化很容易走得太远。他发明了第7范式(当然，这在专业数据库文献中并不存在)。

test=# CREATE TABLE t_test (name text);
CREATE TABLE
test=# INSERT INTO t_test VALUES ('sealevel');
INSERT 0 1

这里头，sealevel有冗余(天啊),  有些字母重复了两次。于是，处理成下边这个样子：

test=# CREATE TABLE t_char (id int, char char(1));
CREATE TABLE
test=# INSERT INTO t_char VALUES
 (1, 's'), (2, 'e'), (3, 'a'), (4, 'l'), (5, 'v');
INSERT 0 5

test=# CREATE TABLE t_word (order_id int, char_id int);
CREATE TABLE

sealevel可以弄成下边这样：

test=# INSERT INTO t_word VALUES
 (1, 1), (2, 2), (3, 3), (4, 4), (5, 2),
 (6, 5), (7, 2), (8, 4);
INSERT 0 8

两张表关联一下，得出：

test=# SELECT order_id, char
 FROM t_word AS a, t_char AS b
 WHERE a.char_id = b.id
 ORDER BY order_id;
 order_id | char
----------+------
 1 | s
 2 | e
 3 | a
 4 | l
 5 | e
 6 | v
 7 | e
 8 | l
(8 rows)

这一切的意义是什么?关键是您可以将数据规范化，使其无法识别。但这真的有意义吗? 在刚刚概述的例子中，每个人都会同意这显然是无用的，而且远远超出了合理的范围。因此，如果确实值得引入新的关系，即使没有机会对它们进行更改，也强烈建议这样做。

2.2.6 数组与规范化

为了避免过度规范化，您可以将注意力转向数组。数组是一种简单的，而且在很多情况下是减少关系数量的好方法，尤其是在不需要硬完整性的情况下。

数组和避免连接的主要好处是什么? 请记住，在使用连接时，必须从两个表中读取数据。这通常意味着额外的I/O，最重要的是，您必须连接数据，这会导致排序、散列、合并等等。

如果数据内联存储在数组中，则不需要从两个表中读取数据并进行连接、合并等操作。您可以享受顺序扫描的简单性或单索引查询所需数据的速度。

看一个简单的例子：

test=# CREATE TABLE t_country (
 id int,
 country_code char(2)
);
CREATE TABLE

添加些数据 ，如下：

test=# INSERT INTO t_country VALUES
 (1, 'DE'), (2, 'AT'), (3, 'BE'), (4, 'NL');
INSERT 0 4

有另一张表，需要用到国家的编码信息：

test=# CREATE TABLE t_travel (
 person text,
 country_id int); 
CREATE TABLE

如果一个人他去过多个国家，你完全可以这么定义：

test=# CREATE TABLE t_travel (
 person text,
 country_list char(2)[]
);
CREATE TABLE

一个国家的名字不太可能改变，即使改变了，你可能也不会太在意。假设有人去过苏联。即使在它解体后，你仍然不会改变你去过的国家的名字。因此，诚信在这里真的不是一个问题。

好处是，您可以节省所有可能由连接引起的费用。实际上，在大型系统中，随机I/O可能是一个主要的瓶颈，应该避免。通过将数据紧密地放在一起，您可以避免大量问题。

事实上，在生产系统中，过多数据上的过多连接可能是真正的麻烦来源，因为一旦服务器达到磁盘强加给您的随机I/O限制，性能就会急剧下降。