暂无图片
暂无图片
5
暂无图片
暂无图片
暂无图片
首页 / 13_PostgreSQL备份与恢复

13_PostgreSQL备份与恢复

原创 惠星星 All China Database Union 2022-12-05
1152

13_PostgreSQL备份与恢复

内容概述

任何系统都有崩溃的可能，通过备份和恢复来保护数据，避免数据丢失，在发生灾难或人为误操作的情况下，能够进行恢复是DBA的日常最重要的工作。正在使用的WAL文件在未归档前如果被损坏，恢复时存在数据丢失的风险，如果其它文件都损坏，但基础备份和正在使用的WAL文件及备份开始后的归档都保留完整，则可以进行完全恢复无数据丢失。

开启WAL归档

### 1. wal_level解释
wal_level决定向WAL写入多少信息。默认值为“replica”。
minimal：removes all logging except the information required to recover from a crash or immediate shutdown.
replica： which writes enough data to support WAL archiving and replication, including running read-only queries on a standby server. 
logical：adds information necessary to support logical decoding. 
#### NOTE：
This parameter can only be set at server start.
### 2. 创建归档目录
mkdir -p /u01/pg14/{data,backups,pgsql,archive_wal}
chown -R postgres:dba /u01/pg14

### 3. 修改wal_level参数
alter system set wal_level='replica';

### 4. archive_mode 解释
When archive_mode is enabled, completed WAL segments are sent to archive storage by setting archive_command or archive_library.
off：关闭archive归档
on： 开启archive归档
always：when set to always the WAL archiver is enabled also during archive recovery or standby mode. In always mode, all files restored from the archive or streamed with streaming replication will be archived (again). 
#### NOTE：
This parameter can only be set at server start. archive_mode cannot be enabled when wal_level is set to minimal.

### 5. 修改archive_mode参数
alter system set archive_mode='on';

### 6. 修改archive_command参数
alter system set archive_command='cp %p /u01/pg14/archive_wal/%f';
#### NOTE:
"%P"表示将要归档的WAL文件的包含完整路径信息的文件名。
"%f"代表不包含路径信息的WAL文件的文件名。

### 7. 重启生效
pg_ctl restart

使用pg_basebackup创建基础备份

[postgres@enmo pgbackup]$ pg_basebackup -Ft -Pv -Xf -z -Z5 -p 5414 -D /u01/pg14/backups/
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
42628/42628 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/2000138
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
[postgres@enmo pgbackup]$ ls
backup_manifest  base.tar.gz
[postgres@enmo pgbackup]$

#### NOTE:
pg_basebackup详细解释请参考官方文档：https://www.postgresql.org/docs/15/app-pgbasebackup.html

恢复到最近时间点-PGDATA损坏恢复-未归档WAL丢失

### 1. 创建测试表
create table tbl(
id serial primary key,
ival int not null default 0,
description text,
created_time timestamptz not null default now()
);
insert into tbl(ival) values(1);
select pg_switch_wal();
insert into tbl(ival) values(2);
select * from tbl;
postgres=# \q
pg_ctl stop -m immediate
### 2. 删除 $PGDATA
rm -rf $PGDATA

### 3. 创建数据目录
mkdir -p /u01/pg14/data
chmod 0700 /u01/pg14/data

### 4. 解压备份集
tar -xvf /u01/pg14/backups/base.tar.gz -C /u01/pg14/data/


### 5. 配置restore_command参数并创建recovery.signal文件
cat >> $PGDATA/postgresql.conf <<-EOF
restore_command = 'cp /u01/pg14/archive_wal/%f %p'
EOF

touch $PGDATA/recovery.signal

### 6. 启动数据库开始恢复
[postgres@enmo pg14]$ pg_ctl start
waiting for server to start....2022-12-03 03:31:59.175 CST [27324] LOG:  redirecting log output to logging collector process
2022-12-03 03:31:59.175 CST [27324] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@enmo pg14]$ psql
psql (14.3)
Type "help" for help.

postgres=# select * from tbl;
 id | ival | description |         created_time          
----+------+-------------+-------------------------------
  1 |    1 |             | 2022-12-03 03:21:52.706705+08
(1 row)

postgres=#


