分类目录归档：PostgreSQL

PostgreSQL恢复系列:pg_filedump恢复字典构造

发表于 2024 年 4 月 15 日 由 惜分飞

pg_filedump是在pg数据库极端情况下直接解析数据库文件的利器,但是由于是开源软件,本身难以实现批量处理,通过对底层基表分析,可以实现批量处理功能
分析PostgreSQL库中数据库信息

--数据库查询结果
postgres=# select oid,datname,datdba,dattablespace from pg_database;
  oid  |   datname   | datdba | dattablespace 
-------+-------------+--------+---------------
 14187 | postgres    |     10 |          1663
 16403 | db_xff      |     10 |          1663
     1 | template1   |     10 |          1663
 14186 | template0   |     10 |          1663
 16407 | db_xifenfei |  16405 |         16406
(5 rows)

--通过dump 该文件解析数据
<Data> -----
 Item   1 -- Length:    0  Offset:    5 (0x0005)  Flags: REDIRECT
 Item   2 -- Length:    0  Offset:    6 (0x0006)  Flags: REDIRECT
 Item   3 -- Length:  260  Offset: 7320 (0x1c98)  Flags: NORMAL
COPY: 14187     postgres
 Item   4 -- Length:  260  Offset: 7056 (0x1b90)  Flags: NORMAL
COPY: 16403     db_xff
 Item   5 -- Length:  297  Offset: 7888 (0x1ed0)  Flags: NORMAL
COPY: 1 template1
 Item   6 -- Length:  297  Offset: 7584 (0x1da0)  Flags: NORMAL
COPY: 14186     template0
 Item   7 -- Length:  260  Offset: 6792 (0x1a88)  Flags: NORMAL
COPY: 16407     db_xifenfei

分析PostgreSQL 表空间信息

--sql查询表空间信息
postgres=# select * from pg_tablespace;
  oid  |   spcname    | spcowner | spcacl | spcoptions 
-------+--------------+----------+--------+------------
  1663 | pg_default   |       10 |        | 
  1664 | pg_global    |       10 |        | 
 16406 | tbs_xifenfei |    16405 |        | 
(3 rows)


--通过dump 该文件解析数据
<Data> -----
 Item   1 -- Length:   96  Offset: 8096 (0x1fa0)  Flags: NORMAL
COPY: 1663      pg_default
 Item   2 -- Length:   96  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 1664      pg_global
 Item   3 -- Length:   96  Offset: 7904 (0x1ee0)  Flags: NORMAL
COPY: 16406     tbs_xifenfei

分析PostgreSQL 对象id、name、path对应关系

--对象信息查询
postgres=# select oid ,relname,relnamespace,reltype,reloftype,relowner,relam,relfilenode,
           reltablespace from pg_class where relname like 't_t%' or relname like 't_x%';
  oid  |  relname   | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace 
-------+------------+--------------+---------+-----------+----------+-------+-------------+---------------
 16387 | t_xifenfei |         2200 |   16389 |         0 |       10 |     2 |       16390 |             0
 16391 | t_xff      |         2200 |   16393 |         0 |       10 |     2 |       16391 |             0
 16394 | t_xff2     |         2200 |   16396 |         0 |       10 |     2 |       16394 |             0
 16397 | t_xff3     |         2200 |   16399 |         0 |       10 |     2 |       16397 |             0
 16400 | t_xff4     |         2200 |   16402 |         0 |       10 |     2 |       16400 |             0
 16408 | t_tbs      |         2200 |   16410 |         0 |       10 |     2 |       16408 |         16406
(6 rows)

--通过dump 该文件解析数据（显示部分）
COPY: 16394     t_xff2  2200    16396   0       10      2       16394   0       0       0.000000000000  0       0       f       f       p       r
 Item  29 -- Length:    0  Offset:   31 (0x001f)  Flags: REDIRECT
 Item  30 -- Length:  172  Offset: 2592 (0x0a20)  Flags: NORMAL
COPY: 16397     t_xff3  2200    16399   0       10      2       16397   0       0       0.000000000000  0       0       f       f       p       r
 Item  31 -- Length:  205  Offset: 3376 (0x0d30)  Flags: NORMAL
COPY: 12093     pg_shadow       11      12094   0       10      0       0       0       0       0.000000000000  0       0       f       f       p       v
 Item  32 -- Length:  172  Offset: 2416 (0x0970)  Flags: NORMAL
COPY: 16400     t_xff4  2200    16402   0       10      2       16400   0       0       0.000000000000  0       0       f       f       p       r

这个里面获取到pg_class.reltablespace是表空间的id值,根据自定义表空间的规则:在pgdata/pg_tblspc创建link指向创建表空间时候的文件夹路径

