postgresql – 第2页

PostgreSQL PANIC: could not locate a valid checkpoint record

2018/11/19 by admin Leave a Comment

2019-11-19 06:37:09.459 UTC [2705] FATAL:  terminating autovacuum process due to administrator command
2019-11-19 06:38:35.142 UTC [2791] LOG:  listening on IPv6 address "::1", port 5432
2019-11-19 06:38:35.142 UTC [2791] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-11-19 06:38:35.143 UTC [2791] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-11-19 06:38:35.162 UTC [2792] LOG:  database system was interrupted; last known up at 2019-11-19 06:36:47 UTC
2019-11-19 06:38:35.162 UTC [2792] LOG:  creating missing WAL directory "pg_wal/archive_status"
2019-11-19 06:38:35.209 UTC [2792] LOG:  invalid primary checkpoint record
2019-11-19 06:38:35.210 UTC [2792] LOG:  invalid secondary checkpoint record
2019-11-19 06:38:35.210 UTC [2792] PANIC:  could not locate a valid checkpoint record
2019-11-19 06:38:37.204 UTC [2791] LOG:  startup process (PID 2792) was terminated by signal 6: Aborted
2019-11-19 06:38:37.206 UTC [2791] LOG:  aborting startup due to startup process failure
2019-11-19 06:38:37.222 UTC [2791] LOG:  database system is shut down
pg_ctl: could not start server
Examine the log output.


could not read block N of relation X/Y/Z: read only 0 of 8192 bytes
catalog is missing N attribute(s) for relid M
WARNING: page verification failed, calculated checksum %u but expected %u
ERROR: invalid page in block %u of relation %s

Filed Under: postgresql

PostgreSQL pg_resetwal命令介绍

2018/11/19 by admin Leave a Comment

pg_resetwal就是之前的pg_resetxlog

pg_resetwal — 重置一个PostgreSQL数据库集簇的预写式日志以及其他控制信息

大纲

pg_resetwal [-f] [-n] [option…] {[-D] datadir}

描述

pg_resetwal会清除预写式日志（WAL）并且有选择地重置存储在 pg_control文件中的一些其他控制信息。如果这些文件已经被损坏，某些时候就需要这个功能。当服务器由于这样的损坏而无法启动时，这只应该被用作最后的手段。

在运行这个命令之后，就应该可以启动服务器，但是记住数据库可能包含由于部分提交事务产生的不一致数据。你应当立刻转储你的数据、运行initdb并且重新载入。重新载入后，检查不一致并且根据需要修复之。

这个工具只能被安装服务器的用户运行，因为它要求对数据目录的读写访问。出于安全原因，你必须在命令行中指定数据目录。pg_resetwal 不使用环境变量PGDATA。

如果pg_resetwal抱怨它无法为pg_control 决定合法数据，你可以通过指定-f（强制）选项强制它继续。在这种情况下，丢失的数据将被替换为看似合理的值。可以期望大部分域是匹配的，但是下一个 OID、下一个事务 ID 和纪元、下一个多事务 ID 和偏移以及 WAL 开始地址域可能还是需要人工协助。这些域可以使用下面讨论的选项设置。如果你不能为所有这些域决定正确的值，-f还是可以被使用，但是恢复的数据库还是值得怀疑：一次立即的转储和重新载入是势在必行的。在你转储之前不要在该数据库中执行任何数据修改操作，因为任何这样的动作都可能使破坏更严重。

选项

-f: 即使pg_resetwal无法从pg_control 中确定有效的数据（如前面所解释的），也强迫pg_resetwal 继续运行。
-n: -n（无操作）选项指示pg_resetwal打印从 pg_control重构出来的值以及要被改变的值，然后不修改任何东西退出。这主要是一个调试工具，但是可以用来在允许pg_resetwal 真正执行下去之前进行完整性检查。
-V --version: 显示版本信息然后退出。
-? --help: 显示帮助然后退出。

只有当pg_resetwal无法通过读取pg_control 确定合适的值时，才需要下列选项。安全值可以按下文所述来确定。对于接收数字参数的值，可以使用前缀0x指定 16 进制值。

-c xid,xid

手工设置提交时间可以检索到的最老的和最新的事务 ID。

能检索到提交时间的最老事务 ID 的安全值（第一部分）可以通过在数据目录下 pg_commit_ts目录中数字上最小的文件名来决定。反过来，能检索到提交时间的最新事务 ID 的安全值（第二部分）可以通过同一个目录中数字上最大的文件名来决定。文件名都是十六进制的。

-e xid_epoch

手工设置下一个事务 ID 的 epoch。

事务 ID 的 epoch 实际上并没有存储在数据库中的任何地方，除了被pg_resetwal设置在这个域中，所以只要关心的是数据库本身，任何值都可以用。你可能需要调整这个值来确保诸如Slony-I和Skytools 之类的复制系统正确地工作 — 如果确实需要调整，应该可以从下游的复制数据库的状态中获得一个合适的值。

-l walfile

手工设置 WAL 开始地址。

WAL 起始地址应该比当前存在于数据目录下pg_wal 目录中的任意 WAL 段文件名更大。这些名称也是十六进制的并且有三个部分。第一部分是“时间线 ID”并且通常应该被保持相同。例如，如果00000001000000320000004A是pg_wal中最大的项，则使用-l 00000001000000320000004B或更高的值。

注意

pg_resetwal本身查看pg_wal 中的文件并选择一个超出最新现存文件名的默认-l设置。因此，只有当你知道 WAL 段文件当前不在pg_wal中时，或者当pg_wal的内容完全丢失时，才需要对-l 的手工调整，例如一个离线归档中的项。

-m mxid,mxid

手工设置下一个和最老的多事务 ID。

确定下一个多事务 ID（第一部分）的安全值的方法：在数据目录下的 pg_multixact/offsets目录中查找最大的数字文件名，然后在它的基础上加一并且乘以 65536 (0x10000)。反过来，确定最老的多事务 ID（-m的第二部分）的方法：在同一个目录中查找最小的数字文件名并且乘以 65536。文件名是十六进制的数字，因此实现上述方法最简单的方式是以十六进制指定选项值并且追加四个零。

-o oid

手工设置下一个 OID。

没有相对容易的方法来决定超过数据库中最大 OID 的下一个 OID。但幸运的是正确地得到下一个 OID 设置并不是决定性的。

-O mxoff

手工设置下一个多事务偏移量。

确定安全值的方法：查找数据目录下pg_multixact/members 目录中最大的数字文件名，然后在它的基础上加一并且乘以 52352 (0xCC80)。文件名是十六进制数字。没有像其他选项那样追加零的简单方法。

-x xid

手工设置下一个事务 ID。