### 7. 日志信息
[postgres@enmo log]$ cat postgresql-2022-12-03_033159.log 
2022-12-03 03:31:59.175 CST [27324] LOG:  starting PostgreSQL 14.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
2022-12-03 03:31:59.175 CST [27324] LOG:  listening on IPv6 address "::1", port 5414
2022-12-03 03:31:59.175 CST [27324] LOG:  listening on IPv4 address "127.0.0.1", port 5414
2022-12-03 03:31:59.176 CST [27324] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5414"
2022-12-03 03:31:59.178 CST [27326] LOG:  database system was interrupted; last known up at 2022-12-03 03:21:38 CST
cp: cannot stat ‘/u01/pg14/archive_wal/00000002.history’: No such file or directory
2022-12-03 03:31:59.186 CST [27326] LOG:  starting archive recovery
2022-12-03 03:31:59.193 CST [27326] LOG:  restored log file "000000010000000000000005" from archive
2022-12-03 03:31:59.208 CST [27326] LOG:  redo starts at 0/5000028
2022-12-03 03:31:59.209 CST [27326] LOG:  consistent recovery state reached at 0/5000138
2022-12-03 03:31:59.209 CST [27324] LOG:  database system is ready to accept read-only connections
2022-12-03 03:31:59.217 CST [27326] LOG:  restored log file "000000010000000000000006" from archive
cp: cannot stat ‘/u01/pg14/archive_wal/000000010000000000000007’: No such file or directory
2022-12-03 03:31:59.231 CST [27326] LOG:  redo done at 0/6027CC0 system usage: CPU: user: 0.00 s, system: 0.01 s, elapsed: 0.02 s
2022-12-03 03:31:59.231 CST [27326] LOG:  last completed transaction was at log time 2022-12-03 03:21:52.70731+08
2022-12-03 03:31:59.238 CST [27326] LOG:  restored log file "000000010000000000000006" from archive
cp: cannot stat ‘/u01/pg14/archive_wal/00000002.history’: No such file or directory
2022-12-03 03:31:59.253 CST [27326] LOG:  selected new timeline ID: 2
2022-12-03 03:31:59.271 CST [27326] LOG:  archive recovery complete
cp: cannot stat ‘/u01/pg14/archive_wal/00000001.history’: No such file or directory
2022-12-03 03:31:59.280 CST [27324] LOG:  database system is ready to accept connections
[postgres@enmo log]$ ls -ltr /u01/pg14/archive_wal/
total 98312
-rw-------. 1 postgres postgres 16777216 Dec  3 03:16 000000010000000000000001
-rw-------. 1 postgres postgres 16777216 Dec  3 03:16 000000010000000000000002
-rw-------. 1 postgres postgres 16777216 Dec  3 03:19 000000010000000000000003
-rw-------. 1 postgres postgres 16777216 Dec  3 03:21 000000010000000000000004
-rw-------. 1 postgres postgres 16777216 Dec  3 03:21 000000010000000000000005
-rw-------. 1 postgres postgres      338 Dec  3 03:21 000000010000000000000005.00000028.backup
-rw-------. 1 postgres postgres 16777216 Dec  3 03:21 000000010000000000000006
-rw-------. 1 postgres postgres       41 Dec  3 03:31 00000002.history
[postgres@enmo log]$ 


小结：
如果$PGDATA目录损坏，可使用base backup和归档WAL进行恢复，但未归档的WAL包含的数据会丢失。

恢复到最近时间点-PGDATA损坏恢复-未归档WAL保留

### 1. 创建测试表
drop table tbl;
create table tbl(
id serial primary key,
ival int not null default 0,
description text,
created_time timestamptz not null default now()
);
insert into tbl(ival) values(1);
select pg_switch_wal();
insert into tbl(ival) values(2);
select * from tbl;
postgres=# \q
pg_ctl stop -m immediate
### 2. 删除 $PGDATA
rm -rf $PGDATA/base/
rm -rf $PGDATA/global/

### 3. 备份PGDATA下所有保留的文件及tablespace文件
rm -rf /u01/pg14/backups/tmp/
mkdir -p /u01/pg14/backups/tmp/
mv $PGDATA /u01/pg14/backups/tmp/

### 4. 创建PGDATA目录
mkdir -p /u01/pg14/data
chmod 0700 /u01/pg14/data

### 5. 解压备份集
tar -xvf /u01/pg14/backups/base.tar.gz -C /u01/pg14/data/

### 6. 配置restore_command参数并创建recovery.signal文件
cat >> $PGDATA/postgresql.conf <<-EOF
restore_command = 'cp /u01/pg14/archive_wal/%f %p'
EOF

touch $PGDATA/recovery.signal

