创建一个测试表
postgres=# create table t1(a int);
CREATE TABLE
postgres=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/75062/75297
(1 row)
postgres=#
在操作系统上已经可以看到该文件。
$ ls -la $PGDATA/base/75062/75297
-rw------- 1 postgres postgres 0 Nov 9 11:11 /data/pgdata/11/data/base/75062/75297
插入一些数据：
postgres=# show segment_size;
segment_size
--------------
1GB
(1 row)
postgres=# insert into t1 select * from generate_series(1,100000000);
INSERT 0 100000000
postgres=#
因为segment_size的设置为1GB，磁盘上已经有了多个文件
$ ls -la $PGDATA/base/75062/75297*
-rw------- 1 postgres postgres 1073741824 Nov 9 11:19 /data/pgdata/11/data/base/75062/75297
-rw------- 1 postgres postgres 1073741824 Nov 9 11:17 /data/pgdata/11/data/base/75062/75297.1
-rw------- 1 postgres postgres 1073741824 Nov 9 11:18 /data/pgdata/11/data/base/75062/75297.2
-rw------- 1 postgres postgres 439803904 Nov 9 11:19 /data/pgdata/11/data/base/75062/75297.3
-rw------- 1 postgres postgres 917504 Nov 9 11:18 /data/pgdata/11/data/base/75062/75297_fsm
现在，开启另一个会话(session 2)。
在session2中，启动一个事务并创建一个空表，但是不提交事务：
postgres=# begin;
BEGIN
postgres=# create table t2(a int);
CREATE TABLE
postgres=# select pg_relation_filepath('t2');
pg_relation_filepath
----------------------
base/75062/75300
(1 row)
postgres=# select * from pg_backend_pid();
pg_backend_pid
----------------
17710
(1 row)
postgres=#
在操作系统已经可以看到对应的文件：
$ ls -la $PGDATA/base/75062/75300
-rw------- 1 postgres postgres 0 Nov 9 11:23 /data/pgdata/11/data/base/75062/75300
如果这个时候，posrgresql server发生了奔溃、或者发生了oom被kill了或者session被kill了。会发生什么呢？
我们来模拟一下session被kill的场景：
再次在session2中执行查询：
postgres=# select 1;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=#
这个session在事务提交之前被kill了，事务无法正常完成，但是事务已经创建了一个表。应该发生什么呢？事务被回滚，创建的表应该不存在了。
postgres=# select * from t2;
ERROR: relation "t2" does not exist
LINE 1: select * from t2;
^
postgres=#
这正是我们所预期的。但在操作系统上，文件仍然存在：
$ ls -la $PGDATA/base/75062/75300
-rw------- 1 postgres postgres 0 Nov 9 11:23 /data/pgdata/11/data/base/75062/75300
这样，文件就成了孤儿文件(orphaned file)。
postgresql并不知道这个文件属于哪个relation
postgres=# select relname from pg_class where oid = '75300';
relname
---------
(0 rows)
postgres=#
这样，你就需要自己手动清理孤儿文件了！
假设你做了大量的数据的加载，就在加载完成之前，会话被杀死:
postgres=# begin;
BEGIN
postgres=# create table t3(a int);
CREATE TABLE
postgres=# select pg_relation_filepath('t3');
pg_relation_filepath
----------------------
base/75062/99528
(1 row)
postgres=# select * from pg_backend_pid();
pg_backend_pid
----------------
21988
(1 row)
postgres=# insert into t3 select * from generate_series(1,1000000000);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
虽然会话被kill了。但是磁盘上的空间并没有被释放。
$ ls -la $PGDATA/base/75062/99528*
-rw------- 1 postgres postgres 1073741824 Nov 9 11:51 /data/pgdata/11/data/base/75062/99528
-rw------- 1 postgres postgres 413777920 Nov 9 11:51 /data/pgdata/11/data/base/75062/99528.1
-rw------- 1 postgres postgres 385024 Nov 9 11:51 /data/pgdata/11/data/base/75062/99528_fsm
在最糟糕的时候，可能会占用大量的磁盘空间。那是否有什么方法去检测这些孤儿文件呢？
你需要比较postgresql中的目录表中的记录和文件系统上信息，然后删除这些孤儿文件。这个过程需要小心谨慎。
首先获得你要检测的数据库的oid：
postgres=# select oid from pg_database where datname = 'postgres';
oid
-------
75062
(1 row)
postgres=#
这样就可以知道文件在文件系统上的位置。即 $PGDATA/base/[OID_OF_THE_DATABASE]
然后，获得孤儿文件：
postgres=# select * from pg_ls_dir ( '/data/pgdata/11/data/base/75062' ) as file where file ~ '^[0-9]*$' and file::text not in (select oid::text from pg_class );
file
-------
75280
75281
75282
75283
75300
83144
99528
(7 rows)
postgres=#
补充：理解postgreSQL中的prepared transactions和处理孤儿(orphans)事务
Prepared transactions是PostgreSQL的一个关键特性。理解该特性提供的功能和处理任何潜在的陷阱对于系统的维护是很关键的。所以，我们来深入研究一下具体什么是prepared transactions。
关于事务
在数据库系统中，事务是一种处理通常包含多个语句的块中的全部或零个语句的方法。在提交整个块之前，该块中语句的结果对其他事务不可见。 如果事务失败或回滚，则对数据库完全没有影响。
事务依附于会话。但是，当要执行与会话独立的事务时（也有其他好处）。这就是“prepared transactions”的来源。
prepared transactions
prepared transaction是独立于会话、抗崩溃、状态维护的事务。事务的状态存储在磁盘上，这使得数据库服务器即使在从崩溃中重新启动后也可以恢复事务。在对prepared transaction执行回滚或提交操作之前，将一直维护该事务。
PostgreSQL文档声明，在一个已存在的事务块中，可以使用prepare transaction 'transaction_id‘命令创建一个prepared transaction。它进一步声明该过程为两阶段提交准备了一个事务。
此外，建议应用程序或交互式会话不要使用prepared transaction。理想情况下，外部事务管理器应该跨同构或异构数据库资源执行原子的全局事务。
在postgreSQL中，缺省的max_prepared_transaction=0；即关闭了prepared transaction。如果你想使用prepared transaction，建议将max_prepared_transaction设置成max_connections的值。在同步的流复制standby库上，最好将其设置的比max_connections大一点，以免standby不能接收查询。
在任何给定的时间，你可以查看活跃状态的prepared transactions，通过查看视图pg_prepared_xacts。
pg_prepared_xacts视图含有以下一些列：
#select * from pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)
1.transaction：事务id
2.gid：用户为prepared transaction定义的名称
3.prepared：prepared日期，创建事务时带有时区的时间戳
4.owner：创建该prepared transaction的事务
5.database：数据库名
创建prepared transaction
知道什么是prepared transaction之后，现在来看看如何创建一个prepared transaction。创建一个该事务通常需要四个步骤：
1.begin(或start transaction)
2.执行需要的操作
3.prepare transaction
4.commit(或rollback prepared)
prepare transaction、commit prepared、或rollback prepared后面加上一个gid，可以唯一标识prepared transaction。
例如下面的代码块：
postgres=# begin;
BEGIN
postgres=# create table abce(id int);
CREATE TABLE
postgres=# insert into abce values(1);
INSERT 0 1
postgres=# prepare transaction 'abce_insert';
PREPARE TRANSACTION
postgres=# select * from pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+-------------+-------------------------------+----------+----------
16362 | abce_insert | 2020-12-09 11:41:45.742375+08 | postgres | postgres
(1 row)
postgres=# commit prepared 'abce_insert';
COMMIT PREPARED
postgres=# select * from pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)
postgres=#
当一个含有一个或多个活跃的prepared transactions的postgresql停止了或者奔溃了，会为每个活跃的prepared transaction创建一个文件，在目录pg_twophase中。
比如，我们有个prepared transaction：
postgres=# select * from pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+--------------+-------------------------------+----------+----------
16363 | abce_insert2 | 2020-12-09 11:46:01.983483+08 | postgres | postgres
(1 row)
postgres=#
所以我没有提交事务就停止了postgresql server。postgresql就会创建一个名为00003FEB的文件，对应于prepared transaction的事务id。
$ ls -l ../data/pg_twophase/
total 4
-rw------- 1 postgres postgres 220 Dec 9 11:47 00003FEB
00003FEB等价于16363。在postgresql被重启后，在启动日志会报如下信息：
2020-12-09 11:51:28.112 CST [963] LOG: database system was shut down at 2020-12-09 11:47:39 CST
2020-12-09 11:51:28.113 CST [963] LOG: recovering prepared transaction 16363 from shared memory
2020-12-09 11:51:28.132 CST [960] LOG: database system is ready to accept connections
如果你不希望恢复一个prepared transaction，可以简单地删除pg_twophase文件夹下的相应文件。
这很简单，不是吗?那么我们为什么不经常地使用它呢?毕竟，它提供了更高的提交操作成功的可能性。事情要是这么简单就好了!
prepared transaction可能遇到哪些错误？
如果客户端消失了，则prepared transaction可以未完成(既不提交也不回滚)。发生这种情况的原因多种多样，包括客户机崩溃，或者服务器崩溃导致客户机连接被终止而无法重新连接。你实际上是依靠事务管理器来确保没有孤立的prepared transaction。
除了崩溃之外，还有另一种原因可以使prepared transaction未完成。如果一个用于恢复的备份包含了事务的prepared阶段，但是没有包含关闭事务的阶段，仍然会生成孤儿事务。
或者，DBA创建了一个prepared transaction，却忘记了关闭它。
所以，如果一个prepared transaction没有完成，又会有什么大不了的呢?
真正的问题
真正的问题是，孤儿prepared transaction继续持有可能包含锁的关键系统资源，或者使事务ID保持活动状态，该事务ID可能会阻止vacuum清除只对该孤儿事务可见、对其它事务不可见的死的元组。
回想一下我在上面创建的prepared 事务。当事务prepared，并且在提交该事务之前，如果另一个事务试图更改该表，它将无法获取所需的锁并挂起，直到解决了prepared事务（提交或回滚）为止。 否则，alter命令会无限期挂起，最终，我必须发出CTRL + C来停止该命令。
postgres=# select * from pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+--------------+-------------------------------+----------+----------
16363 | abce_insert2 | 2020-12-09 11:46:01.983483+08 | postgres | postgres
(1 row)
postgres=# alter table abce add column b int;
^CCancel request sent
ERROR: canceling statement due to user request
postgres=# select c.oid,c.relname,l.locktype,l.relation,l.mode
postgres-# from pg_class c
postgres-# inner join pg_locks l on c.oid=l.relation
postgres-# where c.relname='abce';
oid | relname | locktype | relation | mode
--------+---------+----------+----------+------------------
370883 | abce | relation | 370883 | RowExclusiveLock
(1 row)
postgres=#
对vacuum的阻塞可能会更严重，在极端情况下，会导致数据库关闭，因为孤儿prepared事务会阻止事务id的wrap around。
发现和通知
虽然一般的预期是prepared事务在几秒钟内完成，但是情况并不总是这样。一个prepared事务可能持续几分钟、几小时甚至几天。
为这些事务维护元数据本身可能是一项挑战。但是，我建议设置一个术语来定义prepared事务可以存在的最大时间。例如，考虑以下的prepared事务:
postgres=# BEGIN;
BEGIN
postgres=# INSERT INTO abce VALUES(3);
INSERT 0 1
postgres=# PREPARE TRANSACTION 'abce_insert 1m';
PREPARE TRANSACTION
或者下面的事务：
postgres=# BEGIN;
BEGIN
postgres=# INSERT INTO abce VALUES(4);
INSERT 0 1
postgres=# PREPARE TRANSACTION 'abce_insert 1d';
PREPARE TRANSACTION
在这些事务名称中，最后一部分定义事务的时间。任何超出时间的事务可以通过sql查询轻易地找出来：
postgres=# select gid,prepared,regexp_replace(gid, '.* ', '') AS age
from pg_prepared_xacts
WHERE prepared + CAST(regexp_replace(gid, '.* ', '') AS INTERVAL) NOW();
gid | prepared | age
----------------+-------------------------------+-----
abce_insert 1m | 2020-12-09 13:39:01.383091+08 | 1m
(1 row)
postgres=#
这里就很清晰地显示了一个不应该再有效的事务。因此，使用一个外部代理或者cron任务可以轻易找出这些事务，或者通知管理员、或者回滚事务。
在我看来，这是一种简单而容易的方式，可以确保即使事务管理器失败或DBA意外地留下了一个事务，也可以在你的环境中管理孤儿事务。
结论
Prepared transactions显然是一个非常重要的功能，但是需要使用回退通知程序或清理程序仔细设置环境，以轻松确保这些事务不会不必要地占用关键资源，并且系统保持良好状态。
PostgreSQL社区中仍在讨论如何处理孤儿prepared事务。它是否成为postgresql核心的一部分尚待观察。同时，我们需要使用外部工具来管理这些事务，或者设法解决这个问题。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。如有错误或未考虑完全的地方，望不吝赐教。
您可能感兴趣的文章:- PostgreSQL 对IN,EXISTS,ANY/ALL,JOIN的sql优化方案
- postgresql 13.1 insert into select并行查询的实现
- PostgreSQL 启动失败的解决方案
- 解决postgresql无法远程访问的情况
- 浅谈Postgresql默认端口5432你所不知道的一点
- postgresql修改完端口后直接psql连接数据库报错的解决
- postgresql 实现启动、状态查看、关闭
- postgresql连续归档及时间点恢复的操作