确定安全值的方法：在数据目录下的pg_xact目录中查找最大的数字文件名，然后在它的基础上加一并且乘以 1048576 (0x100000)。注意文件名是十六进制的数字。通常以十六进制的形式指定该选项值也是最容易的。例如，如果0011 是pg_xact中的最大项，-x 0x1200000就可以（五个尾部的零就表示了前面说的乘数）。

注解

这个命令不能在服务器正在运行时被使用。如果在数据目录中发现一个服务器锁文件， pg_resetwal将拒绝启动。如果服务器崩溃那么一个锁文件可能会被留下，在那种情况下你能移除该锁文件来让pg_resetwal运行。但是在你那样做之前，再次确认没有服务器进程仍然存活。

pg_resetwal仅适用于相同主要版本的服务器。

Filed Under: postgresql

PostgreSQL pg_resetxlog整理及测试

2018/11/19 by admin Leave a Comment

文章来源：https://yq.aliyun.com/articles/55691

pg_resetxlog说明

pg_resetxlog，用来重置/清空一个数据库集群的预写日志以及其它控制内容，其中控制内容由命令pg_controldata可以查看，而内容的来源则是位于$PGDATA/global目录下名为pg_control的控制文件

可选参数有：

yunbodeMacBook-Pro:~ postgres$ pg_resetxlog –help
pg_resetxlog resets the PostgreSQL transaction log.

Usage:
pg_resetxlog [OPTION]… DATADIR

Options:
-e XIDEPOCH set next transaction ID epoch
-f force update to be done
-l XLOGFILE force minimum WAL starting location for new transaction log
-m MXID,MXID set next and oldest multitransaction ID
-n no update, just show what would be done (for testing)
-o OID set next OID
-O OFFSET set next multitransaction offset
-V, –version output version information, then exit
-x XID set next transaction ID
-?, –help show this help, then exit

具体含义：

-l timelineid,fileid,seg

为新的事务日志指定最小的WAL起始位置，应该比当前存在于pg_xlog中任何一个WAL日志文件名都要大。
名字以十六进制表示且分为三个部分，第一部分是时间线，一般保持该部分值不变。第三部分值不能超过255，即0xFF，如果是该值，则将第二部分加1，第三部分变为0。举例来说，如果00000001000000320000004A是最大的文件名，则-l后跟的参数为0x1,0x32,0x4B；但是，如果最大的文件名为000000010000003A000000FF，则选择-l 0x1,0x3B,0x0
The WAL starting address (-l) should be larger than any WAL segment file name currently existing in the
directory pg_xlog under the data directory. These names are also in hexadecimal and have three parts. The
first part is the “timeline ID” and should usually be kept the same. Do not choose a value larger than 255
(0xFF) for the third part; instead increment the second part and reset the third part to 0. For example, if
00000001000000320000004A is the largest entry in pg_xlog, -l 0x1,0x32,0x4B will work; but if the largest
entry is 000000010000003A000000FF, choose -l 0x1,0x3B,0x0 or more.

Note

pg_resetxlog itself looks at the files in pg_xlog and chooses a default -l setting beyond the last
existing file name. Therefore, manual adjustment of -l should only be needed if you are aware of WAL
segment files that are not currently present in pg_xlog, such as entries in an offline archive; or if
the contents of pg_xlog have been lost entirely.

-e XIDEPOCH

关于transaction ID epoch的信息并不会存储在数据库的任何地方，除非要在pg_resetxlog中指明。所以，可以赋予任意值，但对于复制系统，例如Slony-I，则可以从从节点中获取，以使复制能够正常工作。
The transaction ID epoch is not actually stored anywhere in the database except in the field that is set by
pg_resetxlog, so any value will work so far as the database itself is concerned. You might need to adjust
this value to ensure that replication systems such as Slony-I work correctly – if so, an appropriate value
should be obtainable from the state of the downstream replicated database.

-x XID

一个安全的设置值应该是由pg_clog下最大的文件名，然后加1，再乘以1048576得出。注意，文件名和参数都应是十六进制的格式。例如0011是最大的文件名，则-x后应该跟上0x1200000，即-x 0x1200000

A safe value for the next transaction ID (-x) can be determined by looking for the numerically largest file
name in the directory pg_clog under the data directory, adding one, and then multiplying by 1048576. Note
that the file names are in hexadecimal. It is usually easiest to specify the option value in hexadecimal

For example, if 0011 is the largest entry in pg_clog, -x 0x1200000 will work (five trailing zeroes
the proper multiplier).

-m XID

一个安全的设置值应该是由pg_multixact/offsets下最大的文件名，然后加1，再乘以65536得出。文件名和参数都应是十六进制的格式。如果pg_multixact/offsets目录下不存在任何数据，

则得出-m 0x10000

A safe value for the next multitransaction ID (-m) can be determined by looking for the numerically largest
file name in the directory pg_multixact/offsets under the data directory, adding one, and then multiplying
by 65536. As above, the file names are in hexadecimal, so the easiest way to do this is to specify the
option value in hexadecimal and add four zeroes.

-O OFFSET

一个安全的设置值应该是由pg_multixact/members下最大的文件名，然后加1，再乘以65536得出。文件名和参数都应是十六进制的格式。如果pg_multixact/members-m 0x10000

A safe value for the next multitransaction offset (-O) can be determined by looking for the numerically
largest file name in the directory pg_multixact/members under the data directory, adding one, and then
multiplying by 65536. As above, the file names are in hexadecimal, so the easiest way to do this is to
specify the option value in hexadecimal and add four zeroes.

-o OID

一般情况下，很难获得该值，但该值的正确性与否并不是十分的总要。

There is no comparably easy way to determine a next OID that’s beyond the largest one in the database, but
fortunately it is not critical to get the next-OID setting right.

-n

不做更新，只是显示抽取出来的信息，做测试用。
no update, just show extracted control values (for testing)

-f
force
强制重置WAL日志和控制信息

pg_resetxlog使用
模拟在执行大的插入数据的动作的同时，使用kill -9终止postmaster和连接进程，同时清空pg_xlog目录下的所有文件。
试验前，先查看pg_control中的内容信息：
pg_control version number: 942
Catalog version number: 201409291
Database system identifier: 6264417882840803310
Database cluster state: in production
pg_control last modified: Wed May 18 14:58:31 2016
Latest checkpoint location: 2/4EBD3538
Prior checkpoint location: 2/4BB504A8
Latest checkpoint’s REDO location: 2/4D3C6D30
Latest checkpoint’s REDO WAL file: 00000001000000020000004D
Latest checkpoint’s TimeLineID: 1
Latest checkpoint’s PrevTimeLineID: 1
Latest checkpoint’s full_page_writes: on
Latest checkpoint’s NextXID: 0/676247
Latest checkpoint’s NextOID: 68691
Latest checkpoint’s NextMultiXactId: 1
Latest checkpoint’s NextMultiOffset: 0
Latest checkpoint’s oldestXID: 991
Latest checkpoint’s oldestXID’s DB: 1
Latest checkpoint’s oldestActiveXID: 0
Latest checkpoint’s oldestMultiXid: 1
Latest checkpoint’s oldestMulti’s DB: 1
Time of latest checkpoint: Wed May 18 14:58:30 2016
Fake LSN counter for unlogged rels: 0/1
Minimum recovery ending location: 0/0
Min recovery ending loc’s timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
Current wal_level setting: minimal
Current wal_log_hints setting: off
Current max_connections setting: 100
Current max_worker_processes setting: 8
Current max_prepared_xacts setting: 0
Current max_locks_per_xact setting: 64
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0