### 7. cp 未归档WAL到 ./pg_wal
[postgres@enmo pg_wal]$ ls -ltr /u01/pg14/backups/tmp/data/pg_wal/
total 114700
-rw-------. 1 postgres postgres 16777216 Dec  3 04:00 00000004000000000000000F
-rw-------. 1 postgres postgres       83 Dec  3 04:01 00000003.history
-rw-------. 1 postgres postgres 16777216 Dec  3 04:01 00000004000000000000000B
-rw-------. 1 postgres postgres       41 Dec  3 04:01 00000002.history
-rw-------. 1 postgres postgres 16777216 Dec  3 04:01 00000004000000000000000C
-rw-------. 1 postgres postgres 16777216 Dec  3 04:01 00000004000000000000000D
-rw-------. 1 postgres postgres 16777216 Dec  3 04:01 00000004000000000000000E
-rw-------. 1 postgres postgres      125 Dec  3 04:01 00000004.history
-rw-------. 1 postgres postgres 16777216 Dec  3 04:08 000000040000000000000009
drwx------. 2 postgres postgres      130 Dec  3 04:08 archive_status
-rw-------. 1 postgres postgres 16777216 Dec  3 04:08 00000004000000000000000A
[postgres@enmo pg_wal]$ ls -ltr /u01/pg14/backups/tmp/data/pg_wal/archive_status
total 0
-rw-------. 1 postgres postgres 0 Dec  3 04:00 00000002.history.done
-rw-------. 1 postgres postgres 0 Dec  3 04:01 00000003.history.done
-rw-------. 1 postgres postgres 0 Dec  3 04:01 00000004.history.done
-rw-------. 1 postgres postgres 0 Dec  3 04:08 000000040000000000000009.done
[postgres@enmo pg_wal]$ cp /u01/pg14/backups/tmp/data/pg_wal/00000004000000000000000A $PGDATA/pg_wal/
[postgres@enmo pg_wal]$ ls -ltr $PGDATA/pg_wal/
total 32768
-rw-------. 1 postgres postgres 16777216 Dec  3 03:21 000000010000000000000005
drwx------. 2 postgres postgres       43 Dec  3 04:13 archive_status
-rw-------. 1 postgres postgres 16777216 Dec  3 04:14 00000004000000000000000A
[postgres@enmo pg_wal]$

### 8. 启动数据库开始恢复
[postgres@enmo pg_wal]$ pg_ctl start
waiting for server to start....2022-12-03 04:15:11.728 CST [27607] LOG:  redirecting log output to logging collector process
2022-12-03 04:15:11.728 CST [27607] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@enmo pg_wal]$ psql
psql (14.3)
Type "help" for help.

postgres=# select * from tbl;
 id | ival | description |         created_time          
----+------+-------------+-------------------------------
  1 |    1 |             | 2022-12-03 04:08:36.063502+08
  2 |    2 |             | 2022-12-03 04:08:36.104362+08
(2 rows)

postgres=#

小结：
如果$PGDATA目录损坏，可使用base backup和归档WAL进行恢复，未归档的WAL如果保留可进行完全恢复，无数据丢失。

恢复到指定时间点

### 1. 模拟异常操作
postgres=# select * from tbl;
 id | ival | description |         created_time          
----+------+-------------+-------------------------------
  1 |    1 |             | 2022-12-03 05:19:49.586279+08
  2 |    2 |             | 2022-12-03 05:19:49.622909+08
  3 |    3 |             | 2022-12-03 05:19:57.280795+08
(2 rows)

postgres=# select current_timestamp;
       current_timestamp       
-------------------------------
 2022-12-03 05:20:16.259671+08
(1 row)

postgres=# delete from tbl;
DELETE 2
postgres=# select * from tbl;
 id | ival | description | created_time 
----+------+-------------+--------------
(0 rows)

postgres=#

**********省略部分操作步骤，请参考上面两个例子

### 2. 配置restore_command参数并创建recovery.signal文件
cat >> $PGDATA/postgresql.conf <<-EOF
restore_command = 'cp /u01/pg14/archive_wal/%f %p'
recovery_target_time = '2022-12-03 05:20:16.259671+08'
EOF

touch $PGDATA/recovery.signal

### 3. 启动数据库开始恢复
pg_ctl start
[postgres@enmo backups]$ psql
psql (14.3)
Type "help" for help.

postgres=# select * from tbl;
 id | ival | description |         created_time          
----+------+-------------+-------------------------------
  1 |    1 |             | 2022-12-03 05:19:49.586279+08
  2 |    2 |             | 2022-12-03 05:19:49.622909+08
  3 |    3 |             | 2022-12-03 05:19:57.280795+08
(3 rows)

postgres=# 