-bash-4.2$ pwd
/var/lib/pgsql/12/data/pg_tblspc
-bash-4.2$ ls -ltr
total 0
lrwxrwxrwx 1 postgres postgres 30 Apr 15 20:13 16406 -> /var/lib/pgsql/12/data/tbs_xff

结合上述的pg_database,pg_tablespace,pg_class信息,可以获取到每个表对应实际的存储路径
分析PostgreSQL 模式信息

--sql查询模式信息
postgres=# select * from pg_namespace;
  oid  |      nspname       | nspowner |               nspacl                
-------+--------------------+----------+-------------------------------------
    99 | pg_toast           |       10 | 
 12314 | pg_temp_1          |       10 | 
 12315 | pg_toast_temp_1    |       10 | 
    11 | pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
  2200 | public             |       10 | {postgres=UC/postgres,=UC/postgres}
 13887 | information_schema |       10 | {postgres=UC/postgres,=U/postgres}
 16404 | u_xifenfei         |       10 | 
(7 rows)


--通过dump 该文件解析数据
<Data> -----
 Item   1 -- Length:    0  Offset:    6 (0x0006)  Flags: REDIRECT
 Item   2 -- Length:   96  Offset: 8096 (0x1fa0)  Flags: NORMAL
COPY: 99        pg_toast
 Item   3 -- Length:    0  Offset:    7 (0x0007)  Flags: REDIRECT
 Item   4 -- Length:   96  Offset: 8000 (0x1f40)  Flags: NORMAL
COPY: 12314     pg_temp_1
 Item   5 -- Length:   96  Offset: 7904 (0x1ee0)  Flags: NORMAL
COPY: 12315     pg_toast_temp_1
 Item   6 -- Length:  141  Offset: 7760 (0x1e50)  Flags: NORMAL
COPY: 11        pg_catalog
 Item   7 -- Length:  141  Offset: 7616 (0x1dc0)  Flags: NORMAL
COPY: 2200      public
 Item   8 -- Length:    0  Offset:    9 (0x0009)  Flags: REDIRECT
 Item   9 -- Length:  141  Offset: 7472 (0x1d30)  Flags: NORMAL
COPY: 13887     information_schema
 Item  10 -- Length:   96  Offset: 7376 (0x1cd0)  Flags: NORMAL
COPY: 16404     u_xifenfei

通过pg_namespace，pg_class信息，可以获取到对象所属的模式关系，基于上述汇总,可以获取到某个模式下面,所有表id和实际存储路径,现在使用pg_filedump进行恢复,还缺少表的列类型信息,通过pg_type和pg_attribute来获取。

获取PostgreSQL表的列名称和类型[编号]信息

--sql查询列信息
postgres=# \d t_tbs
               Table "public.t_tbs"
  Column  | Type | Collation | Nullable | Default 
----------+------+-----------+----------+---------
 oid      | oid  |           |          | 
 spcname  | name |           |          | 
 spcowner | oid  |           |          | 
Tablespace: "tbs_xifenfei"

postgres=# select attrelid,attname,atttypid,attstattarget,attlen,attnum from pg_attribute where attrelid=16408;
 attrelid | attname  | atttypid | attstattarget | attlen | attnum 
----------+----------+----------+---------------+--------+--------
    16408 | tableoid |       26 |             0 |      4 |     -6
    16408 | cmax     |       29 |             0 |      4 |     -5
    16408 | xmax     |       28 |             0 |      4 |     -4
    16408 | cmin     |       29 |             0 |      4 |     -3
    16408 | xmin     |       28 |             0 |      4 |     -2
    16408 | ctid     |       27 |             0 |      6 |     -1
    16408 | oid      |       26 |            -1 |      4 |      1
    16408 | spcname  |       19 |            -1 |     64 |      2
    16408 | spcowner |       26 |            -1 |      4 |      3
(9 rows)

--dump 内容（截取部分）
 Item  11 -- Length:  144  Offset: 1424 (0x0590)  Flags: NORMAL
COPY: 16408     oid     26      -1      4       1
 Item  12 -- Length:  144  Offset: 1280 (0x0500)  Flags: NORMAL
COPY: 16408     spcname 19      -1      64      2
 Item  13 -- Length:  144  Offset: 1136 (0x0470)  Flags: NORMAL
COPY: 16408     spcowner        26      -1      4       3
 Item  14 -- Length:  144  Offset:  992 (0x03e0)  Flags: NORMAL
COPY: 16408     ctid    27      0       6       -1
 Item  15 -- Length:  144  Offset:  848 (0x0350)  Flags: NORMAL