窗口A：

test=# create table qt (id integer,vname text);
test=# insert into qt select generate_series(1,10000000), generate_series(1,10000000)||’insert_test’;

该命令会执行一段时间，在插入操作执行的同时，在窗口B执行：

yunbodeMacBook-Pro:pg_xlog postgres$ ps -ef|grep postgres
502 26703 26702 0 2:48PM ?? 0:00.00 postgres: logger process
502 26705 26702 0 2:48PM ?? 0:00.37 postgres: checkpointer process
502 26706 26702 0 2:48PM ?? 0:00.04 postgres: writer process
502 26707 26702 0 2:48PM ?? 0:00.01 postgres: wal writer process
502 26708 26702 0 2:48PM ?? 0:00.01 postgres: autovacuum launcher process
502 26709 26702 0 2:48PM ?? 0:00.08 postgres: stats collector process
502 26717 26702 0 2:49PM ?? 0:00.46 postgres: postgres test [local] idle

0 21731 21334 0 Mon05PM ttys002 0:00.02 sudo su – postgres
0 21732 21731 0 Mon05PM ttys002 0:00.01 su – postgres
502 26716 21733 0 2:49PM ttys002 0:00.01 psql -U postgres -d test

0 24267 24253 0 5:55PM ttys004 0:00.02 sudo su – postgres
0 24268 24267 0 5:55PM ttys004 0:00.01 su – postgres
502 26702 1 0 2:48PM ttys004 0:00.05 /Library/PostgreSQL/9.4/bin/postgres -D /Library/PostgreSQL/9.4/data
502 26746 24269 0 2:58PM ttys004 0:00.00 grep postgres
yunbodeMacBook-Pro:pg_xlog postgres$ kill -9 26702 26717

此时在窗口A会出现如下错误：

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.

回到窗口B：
yunbodeMacBook-Pro:pg_xlog postgres$ ll
total 294912
-rw——- 1 postgres daemon 16777216 May 18 14:58 00000001000000020000004F
-rw——- 1 postgres daemon 16777216 May 18 14:58 000000010000000200000050
-rw——- 1 postgres daemon 16777216 May 18 14:58 00000001000000020000004D
-rw——- 1 postgres daemon 16777216 May 18 14:58 00000001000000020000004E
-rw——- 1 postgres daemon 16777216 May 18 14:58 00000001000000020000004B
-rw——- 1 postgres daemon 16777216 May 18 14:58 00000001000000020000004C
-rw——- 1 postgres daemon 16777216 May 18 14:58 00000001000000020000004A
-rw——- 1 postgres daemon 16777216 May 18 14:58 000000010000000200000052
-rw——- 1 postgres daemon 16777216 May 18 14:58 000000010000000200000051
drwx—— 2 postgres daemon 68 May 17 17:58 archive_status

yunbodeMacBook-Pro:pg_xlog postgres$ mv 0000000100000002000000* /tmp
yunbodeMacBook-Pro:pg_xlog postgres$ ll
total 0
drwx—— 2 postgres daemon 68 May 17 17:58 archive_status

xlog已经被清空，此时尝试重启服务：

yunbodeMacBook-Pro:pg_xlog postgres$ pg_ctl start -D /Library/PostgreSQL/9.4/data/
pg_ctl: another server might be running; trying to start server anyway
server starting
yunbodeMacBook-Pro:pg_xlog postgres$ % FATAL: pre-existing shared memory block (key 5432001, ID 3473409) is still in use
% HINT: If you’re sure there are no old server processes still running, remove the shared memory block or just delete the file “postmaster.pid”.

因为非正常关闭PostgreSQL，所以进程文件没有被清理，手工清理pid文件，再重新尝试启动：

yunbodeMacBook-Pro:pg_xlog postgres$ rm ../postmaster.pid
yunbodeMacBook-Pro:pg_xlog postgres$ pg_ctl start -D /Library/PostgreSQL/9.4/data/
server starting
yunbodeMacBook-Pro:pg_xlog postgres$ % LOG: redirecting log output to logging collector process
% HINT: Future log output will appear in directory “pg_log”.

查看日志文件，发现如下报错：

yunbodeMacBook-Pro:pg_xlog postgres$ cat ../pg_log/postgresql-2016-05-18_145935.csv
2016-05-18 14:59:35.491 CST,,,26761,,573c12d7.6889,1,,2016-05-18 14:59:35 CST,,0,LOG,00000,”ending log output to stderr”,,”Future log output will go to log destination “”csvlog””.”,,,,,,,””
2016-05-18 14:59:35.493 CST,,,26763,,573c12d7.688b,1,,2016-05-18 14:59:35 CST,,0,LOG,00000,”database system was interrupted; last known up at 2016-05-18 14:58:31 CST”,,,,,,,,,””
2016-05-18 14:59:35.509 CST,,,26763,,573c12d7.688b,2,,2016-05-18 14:59:35 CST,,0,LOG,00000,”invalid primary checkpoint record”,,,,,,,,,””
2016-05-18 14:59:35.509 CST,,,26763,,573c12d7.688b,3,,2016-05-18 14:59:35 CST,,0,LOG,00000,”invalid secondary checkpoint record”,,,,,,,,,””
2016-05-18 14:59:35.509 CST,,,26763,,573c12d7.688b,4,,2016-05-18 14:59:35 CST,,0,PANIC,XX000,”could not locate a valid checkpoint record”,,,,,,,,,””
2016-05-18 14:59:37.282 CST,,,26761,,573c12d7.6889,2,,2016-05-18 14:59:35 CST,,0,LOG,00000,”startup process (PID 26763) was terminated by signal 6: Abort trap”,,,,,,,,,””
2016-05-18 14:59:37.282 CST,,,26761,,573c12d7.6889,3,,2016-05-18 14:59:35 CST,,0,LOG,00000,”aborting startup due to startup process failure”,,,,,,,,,””