### 4. 恢复日志如下
[postgres@enmo log]$ cat postgresql-2022-12-03_052229.log
2022-12-03 05:22:29.679 CST [28119] LOG:  starting PostgreSQL 14.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
2022-12-03 05:22:29.679 CST [28119] LOG:  listening on IPv6 address "::1", port 5414
2022-12-03 05:22:29.679 CST [28119] LOG:  listening on IPv4 address "127.0.0.1", port 5414
2022-12-03 05:22:29.681 CST [28119] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5414"
2022-12-03 05:22:29.683 CST [28121] LOG:  database system was interrupted; last known up at 2022-12-03 05:17:20 CST
2022-12-03 05:22:29.690 CST [28121] LOG:  restored log file "00000002.history" from archive
cp: cannot stat ‘/u01/pg14/archive_wal/00000003.history’: No such file or directory
2022-12-03 05:22:29.691 CST [28121] LOG:  starting point-in-time recovery to 2022-12-03 05:20:16.259671+08
2022-12-03 05:22:29.693 CST [28121] LOG:  restored log file "00000002.history" from archive
2022-12-03 05:22:29.701 CST [28121] LOG:  restored log file "000000010000000000000002" from archive
2022-12-03 05:22:29.713 CST [28121] LOG:  redo starts at 0/2000028
2022-12-03 05:22:29.714 CST [28121] LOG:  consistent recovery state reached at 0/2000138
2022-12-03 05:22:29.714 CST [28119] LOG:  database system is ready to accept read-only connections
2022-12-03 05:22:29.721 CST [28121] LOG:  restored log file "000000010000000000000003" from archive
2022-12-03 05:22:29.740 CST [28121] LOG:  restored log file "000000020000000000000004" from archive
cp: cannot stat ‘/u01/pg14/archive_wal/000000020000000000000005’: No such file or directory
2022-12-03 05:22:29.754 CST [28121] LOG:  recovery stopping before commit of transaction 740, time 2022-12-03 05:20:27.023805+08
2022-12-03 05:22:29.754 CST [28121] LOG:  pausing at the end of recovery
2022-12-03 05:22:29.754 CST [28121] HINT:  Execute pg_wal_replay_resume() to promote.
[postgres@enmo log]$ 

### 5. 处理db readonly 问题
postgres=# insert into tbl(ival) values(4);
ERROR:  cannot execute INSERT in a read-only transaction
postgres=#
postgres=# select pg_promote(true,60);
 pg_promote 
------------
 t
(1 row)

postgres=# 

2022-12-05 16:52:37.248 CST [32140] LOG:  received promote request
2022-12-05 16:52:37.248 CST [32140] LOG:  redo done at 0/50002A0 system usage: CPU: user: 0.00 s, system: 0.01 s, elapsed: 384.67 s
2022-12-05 16:52:37.248 CST [32140] LOG:  last completed transaction was at log time 2022-12-03 05:19:57.281024+08
cp: cannot stat ‘/u01/pg14/archive_wal/00000003.history’: No such file or directory
2022-12-05 16:52:37.252 CST [32140] LOG:  selected new timeline ID: 3
2022-12-05 16:52:37.270 CST [32140] LOG:  archive recovery complete
2022-12-05 16:52:37.272 CST [32140] LOG:  restored log file "00000002.history" from archive
2022-12-05 16:52:37.274 CST [32138] LOG:  database system is ready to accept connections

恢复到指定还原点

### 1. 模拟业务数据
postgres=# select * from tbl;
 id | ival | description |         created_time          
----+------+-------------+-------------------------------
  1 |    1 |             | 2022-12-03 05:19:49.586279+08
  2 |    2 |             | 2022-12-03 05:19:49.622909+08
  3 |    3 |             | 2022-12-03 05:19:57.280795+08
 34 |    4 |             | 2022-12-05 16:54:16.474884+08
(4 rows)

postgres=#

postgres=# \df pg_create_restore_point
                                  List of functions
   Schema   |          Name           | Result data type | Argument data types | Type 
------------+-------------------------+------------------+---------------------+------
 pg_catalog | pg_create_restore_point | pg_lsn           | text                | func
(1 row)

postgres=# 

### 2. 创建还原点
postgres=# select pg_create_restore_point('restore_point');
 pg_create_restore_point 
-------------------------
 0/5000698
(1 row)

postgres=#

postgres=# delete from tbl where ival=4;
DELETE 1
postgres=# alter table tbl drop column description;
ALTER TABLE
postgres=#  select * from tbl order by created_time desc;
 id | ival |         created_time          
----+------+-------------------------------
  3 |    3 | 2022-12-03 05:19:57.280795+08
  2 |    2 | 2022-12-03 05:19:49.622909+08
  1 |    1 | 2022-12-03 05:19:49.586279+08
(3 rows)

postgres=#

### 3. 配置restore_command参数并创建recovery.signal文件
cat >> $PGDATA/postgresql.conf <<-EOF
restore_command = 'cp /u01/pg14/archive_wal/%f %p'
recovery_target_name = 'restore_point'
EOF

touch $PGDATA/recovery.signal