COPY: 16408     xmin    28      0       4       -2
 Item  16 -- Length:  144  Offset:  704 (0x02c0)  Flags: NORMAL
COPY: 16408     cmin    29      0       4       -3
 Item  17 -- Length:  144  Offset:  560 (0x0230)  Flags: NORMAL
COPY: 16408     xmax    28      0       4       -4
 Item  18 -- Length:  144  Offset:  416 (0x01a0)  Flags: NORMAL
COPY: 16408     cmax    29      0       4       -5

PostgreSQL获取类型编号和实际类型名称对应关系

--查询类型编号和实际类型关系
postgres=# select oid,typname from pg_type;
  oid  |                typname                
-------+---------------------------------------
    16 | bool
    17 | bytea
    18 | char
    19 | name
    20 | int8
    21 | int2
    22 | int2vector
    23 | int4
    24 | regproc
    25 | text
    26 | oid
    27 | tid
    28 | xid
    29 | cid
……

--dump 内容（截取部分）
 Item   1 -- Length:  176  Offset: 8016 (0x1f50)  Flags: NORMAL
COPY: 16        bool
 Item   2 -- Length:  176  Offset: 7840 (0x1ea0)  Flags: NORMAL
COPY: 17        bytea
 Item   3 -- Length:  176  Offset: 7664 (0x1df0)  Flags: NORMAL
COPY: 18        char
 Item   4 -- Length:  176  Offset: 7488 (0x1d40)  Flags: NORMAL
COPY: 19        name
 Item   5 -- Length:  176  Offset: 7312 (0x1c90)  Flags: NORMAL
COPY: 20        int8
 Item   6 -- Length:  176  Offset: 7136 (0x1be0)  Flags: NORMAL
COPY: 21        int2
 Item   7 -- Length:  176  Offset: 6960 (0x1b30)  Flags: NORMAL
COPY: 22        int2vector
 Item   8 -- Length:  176  Offset: 6784 (0x1a80)  Flags: NORMAL
COPY: 23        int4
 Item   9 -- Length:  176  Offset: 6608 (0x19d0)  Flags: NORMAL
COPY: 24        regproc
 Item  10 -- Length:  176  Offset: 6432 (0x1920)  Flags: NORMAL
COPY: 25        text
 Item  11 -- Length:  176  Offset: 6256 (0x1870)  Flags: NORMAL
COPY: 26        oid
 Item  12 -- Length:  176  Offset: 6080 (0x17c0)  Flags: NORMAL
COPY: 27        tid
 Item  13 -- Length:  176  Offset: 5904 (0x1710)  Flags: NORMAL
COPY: 28        xid
 Item  14 -- Length:  176  Offset: 5728 (0x1660)  Flags: NORMAL
COPY: 29        cid
 Item  15 -- Length:  176  Offset: 5552 (0x15b0)  Flags: NORMAL
COPY: 30        oidvector

通过pg_class、pg_type和pg_attribute可以获取对象的表的列名称，数据类型等信息。通过以上几个对象，即可获取到pg_filedmp处理所需要的所有信息,然后可以实现批量处理

发表在 PostgreSQL恢复 | 标签为 , , | 评论关闭

PostgreSQL 16 源码安装

发表于 2024 年 4 月 14 日 由 惜分飞

空闲中尝试在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 | 标签为 , | 评论关闭

PostgreSQL恢复系列:pg_filedump基本使用

发表于 2022 年 6 月 3 日 由 惜分飞

当PostgreSQL遇到重大故障,使用各种方法都无法直接启动数据库，可以考虑使用类似oracle dul工具,直接离线方式读取文件进行恢复.这个工具为pg_filedump
pg_filedump安装

[root@xifenfei ~]# yum install pg_filedump_14.x86_64
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package pg_filedump_14.x86_64 0:14.1-1.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

======================================================================================================================
 Package                            Arch      Version                 Repository                                 Size
======================================================================================================================
Installing:                                 
 pg_filedump_14                     x86_64    14.1-1.rhel7            pgdg14                                     43 k
                                            
Transaction Summary                         
======================================================================================================================
Install  1 Package

Total download size: 43 k
Installed size: 81 k
Is this ok [y/d/N]: y
Downloading packages:
pg_filedump_14-14.1-1.rhel7.x86_64.rpm                                              |  43 kB  00:00:02     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : pg_filedump_14-14.1-1.rhel7.x86_64                                                      1/1 
  Verifying  : pg_filedump_14-14.1-1.rhel7.x86_64                                                      1/1 

Installed:
  pg_filedump_14.x86_64 0:14.1-1.rhel7                                                                     

