PostgreSQL – Database SOS

PostgreSQL查询一个表相关的所有oid

在pg中查询一个表相关的所有oid(表OID、TOAST表OID、索引OID、TOAST表的索引OID、约束OID)

-- 替换这里的表名和模式名
\set search_path = public;
\set table_name '\'AO_6384AB_DISCOVERED\''
\set schema_name '\'public\''

WITH table_info AS (
    -- 基础表信息：表OID、TOAST表OID
    SELECT
        c.oid AS table_oid,
        c.relname AS table_name,
        c.reltoastrelid AS toast_oid,
        n.nspname AS schema_name
    FROM
        pg_catalog.pg_class c
    JOIN
        pg_catalog.pg_namespace n ON c.relnamespace = n.oid
    WHERE
        c.relname = :table_name
        AND n.nspname = :schema_name
        AND c.relkind = 'r'
),
index_info AS (
    -- 索引信息：索引OID
    SELECT
        t.table_oid,
        i.oid AS object_oid,
        i.relname AS object_name,
        'index' AS object_type
    FROM
        pg_catalog.pg_class i
    JOIN
        pg_catalog.pg_index idx ON i.oid = idx.indexrelid
    JOIN
        table_info t ON idx.indrelid = t.table_oid
    WHERE
        i.relkind = 'i'
),
toast_info AS (
    -- TOAST表信息：TOAST表OID
    SELECT
        t.table_oid,
        t.toast_oid AS object_oid,
        c.relname AS object_name,
        'toast_table' AS object_type
    FROM
        table_info t
    JOIN
        pg_catalog.pg_class c ON t.toast_oid = c.oid
    WHERE
        t.toast_oid <> 0
),
toast_index_info AS (
    -- TOAST索引信息：TOAST表的索引OID
    SELECT
        t.table_oid,
        i.oid AS object_oid,
        i.relname AS object_name,
        'toast_index' AS object_type
    FROM
        table_info t
    JOIN
        pg_catalog.pg_class c ON t.toast_oid = c.oid
    JOIN
        pg_catalog.pg_index idx ON c.oid = idx.indrelid
    JOIN
        pg_catalog.pg_class i ON idx.indexrelid = i.oid
    WHERE
        t.toast_oid <> 0
        AND i.relkind = 'i'
),
constraint_info AS (
    -- 约束信息：约束OID
    SELECT
        t.table_oid,
        con.oid AS object_oid,
        con.conname AS object_name,
        CASE con.contype
            WHEN 'p' THEN 'primary_key'
            WHEN 'u' THEN 'unique_constraint'
            WHEN 'f' THEN 'foreign_key'
            WHEN 'c' THEN 'check_constraint'
            ELSE 'other_constraint'
        END AS object_type
    FROM
        pg_catalog.pg_constraint con
    JOIN
        table_info t ON con.conrelid = t.table_oid
)
-- 合并所有结果，先输出表本身的信息，再输出其他关联对象
SELECT
    t.table_oid AS object_oid,
    t.table_name AS object_name,
    'table' AS object_type,
    t.schema_name AS schema_name
FROM
    table_info t
UNION ALL
SELECT
    object_oid,
    object_name,
    object_type,
    (SELECT schema_name FROM table_info) AS schema_name
FROM
    index_info
UNION ALL
SELECT
    object_oid,
    object_name,
    object_type,
    (SELECT schema_name FROM table_info) AS schema_name
FROM
    toast_info
UNION ALL
SELECT
    object_oid,
    object_name,
    object_type,
    (SELECT schema_name FROM table_info) AS schema_name
FROM
    toast_index_info
UNION ALL
SELECT
    object_oid,
    object_name,
    object_type,
    (SELECT schema_name FROM table_info) AS schema_name
FROM
    constraint_info
ORDER BY
    object_type;

PostgreSQL oid文件替换实现数据访问

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：PostgreSQL oid文件替换实现数据访问

对于postgresql数据库,我们都知道他的数据在独立的oid文件里面,而字典主要在pg_type/1247,pg_class/1259,pg_attribute/1260等对象中,而数据是存储在独立的oid和toast oid文件中.如果通过提供oid文件,可以实现狸猫换太子的恢复数据.这里做一个简单测试,大概思路是这样的库里面有一个表a,然后创建一个空表b,然后把a表相关的oid文件复制成b表的oid文件,然后验证数据是否可以正常查询
创建一个空的b表,结构和a表一致

[postgres@xifenfei 13676]$ psql
psql (12.8)
Type "help" for help.