### 4. cp 未归档的WAL文件
[postgres@enmo ~]$ ls -ltr /u01/pg14/backups/tmp/data/pg_wal/
total 81928
-rw-------. 1 postgres postgres 16777216 Dec  3 05:21 000000020000000000000005.partial
-rw-------. 1 postgres postgres 16777216 Dec  3 05:22 000000030000000000000006
-rw-------. 1 postgres postgres 16777216 Dec  3 05:22 000000030000000000000007
-rw-------. 1 postgres postgres       41 Dec  5 16:46 00000002.history
-rw-------. 1 postgres postgres 16777216 Dec  5 16:46 000000030000000000000008
-rw-------. 1 postgres postgres       92 Dec  5 16:52 00000003.history
drwx------. 2 postgres postgres      109 Dec  5 16:52 archive_status
-rw-------. 1 postgres postgres 16777216 Dec  5 16:57 000000030000000000000005
[postgres@enmo ~]$ ls -ltr /u01/pg14/backups/tmp/data/pg_wal/archive_status
total 0
-rw-------. 1 postgres postgres 0 Dec  3 05:22 00000002.history.done
-rw-------. 1 postgres postgres 0 Dec  5 16:52 00000003.history.done
-rw-------. 1 postgres postgres 0 Dec  5 16:52 000000020000000000000005.partial.done
[postgres@enmo ~]$ ls -ltr $PGDATA/pg_wal/
total 16384
-rw-------. 1 postgres postgres 16777216 Dec  3 05:17 000000010000000000000002
drwx------. 2 postgres postgres       43 Dec  5 17:18 archive_status
[postgres@enmo ~]$ ls -ltr /u01/pg14/archive_wal/
total 81932
-rw-------. 1 postgres postgres 16777216 Dec  3 05:17 000000010000000000000001
-rw-------. 1 postgres postgres 16777216 Dec  3 05:17 000000010000000000000002
-rw-------. 1 postgres postgres      338 Dec  3 05:17 000000010000000000000002.00000028.backup
-rw-------. 1 postgres postgres 16777216 Dec  3 05:17 000000010000000000000003
-rw-------. 1 postgres postgres       41 Dec  3 05:19 00000002.history
-rw-------. 1 postgres postgres 16777216 Dec  3 05:19 000000020000000000000004
-rw-------. 1 postgres postgres       92 Dec  5 16:52 00000003.history
-rw-------. 1 postgres postgres 16777216 Dec  5 16:52 000000020000000000000005.partial
[postgres@enmo ~]$ cp /u01/pg14/backups/tmp/data/pg_wal/000000030000000000000005 $PGDATA/pg_wal/
[postgres@enmo ~]$ cp /u01/pg14/backups/tmp/data/pg_wal/000000030000000000000006 $PGDATA/pg_wal/
[postgres@enmo ~]$ cp /u01/pg14/backups/tmp/data/pg_wal/000000030000000000000007 $PGDATA/pg_wal/
[postgres@enmo ~]$ cp /u01/pg14/backups/tmp/data/pg_wal/000000030000000000000008 $PGDATA/pg_wal/
[postgres@enmo ~]$ ls -ltr $PGDATA/pg_wal/
total 81920
-rw-------. 1 postgres postgres 16777216 Dec  3 05:17 000000010000000000000002
drwx------. 2 postgres postgres       43 Dec  5 17:18 archive_status
-rw-------. 1 postgres postgres 16777216 Dec  5 17:22 000000030000000000000005
-rw-------. 1 postgres postgres 16777216 Dec  5 17:22 000000030000000000000006
-rw-------. 1 postgres postgres 16777216 Dec  5 17:22 000000030000000000000007
-rw-------. 1 postgres postgres 16777216 Dec  5 17:22 000000030000000000000008
[postgres@enmo ~]$ 