此时没有办法定位到有效的检查点，而且WAL日志完全丢失，且没有任何归档，此时只能通过重置xlog的方式来恢复数据库：

yunbodeMacBook-Pro:pg_xlog postgres$ pg_resetxlog /Library/PostgreSQL/9.4/data/
The database server was not shut down cleanly.
Resetting the transaction log might cause data to be lost.
If you want to proceed anyway, use -f to force reset.
yunbodeMacBook-Pro:pg_xlog postgres$ pg_resetxlog -f /Library/PostgreSQL/9.4/data/
Transaction log reset

接下来重新尝试启动数据库：

yunbodeMacBook-Pro:pg_xlog postgres$ pg_ctl start -D /Library/PostgreSQL/9.4/data/
server starting
yunbodeMacBook-Pro:pg_xlog postgres$ % LOG: redirecting log output to logging collector process
% HINT: Future log output will appear in directory “pg_log”.

查看日志，确认是否已经启动成功：

yunbodeMacBook-Pro:pg_xlog postgres$ cat ../pg_log/postgresql-2016-05-18_150116.csv
2016-05-18 15:01:16.588 CST,,,26777,,573c133c.6899,1,,2016-05-18 15:01:16 CST,,0,LOG,00000,”ending log output to stderr”,,”Future log output will go to log destination “”csvlog””.”,,,,,,,””
2016-05-18 15:01:16.589 CST,,,26779,,573c133c.689b,1,,2016-05-18 15:01:16 CST,,0,LOG,00000,”database system was shut down at 2016-05-18 15:00:26 CST”,,,,,,,,,””
2016-05-18 15:01:16.590 CST,,,26779,,573c133c.689b,2,,2016-05-18 15:01:16 CST,,0,LOG,00000,”MultiXact member wraparound protections are now enabled”,,,,,,,,,””
2016-05-18 15:01:16.592 CST,,,26777,,573c133c.6899,2,,2016-05-18 15:01:16 CST,,0,LOG,00000,”database system is ready to accept connections”,,,,,,,,,””
2016-05-18 15:01:16.592 CST,,,26783,,573c133c.689f,1,,2016-05-18 15:01:16 CST,,0,LOG,00000,”autovacuum launcher started”,,,,,,,,,””

数据库已经启动成功，可以对比前后pg_controldata输出：

pg_control version number: 942
Catalog version number: 201409291
Database system identifier: 6264417882840803310
Database cluster state: in production
pg_control last modified: Wed May 18 15:01:16 2016
Latest checkpoint location: 2/4E000028
Prior checkpoint location: 0/0
Latest checkpoint’s REDO location: 2/4E000028
Latest checkpoint’s REDO WAL file: 00000001000000020000004E
Latest checkpoint’s TimeLineID: 1
Latest checkpoint’s PrevTimeLineID: 1
Latest checkpoint’s full_page_writes: on
Latest checkpoint’s NextXID: 0/676247
Latest checkpoint’s NextOID: 68691
Latest checkpoint’s NextMultiXactId: 1
Latest checkpoint’s NextMultiOffset: 0
Latest checkpoint’s oldestXID: 991
Latest checkpoint’s oldestXID’s DB: 1
Latest checkpoint’s oldestActiveXID: 0
Latest checkpoint’s oldestMultiXid: 1
Latest checkpoint’s oldestMulti’s DB: 1
Time of latest checkpoint: Wed May 18 15:00:26 2016
Fake LSN counter for unlogged rels: 0/1
Minimum recovery ending location: 0/0
Min recovery ending loc’s timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
Current wal_level setting: minimal
Current wal_log_hints setting: off
Current max_connections setting: 100
Current max_worker_processes setting: 8
Current max_prepared_xacts setting: 0
Current max_locks_per_xact setting: 64
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0

判断数据丢失量

根据pg_controldata输出结果中Latest checkpoint location的输出，可以查看重置前后，WAL位置的变化，通过函数pg_xlog_location_diff()可以确认数据的丢失情况。
查看两个事务日志位置之间相差字节数：

select pg_xlog_location_diff(‘1/911974A8′,’1/91197440’);
pg_xlog_location_diff
———————–
104

对于正在运行对PG实例，查看当前正在写入的xlog文件名：

select pg_xlogfile_name(pg_current_xlog_location()); –pg_current_xlog_location()函数显示当前事务日志的写位置

Filed Under: postgresql

Postgresql 特性分析 checkpoint机制浅析

2018/11/19 by admin Leave a Comment

文章来源：http://mysql.taobao.org/monthly/2017/04/04/

上期月报PgSQL · 特性分析 · Write-Ahead Logging机制浅析中简单介绍了PostgreSQL中WAL机制，其中讲到如果是创建checkpoint会触发刷新xlog日志页到磁盘，本文主要分析下PostgreSQL中checkpoint机制。

checkpoint又名检查点，一般checkpoint会将某个时间点之前的脏数据全部刷新到磁盘，以实现数据的一致性与完整性。目前各个流行的关系型数据库都具备checkpoint功能，其主要目的是为了缩短崩溃恢复时间，以Oracle为例，在进行数据恢复时，会以最近的checkpoint为参考点执行事务前滚。而在WAL机制的浅析中，也提过PostgreSQL在崩溃恢复时会以最近的checkpoint为基础，不断应用这之后的WAL日志。

检查点发生时机

在xlog.h文件中，有如下代码对checkpoint进行了相应的分类：

/*
 * OR-able request flag bits for checkpoints.  The "cause" bits are used only
 * for logging purposes.  Note: the flags must be defined so that it's
 * sensible to OR together request flags arising from different requestors.
 */

/* These directly affect the behavior of CreateCheckPoint and subsidiaries */
#define CHECKPOINT_IS_SHUTDOWN	0x0001	/* Checkpoint is for shutdown */
#define CHECKPOINT_END_OF_RECOVERY	0x0002		/* Like shutdown checkpoint,
												 * but issued at end of WAL
												 * recovery */
#define CHECKPOINT_IMMEDIATE	0x0004	/* Do it without delays */
#define CHECKPOINT_FORCE		0x0008	/* Force even if no activity */
/* These are important to RequestCheckpoint */
#define CHECKPOINT_WAIT			0x0010	/* Wait for completion */
/* These indicate the cause of a checkpoint request */
#define CHECKPOINT_CAUSE_XLOG	0x0020	/* XLOG consumption */
#define CHECKPOINT_CAUSE_TIME	0x0040	/* Elapsed time */
#define CHECKPOINT_FLUSH_ALL	0x0080	/* Flush all pages, including those
										 * belonging to unlogged tables */

也就是说，以下几种情况会触发数据库操作系统做检查点操作：

超级用户（其他用户不可）执行CHECKPOINT命令
数据库shutdown
数据库recovery完成
XLOG日志量达到了触发checkpoint阈值
周期性地进行checkpoint
需要刷新所有脏页