postgres=# CREATE TABLE "public"."AO_6384AB_DISCOVERED1" (
postgres(#   "DATE" "pg_catalog"."timestamp",
postgres(#   "ID" "pg_catalog"."int4",
postgres(#   "KEY" "pg_catalog"."varchar" COLLATE "pg_catalog"."default",
postgres(#   "PLUGIN_KEY" "pg_catalog"."varchar" COLLATE "pg_catalog"."default",
postgres(#   "USER_KEY" "pg_catalog"."varchar" COLLATE "pg_catalog"."default"
postgres(# )
postgres-# ;
CREATE TABLE

检查a/b的情况

postgres=# select count(1) from "AO_6384AB_DISCOVERED";
 count 
-------
   544
(1 row)

postgres=# select count(1) from "AO_6384AB_DISCOVERED1";
 count 
-------
     0
(1 row)

postgres=# \d "AO_6384AB_DISCOVERED"
                    Table "public.AO_6384AB_DISCOVERED"
   Column   |            Type             | Collation | Nullable | Default 
------------+-----------------------------+-----------+----------+---------
 DATE       | timestamp without time zone |           |          | 
 ID         | integer                     |           |          | 
 KEY        | character varying           |           |          | 
 PLUGIN_KEY | character varying           |           |          | 
 USER_KEY   | character varying           |           |          | 

postgres=# \d "AO_6384AB_DISCOVERED1"
                   Table "public.AO_6384AB_DISCOVERED1"
   Column   |            Type             | Collation | Nullable | Default 
------------+-----------------------------+-----------+----------+---------
 DATE       | timestamp without time zone |           |          | 
 ID         | integer                     |           |          | 
 KEY        | character varying           |           |          | 
 PLUGIN_KEY | character varying           |           |          | 
 USER_KEY   | character varying           |           |          |

存在数据的a/b表相关oid信息



postgres=# select oid,relname,relfilenode from pg_class where relname like '%AO_6384AB_DISCOVERED%';
  oid  |        relname        | relfilenode 
-------+-----------------------+-------------
 16516 | AO_6384AB_DISCOVERED  |       16516
 17069 | AO_6384AB_DISCOVERED1 |       17069
(2 rows)

postgres=# select oid,relname,relfilenode from pg_class where relname like '%16516%' or  relname like '%17069%'  ;
  oid  |       relname        | relfilenode 
-------+----------------------+-------------
 16519 | pg_toast_16516       |       16519
 16521 | pg_toast_16516_index |       16521
 17072 | pg_toast_17069       |       17072
 17074 | pg_toast_17069_index |       17074
(4 rows)

关闭数据库把a表相关的oid文件拷贝替换b表的oid文件

[postgres@xifenfei 13676]$ pg_ctl stop -D /pgdata
waiting for server to shut down....2025-12-12 20:00:58.804 HKT [21445] LOG:  received fast shutdown request
2025-12-12 20:00:58.805 HKT [21445] LOG:  aborting any active transactions
2025-12-12 20:00:58.805 HKT [21471] FATAL:  terminating connection due to administrator command
2025-12-12 20:00:58.805 HKT [21473] FATAL:  terminating connection due to administrator command
2025-12-12 20:00:58.805 HKT [21856] FATAL:  terminating connection due to administrator command
2025-12-12 20:00:58.806 HKT [21472] FATAL:  terminating connection due to administrator command
2025-12-12 20:00:58.806 HKT [21445] LOG: background worker"logical replication launcher"(PID 252)exited with exit code 1
2025-12-12 20:00:58.807 HKT [21447] LOG:  shutting down
2025-12-12 20:00:58.811 HKT [21445] LOG:  database system is shut down
 done
server stopped
[postgres@xifenfei 13676]$ cp  16516 17069
[postgres@xifenfei 13676]$ cp  16519 17072
[postgres@xifenfei 13676]$ cp  16521 17074
[postgres@xifenfei 13676]$ pg_ctl start -D /pgdata
waiting for server to start....2025-12-12 20:02:05.985 HKT [22241] LOG:  
starting PostgreSQL 12.8 on x86_64-pc-linux-gnu, 
compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-26.0.1), 64-bit
2025-12-12 20:02:05.985 HKT [22241] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2025-12-12 20:02:05.986 HKT [22241] LOG:  listening on IPv6 address "::", port 5432
2025-12-12 20:02:05.986 HKT [22241] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-12-12 20:02:05.993 HKT [22242] LOG:  database system was shut down at 2025-12-12 20:00:58 HKT
2025-12-12 20:02:05.995 HKT [22241] LOG:  database system is ready to accept connections
 done
server started

查询a/b表数据

[postgres@xifenfei 13676]$ psql
psql (12.8)
Type "help" for help.

postgres=# select count(1) from "AO_6384AB_DISCOVERED1";
 count 
-------
   544
(1 row)

postgres=# select count(1) from "AO_6384AB_DISCOVERED";
 count 
-------
   544
(1 row)

通过上述从测试,证明创建一个新的表结构,完全可以访问老的oid文件中的数据

pg_wal中文件的名称中的logseq和实际文件中的logseq不匹配

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：pg_wal中文件的名称中的logseq和实际文件中的logseq不匹配

一个朋友由于某种原因给我发了一个pg_wal里面的wal文件,让我帮忙分析下故障原因,我打开文件之后发现文件编号小的修改时间比编号大的文件修改时间要新

wal日志文件命名规则：
我们看到的wal日志是这样的：0000000100004D6E000000CF
其中前8位:00000001表示timeline
中间8位：00004D6E表示logid
最后8位：000000CF表示logseg
在上述截图中,可以看到logseq为CF的文件比D0的要新很多,这个初步给人感觉不太正常.使用pg_waldump分别对其进行dump操作

[postgres@xifenfei bin]$ pg_waldump /data/wal/0000000100004D6E000000D0|head -10
pg_waldump: fatal: could not find a valid record after 4D6E/D0000000

这样可以看出来CF的wal文件可以正常dump出来,但是D0的文件dump报pg_waldump: fatal: could not find a valid record after类似异常.通过od命令分别对两个文件进行分析

[postgres@xifenfei bin]$ od -x /data/wal/0000000100004D6E000000CF|head -1
0000000 d101 0006 0001 0000 0000 cf00 4d6e 0000
[postgres@xifenfei bin]$ od -x /data/wal/0000000100004D6E000000D0|head -1
0000000 d101 0007 0001 0000 0000 9400 4d6e 0000

从第8个字节到第12个字节结束为logseq的值,这里明显可以看出来D0文件的logseq值和实际文件中的不一致.尝试把D0修改为94之后即可正常的pg_waldump进行分析

通过这里可以的出来一个结论,pg_wal中文件的名称中的logseq和实际文件中的logseq不匹配.出现这种问题的本质是由于pg_wal中的wal日志是相当oracle的redo,是通过类似重命名机制(看到有文档介绍是说硬链接指向旧文件然后删除旧文件)引起的问题.

pg创建gbk字符集库

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：pg创建gbk字符集库

记录下，Postgres库创建中文字符集

postgres=# CREATE DATABASE mydb_gbk
postgres-#     ENCODING 'EUC_CN'
postgres-#     LC_COLLATE 'zh_CN' 
postgres-#     LC_CTYPE 'zh_CN'
postgres-#     TEMPLATE template0;
CREATE DATABASE
postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 mydb_gbk  | postgres | EUC_CN   | zh_CN       | zh_CN       |            | libc            | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
(4 rows)

前提系统必须支持zh_CN语言包,检查命令为:

[root@xifenfei yum.repos.d]# locale -a |grep zh_CN
zh_CN
zh_CN.gb18030
zh_CN.gbk
zh_CN.utf8

如果没有使用yum以下命令安装

yum groupinstall "fonts"
yum install glibc-langpack-zh.x86_64

PostgreSQL运行日志管理

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：PostgreSQL运行日志管理

PostgreSQL目前配置中,不直接记录日志文件,这样的情况给数据库后期出现问题(特别是无法正常启动的情况)分析带来很大麻烦,不知道具体问题所在,建议在PG安装完成之后,启用日志功能,便于数据库运行状态检查和错误跟踪,主要日志参数涉及以下配置

log_destination = 'stderr'              # Valid values are combinations of
                                        # stderr, csvlog, jsonlog, syslog, and
                                        # eventlog, depending on platform.
                                        # csvlog and jsonlog require
                                        # logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on          # Enable capturing of stderr, jsonlog,
                                        # and csvlog into log files. Required
                                        # to be on for csvlogs and jsonlogs.
                                        # (change requires restart)
//是否将日志重定向至文件中，默认是off。

# These are only used if logging_collector is on:
log_directory = 'pg_log'                   # directory where log files are written,
                                        # can be absolute or relative to PGDATA
//日志文件目录，默认是PGDATA的相对路径，即PGDATA的相对路径，即{PGDATA}/pg_log，也可以改为绝对路径。
//日志文件可能会非常多，建议将日志重定向到其他目录或分区。
//将此配置修改其他目录时，必须先创建此目录，并修改权限，使得postgres用户对该目录有写权限。

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
                                        # can include strftime() escapes
log_file_mode = 0600                    # creation mode for log files,
                                        # begin with 0 to use octal notation
log_rotation_age = 1d                   # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
log_rotation_size = 10MB                # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.
#log_truncate_on_rotation = off         # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
//当日志文件已存在时，该配置如果为off，新生成的日志将在文件尾部追加，如果为on，则会覆盖原来的日志。

上述配置得到的结果如下

[postgres@xifenfei pg_log]$ pwd
/data/pg/15/data/pg_log
[postgres@xifenfei pg_log]$ ls -l
total 4
-rw------- 1 postgres postgres 1263 Apr  3 22:37 postgresql-2025-04-03_223117.log

Navicat连接postgresql时出现column “datlastsysoid” does not exist错误解决

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Navicat连接postgresql时出现column “datlastsysoid” does not exist错误解决

使用Navicat Premium 15访问PostgreSQL 16版本的库,报如下错误

ERROR:  column "datlastsysoid" does not exist
LINE 1: SELECT DISTINCT datlastsysoid FROM pg_database;

QQ20250308-105201
QQ20250308-105222

登录pg库查看

postgres=# SELECT DISTINCT datlastsysoid FROM pg_database;
2025-03-07 22:09:03.569 EST [62553] ERROR:  column "datlastsysoid" does not exist at character 17
2025-03-07 22:09:03.569 EST [62553] STATEMENT:  SELECT DISTINCT datlastsysoid FROM pg_database;
ERROR:  column "datlastsysoid" does not exist
LINE 1: SELECT DISTINCT datlastsysoid FROM pg_database;
                        ^
postgres=# \d pg_database;
               Table "pg_catalog.pg_database"
     Column     |   Type    | Collation | Nullable | Default 
----------------+-----------+-----------+----------+---------
 oid            | oid       |           | not null | 
 datname        | name      |           | not null | 
 datdba         | oid       |           | not null | 
 encoding       | integer   |           | not null | 
 datlocprovider | "char"    |           | not null | 
 datistemplate  | boolean   |           | not null | 
 datallowconn   | boolean   |           | not null | 
 datconnlimit   | integer   |           | not null | 
 datfrozenxid   | xid       |           | not null | 
 datminmxid     | xid       |           | not null | 
 dattablespace  | oid       |           | not null | 
 datcollate     | text      | C         | not null | 
 datctype       | text      | C         | not null | 
 daticulocale   | text      | C         |          | 
 daticurules    | text      | C         |          | 
 datcollversion | text      | C         |          | 
 datacl         | aclitem[] |           |          | 
Indexes:
    "pg_database_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
    "pg_database_datname_index" UNIQUE CONSTRAINT, btree (datname), tablespace "pg_global"
Tablespace: "pg_global"

确认没有了datlastsysoid列,通过查看文档确认从Postgres 15版本开始 pg_database表中删除了 datlastsysoid,但是可以通过查询dattablespace替代,使用二进制工具修改libcc.dll文件中的SELECT DISTINCT datlastsysoid部分语句为:SELECT DISTINCT dattablespace
QQ20250308-105427
QQ20250308-105455

然后重新启动Navicat，访问pg库正常
QQ20250308-111555

当然这个问题如果使用高版本的Navicat或者访问低版本的PostgreSQL库不会出现

PostgreSQL部分主要字典信息

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：PostgreSQL部分主要字典信息

一、pg_class:

该系统表记录了数据表、索引(仍然需要参阅pg_index)、序列、视图、复合类型和一些特殊关系类型的元数据。注意：不是所有字段对所有对象类型都有意义。

名字	类型	引用	描述
relname	name	数据类型名字。
relnamespace	oid	pg_namespace.oid	包含这个对象的名字空间(模式)的OI。
reltype	oid	pg_type.oid	对应这个表的行类型的数据类型。
relowner	oid	pg_authid.oid	对象的所有者。
relam	oid	pg_am.oid	对于索引对象，表示该索引的类型(B-tree，hash)。
relfilenode	oid	对象存储在磁盘上的文件名，如果没有则为0。
reltablespace	oid	pg_tablespace.oid	对象所在的表空间。如果为零，则表示使用该数据库的缺省表空间。(如果对象在磁盘上没有文件，这个字段就没有什么意义)
relpages	int4	该数据表或索引所占用的磁盘页面数量，查询规划器会借助该值选择最优路径。
reltuples	float4	表中行的数量，该值只是被规划器使用的一个估计值。
reltoastrelid	oid	pg_class.oid	与此表关联的TOAST表的OID，如果没有为0。TOAST表在一个从属表里”离线”存储大字段。
reltoastidxid	oid	pg_class.oid	如果是TOAST表，该字段为它索引的OID，如果不是TOAST表则为0。
relhasindex	bool	如果这是一个数据表而且至少有(或者最近有过)一个索引，则为真。它是由CREATE INDEX设置的，但DROP INDEX不会立即将它清除。如果VACUUM发现一个表没有索引，那么它清理 relhasindex。
relisshared	bool	如果该表在整个集群中由所有数据库共享，则为真。
relkind	char	r = 普通表，i = 索引，S = 序列，v = 视图， c = 复合类型，s = 特殊，t = TOAST表
relnatts	int2	数据表中用户字段的数量(除了系统字段以外，如oid)。在pg_attribute里肯定有相同数目的数据行。见pg_attribute.attnum.
relchecks	int2	表中检查约束的数量，参阅pg_constraint表。
reltriggers	int2	表中触发器的数量；参阅pg_trigger表。
relhasoids	bool	如果我们为对象中的每行都生成一个OID，则为真。
relhaspkey	bool	如果该表存在主键，则为真。
relhasrules	bool	如表有规则就为真；参阅pg_rewrite表。
relhassubclass	bool	如果该表有子表，则为真。
relacl	aclitem[]	访问权限。

二、pg_attribute:

该系统表存储所有表(包括系统表，如pg_class)的字段信息。数据库中的每个表的每个字段在pg_attribute表中都有一行记录。

名字	类型	引用	描述
attrelid	oid	pg_class.oid	此字段所属的表。
attname	name	字段名。
atttypid	oid	pg_type.oid	字段的数据类型。
attstattarget	int4	attstattarget控制ANALYZE为这个字段设置的统计细节的级别。零值表示不收集统计信息，负数表示使用系统缺省的统计对象。正数值的确切信息是和数据类型相关的。
attlen	int2	该字段所属类型的长度。(pg_type.typlen的拷贝)
attnum	int2	字段的编号，普通字段是从1开始计数的。系统字段，如oid，是任意的负数。
attndims	int4	如果该字段是数组，该值表示数组的维数，否则是0。
attcacheoff	int4	在磁盘上总是-1，但是如果装载入内存中的行描述器中，它可能会被更新为缓冲在行中字段的偏移量。
atttypmod	int4	表示数据表在创建时提供的类型相关的数据(比如，varchar字段的最大长度)。其值对那些不需要atttypmod的类型而言通常为-1。
attbyval	bool	pg_type.typbyval字段值的拷贝。
attstorage	char	pg_type.typstorage字段值的拷贝。
attalign	char	pg_type.typalign字段值的拷贝。
attnotnull	bool	如果该字段带有非空约束，则为真，否则为假。
atthasdef	bool	该字段是否存在缺省值，此时它对应pg_attrdef表里实际定义此值的记录。
attisdropped	bool	该字段是否已经被删除。如果被删除，该字段在物理上仍然存在表中，但会被分析器忽略，因此不能再通过SQL访问。
attislocal	bool	该字段是否局部定义在对象中的。
attinhcount	int4	该字段所拥有的直接祖先的个数。如果一个字段的祖先个数非零，那么它就不能被删除或重命名。

三、pg_attrdef:

该系统表主要存储字段默认值，字段中的主要信息存放在pg_attribute系统表中。注意：只有明确声明了缺省值的字段在该表中才会有记录。

名字	类型	引用	描述
adrelid	oid	pg_class.oid	这个字段所属的表
adnum	int2	pg_attribute.attnum	字段编号，其规则等同于pg_attribute.attnum
adbin	text	字段缺省值的内部表现形式。
adsrc	text	缺省值的人可读的表现形式。

四、pg_constraint:

该系统表存储PostgreSQL中表对象的检查约束、主键、唯一约束和外键约束。

名字	类型	引用	描述
conname	name	约束名字(不一定是唯一的)。
connamespace	oid	pg_namespace.oid	包含这个约束的名字空间(模式)的OID。
contype	char	c = 检查约束， f = 外键约束， p = 主键约束， u = 唯一约束
condeferrable	bool	该约束是否可以推迟。
condeferred	bool	缺省时这个约束是否是推迟的？
conrelid	oid	pg_class.oid	该约束所在的表，如果不是表约束则为0。
contypid	oid	pg_type.oid	该约束所在的域，如果不是域约束则为0。
confrelid	oid	pg_class.oid	如果为外键，则指向参照的表，否则为0。
confupdtype	char	外键更新动作代码。
confdeltype	char	外键删除动作代码。
confmatchtype	char	外键匹配类型。
conkey	int2[]	pg_attribute.attnum	如果是表约束，则是约束控制的字段列表。
confkey	int2[]	pg_attribute.attnum	如果是外键，则是参照字段的列表。
conbin	text	如果是检查约束，则表示表达式的内部形式。
consrc	text	如果是检查约束，则是表达式的人可读的形式。

五、pg_tablespace:

该系统表存储表空间的信息。注意：表可以放在特定的表空间里，以帮助管理磁盘布局和解决IO瓶颈。

名字	类型	引用	描述
spcname	name	表空间名称。
spcowner	oid	pg_authid.oid	表空间的所有者，通常是创建它的角色。
spclocation	text	表空间的位置(目录路径)。
spcacl	aclitem[]	访问权限。

六、pg_namespace:

该系统表存储名字空间(模式)。

名字	类型	引用	描述
nspname	name	名字空间(模式)的名称。
nspowner	oid	pg_authid.oid	名字空间(模式)的所有者
nspacl	aclitem[]	访问权限。

七、pg_database:

该系统表存储数据库的信息。和大多数系统表不同的是，在一个集群里该表是所有数据库共享的，即每个集群只有一份pg_database拷贝，而不是每个数据库一份。

名字	类型	引用	描述
datname	name	数据库名称。
datdba	oid	pg_authid.oid	数据库所有者，通常为创建该数据库的角色。
encoding	int4	数据库的字符编码方式。
datistemplate	bool	如果为真，此数据库可以用于CREATE DATABASE TEMPLATE子句，把新数据库创建为此数据库的克隆。
datallowconn	bool	如果为假，则没有人可以联接到这个数据库。
datlastsysoid	oid	数据库里最后一个系统OID，此值对pg_dump特别有用。
datvacuumxid	xid
datfrozenxid	xid
dattablespace	text	pg_tablespace.oid	该数据库的缺省表空间。在这个数据库里，所有pg_class.reltablespace为零的表都将保存在这个表空间里，特别要指出的是，所有非共享的系统表也都存放在这里。
datconfig	text[]	运行时配置变量的会话缺省值。
datacl	aclitem[]	访问权限。

八、pg_index:

该系统表存储关于索引的一部分信息。其它的信息大多数存储在pg_class。

名字	类型	引用	描述
indexrelid	oid	pg_class.oid	该索引在pg_class里的记录的OID。
indrelid	oid	pg_class.oid	索引所在表在pg_class里的记录的OID。
indnatts	int2	索引中的字段数量(拷贝的pg_class.relnatts)。
indisunique	bool	如果为真，该索引是唯一索引。
indisprimary	bool	如果为真，该索引为该表的主键。
indisclustered	bool	如果为真，那么该表在这个索引上建了簇。
indkey	int2vector	pg_attribute.attnum	该数组的元素数量为indnatts，数组元素值表示建立这个索引时所依赖的字段编号，如1 3，表示第一个字段和第三个字段构成这个索引的键值。如果为0，则表示是表达式索引，而不是基于简单字段的索引。
indclass	oidvector	pg_opclass.oid	对于构成索引键值的每个字段，这个字段都包含一个指向所使用的操作符表的OID。
indexprs	text	表达式树用于那些非简单字段引用的索引属性。它是一个列表，在indkey里面的每个零条目一个元素。如果所有索引属性都是简单的引用，则为空。
indpred	text	部分索引断言的表达式树。如果不是部分索引，则是空字串。

PostgreSQL 16 源码安装

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：PostgreSQL 16 源码安装

空闲中尝试在linux 8的版本上,源码安装PostgreSQL 16，参考文章:PostgreSQL16.2源码部署教程

[root@xifenfei tmp]# cat /etc/redhat-release 
Red Hat Enterprise Linux release 8.7 (Ootpa)
[root@xifenfei tmp]#  uname -osr
Linux 5.15.0-3.60.5.1.el8uek.x86_64 GNU/Linux

下载pg源码程序:PostgreSQL 16源码下载
linux配置

--关闭selinux
sed -i "s/SELINUX=enforcing/SELINUX=disabled/" /etc/selinux/config
setenforce 0

--安装相应的包
yum -y install readline readline-devel zlib zlib-devel gettext \
 gettext-devel openssl openssl-devel pam pam-devel libxml2 \
libxml2-devel libxslt libxslt-devel perl perl-devel tcl-devel \
libuuid-devel gcc gcc-c++ make flex bison perl-ExtUtils*  libicu  libicu-devel

--修改内核参数
cat>>/etc/sysctl.conf<<EOF
fs.file-max = 76724200
kernel.sem = 10000 10240000 10000 1024
kernel.shmmni = 4096
kernel.shmall = 253702
kernel.shmmax = 1039163392
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 40960000
vm.dirty_ratio=20
vm.dirty_background_ratio=3
vm.dirty_writeback_centisecs=100
vm.dirty_expire_centisecs=500
vm.swappiness=10
vm.min_free_kbytes=524288
vm.swappiness=0
vm.overcommit_memory=2
vm.overcommit_ratio=75
net.ipv4.ip_local_port_range = 10000 65535
EOF
sysctl -p

--创建组和用户
groupadd postgres -g 3000
useradd postgres -g 3000 -u 3000

--用户 limits 配置
cat>>/etc/security/limits.conf<<EOF
postgres soft nofile 1048576
postgres hard nofile 1048576
postgres soft nproc 131072
postgres hard nproc 131072
postgres soft stack 10240
postgres hard stack 32768
postgres soft core 6291456
postgres hard core 6291456
EOF

--创建相关目录和权限
mkdir -p /pg/database/server
mkdir -p /pg/database/data
mkdir -p /pg/database/wal
mkdir -p /pg/database/archive
chown -R postgres:postgres /pg -R
chmod 0775 /pg
chmod 0700 /pg/database/data

--设置环境变量
vi /home/postgres/.bash_profile
export PGPORT=5432
export PGUSER=postgres
export PGHOME=/pg/database/server
export PGDATA=/pg/database/data
export PATH=$PGHOME/bin:$PATH

--编译pg软件
su - postgres
tar xzvf postgresql-16.2.tar.gz
cd /tmp/postgresql-16.2
./configure --prefix=/pg/database/server --with-pgport=5432
make
make install

--确认软件安装成功（检查编译日志和测试如下命令）
[postgres@xifenfei ~]$ postgres --version
postgres (PostgreSQL) 16.2

创建数据库

[postgres@xifenfei ~]$  /pg/database/server/bin/initdb -D/pg/database/data\
 -X/pg/database/wal -EUTF8 -Upostgres -W
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 text search configuration will be set to "english".

Data page checksums are disabled.

Enter new superuser password: 
Enter it again: 

fixing permissions on existing directory /pg/database/data ... ok
fixing permissions on existing directory /pg/database/wal ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/New_York
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
initdb: hint: You can change this by editing pg_hba.conf or using the option -A,
 or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /pg/database/server/bin/pg_ctl -D /pg/database/data -l logfile start

启动数据库

[postgres@xifenfei ~]$ /pg/database/server/bin/pg_ctl -D /pg/database/data -l logfile start
waiting for server to start.... done
server started
[postgres@xifenfei ~]$ psql
psql (16.2)
Type "help" for help.

postgres=# \d pg_class
                     Table "pg_catalog.pg_class"
       Column        |     Type     | Collation | Nullable | Default 
---------------------+--------------+-----------+----------+---------
 oid                 | oid          |           | not null | 
 relname             | name         |           | not null | 
 relnamespace        | oid          |           | not null | 
 reltype             | oid          |           | not null | 
 reloftype           | oid          |           | not null | 
 relowner            | oid          |           | not null | 
 relam               | oid          |           | not null | 
 relfilenode         | oid          |           | not null | 
 reltablespace       | oid          |           | not null | 
 relpages            | integer      |           | not null | 
 reltuples           | real         |           | not null | 
 relallvisible       | integer      |           | not null | 
 reltoastrelid       | oid          |           | not null | 
 relhasindex         | boolean      |           | not null | 
 relisshared         | boolean      |           | not null | 
 relpersistence      | "char"       |           | not null | 
 relkind             | "char"       |           | not null | 
 relnatts            | smallint     |           | not null | 
 relchecks           | smallint     |           | not null | 
 relhasrules         | boolean      |           | not null | 
 relhastriggers      | boolean      |           | not null | 
 relhassubclass      | boolean      |           | not null | 
 relrowsecurity      | boolean      |           | not null | 
 relforcerowsecurity | boolean      |           | not null | 
 relispopulated      | boolean      |           | not null | 
 relreplident        | "char"       |           | not null | 
 relispartition      | boolean      |           | not null | 
 relrewrite          | oid          |           | not null | 
 relfrozenxid        | xid          |           | not null | 
 relminmxid          | xid          |           | not null | 
 relacl              | aclitem[]    |           |          | 
 reloptions          | text[]       | C         |          | 
 relpartbound        | pg_node_tree | C         |          | 
Indexes:
    "pg_class_oid_index" PRIMARY KEY, btree (oid)
    "pg_class_relname_nsp_index" UNIQUE CONSTRAINT, btree (relname, relnamespace)
    "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)

配置开机自动启动

[root@xifenfei tmp]# cp /tmp/postgresql-16.2/contrib/start-scripts/linux /etc/init.d/PostgreSQL
[root@xifenfei tmp]# vi /etc/init.d/PostgreSQL 
--修改如下值
prefix=/pg/database/server
PGDATA="/pg/database/data"
[root@xifenfei tmp]# chkconfig --add PostgreSQL
[root@xifenfei tmp]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

PostgreSQL      0:off   1:off   2:on    3:on    4:on    5:on    6:off

PostgreSQL恢复系列:wal日志丢失恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：PostgreSQL恢复系列:wal日志丢失恢复

WAL是Write Ahead Log的简写，和oracle的redo日志类似，存放在$PGDATA/pg_xlog中，10版本以后在$PGDATA/pg_wal目录.在oracle数据库中,如果redo丢失,分为active/current和inactive的redo,分别有不同的处理方式,对于oracle需要实例恢复的redo丢失，需要屏蔽数据库一致性，强制打开数据库,对于PG数据库这部分日志丢失该如何恢复,主要是通过pg_resetwal/pg_resetxlog（10以前版本）命令来实现，这里通过一个测试来验证
创建测试表并强制kill数据库

-bash-4.2$ psql
psql (14.3)
Type "help" for help.

postgres=# create table t_xifenfei as select * from pg_database;
SELECT 4
postgres=# select count(1) from t_xifenfei;
 count 
-------
     4
(1 row)

postgres=# \q
-bash-4.2$ ps -ef|grep post
root       1819      1  0 May28 ?        00:00:00 /usr/libexec/postfix/master -w
postfix    1838   1819  0 May28 ?        00:00:00 qmgr -l -t unix -u
postgres  11102      1  0 05:49 ?        00:00:00 /usr/pgsql-14/bin/postgres -D /var/lib/pgsql/14/data
postgres  11103  11102  0 05:49 ?        00:00:00 postgres: logger 
postgres  11105  11102  0 05:49 ?        00:00:00 postgres: checkpointer 
postgres  11106  11102  0 05:49 ?        00:00:00 postgres: background writer 
postgres  11107  11102  0 05:49 ?        00:00:00 postgres: walwriter 
postgres  11108  11102  0 05:49 ?        00:00:00 postgres: autovacuum launcher 
postgres  11109  11102  0 05:49 ?        00:00:01 postgres: stats collector 
postgres  11110  11102  0 05:49 ?        00:00:00 postgres: logical replication launcher 
root      22743  22300  0 18:26 pts/3    00:00:00 su - postgres
postgres  22744  22743  0 18:26 pts/3    00:00:00 -bash
postgres  22937  22744  0 18:28 pts/3    00:00:00 psql
postgres  22938  11102  0 18:28 ?        00:00:00 postgres: postgres postgres [local] idle
postfix   32623   1819  0 21:10 ?        00:00:00 pickup -l -t unix -u
root      33032  32912  0 21:15 pts/2    00:00:00 su - postgres
postgres  33033  33032  0 21:15 pts/2    00:00:00 -bash
postgres  35210  33033  0 21:51 pts/2    00:00:00 ps -ef
postgres  35211  33033  0 21:51 pts/2    00:00:00 grep --color=auto post
-bash-4.2$ kill -9 11102

删除wal日志

-bash-4.2$ pwd
/var/lib/pgsql/14/data/pg_wal
-bash-4.2$ ls -ltr
total 311296
drwx------. 2 postgres postgres        6 May 24 02:20 archive_status
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000014
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000015
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000016
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000017
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000018
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000019
-rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001A
-rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001B
-rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001C
-rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001D
-rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001E
-rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001F
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000020
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000021
-rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000022
-rw-------. 1 postgres postgres 16777216 May 28 21:30 000000010000000000000023
-rw-------. 1 postgres postgres 16777216 May 28 21:30 000000010000000000000024
-rw-------. 1 postgres postgres 16777216 May 28 21:30 000000010000000000000025
-rw-------. 1 postgres postgres 16777216 May 29 21:51 000000010000000000000013
-bash-4.2$ rm -rf 0000000100000000000000*
-bash-4.2$ ls
archive_status

查询当时数据库需要的最小wal记录

-bash-4.2$ pg_controldata 
pg_control version number:            1300
Catalog version number:               202107181
Database system identifier:           7100998319216817119
Database cluster state:               in production
pg_control last modified:             Sat 28 May 2022 09:36:11 PM CST
Latest checkpoint location:           0/13692F80
Latest checkpoint's REDO location:    0/13692F48
Latest checkpoint's REDO WAL file:    000000010000000000000013   <===需要的记录
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:17824
Latest checkpoint's NextOID:          32769
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        727
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  17824
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Sat 28 May 2022 09:31:41 PM CST

尝试启动PG

-bash-4.2$ pg_ctl start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2022-05-29 21:52:22.926 CST [35270] LOG:  
redirecting log output to logging collector process
2022-05-29 21:52:22.926 CST [35270] HINT:  Future log output will appear in directory "log".
. stopped waiting
pg_ctl: could not start server
Examine the log output.

启动pg失败,查看日志记录

2022-05-29 21:52:22.926 CST [35270] LOG:  starting PostgreSQL 14.3 on x86_64-pc-linux-gnu, 
            compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2022-05-29 21:52:22.927 CST [35270] LOG:  listening on IPv6 address "::1", port 5432
2022-05-29 21:52:22.927 CST [35270] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-05-29 21:52:22.929 CST [35270] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-05-29 21:52:22.931 CST [35270] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-05-29 21:52:22.936 CST [35272] LOG:  database system was interrupted; last known up at 2022-05-28 21:36:11 CST
2022-05-29 21:52:23.049 CST [35272] LOG:  invalid primary checkpoint record
2022-05-29 21:52:23.049 CST [35272] PANIC:  could not locate a valid checkpoint record
2022-05-29 21:52:24.211 CST [35270] LOG:  startup process (PID 35272) was terminated by signal 6: Aborted
2022-05-29 21:52:24.211 CST [35270] LOG:  aborting startup due to startup process failure
2022-05-29 21:52:24.218 CST [35270] LOG:  database system is shut down

错误比较明显,无法定位到有效的checkpoint记录,在oracle里面的意思可以理解为无法进行实例恢复,pg启动失败

重设wal
由于数据库为不一致状态,需要使用-f进行强制重设

-bash-4.2$ pg_resetwal $PGDATA
The database server was not shut down cleanly.
Resetting the write-ahead log might cause data to be lost.
If you want to proceed anyway, use -f to force reset.
-bash-4.2$ pg_resetwal -f $PGDATA
Write-ahead log reset

启动PG成功

-bash-4.2$ pg_ctl start -D $PGDATA
waiting for server to start....2022-05-29 22:01:02.647 CST [37178] LOG:  
redirecting log output to logging collector process
2022-05-29 22:01:02.647 CST [37178] HINT:  Future log output will appear in directory "log".
 done
server started

日志记录

2022-05-29 22:01:02.647 CST [37178] LOG:  starting PostgreSQL 14.3 on x86_64-pc-linux-gnu, 
                    compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2022-05-29 22:01:02.648 CST [37178] LOG:  listening on IPv6 address "::1", port 5432
2022-05-29 22:01:02.648 CST [37178] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-05-29 22:01:02.649 CST [37178] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-05-29 22:01:02.651 CST [37178] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-05-29 22:01:02.653 CST [37180] LOG:  database system was shut down at 2022-05-29 22:00:47 CST
2022-05-29 22:01:02.661 CST [37178] LOG:  database system is ready to accept connections

查看wal日志,产生新记录

-bash-4.2$ pwd
/var/lib/pgsql/14/data/pg_wal
-bash-4.2$ ls -ltr
total 16384
drwx------. 2 postgres postgres        6 May 24 02:20 archive_status
-rw-------. 1 postgres postgres 16777216 May 29 22:01 000000010000000000000014

验证刚刚创建测试表

-bash-4.2$ psql
psql (14.3)
Type "help" for help.

postgres=# select count(1) from t_xifenfei;
ERROR:  relation "t_xifenfei" does not exist
LINE 1: select count(1) from t_xifenfei;
                             ^

由于需要进行实例恢复的wal日志丢失导致这表记录也丢失.由此可见这类操作可能导致数据丢失风险,对于生产环境,需要慎重,

sql server数据库比特币加密勒索恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：sql server数据库比特币加密勒索恢复

对于GANDCRAB病毒加密的Oracle数据库，我们可以提供较为完美的恢复见(GANDCRAB V5.0.4 比特币加密oracle数据库恢复和GANDCRAB升级版Oracle恢复,对于被GANDCRAB加密的SQL Server数据库近期我们对其进行了一些研究，现在也可以比较好的恢复.
gandcrab5.2-sql-server

而且如果找黑客解密需要费用为10w美元,客户无法接受该费用,系统中主要的是sql server数据库被加密,客户有几个月之前的备份,但是数据丢失严重,无法承受相关损失,请求我们给予恢复支持.通过我们一系列恢复之后,实现较为完美恢复该数据库
gandcrab5.2-sql-server1

gandcrab5.2-sql-server2

如果您的sql server数据库不幸被比特币加密,可以随时联系我们,提供数据库级别恢复支持
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com

分类目录归档：PostgreSQL