### 5. 启动db及恢复
[postgres@enmo ~]$ pg_ctl start
[postgres@enmo log]$ cat postgresql-2022-12-05_172731.log
2022-12-05 17:27:31.357 CST [32271] LOG:  starting PostgreSQL 14.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
2022-12-05 17:27:31.358 CST [32271] LOG:  listening on IPv6 address "::1", port 5414
2022-12-05 17:27:31.358 CST [32271] LOG:  listening on IPv4 address "127.0.0.1", port 5414
2022-12-05 17:27:31.360 CST [32271] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5414"
2022-12-05 17:27:31.361 CST [32273] LOG:  database system was interrupted; last known up at 2022-12-03 05:17:20 CST
2022-12-05 17:27:31.369 CST [32273] LOG:  restored log file "00000002.history" from archive
2022-12-05 17:27:31.371 CST [32273] LOG:  restored log file "00000003.history" from archive
cp: cannot stat ‘/u01/pg14/archive_wal/00000004.history’: No such file or directory
2022-12-05 17:27:31.374 CST [32273] LOG:  starting point-in-time recovery to "restore_point"
2022-12-05 17:27:31.375 CST [32273] LOG:  restored log file "00000003.history" from archive
2022-12-05 17:27:31.384 CST [32273] LOG:  restored log file "000000010000000000000002" from archive
2022-12-05 17:27:31.397 CST [32273] LOG:  restored log file "00000002.history" from archive
2022-12-05 17:27:31.398 CST [32273] LOG:  redo starts at 0/2000028
2022-12-05 17:27:31.399 CST [32273] LOG:  consistent recovery state reached at 0/2000138
2022-12-05 17:27:31.399 CST [32271] LOG:  database system is ready to accept read-only connections
2022-12-05 17:27:31.407 CST [32273] LOG:  restored log file "000000010000000000000003" from archive
2022-12-05 17:27:31.427 CST [32273] LOG:  restored log file "000000020000000000000004" from archive
cp: cannot stat ‘/u01/pg14/archive_wal/000000030000000000000005’: No such file or directory
2022-12-05 17:27:31.439 CST [32273] LOG:  recovery stopping at restore point "restore_point", time 2022-12-05 16:55:42.259004+08
2022-12-05 17:27:31.439 CST [32273] LOG:  pausing at the end of recovery
2022-12-05 17:27:31.439 CST [32273] HINT:  Execute pg_wal_replay_resume() to promote.
[postgres@enmo log]$ 

postgres=# select pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
 
(1 row)

postgres=#

2022-12-05 17:43:04.173 CST [32294] STATEMENT:  Execute pg_wal_replay_resume();
2022-12-05 17:43:13.266 CST [32273] LOG:  redo done at 0/5000630 system usage: CPU: user: 0.00 s, system: 0.05 s, elapsed: 941.86 s
2022-12-05 17:43:13.266 CST [32273] LOG:  last completed transaction was at log time 2022-12-05 16:54:16.475472+08
cp: cannot stat ‘/u01/pg14/archive_wal/00000004.history’: No such file or directory
2022-12-05 17:43:13.269 CST [32273] LOG:  selected new timeline ID: 4
2022-12-05 17:43:13.285 CST [32273] LOG:  archive recovery complete
2022-12-05 17:43:13.287 CST [32273] LOG:  restored log file "00000003.history" from archive
2022-12-05 17:43:13.290 CST [32271] LOG:  database system is ready to accept connections
[postgres@enmo log]$ 


postgres=# select * from tbl order by created_time desc;
 id | ival | description |         created_time          
----+------+-------------+-------------------------------
 34 |    4 |             | 2022-12-05 16:54:16.474884+08
  3 |    3 |             | 2022-12-03 05:19:57.280795+08
  2 |    2 |             | 2022-12-03 05:19:49.622909+08
  1 |    1 |             | 2022-12-03 05:19:49.586279+08
(4 rows)

postgres=# insert into tbl(ival) values(5);
INSERT 0 1
postgres=# select * from tbl order by created_time desc;
 id | ival | description |         created_time          
----+------+-------------+-------------------------------
 67 |    5 |             | 2022-12-05 17:44:14.573222+08
 34 |    4 |             | 2022-12-05 16:54:16.474884+08
  3 |    3 |             | 2022-12-03 05:19:57.280795+08
  2 |    2 |             | 2022-12-03 05:19:49.622909+08
  1 |    1 |             | 2022-12-03 05:19:49.586279+08
(5 rows)

postgres=#

恢复到指定事务

### 1. 模拟业务数据
postgres=# select * from tbl order by created_time desc;
 id | ival | description |         created_time          
----+------+-------------+-------------------------------
 67 |    5 |             | 2022-12-05 17:44:14.573222+08
 34 |    4 |             | 2022-12-05 16:54:16.474884+08
  3 |    3 |             | 2022-12-03 05:19:57.280795+08
  2 |    2 |             | 2022-12-03 05:19:49.622909+08
  1 |    1 |             | 2022-12-03 05:19:49.586279+08
(5 rows)

postgres=# 

postgres=# select txid_current();
 txid_current 
--------------
          743
(1 row)

postgres=# 

postgres=# select txid_current();
 txid_current 
--------------
          743
(1 row)

postgres=# delete from tbl where id > 1;
DELETE 4
postgres=# select * from tbl;
 id | ival | description |         created_time          
----+------+-------------+-------------------------------
  1 |    1 |             | 2022-12-03 05:19:49.586279+08
(1 row)

postgres=# 

### 2. 配置restore_command参数并创建recovery.signal文件
cat >> $PGDATA/postgresql.conf <<-EOF
restore_command = 'cp /u01/pg14/archive_wal/%f %p'
recovery_target_xid = '743'
EOF