为了能够周期性的创建检查点，减少崩溃恢复时间，同时合并I/O，PostgreSQL提供了辅助进程checkpointer。它会对不断检测周期时间以及上面的XLOG日志量阈值是否达到，而周期时间以及XLOG日志量阈值可以通过参数来设置大小，接下来介绍下与checkpoints相关的参数。

与检查点相关参数

checkpoint_segments
- WAL log的最大数量，系统默认值是3。超过该数量的WAL日志，会自动触发checkpoint。
checkpoint_timeout
- 系统自动执行checkpoint之间的最大时间间隔。系统默认值是5分钟。
checkpoint_completion_target
- 该参数表示checkpoint的完成时间占两次checkpoint时间间隔的比例，系统默认值是0.5,也就是说每个checkpoint需要在checkpoints间隔时间的50%内完成。
checkpoint_warning
- 系统默认值是30秒，如果checkpoints的实际发生间隔小于该参数，将会在server log中写入写入一条相关信息。可以通过设置为0禁用。

创建检查点具体过程

CreateCheckPoint具体过程

当PostgreSQL触发checkpoint发生的条件后，会调用CreateCheckPoint函数创建具体的检查点，具体过程如下：

遍历所有的数据buffer，将脏页块状态从BM_DIRTY改为BM_CHECKPOINT_NEEDED，表示这些脏页将要被checkpoint刷新到磁盘
调用CheckPointGuts函数将共享内存中的脏页刷出到磁盘
生成新的Checkpoint 记录写入到XLOG中
更新控制文件、共享内存里XlogCtl的检查点相关成员、检查点的统计信息结构

PostgreSQL 控制文件pg_control里存储的数据是一个ControlFileData结构，具体如下：