Complete!
-bash-4.2$ pg_filedump 

Version 14.1 (for PostgreSQL 8.x .. 14.x)
Copyright (c) 2002-2010 Red Hat, Inc.
Copyright (c) 2011-2022, PostgreSQL Global Development Group

Usage: pg_filedump [-abcdfhikxy] [-R startblock [endblock]] [-D attrlist] [-S blocksize] [-s segsize] [-n segnumber] file

Display formatted contents of a PostgreSQL heap/index/control file
Defaults are: relative addressing, range of the entire file, block
               size as listed on block 0 in the file

The following options are valid for heap and index files:
  -a  Display absolute addresses when formatting (Block header
      information is always block relative)
  -b  Display binary block images within a range (Option will turn
      off all formatting options)
  -d  Display formatted block content dump (Option will turn off
      all other formatting options)
  -D  Decode tuples using given comma separated list of types
      Supported types:
        bigint bigserial bool char charN date float float4 float8 int
        json macaddr name numeric oid real serial smallint smallserial text
        time timestamp timestamptz timetz uuid varchar varcharN xid xml
      ~ ignores all attributes left in a tuple
  -f  Display formatted block content dump along with interpretation
  -h  Display this information
  -i  Display interpreted item details
  -k  Verify block checksums
  -o  Do not dump old values.
  -R  Display specific block ranges within the file (Blocks are
      indexed from 0)
        [startblock]: block to start at
        [endblock]: block to end at
      A startblock without an endblock will format the single block
  -s  Force segment size to [segsize]
  -t  Dump TOAST files
  -v  Ouput additional information about TOAST relations
  -n  Force segment number to [segnumber]
  -S  Force block size to [blocksize]
  -x  Force interpreted formatting of block items as index items
  -y  Force interpreted formatting of block items as heap items

The following options are valid for control files:
  -c  Interpret the file listed as a control file
  -f  Display formatted content dump along with interpretation
  -S  Force block size to [blocksize]
Additional functions:
  -m  Interpret file as pg_filenode.map file and print contents (all
      other options will be ignored)

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

创建测试表

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

postgres=# create table pg_xifenfei(id int,name varchar(100));
CREATE TABLE
postgres=# insert into pg_xifenfei values(1,'www.xifenfei.com');
INSERT 0 1
postgres=# insert into pg_xifenfei values(2,'xienfei_pg_recovery');
INSERT 0 1
postgres=# select * from pg_xifenfei;
 id |        name         
----+---------------------
  1 | www.xifenfei.com
  2 | xienfei_pg_recovery
(2 rows)

postgres=#

pg_filedump恢复数据

-bash-4.2$ pg_filedump /var/lib/pgsql/14/data/base/14487/16384

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: /var/lib/pgsql/14/data/base/14487/16384
* Options used: None
*******************************************************************

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      32 (0x0020)
 Block: Size 8192  Version    4            Upper    8096 (0x1fa0)
 LSN:  logid      0 recoff 0x16299cf0      Special  8192 (0x2000)
 Items:    2                      Free Space: 8064
 Checksum: 0x0000  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 32

<Data> -----
 Item   1 -- Length:   45  Offset: 8144 (0x1fd0)  Flags: NORMAL
 Item   2 -- Length:   48  Offset: 8096 (0x1fa0)  Flags: NORMAL


*** End of File Encountered. Last Block Read: 0 ***

-bash-4.2$ pg_filedump -D int,charn /var/lib/pgsql/14/data/base/14487/16384|grep COPY
COPY: 1 www.xifenfei.com
COPY: 2 xienfei_pg_recovery
-bash-4.2$ pg_filedump -D int,charn /var/lib/pgsql/14/data/base/14487/16384|grep COPY
> |awk '{$1=null;print $0}'>/tmp/pg_xifenfei_rec
-bash-4.2$ sed -i 's/^[ ]*//g' /tmp/pg_xifenfei_rec

导入数据验证

postgres=# truncate table pg_xifenfei;
TRUNCATE TABLE
postgres=# select * from pg_xifenfei;
 id | name 
----+------
(0 rows)
postgres=# copy pg_xifenfei from '/tmp/pg_xifenfei_rec'(DELIMITER ' ');
COPY 2
postgres=# select * from pg_xifenfei;
 id |        name         
----+---------------------
  1 | www.xifenfei.com
  2 | xienfei_pg_recovery
(2 rows)

通过上述简单测试证明,在PG数据库出现极端情况下，可以使用该方法进行最后的数据恢复,减少因为数据丢失带来的损失.

发表在 PostgreSQL恢复 | 标签为 , , | 评论关闭