touch $PGDATA/recovery.signal

### 3. cp 未归档的WAL文件
[postgres@enmo data]$ cp -R /u01/pg14/backups/tmp/data/pg_wal/* $PGDATA/pg_wal/


### 5. 启动db及恢复
[postgres@enmo ~]$ pg_ctl start

[postgres@enmo log]$ cat postgresql-2022-12-05_175535.log
2022-12-05 17:55:35.230 CST [32410] LOG:  starting PostgreSQL 14.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
2022-12-05 17:55:35.231 CST [32410] LOG:  listening on IPv6 address "::1", port 5414
2022-12-05 17:55:35.231 CST [32410] LOG:  listening on IPv4 address "127.0.0.1", port 5414
2022-12-05 17:55:35.233 CST [32410] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5414"
2022-12-05 17:55:35.234 CST [32412] LOG:  database system was interrupted; last known up at 2022-12-03 05:17:20 CST
2022-12-05 17:55:35.241 CST [32412] LOG:  restored log file "00000002.history" from archive
2022-12-05 17:55:35.243 CST [32412] LOG:  restored log file "00000003.history" from archive
2022-12-05 17:55:35.245 CST [32412] LOG:  restored log file "00000004.history" from archive
cp: cannot stat ‘/u01/pg14/archive_wal/00000005.history’: No such file or directory
2022-12-05 17:55:35.247 CST [32412] LOG:  starting point-in-time recovery to XID 743
2022-12-05 17:55:35.249 CST [32412] LOG:  restored log file "00000004.history" from archive
2022-12-05 17:55:35.256 CST [32412] LOG:  restored log file "000000010000000000000002" from archive
2022-12-05 17:55:35.270 CST [32412] LOG:  restored log file "00000002.history" from archive
2022-12-05 17:55:35.271 CST [32412] LOG:  restored log file "00000003.history" from archive
2022-12-05 17:55:35.273 CST [32412] LOG:  redo starts at 0/2000028
2022-12-05 17:55:35.273 CST [32412] LOG:  consistent recovery state reached at 0/2000138
2022-12-05 17:55:35.273 CST [32410] LOG:  database system is ready to accept read-only connections
2022-12-05 17:55:35.281 CST [32412] LOG:  restored log file "000000010000000000000003" from archive
2022-12-05 17:55:35.299 CST [32412] LOG:  restored log file "000000020000000000000004" from archive
2022-12-05 17:55:35.320 CST [32412] LOG:  restored log file "000000040000000000000005" from archive
2022-12-05 17:55:35.330 CST [32412] LOG:  recovery stopping after commit of transaction 743, time 2022-12-05 17:45:27.999483+08
2022-12-05 17:55:35.330 CST [32412] LOG:  pausing at the end of recovery
2022-12-05 17:55:35.330 CST [32412] HINT:  Execute pg_wal_replay_resume() to promote.
2022-12-05 17:59:52.139 CST [32436] ERROR:  cannot execute INSERT in a read-only transaction
2022-12-05 17:59:52.139 CST [32436] STATEMENT:  insert into tbl(ival) values(6);
2022-12-05 18:00:06.809 CST [32412] LOG:  redo done at 0/5000A40 system usage: CPU: user: 0.00 s, system: 0.04 s, elapsed: 271.53 s
2022-12-05 18:00:06.809 CST [32412] LOG:  last completed transaction was at log time 2022-12-05 17:45:27.999483+08
cp: cannot stat ‘/u01/pg14/archive_wal/00000005.history’: No such file or directory
2022-12-05 18:00:06.813 CST [32412] LOG:  selected new timeline ID: 5
2022-12-05 18:00:06.830 CST [32412] LOG:  archive recovery complete
2022-12-05 18:00:06.833 CST [32412] LOG:  restored log file "00000004.history" from archive
2022-12-05 18:00:06.841 CST [32410] LOG:  database system is ready to accept connections
[postgres@enmo log]$ 



postgres=# select * from tbl;
 id | ival | description |         created_time          
----+------+-------------+-------------------------------
  1 |    1 |             | 2022-12-03 05:19:49.586279+08
  2 |    2 |             | 2022-12-03 05:19:49.622909+08
  3 |    3 |             | 2022-12-03 05:19:57.280795+08
 34 |    4 |             | 2022-12-05 16:54:16.474884+08
 67 |    5 |             | 2022-12-05 17:44:14.573222+08
(5 rows)

postgres=# insert into tbl(ival) values(6);
ERROR:  cannot execute INSERT in a read-only transaction
postgres=# select pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
 
(1 row)

postgres=# \q
[postgres@enmo archive_status]$ pg_controldata |grep stat
Database cluster state:               in production
[postgres@enmo archive_status]$ psql
psql (14.3)
Type "help" for help.

postgres=# insert into tbl(ival) values(6);
INSERT 0 1
postgres=# select * from tbl;
 id  | ival | description |         created_time          
-----+------+-------------+-------------------------------
   1 |    1 |             | 2022-12-03 05:19:49.586279+08
   2 |    2 |             | 2022-12-03 05:19:49.622909+08
   3 |    3 |             | 2022-12-03 05:19:57.280795+08
  34 |    4 |             | 2022-12-05 16:54:16.474884+08
  67 |    5 |             | 2022-12-05 17:44:14.573222+08
 100 |    6 |             | 2022-12-05 18:00:36.638259+08
(6 rows)

postgres=#

恢复到指定timeline

cat >> $PGDATA/postgresql.conf <<-EOF
restore_command = 'cp /u01/pg14/archive_wal/%f %p'
recovery_target_timeline=5
recovery_target_time='2022-12-05 18:00:36.638259+08'
EOF

touch $PGDATA/recovery.signal

[postgres@enmo ~]$ pg_ctl start
waiting for server to start....2022-12-05 18:27:09.323 CST [32580] LOG:  redirecting log output to logging collector process
2022-12-05 18:27:09.323 CST [32580] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@enmo ~]$ 
[postgres@enmo ~]$ 
[postgres@enmo ~]$ psql
psql (14.3)
Type "help" for help.

postgres=# select * from tbl;
 id | ival | description |         created_time          
----+------+-------------+-------------------------------
  1 |    1 |             | 2022-12-03 05:19:49.586279+08
  2 |    2 |             | 2022-12-03 05:19:49.622909+08
  3 |    3 |             | 2022-12-03 05:19:57.280795+08
 34 |    4 |             | 2022-12-05 16:54:16.474884+08
 67 |    5 |             | 2022-12-05 17:44:14.573222+08
(5 rows)

postgres=# 

[postgres@enmo log]$ cat postgresql-2022-12-05_182709.log
2022-12-05 18:27:09.323 CST [32580] LOG:  starting PostgreSQL 14.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
2022-12-05 18:27:09.323 CST [32580] LOG:  listening on IPv6 address "::1", port 5414
2022-12-05 18:27:09.323 CST [32580] LOG:  listening on IPv4 address "127.0.0.1", port 5414
2022-12-05 18:27:09.324 CST [32580] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5414"
2022-12-05 18:27:09.326 CST [32582] LOG:  database system was interrupted; last known up at 2022-12-03 05:17:20 CST
2022-12-05 18:27:09.332 CST [32582] LOG:  restored log file "00000005.history" from archive
2022-12-05 18:27:09.332 CST [32582] LOG:  starting point-in-time recovery to 2022-12-05 18:00:36.638259+08
2022-12-05 18:27:09.334 CST [32582] LOG:  restored log file "00000005.history" from archive
2022-12-05 18:27:09.343 CST [32582] LOG:  restored log file "000000010000000000000002" from archive
2022-12-05 18:27:09.360 CST [32582] LOG:  restored log file "00000002.history" from archive
2022-12-05 18:27:09.362 CST [32582] LOG:  restored log file "00000003.history" from archive
2022-12-05 18:27:09.364 CST [32582] LOG:  restored log file "00000004.history" from archive
2022-12-05 18:27:09.365 CST [32582] LOG:  redo starts at 0/2000028
2022-12-05 18:27:09.365 CST [32582] LOG:  consistent recovery state reached at 0/2000138
2022-12-05 18:27:09.365 CST [32580] LOG:  database system is ready to accept read-only connections
2022-12-05 18:27:09.372 CST [32582] LOG:  restored log file "000000010000000000000003" from archive
2022-12-05 18:27:09.392 CST [32582] LOG:  restored log file "000000020000000000000004" from archive
2022-12-05 18:27:09.413 CST [32582] LOG:  restored log file "000000050000000000000005" from archive
2022-12-05 18:27:09.427 CST [32582] LOG:  recovery stopping before commit of transaction 744, time 2022-12-05 18:00:36.638954+08
2022-12-05 18:27:09.427 CST [32582] LOG:  pausing at the end of recovery
2022-12-05 18:27:09.427 CST [32582] HINT:  Execute pg_wal_replay_resume() to promote.
[postgres@enmo log]$

参考文件

《PostgreSQL实战》：第13章 备份与恢复
官方文档：26.3. Continuous Archiving and Point-in-Time Recovery (PITR)
https://www.postgresql.org/docs/15/continuous-archiving.html
墨力计划postgresql
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论