typedefstruct ControlFileData
{
    uint64    system_identifier;
    uint32    pg_control_version;     /*PG_CONTROL_VERSION */
    uint32    catalog_version_no;     /* seecatversion.h */
    DBState      state;       /* see enum above */
    pg_time_t time;        /* time stamp of last pg_control update */

        XLogRecPtr	checkPoint;		/* 最近一次创建checkpoint的LSN*/
        XLogRecPtr	prevCheckPoint; /* 最近一次之前创建checkpoint的LSN */
        /*由于一个检查点的时间比较长，所以有可能系统在所有页面写完之前崩溃，这样磁盘上的检查点可能是不完全的，因此将最后一个完全检查点位置写在prevCheckPoint上*/

	CheckPoint	checkPointCopy; /* 最近一次checkpoint对应的CheckPoint对象 */

	XLogRecPtr	minRecoveryPoint;
	TimeLineID	minRecoveryPointTLI;
	XLogRecPtr	backupStartPoint;
	XLogRecPtr	backupEndPoint;
	bool		backupEndRequired;
   ......

其中，minRecoveryPoint和minRecoveryPointTLI确定数据库启动前，如果做归档恢复，我们必须恢复到的最小检查点。其中minRecoveryPoint指向该检查点对应的LSN位置，minRecoveryPointTLI指向该检查点对应的时间线。其具体的用法，我们将在之后的PostgreSQL崩溃恢复中分析，这里我们主要分析下PostgreSQL中的时间线概念。

PostgreSQL中WAL日志段名称，由时间线ID、日志ID、段ID的八位16进制数依次构成。例如：

00000001	00000001	0000008F
时间线TimeLineID	逻辑日志ID	段ID

其中时间线是作为日志段名称的一部分，用来标识数据库归档恢复后产生的一系列新的WAL记录。在每次归档恢复完成后，都会产生一个新的时间线和新的WAL日志段。时间线可以理解为平行时空中的各个平行宇宙，我们完全可以恢复到某个时间点，重开一条时间线，继续进行数据操作，这样就可以实现完全的PTIR。

在PostgreSQL中，一个新的时间线产生，系统伴随它会建立一个以“新TimeLineID+.history”命名的“时间线历史”文件(timeline history)，它是一个类似于txt的文件，其中包含所有在当前时间线以前的时间线，同时记录了每个时间线开始时的第一个WAL段，这样数据库恢复时，通过读取时间线历史文件文件，根据目标时间点可以快速找到正确的日志段文件。如果上一次恢复是恢复到具体某时刻，在时间线历史文件中还会记录该时间线对应的具体时刻。

在PITR恢复时，无需扫描所有WAL日志文件，而是通过时间线直接定位某个WAL段，再从该WAL段中找到符合该时间点的日志记录，这样就大大提高了效率。同时数据库恢复时，默认是沿着基备份开始时的时间点进行，即利用从基备份完成后产生的第一个日志段文件做恢复，如果想恢复到指定时间点(时间线)，需要在recovery.conf配置文件中设置目标时间线(target timeline ID)，但是target timeline ID不能指定为基备份以前的时间线。

CheckPointGuts函数

CheckPointGuts函数将共享内存里的数据刷出并文件同步到磁盘，具体定义如下：

staticvoid
CheckPointGuts(XLogRecPtrcheckPointRedo,int flags)
{
   CheckPointCLOG();
   CheckPointSUBTRANS();
   CheckPointMultiXact();
   CheckPointPredicate();
   CheckPointRelationMap();
   CheckPointBuffers(flags);   /* performs all required fsyncs */
   /* We deliberately delay 2PC checkpointingas long as possible */
   CheckPointTwoPhase(checkPointRedo);
}

可以看出，CheckPointGuts根据不同的缓存类型，把clog、subtrans、multixact、predicate、relationmap、buffer（数据文件）和twophase相应缓存分别调用不同的方法，将缓存刷到磁盘中：

提交事务日志管理器的方法CheckPointClog
子事务日志管理器的方法CheckPointSUBTRANS
多事务日志管理器的方法CheckPointMultiXact
支持序列化事务隔离级别的谓词锁模块的方法CheckPointPredicate
目录/系统表到文件节点映射模块的方法CheckPointRelationMap
缓存管理器的方法CheckPointBuffers
两阶段提交模块的方法CheckPointTwoPhase

其中，前四个函数最后都调用了SLRU模块的SimpleLruFlush（简单最近最少使用）方法，把相应的共享内存数据写到磁盘，并通过调用pg_fsync方法把相应文件刷到磁盘上对应文件。

后二个函数没有使用SLRU算法，直接调用pg_fsync方法把相应文件刷到磁盘上对应文件。

而目录/系统表到文件节点映射模块的方法CheckPointRelationMap，会将共享内存里系统表和对应物理文件映射的map文件刷到磁盘。

总结

至此，我们大体了解了checkpoint的用法和整个实现过程，但是还需要对一些特别的地方做出说明。

每个检查点后，第一次数据页的变化会导致整个页面会被记录在XLOG日志中
检查点的开销比较高，可以用checkpoint_warning自检，相应调大checkpoint_segments
检查点的位置保存在文件 pg_control，pg_control文件被损坏可能会导致数据库不可用

其中，如果pg_control文件损坏，在数据库崩溃恢复时可能出现一些问题，这些问题我们将在分析PostgreSQL数据库崩溃恢复时具体分析。

Filed Under: postgresql

postgreSQL pg_resetxlog pg_resetwal 工具介绍

2018/11/19 by admin Leave a Comment

pg_resetxlog – 重置一个 PostgreSQL 数据库集羣的预写日志以及其它控制内容

注意从pg 10开始 pg_resetxlog 被 pg_resetwal 替代

root@vultr:/usr/lib/postgresql/10/bin# ./pg_resetwal --help
pg_resetwal resets the PostgreSQL write-ahead log.

Usage:
pg_resetwal [OPTION]... DATADIR

Options:
-c XID,XID set oldest and newest transactions bearing commit timestamp
(zero in either value means no change)
[-D] DATADIR data directory
-e XIDEPOCH set next transaction ID epoch
-f force update to be done
-l WALFILE force minimum WAL starting location for new write-ahead log
-m MXID,MXID set next and oldest multitransaction ID
-n no update, just show what would be done (for testing)
-o OID set next OID
-O OFFSET set next multitransaction offset
-V, --version output version information, then exit
-x XID set next transaction ID
-?, --help show this help, then exit

Report bugs to <pgsql-bugs@postgresql.org>.

pg_resetxlog [ -f ] [ -n ] [ -o oid ] [ -x xid ] [ -l fileid,seg ] datadir

清理预写日志（WAL）并且可以选择地重置其它一些控制信息（存储在 pg_control文件中）。

有时候，如果pg_control损坏或丢失了，我们需要这个功能。我们一定只把它用作最后的方法，就是说只有因爲这样的崩溃导致服务器无法啓动的时候才使用。

在运行这个命令之后，我们可能可以啓动服务器了，但是，一定要记住数据库可能因爲部分提交的事务而含有不完整的数据。

你应该马上转储你的数据，运行 initdb，然后重新装载。在重新装载之后，检查不完整的部分然后根据需要进行修复。

这个命令只能由安装服务器的用户运行，因爲它需要对数据目录的读写权限。

出于安全考虑，你必须在命令行上声明数据目录。 pg_resetxlog 不使用环境变量 PGDATA。

如果 pg_resetxlog 抱怨说它无法判断用于 pg_control 的有效数据，那麽你可以强制它继续处理，方法是声明-f （强制）开关。在这种情况下，那些丢失了的数据的值将用模煳的近似数值代替。
大多数字段都可以匹配上，但是下一个 OID，下一个事务 ID，WAL开始地址以及数据库区域字段可能需要手工帮助，
前面三个可以用下面讨论的开关设置。pg_resetxlog 自己的环境是猜测区域字段的来源；看看 LANG 等等东西，它们应该和 initdb 运行的环境相匹配。

如果你不能判断所有这些字段的正确数值，那麽还是可以使用 -f，但是这样恢复过来的数据库更要怀疑有问题：立即转储和重置是必须的。

在转储之前不要执行任何修改数据的操作，因爲任何这样的动作都可能把事情搞得更糟糕。-o, -x, 和 -l 开关允许我们手工设置下一个 OID，下一个事务 ID，以及 WAL 起始位置的数值。只有在 pg_resetxlog 无法通过读取 pg_control 判断合适的数值的时候才需要它。对于下一个事务ID 而言，一个安全的数值是看看数据目录里的 /pg_clog 里数值最大的文件名，
然后加一，然后再乘上1048576。请注意那些文件名是十六进制的。通常我们也以十六进制的形式声明开关值是最简单得。

比如，如果 0011 是 pg_clog 里最大的记录，-x 0x1200000就可以了（后面的五个零提供了合适的乘积）。

WAL 的起始位置应该比目前存在于数据目录里得/pg_xlog 里面的任何文件号都大。它也是十六进制的，并且有两部分。

比如，如果000000FF0000003A是pg_xlog 里最大的条目，那麽-l 0xFF,0x3B就可以了。

我们没有很容易的办法来判断比数据库中最大的 OID 大一号的下一个 OID，不过很走运的是获取正确的下一个 OID 并非非常关键。
开关 -n（无操作）指示pg_resetxlog 打印从 pg_control重新构造的数值然后不修改任何值就退出。

这主要是一个调试工具，但是在 pg_resetxlog真正处理前进行的整洁性检查的时候可能会有用。

在 postmaster 服务器运行的时候一定不要运行这个命令。
如果发现在数据文件目录里有锁文件，那麽 pg_resetxlog 将拒绝啓动。如果 postmaster崩溃，那麽可能会剩下一个锁文件；
如果这样，你可以删除该锁文件以便允许pg_resetxlog运行。但是在你这麽做之前，一定要确信没有任何postmaster或者后端服务器仍在运行。

Filed Under: postgresql

postgreSQL使用pg_resetxlog恢复pg_control

2018/11/19 by admin Leave a Comment

文章来源： http://blog.sina.com.cn/s/blog_544a710b0101a6xv.html

上一篇测试了通过pg_resetxlog来清理WAL，我们还可以通过它来恢复pg_control。

pg_control在$PGDATA/global下，很小，很不起眼。但一旦这个文件被损坏，PG就启不来。
cd $PGDATA/global

mv pg_control ./..

看日志，可以看到以下信息：

PANIC: could not open control file "global/pg_control": No such file or directory
LOG: checkpointer process (PID 5989) was terminated by signal 6: Aborted
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, 
because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
LOG: archiver process (PID 5993) exited with exit code 1
LOG: all server processes terminated; reinitializing

pg_controldata

pg_controldata: could not open file "/database/pgdata/global/pg_control" for reading: No such file or directory

这时我们只能去恢复pg_control了。

命令不难，难的是如何设置参数值！

-x：

A safe value for the next transaction ID (-x) can be determined by looking for the numerically
largest file name in the directory pg_clog under the data directory, adding one, and then multiplying by 1048576. 
Note that the file names are in hexadecimal. It is usually easiest to specify the option value in hexadecimal too. 
For example, if 0011 is the largest entry in pg_clog, -x 0x1200000 will work (five trailing zeroes provide the proper multiplier).

cd $PGDATA/pg_clog

ls -l

-rw------- 1 postgres postgres 8.0K Mar 7 14:58 0000



根据上述规则，-x = 0000+1跟5个0=0x000100000 （2进制换算成16进制，*1048576 = 加5个0）

#################################################################################################

-m：

A safe value for the next multitransaction ID (-m) can be determined by looking for the numerically largest file name 
in the directory pg_multixact/offsets under the data directory, adding one,and then multiplying by 65536. 
As above, the file names are in hexadecimal, 
so the easiest way to do this is to specify the option value in hexadecimal and add four zeroes

cd $PGDATA/pg_multixact/offsets
ls -l
-rw------- 1 postgres postgres 8.0K Mar 7 14:58 0000
所以：-m = 0000+1 跟4个0 = 0x00010000 （2进制换算成16进制，*65536 = 加4个0）
#################################################################################################
-O：

A safe value for the next multitransaction offset (-O) can be determined by 
looking for the numerically largest file name in the directory pg_multixact/members under the data directory, 
adding one, and then multiplying by 65536. As above, the file names are in hexadecimal, 
so the easiest way to do this is to specify the option value in hexadecimal and add four zeroes

cd $PGDATA/pg_multixact/members

ls -l

-rw------- 1 postgres postgres 8.0K Feb 28 09:04 0000

所以：-O = 0000+1 跟4个0 = 0x00010000 （2进制换算成16进制，*65536 = 加4个0）

#################################################################################################

-l：

TheWAL starting address (-l) should be larger than anyWAL segment file name currently existing
in the directory pg_xlog under the data directory. These names are also in hexadecimal and have
three parts. The first part is the “timeline ID” and should usually be kept the same. Do not choose a value larger than 255 (0xFF) for the third part; instead increment the second part and reset the third part to 0. For example, if 00000001000000320000004A is the largest entry in pg_xlog, -l 0x1,0x32,0x4B will work; but if the largest entry is 000000010000003A000000FF, choose -l 0x1,0x3B,0x0 or more.

cd $PGDATA/pg_xlog

ls -l

total 129M
-rw------- 1 postgres postgres 56 Feb 28 10:40 00000002.history
-rw------- 1 postgres postgres 64M Mar 7 15:03 000000030000000D0000002D
-rw------- 1 postgres postgres 64M Mar 7 15:21 000000030000000D0000002E
drwx------ 2 postgres postgres 32K Mar 7 15:21 archive_status

这里的算法，根据“larger than anyWAL segment file name currently existing
in the directory pg_xlog under the data directory”，

所以：
TLI （timeline ID）=0x00000003（同第一段）
FILE = 0x0000000D （同第二段）
SEG = 0x0000002E + 1 = 0x0000002F
-l = 0x3,0xD,0x2F （这里前置的0都去处了）
###########################################################################################

-e和-o不清楚怎么设置，官方文档也没有介绍，幸亏提到is not critical，所以就不管了。

接下来就开始恢复了。

1，

cd $PGDATA/global

touch pg_control

2，

pg_resetxlog -x 0x100000 -m 0x10000 -O 0x10000 -l 0x3,0xD,0x2F -f $PGDATA

pg_resetxlog: pg_control exists but is broken or unknown version; ignoring it
Transaction log reset

3，

看下control内容

pg_controldata

pg_control version number: 922
Catalog version number: 201204301
Database system identifier: 5852524061837064188
Database cluster state: shut down
pg_control last modified: Thu 07 Mar 2013 05:02:24 PM CST
Latest checkpoint location: D/BC000020
Prior checkpoint location: 0/0
Latest checkpoint’s REDO location: D/BC000020
Latest checkpoint’s TimeLineID: 3
Latest checkpoint’s full_page_writes: off
Latest checkpoint’s NextXID: 0/1048576
Latest checkpoint’s NextOID: 10000
Latest checkpoint’s NextMultiXactId: 65536
Latest checkpoint’s NextMultiOffset: 65536
Latest checkpoint’s oldestXID: 2296015872
Latest checkpoint’s oldestXID’s DB: 0
Latest checkpoint’s oldestActiveXID: 0
Time of latest checkpoint: Thu 07 Mar 2013 05:02:24 PM CST
Minimum recovery ending location: 0/0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
Current wal_level setting: minimal
Current max_connections setting: 100
Current max_prepared_xacts setting: 0
Current max_locks_per_xact setting: 64
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 67108864
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value

红色标注部分已改变。

4，pg_ctl start -D $PGDATA

Filed Under: postgresql

postgreSQL 常见数据页损坏坏块问题

2018/11/18 by admin Leave a Comment

pg 中的常见几个数据页损坏坏块问题

could not read block N of relation X/Y/Z: read only 0 of 8192 bytes
catalog is missing N attribute(s) for relid M
WARNING: page verification failed, calculated checksum %u but expected %u 
ERROR: invalid page in block %u of relation %s

这些错误大部分情况下是存储子系统:文件系统或磁盘的问题导致，可以考虑先冷备份整个数据文件夹，然后设置参数 zero_damaged_pages和 ignore_checksum_failure

之后使用 pg_dumpall 导出全库

可以使用 pg_catcheck 检查postgreSQL catalog字典是否存在问题：

ubuntu 


git clone https://github.com/EnterpriseDB/pg_catcheck
apt-get install libselinux-dev
apt-get install libxslt-dev
apt-get install libpam-dev
apt-get install libssl-dev
apt-get install libedit-dev
apt-get install -y libkrb5-dev

cd pg_catcheck
make 
chown postgres pg_catcheck
chmod 755 pg_catcheck

su - postgres

./pg_catcheck 
progress: done (0 inconsistencies, 0 warnings, 0 errors)

Filed Under: postgresql

postgreSQL zero_damaged_pages 参数

2018/11/18 by admin Leave a Comment

There is a special option: zero_damaged_pages=on that you can use on postgresql.conf, it is documented here.

This option will allow for a pg_dump (or pg_dump_all) that do not stop on critical errors and get as much data back as possible, but you will loose the data that cannot be read.:

(exceprt from documentation, I added the strong.

Detection of a damaged page header normally causes PostgreSQL to report an error, aborting the current transaction. Setting zero_damaged_pages to on causes the system to instead report a warning, zero out the damaged page in memory, and continue processing. This behavior will destroy data, namely all the rows on the damaged page. However, it does allow you to get past the error and retrieve rows from any undamaged pages that might be present in the table. It is useful for recovering data if corruption has occurred due to a hardware or software error. You should generally not set this on until you have given up hope of recovering data from the damaged pages of a table. Zeroed-out pages are not forced to disk so it is recommended to recreate the table or the index before turning this parameter off again. The default setting is off, and it can only be changed by a superuser.

zero_damaged_pages 参数主要作用是让pg_dump 等程序不要直接报错退出


    /*
     * We get here only in the corner case where we are trying to extend
     * the relation but we found a pre-existing buffer marked BM_VALID.
     * This can happen because mdread doesn't complain about reads beyond
     * EOF (when zero_damaged_pages is ON) and so a previous attempt to
     * read a block beyond EOF could have left a "valid" zero-filled
     * buffer.  Unfortunately, we have also seen this case occurring
     * because of buggy Linux kernels that sometimes return an
     * lseek(SEEK_END) result that doesn't account for a recent write. In
     * that situation, the pre-existing buffer would contain valid data
     * that we don't want to overwrite.  Since the legitimate case should
     * always have left a zero-filled buffer, complain if not PageIsNew.
     */
	  
        /* check for garbage data */
        if (!PageIsVerified((Page) bufBlock, blockNum))
        {
            if (mode == RBM_ZERO_ON_ERROR || zero_damaged_pages)
            {
                ereport(WARNING,
                        (errcode(ERRCODE_DATA_CORRUPTED),
                         errmsg("invalid page in block %u of relation %s; zeroing out page",
                                blockNum,
                                relpath(smgr->smgr_rnode, forkNum))));
                MemSet((char *) bufBlock, 0, BLCKSZ);
            }
            else
                ereport(ERROR,
                        (errcode(ERRCODE_DATA_CORRUPTED),
                         errmsg("invalid page in block %u of relation %s",
                                blockNum,
                                relpath(smgr->smgr_rnode, forkNum))));
        }
    }
}

Filed Under: postgresql

详述在无备份情况下postgreSQL中为什么drop truncate table基本是不能恢复的

2018/11/18 by admin Leave a Comment

最近在研究postgreSQL的特殊恢复手段；pg的表数据直接独占存放在单个或多个数据文件，这让pg本身的恢复格局较为简单。

postgreSQL的基本情况：

每个表和索引都是单独的文件，当表或索引太大时会扩展到多个文件
每套库都有自己的数据字典表 pg_class等，pg_class的文件号是1529
pg_global表空间里记录了核心字典信息就是有哪些数据库和数据库的oid
postgreSQL的块头是 pageHeader ，pageheader 24个字节之后是 ItemIdData 即行指针，之后是free space，之后是数据tuple heap
pageheader里没有该page的位置信息，但这个文档 https://www.jianshu.com/p/375e2b9fd079 里说有pd_type和 pd_oid 信息，即24个字节里的最后4个字节，但源代码里看没有这些结构，源代码里最后4字节是 TransactionId(uint32) pd_prune_xid; https://doxygen.postgresql.org/bufpage_8h_source.html 。这些结构可能是某些特殊发行版本里搞出来的。
另外在tuple的ctid里有块号和行号，但是没有文件号，即如(0,0),(0,1),(0,3)这样的序列信息没有意义

基于以上事实做的一些实验：

truncate table xx 表对应的文件马上被vacuum掉，即文件大小归零，文件没有被删除
delete from xx 删除全表，表对应的文件马上被vacuum掉，即文件大小归零，文件没有被删除
drop table xx 表对应的文件马上被vacuum掉，即文件大小归零，文件没有被删除

以上三种情况会因为pg的page缺少必要区分page的page内特征信息（例如oracle的rdba)，虽然可以通过扫描磁盘获得这些块，但很难搞清楚这些块属于哪个文件（哪个表或索引）

另做了一个实验，在pg 12下创建2张表结构一致，数据不一致。通过对这2张表对应对文件相互替换冒充，通过pg实例可以访问这2张表（被互相替换后），这说明pg是不验证也无法验证文件内容与库中的表的强一致性的。 page中缺乏一个重要的oid信息，这将导致pg的page不具有碎片扫描合并的可能性。

但因为pg在做drop table , truncate table 时会附带收缩数据文件，又因为其page数据结构中没有合适的特征信息，所以造成其在无备份情况下基本不可能恢复数据。

对于truncate，官方的说明是 TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables.

https://www.postgresql.org/docs/9.1/sql-truncate.html

drop table /truncate table都会引发数据文件收缩，即文档所说 it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation ， 都不需要你去vacuum表。

虽然对于这些被回收的空间，drop /truncate操作都没有去刻意填零，这部分空间会被文件系统回收。

对于Oracle数据库的数据文件而言，即便其被从文件系统或ASM上删除了，但因为其数据块仍存在于磁盘上，我们还是可以通过PRMSCAN工具来将这些数据块扫描后合并为数据文件，这是因为oracle的数据块自带身份信息 rdba_kcbh , rdba代表了该数据块的文件号和块号，从而可以基于rdba来重组数据文件。

但对于postgreSQL的 page而言，其没有有效的类似rdba的信息，唯一类似的是每一行有一个ctid信息，该信息类似oracle的rowid，但是ctid里只有块号和行号，缺少文件号，且用户表不是默认都有OIDS:

OIDs are not added to user-created tables, unless WITH OIDS is specified when the table is created, or the default_with_oids configuration variable is enabled.

所以由于上述信息的缺少，导致虽然你可以扫描磁盘上的pg的page页，但很难将它们有效合并。

Overall Page Layout

Item	Description
PageHeaderData	20 bytes long. Contains general information about the page, including free space pointers.
ItemIdData	Array of (offset,length) pairs pointing to the actual items. 4 bytes per item.
Free space	The unallocated space. New item pointers are allocated from the start of this area, new items from the end.
Items	The actual items themselves.
Special space	Index access method specific data. Different methods store different data. Empty in ordinary tables.

PageHeaderData Layout

Field	Type	Length	Description
pd_lsn	XLogRecPtr	8 bytes	LSN: next byte after last byte of xlog record for last change to this page
pd_tli	TimeLineID	4 bytes	TLI of last change
pd_lower	LocationIndex	2 bytes	Offset to start of free space
pd_upper	LocationIndex	2 bytes	Offset to end of free space
pd_special	LocationIndex	2 bytes	Offset to start of special space
pd_pagesize_version	uint16	2 bytes	Page size and layout version number information

HeapTupleHeaderData Layout

Field	Type	Length	Description
t_xmin	TransactionId	4 bytes	insert XID stamp
t_cmin	CommandId	4 bytes	insert CID stamp
t_xmax	TransactionId	4 bytes	delete XID stamp
t_cmax	CommandId	4 bytes	delete CID stamp (overlays with t_xvac)
t_xvac	TransactionId	4 bytes	XID for VACUUM operation moving a row version
t_ctid	ItemPointerData	6 bytes	current TID of this or newer row version
t_natts	int16	2 bytes	number of attributes
t_infomask	uint16	2 bytes	various flag bits
t_hoff	uint8	1 byte	offset to user data

基于上述情况，目前得出的结论是对于postgreSQL中的drop 和 truncate由于postgreSQL先天不在page中存放文件号或表号或对象号这些信息，其基本上是没法做到碎片合并的，因此也很难基于软件工程去恢复。

Filed Under: postgresql