PostgreSQL 10 基于日志的备份与还原

最新推荐文章于 2024-06-20 19:31:40 发布
最新推荐文章于 2024-06-20 19:31:40 发布
阅读量2.3k 收藏 1
点赞数
分类专栏： postgresql 文章标签： 备份 恢复
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/liuhhaiffeng/article/details/104365215
版权

PostgreSQL 10 基于日志的备份与还原

wal，即预写式日志，是日志的标准实现方式，简单而言就是将对数据库的变动先记录到日志中，而后在将具体的新数据刷新到磁盘。PostgreSQL将该日志维护在数据文件夹下的子文件夹pg_wal中。当数据库崩溃后，可以通过“重放(replay)”日志中的“动作”，将数据库恢复。也就是说，只要拥有一个基础备份和完整的日志文件，理论上可以将数据库库恢复到任意基础备份以来的任意时刻点。不仅如此，如果在另一个实例上将这些日志不停的“重放”，那么就拥有了一个完整的在线备份，也就是“复制”。

pg_wal下日志文件不会无限制增多，也就是说并不用担心日志的增多使得磁盘空间捉襟见肘。默认每个日志文件为16M大小，即当增长到16M时，就会切换到别的文件，并复用之前的文件 。因此，为了保证有个完整的日志链，必须将写满的文件复制保存到一个特定的文件 夹。对于最后一个还未满16M的日志文件，可以手动触发一次切换。

备份 (操作均使用postgres用户完成)

1. 登录数据库创建测试数据库

[postgres@localhost ~]$ psql 
psql (10.10)
Type "help" for help.

postgres=# create database test;
CREATE DATABASE
postgres=#

2. 修改配置文件, 开启日志备份, 将写满的文件复制到archivedir目录下

注: PG9.x 中 wal_level 归档的的取值为archive, PG10.x 中修改为了 replica . replica代表了"流复制"或"归档".

wal_level = replica            # minimal, replica, or logical

archive_mode = on

archive_command = 'test ! -f /home/postgres/archivedir/%f && cp %p /home/postgres/archivedir/%f'       # command to use to archive a logfile segment
220                 # placeholders: %p = path of file to archive
221                 #               %f = file name only
222                 # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

3. 创建archivedir目录, 并重启数据库服务

[postgres@localhost ~]$ mkdir archivedir
[postgres@localhost ~]$ pg_ctl -D db1 restart
waiting for server to shut down....2020-02-17 20:18:53.755 CST [50766] LOG:  received fast shutdown request
2020-02-17 20:18:53.756 CST [50766] LOG:  aborting any active transactions
2020-02-17 20:18:53.757 CST [50766] LOG:  worker process: logical replication launcher (PID 50773) exited with exit code 1
2020-02-17 20:18:53.757 CST [50768] LOG:  shutting down
2020-02-17 20:18:53.769 CST [50766] LOG:  database system is shut down
 done
server stopped
waiting for server to start....2020-02-17 20:18:53.867 CST [50861] LOG:  listening on IPv6 address "::1", port 5432
2020-02-17 20:18:53.867 CST [50861] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2020-02-17 20:18:53.871 CST [50861] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-02-17 20:18:53.888 CST [50862] LOG:  database system was shut down at 2020-02-17 20:18:53 CST
2020-02-17 20:18:53.891 CST [50861] LOG:  database system is ready to accept connections
 done
server started
[postgres@localhost ~]$

4. 创建测试表

[postgres@localhost ~]$ psql
psql (10.10)
Type "help" for help.

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# CREATE TABLE testPITR1 AS SELECT * FROM pg_class, pg_description;
SELECT 1338084
test=# 
[postgres@localhost ~]$ ll -h archivedir/
total 336M
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000001
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000002
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000003
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000004
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000005
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000006
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000007
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000008
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000009
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000A
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000B
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000C
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000D
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000E
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000F
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000010
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000011
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000012
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000013
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000014
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000015
[postgres@localhost ~]$

5. 创建基础备份

[postgres@localhost ~]$ psql -c "SELECT pg_start_backup('base', true)"
 pg_start_backup 
-----------------
 0/17000028
(1 row)


[postgres@localhost ~]$ tar -cvf db1.tar db1
......
[postgres@localhost ~]$ psql -c "SELECT pg_stop_backup()"
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup 
----------------
 0/17000168
(1 row)
[postgres@localhost ~]$ 
[postgres@localhost ~]$ ll -h archivedir/
total 369M
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000001
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000002
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000003
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000004
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000005
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000006
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000007
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000008
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000009
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000A
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000B
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000C
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000D
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000E
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000F
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000010
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000011
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000012
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000013
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000014
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000015
-rw------- 1 postgres postgres 16M Feb 17 20:20 000000010000000000000016
-rw------- 1 postgres postgres 16M Feb 17 20:21 000000010000000000000017
-rw------- 1 postgres postgres 291 Feb 17 20:21 000000010000000000000017.00000028.backup
[postgres@localhost ~]$

6. 继续创建测试表, 切换日志

注: pg9.x 中 为 select pg_switch_xlog(); pg10.x中为 select pg_switch_wal();

[postgres@localhost ~]$ psql
psql (10.10)
Type "help" for help.

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# CREATE TABLE testPITR2 AS SELECT * FROM pg_class, pg_description;
SELECT 1349856
test=# select * from current_timestamp;
       current_timestamp       
-------------------------------
 2020-02-17 20:22:46.40963+08
(1 row)

test=# CREATE TABLE testPITR3 AS SELECT * FROM pg_class, pg_description;
SELECT 1361628
test=# select * from current_timestamp;
       current_timestamp       
-------------------------------
 2020-02-17 20:23:08.669018+08
(1 row)

test=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/431112D0
(1 row)

test=#

7. 恢复

关闭数据库，模拟数据库宕机，此时，数据库test中应该有3张表，其中1张表在基础备份前，也就是恢复完数据文件即可找回，而另2张表则需恢复相应的日志文件。模拟恢复到testPITR2创建时刻点。

test=# \d
           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | testpitr1 | table | postgres
 public | testpitr2 | table | postgres
 public | testpitr3 | table | postgres
(3 rows)

test=#

看看archivedir的情况:

[postgres@localhost ~]$ ll -h archivedir/
total 1.1G
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000001
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000002
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000003
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000004
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000005
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000006
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000007
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000008
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000009
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000A
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000B
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000C
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000D
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000E
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000F
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000010
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000011
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000012
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000013
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000014
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000015
-rw------- 1 postgres postgres 16M Feb 17 20:20 000000010000000000000016
-rw------- 1 postgres postgres 16M Feb 17 20:21 000000010000000000000017
-rw------- 1 postgres postgres 291 Feb 17 20:21 000000010000000000000017.00000028.backup
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000018
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000019
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000001A
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000001B
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000001C
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000001D
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000001E
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000001F
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000020
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000021
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000022
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000023
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000024
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000025
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000026
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000027
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000028
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000029
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000002A
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000002B
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000002C
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000002D
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000002E
-rw------- 1 postgres postgres 16M Feb 17 20:23 00000001000000000000002F
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000030
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000031
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000032
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000033
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000034
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000035
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000036
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000037
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000038
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000039
-rw------- 1 postgres postgres 16M Feb 17 20:23 00000001000000000000003A
-rw------- 1 postgres postgres 16M Feb 17 20:23 00000001000000000000003B
-rw------- 1 postgres postgres 16M Feb 17 20:23 00000001000000000000003C
-rw------- 1 postgres postgres 16M Feb 17 20:23 00000001000000000000003D
-rw------- 1 postgres postgres 16M Feb 17 20:23 00000001000000000000003E
-rw------- 1 postgres postgres 16M Feb 17 20:23 00000001000000000000003F
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000040
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000041
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000042
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000043
[postgres@localhost ~]$

7.1 关闭数据库服务, 重命名数据目录

[postgres@localhost ~]$ pg_ctl -D db1 stop
waiting for server to shut down....2020-02-17 20:25:17.173 CST [50861] LOG:  received fast shutdown request
2020-02-17 20:25:17.177 CST [50861] LOG:  aborting any active transactions
2020-02-17 20:25:17.178 CST [51099] FATAL:  terminating autovacuum process due to administrator command
2020-02-17 20:25:17.179 CST [50861] LOG:  worker process: logical replication launcher (PID 50869) exited with exit code 1
2020-02-17 20:25:17.180 CST [50863] LOG:  shutting down
2020-02-17 20:25:17.494 CST [50861] LOG:  database system is shut down
 done
server stopped
[postgres@localhost ~]$ 
[postgres@localhost ~]$ mv db1 db1.old

7.2 解压备份数据文件, 启动服务, 验证此时只有基础备份前的testpitr1

[postgres@localhost ~]$ tar -xvf db1.tar
......
[postgres@localhost ~]$ pg_ctl -D db1 start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2020-02-17 20:26:28.315 CST [51138] LOG:  listening on IPv6 address "::1", port 5432
2020-02-17 20:26:28.315 CST [51138] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2020-02-17 20:26:28.324 CST [51138] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-02-17 20:26:28.343 CST [51139] LOG:  database system was interrupted; last known up at 2020-02-17 20:20:36 CST
2020-02-17 20:26:28.370 CST [51139] LOG:  redo starts at 0/17000028
2020-02-17 20:26:28.370 CST [51139] LOG:  invalid record length at 0/17000140: wanted 24, got 0
2020-02-17 20:26:28.370 CST [51139] LOG:  redo done at 0/17000108
2020-02-17 20:26:28.439 CST [51138] LOG:  database system is ready to accept connections
 done
server started
[postgres@localhost ~]$ 
[postgres@localhost ~]$ psql
psql (10.10)
Type "help" for help.

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \d
           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | testpitr1 | table | postgres
(1 row)

test=#

7.3 停掉数据库, 删除目录, 重新解压基础备份, 创建recovery.conf, 将数据库恢复到testpitr2时刻

[postgres@localhost ~]$ pg_ctl -D db1 stop
waiting for server to shut down....2020-02-17 20:27:36.615 CST [51138] LOG:  received fast shutdown request
2020-02-17 20:27:36.616 CST [51138] LOG:  aborting any active transactions
2020-02-17 20:27:36.618 CST [51138] LOG:  worker process: logical replication launcher (PID 51146) exited with exit code 1
2020-02-17 20:27:36.618 CST [51140] LOG:  shutting down
2020-02-17 20:27:36.655 CST [51144] LOG:  archive command failed with exit code 1
2020-02-17 20:27:36.655 CST [51144] DETAIL:  The failed archive command was: test ! -f /home/postgres/archivedir/000000010000000000000017 && cp pg_wal/000000010000000000000017 /home/postgres/archivedir/000000010000000000000017
2020-02-17 20:27:36.663 CST [51144] LOG:  archive command failed with exit code 1
2020-02-17 20:27:36.663 CST [51144] DETAIL:  The failed archive command was: test ! -f /home/postgres/archivedir/000000010000000000000017 && cp pg_wal/000000010000000000000017 /home/postgres/archivedir/000000010000000000000017
.2020-02-17 20:27:37.670 CST [51144] LOG:  archive command failed with exit code 1
2020-02-17 20:27:37.670 CST [51144] DETAIL:  The failed archive command was: test ! -f /home/postgres/archivedir/000000010000000000000017 && cp pg_wal/000000010000000000000017 /home/postgres/archivedir/000000010000000000000017
2020-02-17 20:27:37.670 CST [51144] WARNING:  archiving write-ahead log file "000000010000000000000017" failed too many times, will try again later
2020-02-17 20:27:37.673 CST [51144] LOG:  archive command failed with exit code 1
2020-02-17 20:27:37.673 CST [51144] DETAIL:  The failed archive command was: test ! -f /home/postgres/archivedir/000000010000000000000017 && cp pg_wal/000000010000000000000017 /home/postgres/archivedir/000000010000000000000017
.2020-02-17 20:27:38.678 CST [51144] LOG:  archive command failed with exit code 1
2020-02-17 20:27:38.678 CST [51144] DETAIL:  The failed archive command was: test ! -f /home/postgres/archivedir/000000010000000000000017 && cp pg_wal/000000010000000000000017 /home/postgres/archivedir/000000010000000000000017
.2020-02-17 20:27:39.684 CST [51144] LOG:  archive command failed with exit code 1
2020-02-17 20:27:39.684 CST [51144] DETAIL:  The failed archive command was: test ! -f /home/postgres/archivedir/000000010000000000000017 && cp pg_wal/000000010000000000000017 /home/postgres/archivedir/000000010000000000000017
2020-02-17 20:27:39.684 CST [51144] WARNING:  archiving write-ahead log file "000000010000000000000017" failed too many times, will try again later
2020-02-17 20:27:39.687 CST [51138] LOG:  database system is shut down
 done
server stopped
[postgres@localhost ~]$
[postgres@localhost ~]$ rm -rf db1
[postgres@localhost ~]$ tar -xvf db1.tar 
......
[postgres@localhost ~/db1]$ vi recovery.conf
[postgres@localhost ~/db1]$ cat recovery.conf 
restore_command = 'cp /home/postgres/archivedir/%f %p'
recovery_target_time = '2020-02-17 20:22:46'

[postgres@localhost ~/db1]$

启动数据库

[postgres@localhost ~]$ pg_ctl -D db1 start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2020-02-17 20:31:31.804 CST [51224] LOG:  listening on IPv6 address "::1", port 5432
2020-02-17 20:31:31.804 CST [51224] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2020-02-17 20:31:31.806 CST [51224] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-02-17 20:31:31.821 CST [51225] LOG:  database system was interrupted; last known up at 2020-02-17 20:20:36 CST
2020-02-17 20:31:31.838 CST [51225] LOG:  starting point-in-time recovery to 2020-02-17 20:22:46+08
2020-02-17 20:31:31.880 CST [51225] LOG:  restored log file "000000010000000000000017" from archive
2020-02-17 20:31:31.910 CST [51225] LOG:  redo starts at 0/17000028
2020-02-17 20:31:31.911 CST [51225] LOG:  consistent recovery state reached at 0/17000168
2020-02-17 20:31:31.912 CST [51224] LOG:  database system is ready to accept read only connections
2020-02-17 20:31:31.949 CST [51225] LOG:  restored log file "000000010000000000000018" from archive
 done
server started
[postgres@localhost ~]$ 2020-02-17 20:31:32.244 CST [51225] LOG:  restored log file "000000010000000000000019" from archive
2020-02-17 20:31:32.540 CST [51225] LOG:  restored log file "00000001000000000000001A" from archive
2020-02-17 20:31:32.816 CST [51225] LOG:  restored log file "00000001000000000000001B" from archive
2020-02-17 20:31:33.100 CST [51225] LOG:  restored log file "00000001000000000000001C" from archive
2020-02-17 20:31:33.400 CST [51225] LOG:  restored log file "00000001000000000000001D" from archive
2020-02-17 20:31:33.664 CST [51225] LOG:  restored log file "00000001000000000000001E" from archive
2020-02-17 20:31:33.944 CST [51225] LOG:  restored log file "00000001000000000000001F" from archive
2020-02-17 20:31:34.236 CST [51225] LOG:  restored log file "000000010000000000000020" from archive
2020-02-17 20:31:34.528 CST [51225] LOG:  restored log file "000000010000000000000021" from archive
2020-02-17 20:31:34.827 CST [51225] LOG:  restored log file "000000010000000000000022" from archive
2020-02-17 20:31:35.125 CST [51225] LOG:  restored log file "000000010000000000000023" from archive
2020-02-17 20:31:35.418 CST [51225] LOG:  restored log file "000000010000000000000024" from archive
2020-02-17 20:31:35.718 CST [51225] LOG:  restored log file "000000010000000000000025" from archive
2020-02-17 20:31:36.021 CST [51225] LOG:  restored log file "000000010000000000000026" from archive
2020-02-17 20:31:36.321 CST [51225] LOG:  restored log file "000000010000000000000027" from archive
2020-02-17 20:31:36.644 CST [51225] LOG:  restored log file "000000010000000000000028" from archive
2020-02-17 20:31:36.950 CST [51225] LOG:  restored log file "000000010000000000000029" from archive
2020-02-17 20:31:37.249 CST [51225] LOG:  restored log file "00000001000000000000002A" from archive
2020-02-17 20:31:37.594 CST [51225] LOG:  restored log file "00000001000000000000002B" from archive
2020-02-17 20:31:37.891 CST [51225] LOG:  restored log file "00000001000000000000002C" from archive
2020-02-17 20:31:38.673 CST [51225] LOG:  restored log file "00000001000000000000002D" from archive
2020-02-17 20:31:38.967 CST [51225] LOG:  restored log file "00000001000000000000002E" from archive
2020-02-17 20:31:39.279 CST [51225] LOG:  restored log file "00000001000000000000002F" from archive
2020-02-17 20:31:39.558 CST [51225] LOG:  restored log file "000000010000000000000030" from archive
2020-02-17 20:31:39.878 CST [51225] LOG:  restored log file "000000010000000000000031" from archive
2020-02-17 20:31:40.187 CST [51225] LOG:  restored log file "000000010000000000000032" from archive
2020-02-17 20:31:40.488 CST [51225] LOG:  restored log file "000000010000000000000033" from archive
2020-02-17 20:31:40.835 CST [51225] LOG:  restored log file "000000010000000000000034" from archive
2020-02-17 20:31:41.160 CST [51225] LOG:  restored log file "000000010000000000000035" from archive
2020-02-17 20:31:41.482 CST [51225] LOG:  restored log file "000000010000000000000036" from archive
2020-02-17 20:31:41.797 CST [51225] LOG:  restored log file "000000010000000000000037" from archive
2020-02-17 20:31:42.883 CST [51225] LOG:  restored log file "000000010000000000000038" from archive
2020-02-17 20:31:43.168 CST [51225] LOG:  restored log file "000000010000000000000039" from archive
2020-02-17 20:31:43.470 CST [51225] LOG:  restored log file "00000001000000000000003A" from archive
2020-02-17 20:31:43.804 CST [51225] LOG:  restored log file "00000001000000000000003B" from archive
2020-02-17 20:31:44.099 CST [51225] LOG:  restored log file "00000001000000000000003C" from archive
2020-02-17 20:31:44.387 CST [51225] LOG:  restored log file "00000001000000000000003D" from archive
2020-02-17 20:31:44.850 CST [51225] LOG:  restored log file "00000001000000000000003E" from archive
2020-02-17 20:31:45.129 CST [51225] LOG:  restored log file "00000001000000000000003F" from archive
2020-02-17 20:31:45.416 CST [51225] LOG:  restored log file "000000010000000000000040" from archive
2020-02-17 20:31:45.703 CST [51225] LOG:  restored log file "000000010000000000000041" from archive
2020-02-17 20:31:46.049 CST [51225] LOG:  restored log file "000000010000000000000042" from archive
2020-02-17 20:31:46.922 CST [51225] LOG:  restored log file "000000010000000000000043" from archive
2020-02-17 20:31:46.962 CST [51225] LOG:  recovery stopping before commit of transaction 560, time 2020-02-17 20:23:05.430435+08
2020-02-17 20:31:46.962 CST [51225] LOG:  recovery has paused
2020-02-17 20:31:46.962 CST [51225] HINT:  Execute pg_wal_replay_resume() to continue.

[postgres@localhost ~]$

可以在恢复日志中看到这么一句话：

2020-02-17 20:31:46.922 CST [51225] LOG:  restored log file "000000010000000000000043" from archive
2020-02-17 20:31:46.962 CST [51225] LOG:  recovery stopping before commit of transaction 560, time 2020-02-17 20:23:05.430435+08
2020-02-17 20:31:46.962 CST [51225] LOG:  recovery has paused
2020-02-17 20:31:46.962 CST [51225] HINT:  Execute pg_wal_replay_resume() to continue.

7.4 验证，进入数据库，发现testpitr2已经恢复

[postgres@localhost ~]$ psql
psql (10.10)
Type "help" for help.

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \d
           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | testpitr1 | table | postgres
 public | testpitr2 | table | postgres
(2 rows)
test=# select count(*) from testpitr1;
  count  
---------
 1338084
(1 row)

test=# select count(*) from testpitr2;
  count  
---------
 1349856
(1 row)

test=#

如果需要恢复table3，则必须再次删除数据文件夹，建立recovery.conf。

8. 恢复table3

8.1 停掉数据库，删除数据文件夹，重新解压基础备份，创建recovery.conf，将数据库恢复到testpitr3时刻

[postgres@localhost ~]$ pg_ctl -D db1 stop
waiting for server to shut down....2020-02-17 20:34:07.018 CST [51224] LOG:  received fast shutdown request
2020-02-17 20:34:07.019 CST [51224] LOG:  aborting any active transactions
2020-02-17 20:34:07.062 CST [51227] LOG:  shutting down
2020-02-17 20:34:07.083 CST [51224] LOG:  database system is shut down
 done
server stopped
[postgres@localhost ~]$ 
[postgres@localhost ~]$ rm -rf db1
[postgres@localhost ~]$ tar xvf db1.tar
[postgres@localhost ~]$ cd db1
[postgres@localhost ~/db1]$ vi recovery.conf
[postgres@localhost ~/db1]$ cat recovery.conf 
restore_command = 'cp /home/postgres/archivedir/%f %p'
recovery_target_time = '2020-02-17 20:23:08'

8.2 启动数据库

[postgres@localhost ~]$ pg_ctl -D db1 start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2020-02-17 20:38:22.153 CST [51380] LOG:  listening on IPv6 address "::1", port 5432
2020-02-17 20:38:22.153 CST [51380] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2020-02-17 20:38:22.155 CST [51380] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-02-17 20:38:22.174 CST [51381] LOG:  database system was interrupted; last known up at 2020-02-17 20:20:36 CST
2020-02-17 20:38:22.193 CST [51381] LOG:  starting point-in-time recovery to 2020-02-17 20:23:08+08
2020-02-17 20:38:22.227 CST [51381] LOG:  restored log file "000000010000000000000017" from archive
2020-02-17 20:38:22.253 CST [51381] LOG:  redo starts at 0/17000028
2020-02-17 20:38:22.254 CST [51381] LOG:  consistent recovery state reached at 0/17000168
2020-02-17 20:38:22.255 CST [51380] LOG:  database system is ready to accept read only connections
2020-02-17 20:38:22.293 CST [51381] LOG:  restored log file "000000010000000000000018" from archive
 done
server started
[postgres@localhost ~]$ 2020-02-17 20:38:22.577 CST [51381] LOG:  restored log file "000000010000000000000019" from archive
2020-02-17 20:38:22.857 CST [51381] LOG:  restored log file "00000001000000000000001A" from archive
2020-02-17 20:38:23.135 CST [51381] LOG:  restored log file "00000001000000000000001B" from archive
2020-02-17 20:38:23.412 CST [51381] LOG:  restored log file "00000001000000000000001C" from archive
2020-02-17 20:38:23.684 CST [51381] LOG:  restored log file "00000001000000000000001D" from archive
2020-02-17 20:38:23.946 CST [51381] LOG:  restored log file "00000001000000000000001E" from archive
2020-02-17 20:38:24.206 CST [51381] LOG:  restored log file "00000001000000000000001F" from archive
2020-02-17 20:38:24.469 CST [51381] LOG:  restored log file "000000010000000000000020" from archive
2020-02-17 20:38:24.739 CST [51381] LOG:  restored log file "000000010000000000000021" from archive
2020-02-17 20:38:25.008 CST [51381] LOG:  restored log file "000000010000000000000022" from archive
2020-02-17 20:38:25.282 CST [51381] LOG:  restored log file "000000010000000000000023" from archive
2020-02-17 20:38:25.553 CST [51381] LOG:  restored log file "000000010000000000000024" from archive
2020-02-17 20:38:25.840 CST [51381] LOG:  restored log file "000000010000000000000025" from archive
2020-02-17 20:38:26.143 CST [51381] LOG:  restored log file "000000010000000000000026" from archive
2020-02-17 20:38:26.411 CST [51381] LOG:  restored log file "000000010000000000000027" from archive
2020-02-17 20:38:26.690 CST [51381] LOG:  restored log file "000000010000000000000028" from archive
2020-02-17 20:38:26.962 CST [51381] LOG:  restored log file "000000010000000000000029" from archive
2020-02-17 20:38:27.258 CST [51381] LOG:  restored log file "00000001000000000000002A" from archive
2020-02-17 20:38:27.675 CST [51381] LOG:  restored log file "00000001000000000000002B" from archive
2020-02-17 20:38:27.971 CST [51381] LOG:  restored log file "00000001000000000000002C" from archive
2020-02-17 20:38:28.251 CST [51381] LOG:  restored log file "00000001000000000000002D" from archive
2020-02-17 20:38:28.708 CST [51381] LOG:  restored log file "00000001000000000000002E" from archive
2020-02-17 20:38:29.298 CST [51381] LOG:  restored log file "00000001000000000000002F" from archive
2020-02-17 20:38:29.746 CST [51381] LOG:  restored log file "000000010000000000000030" from archive
2020-02-17 20:38:30.008 CST [51381] LOG:  restored log file "000000010000000000000031" from archive
2020-02-17 20:38:30.307 CST [51381] LOG:  restored log file "000000010000000000000032" from archive
2020-02-17 20:38:30.625 CST [51381] LOG:  restored log file "000000010000000000000033" from archive
2020-02-17 20:38:30.954 CST [51381] LOG:  restored log file "000000010000000000000034" from archive
2020-02-17 20:38:31.259 CST [51381] LOG:  restored log file "000000010000000000000035" from archive
2020-02-17 20:38:31.565 CST [51381] LOG:  restored log file "000000010000000000000036" from archive
2020-02-17 20:38:31.855 CST [51381] LOG:  restored log file "000000010000000000000037" from archive
2020-02-17 20:38:32.338 CST [51381] LOG:  restored log file "000000010000000000000038" from archive
2020-02-17 20:38:32.726 CST [51381] LOG:  restored log file "000000010000000000000039" from archive
2020-02-17 20:38:33.057 CST [51381] LOG:  restored log file "00000001000000000000003A" from archive
2020-02-17 20:38:33.360 CST [51381] LOG:  restored log file "00000001000000000000003B" from archive
2020-02-17 20:38:33.758 CST [51381] LOG:  restored log file "00000001000000000000003C" from archive
2020-02-17 20:38:34.166 CST [51381] LOG:  restored log file "00000001000000000000003D" from archive
2020-02-17 20:38:35.764 CST [51381] LOG:  restored log file "00000001000000000000003E" from archive
2020-02-17 20:38:36.050 CST [51381] LOG:  restored log file "00000001000000000000003F" from archive
2020-02-17 20:38:36.316 CST [51381] LOG:  restored log file "000000010000000000000040" from archive
2020-02-17 20:38:36.605 CST [51381] LOG:  restored log file "000000010000000000000041" from archive
2020-02-17 20:38:36.879 CST [51381] LOG:  restored log file "000000010000000000000042" from archive
2020-02-17 20:38:37.180 CST [51381] LOG:  restored log file "000000010000000000000043" from archive
2020-02-17 20:38:37.252 CST [51381] LOG:  restored log file "000000010000000000000044" from archive
2020-02-17 20:38:37.273 CST [51381] LOG:  recovery stopping before commit of transaction 561, time 2020-02-17 20:25:00.622538+08
2020-02-17 20:38:37.273 CST [51381] LOG:  recovery has paused
2020-02-17 20:38:37.273 CST [51381] HINT:  Execute pg_wal_replay_resume() to continue.

[postgres@localhost ~]$

8.3 验证，进入数据库，发现testpitr3已经恢复

[postgres@localhost ~]$ psql
psql (10.10)
Type "help" for help.

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \d
           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | testpitr1 | table | postgres
 public | testpitr2 | table | postgres
 public | testpitr3 | table | postgres
(3 rows)

test=# select count(*) from testpitr1; select count(*) from testpitr2; select count(*) from testpitr3;
  count  
---------
 1338084
(1 row)

  count  
---------
 1349856
(1 row)

  count  
---------
 1361628
(1 row)

test=#

8.4. 1 查看一下 archivedir 的情况

[postgres@localhost ~]$ ll -h archivedir/
total 1.1G
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000001
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000002
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000003
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000004
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000005
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000006
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000007
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000008
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000009
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000A
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000B
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000C
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000D
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000E
-rw------- 1 postgres postgres 16M Feb 17 20:19 00000001000000000000000F
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000010
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000011
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000012
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000013
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000014
-rw------- 1 postgres postgres 16M Feb 17 20:19 000000010000000000000015
-rw------- 1 postgres postgres 16M Feb 17 20:20 000000010000000000000016
-rw------- 1 postgres postgres 16M Feb 17 20:21 000000010000000000000017
-rw------- 1 postgres postgres 291 Feb 17 20:21 000000010000000000000017.00000028.backup
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000018
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000019
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000001A
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000001B
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000001C
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000001D
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000001E
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000001F
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000020
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000021
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000022
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000023
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000024
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000025
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000026
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000027
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000028
-rw------- 1 postgres postgres 16M Feb 17 20:22 000000010000000000000029
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000002A
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000002B
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000002C
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000002D
-rw------- 1 postgres postgres 16M Feb 17 20:22 00000001000000000000002E
-rw------- 1 postgres postgres 16M Feb 17 20:23 00000001000000000000002F
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000030
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000031
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000032
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000033
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000034
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000035
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000036
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000037
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000038
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000039
-rw------- 1 postgres postgres 16M Feb 17 20:23 00000001000000000000003A
-rw------- 1 postgres postgres 16M Feb 17 20:23 00000001000000000000003B
-rw------- 1 postgres postgres 16M Feb 17 20:23 00000001000000000000003C
-rw------- 1 postgres postgres 16M Feb 17 20:23 00000001000000000000003D
-rw------- 1 postgres postgres 16M Feb 17 20:23 00000001000000000000003E
-rw------- 1 postgres postgres 16M Feb 17 20:23 00000001000000000000003F
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000040
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000041
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000042
-rw------- 1 postgres postgres 16M Feb 17 20:23 000000010000000000000043
-rw------- 1 postgres postgres 16M Feb 17 20:25 000000010000000000000044
[postgres@localhost ~]$

总结

出现的错误

上述的"备份和还原"是第2次的结果. 第1次进行"备份和还原", 在恢复table3操作时, 出现"cp: cannot stat ‘/home/postgres/archivedir/00000001000000000000005C’: No such file or directory"错误:

[postgres@localhost ~]$ pg_ctl -D db1 start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2020-02-17 19:50:27.795 CST [50373] LOG:  listening on IPv6 address "::1", port 5432
2020-02-17 19:50:27.795 CST [50373] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2020-02-17 19:50:27.796 CST [50373] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-02-17 19:50:27.811 CST [50374] LOG:  database system was interrupted; last known up at 2020-02-17 19:07:24 CST
2020-02-17 19:50:27.832 CST [50374] LOG:  starting point-in-time recovery to 2020-02-17 19:10:52+08
2020-02-17 19:50:27.871 CST [50374] LOG:  restored log file "00000001000000000000002E" from archive
2020-02-17 19:50:27.902 CST [50374] LOG:  redo starts at 0/2E000028
2020-02-17 19:50:27.903 CST [50374] LOG:  consistent recovery state reached at 0/2E000130
2020-02-17 19:50:27.904 CST [50373] LOG:  database system is ready to accept read only connections
2020-02-17 19:50:27.940 CST [50374] LOG:  restored log file "00000001000000000000002F" from archive
 done
server started
[postgres@localhost ~]$ 2020-02-17 19:50:28.234 CST [50374] LOG:  restored log file "000000010000000000000030" from archive
2020-02-17 19:50:28.519 CST [50374] LOG:  restored log file "000000010000000000000031" from archive
2020-02-17 19:50:28.816 CST [50374] LOG:  restored log file "000000010000000000000032" from archive
2020-02-17 19:50:29.114 CST [50374] LOG:  restored log file "000000010000000000000033" from archive
2020-02-17 19:50:29.408 CST [50374] LOG:  restored log file "000000010000000000000034" from archive
2020-02-17 19:50:29.690 CST [50374] LOG:  restored log file "000000010000000000000035" from archive
2020-02-17 19:50:29.964 CST [50374] LOG:  restored log file "000000010000000000000036" from archive
2020-02-17 19:50:30.260 CST [50374] LOG:  restored log file "000000010000000000000037" from archive
2020-02-17 19:50:30.545 CST [50374] LOG:  restored log file "000000010000000000000038" from archive
2020-02-17 19:50:30.843 CST [50374] LOG:  restored log file "000000010000000000000039" from archive
[postgres@localhost ~]$ 2020-02-17 19:50:31.190 CST [50374] LOG:  restored log file "00000001000000000000003A" from archive
2020-02-17 19:50:31.467 CST [50374] LOG:  restored log file "00000001000000000000003B" from archive
2020-02-17 19:50:31.753 CST [50374] LOG:  restored log file "00000001000000000000003C" from archive
2020-02-17 19:50:32.046 CST [50374] LOG:  restored log file "00000001000000000000003D" from archive
2020-02-17 19:50:32.351 CST [50374] LOG:  restored log file "00000001000000000000003E" from archive
2020-02-17 19:50:32.666 CST [50374] LOG:  restored log file "00000001000000000000003F" from archive
2020-02-17 19:50:32.958 CST [50374] LOG:  restored log file "000000010000000000000040" from archive
2020-02-17 19:50:33.265 CST [50374] LOG:  restored log file "000000010000000000000041" from archive
2020-02-17 19:50:33.622 CST [50374] LOG:  restored log file "000000010000000000000042" from archive
2020-02-17 19:50:33.894 CST [50374] LOG:  restored log file "000000010000000000000043" from archive
2020-02-17 19:50:34.196 CST [50374] LOG:  restored log file "000000010000000000000044" from archive
2020-02-17 19:50:34.482 CST [50374] LOG:  restored log file "000000010000000000000045" from archive
2020-02-17 19:50:34.793 CST [50374] LOG:  restored log file "000000010000000000000046" from archive
2020-02-17 19:50:35.101 CST [50374] LOG:  restored log file "000000010000000000000047" from archive
2020-02-17 19:50:35.386 CST [50374] LOG:  restored log file "000000010000000000000048" from archive
2020-02-17 19:50:35.663 CST [50374] LOG:  restored log file "000000010000000000000049" from archive
2020-02-17 19:50:35.984 CST [50374] LOG:  restored log file "00000001000000000000004A" from archive
2020-02-17 19:50:36.309 CST [50374] LOG:  restored log file "00000001000000000000004B" from archive
2020-02-17 19:50:36.624 CST [50374] LOG:  restored log file "00000001000000000000004C" from archive
2020-02-17 19:50:36.913 CST [50374] LOG:  restored log file "00000001000000000000004D" from archive
2020-02-17 19:50:37.218 CST [50374] LOG:  restored log file "00000001000000000000004E" from archive
2020-02-17 19:50:37.515 CST [50374] LOG:  restored log file "00000001000000000000004F" from archive
2020-02-17 19:50:37.852 CST [50374] LOG:  restored log file "000000010000000000000050" from archive
2020-02-17 19:50:38.201 CST [50374] LOG:  restored log file "000000010000000000000051" from archive
2020-02-17 19:50:38.489 CST [50374] LOG:  restored log file "000000010000000000000052" from archive
2020-02-17 19:50:38.790 CST [50374] LOG:  restored log file "000000010000000000000053" from archive
2020-02-17 19:50:39.088 CST [50374] LOG:  restored log file "000000010000000000000054" from archive
2020-02-17 19:50:39.507 CST [50374] LOG:  restored log file "000000010000000000000055" from archive
2020-02-17 19:50:39.793 CST [50374] LOG:  restored log file "000000010000000000000056" from archive
2020-02-17 19:50:40.240 CST [50374] LOG:  restored log file "000000010000000000000057" from archive
2020-02-17 19:50:40.533 CST [50374] LOG:  restored log file "000000010000000000000058" from archive
2020-02-17 19:50:40.854 CST [50374] LOG:  restored log file "000000010000000000000059" from archive
2020-02-17 19:50:41.135 CST [50374] LOG:  restored log file "00000001000000000000005A" from archive
2020-02-17 19:50:41.210 CST [50374] LOG:  restored log file "00000001000000000000005B" from archive
cp: cannot stat ‘/home/postgres/archivedir/00000001000000000000005C’: No such file or directory
2020-02-17 19:50:41.257 CST [50374] LOG:  redo done at 0/5B000060
2020-02-17 19:50:41.257 CST [50374] LOG:  last completed transaction was at log time 2020-02-17 19:10:45.375614+08
2020-02-17 19:50:41.278 CST [50374] LOG:  restored log file "00000001000000000000005B" from archive
cp: cannot stat ‘/home/postgres/archivedir/00000002.history’: No such file or directory
2020-02-17 19:50:41.312 CST [50374] LOG:  selected new timeline ID: 2
2020-02-17 19:50:41.347 CST [50374] LOG:  archive recovery complete
cp: cannot stat ‘/home/postgres/archivedir/00000001.history’: No such file or directory
2020-02-17 19:50:42.004 CST [50373] LOG:  database system is ready to accept connections

第1次做"备份与还原"时, 创建table3后的, archivedir目录情况如下, 查看archivedir内容, 并没有"00000001000000000000005C"归档文件.

[postgres@localhost ~]$ ll -h archivedir/
total 1.1G
-rw------- 1 postgres postgres 16M Feb 17 19:06 000000010000000000000018
-rw------- 1 postgres postgres 16M Feb 17 19:06 000000010000000000000019
-rw------- 1 postgres postgres 16M Feb 17 19:06 00000001000000000000001A
-rw------- 1 postgres postgres 16M Feb 17 19:06 00000001000000000000001B
-rw------- 1 postgres postgres 16M Feb 17 19:06 00000001000000000000001C
-rw------- 1 postgres postgres 16M Feb 17 19:06 00000001000000000000001D
-rw------- 1 postgres postgres 16M Feb 17 19:06 00000001000000000000001E
-rw------- 1 postgres postgres 16M Feb 17 19:06 00000001000000000000001F
-rw------- 1 postgres postgres 16M Feb 17 19:06 000000010000000000000020
-rw------- 1 postgres postgres 16M Feb 17 19:06 000000010000000000000021
-rw------- 1 postgres postgres 16M Feb 17 19:06 000000010000000000000022
-rw------- 1 postgres postgres 16M Feb 17 19:06 000000010000000000000023
-rw------- 1 postgres postgres 16M Feb 17 19:06 000000010000000000000024
-rw------- 1 postgres postgres 16M Feb 17 19:06 000000010000000000000025
-rw------- 1 postgres postgres 16M Feb 17 19:06 000000010000000000000026
-rw------- 1 postgres postgres 16M Feb 17 19:06 000000010000000000000027
-rw------- 1 postgres postgres 16M Feb 17 19:06 000000010000000000000028
-rw------- 1 postgres postgres 16M Feb 17 19:06 000000010000000000000029
-rw------- 1 postgres postgres 16M Feb 17 19:06 00000001000000000000002A
-rw------- 1 postgres postgres 16M Feb 17 19:06 00000001000000000000002B
-rw------- 1 postgres postgres 16M Feb 17 19:06 00000001000000000000002C
-rw------- 1 postgres postgres 16M Feb 17 19:07 00000001000000000000002D
-rw------- 1 postgres postgres 16M Feb 17 19:08 00000001000000000000002E
-rw------- 1 postgres postgres 291 Feb 17 19:08 00000001000000000000002E.00000028.backup
-rw------- 1 postgres postgres 16M Feb 17 19:10 00000001000000000000002F
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000030
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000031
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000032
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000033
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000034
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000035
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000036
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000037
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000038
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000039
-rw------- 1 postgres postgres 16M Feb 17 19:10 00000001000000000000003A
-rw------- 1 postgres postgres 16M Feb 17 19:10 00000001000000000000003B
-rw------- 1 postgres postgres 16M Feb 17 19:10 00000001000000000000003C
-rw------- 1 postgres postgres 16M Feb 17 19:10 00000001000000000000003D
-rw------- 1 postgres postgres 16M Feb 17 19:10 00000001000000000000003E
-rw------- 1 postgres postgres 16M Feb 17 19:10 00000001000000000000003F
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000040
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000041
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000042
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000043
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000044
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000045
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000046
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000047
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000048
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000049
-rw------- 1 postgres postgres 16M Feb 17 19:10 00000001000000000000004A
-rw------- 1 postgres postgres 16M Feb 17 19:10 00000001000000000000004B
-rw------- 1 postgres postgres 16M Feb 17 19:10 00000001000000000000004C
-rw------- 1 postgres postgres 16M Feb 17 19:10 00000001000000000000004D
-rw------- 1 postgres postgres 16M Feb 17 19:10 00000001000000000000004E
-rw------- 1 postgres postgres 16M Feb 17 19:10 00000001000000000000004F
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000050
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000051
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000052
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000053
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000054
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000055
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000056
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000057
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000058
-rw------- 1 postgres postgres 16M Feb 17 19:10 000000010000000000000059
-rw------- 1 postgres postgres 16M Feb 17 19:11 00000001000000000000005A
-rw------- 1 postgres postgres 16M Feb 17 19:12 00000001000000000000005B
-rw------- 1 postgres postgres  44 Feb 17 19:50 00000002.history
[postgres@localhost ~]$

但table3恢复后的查询结果和第2次均一样: 1361628

[postgres@localhost ~]$ psql
psql (10.10)
Type "help" for help.

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \d
           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | testpitr1 | table | postgres
 public | testpitr2 | table | postgres
 public | testpitr3 | table | postgres
(3 rows)

test=# select count(*) from testpitr3;
  count  
---------
 1361628
(1 row)

test=#

错误可能原因分析

具体原因未知, 唯一找到的差异如下, 且记录下来:

第1次做时, postgres.conf 中 archive_command的赋值如下:

archive_command = ' test ! -f /home/postgres/archivedir/%f && cp %p /home/postgres/archivedir/%f'

第2次做时, postgres.conf 中 archive_command的赋值如下:

archive_command = 'test ! -f /home/postgres/archivedir/%f && cp %p /home/postgres/archivedir/%f'

看出来了没, test 前面多了一个空格, 不知道是不是此问题引起的.

另外, 两次备份与还原后的table3的查询结果均正确, 说明第1次尽管报错了, 但有可能成功恢复了.

参考

PostgreSQL 9.5 基于日志的备份与还原

清海风缘
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
进阶数据库系列（二）：PostgreSQL 目录结构与配置文件 postgresql.conf 详解
pgdba的博客
07-30 1431
前面介绍了 PostgreSQL 基础概念及安装部署 相关的知识点，今天我将详细的为大家介绍 PostgreSQL 目录结构与配置文件 postgresql.conf 介绍 相关知识，希望大家能够从中收获多多！如有帮助，请点在看、转发支持一波！！！环境搭建完成后，从环境变量里看它涉及的目录。
PostgreSQL 备份与恢复
snail_aaa的专栏
05-17 2254
http://www.wohedb.com/db_html_doc/administrator_guide/adm_9_backup_and_restore.htmwww.wohedb.com    中文数据库 第九章   备份与恢复    PostgreSQL提供了三种备份和恢复的方式:SQL dump、文件系统复制和联机热备份。 每一种备份方式都有自己的优点和缺点，
（十二）PostgreSQL开启归档模式
lee_vincent1的博客
04-18 474
查看归档目录，已经有归档日志。目前的配置是最简单的，后期可以进行优化，比如每天的归档日志独立放在一个目录。–修改postgresql.conf配置文件。当前数据库未开启归档模式。
PostgreSQL 基于日志的备份与还原
weixin_30532987的博客
01-18 341
参考：http://www.postgresql.org/docs/9.5/static/continuous-archiving.html http://www.mkyong.com/database/postgresql-point-in-time-recovery-incremental-backup/ wal，即预写式日志，是日志的标准实现方式，简单而言就是将对数据库的...
PostgreSQL数据归档与恢复教学
最新发布
2401_85763803的博客
06-20 843
PostgreSQL是一个功能强大的开源关系型数据库管理系统，它提供了多种数据归档和恢复的策略来确保数据的安全性和可靠性。本教程将详细介绍如何在PostgreSQL中配置数据归档，以及如何进行数据恢复。
（十一）PostgreSQL的wal日志
lee_vincent1的博客
04-17 378
在 PostgreSQL 中，WAL（Write-Ahead Logging）日志是一种用于保证数据库事务日志的完整性和数据恢复的机制。WAL 的核心思想是，在对数据库中的数据进行任何修改之前，先将这些修改记录到磁盘上的日志中。这种方法可以在发生故障时，确保数据的持久性和一致性，因为系统可以使用这些日志来重放或撤销操作，恢复到故障发生前的状态。
PostgreSQL教程：备份与恢复（WAL日志、归档日志）
学亮编程手记
09-17 1906
防止数据丢失的第一道防线就是备份。数据丢失有的是硬件损坏，还有人为的误删之类的，也有BUG的原因导致误删数据。正常备份和恢复，如果公司有DBA，一般咱们不用参与，BUT，学的Java，啥都得会点~~
PostgreSQL主库创建表空间导致备库宕机
01-23 360
PostgreSQL主库创建表空间导致备库宕机 PG版本：11.7 最后编辑时间：2022年1月23日00:17:06 主库创建表空间 [postgres@rhel6wcb /]$ mkdir -p /home/postgres/pg11/pg_tbs/ [postgres@rhel6wcb /]$ psql psql (11.7) Type "help" for help. postgres@postgres=>create tablespace tbs_his owner test loca
postgresql数据库在系统服务中消失找不到
weixin_47800450的博客
05-15 1651
解决办法： postgresql的bin目录 + \pg_ctl register -N PostgreSQL -D + " postgresql的data目录" 例如： E:\postgresqlTTTTTTTTTTTT\postgresql-13.2-1-windows-x64-binaries\pgsql\bin\pg_ctl register -N PostgreSQL -D "E:\postgresqlTTTTTTTTTTTT\postgresql-13.2-1-windows-x64-bin
linux postgres恢复数据库,postgres 数据库备份与恢复
weixin_35658042的博客
05-02 462
postgres 数据库快速备份与恢复，在CMD命令行工具下面非常便捷好用，以下是小编悼念到的方法：利用PostgreSQL内置的工具，pg_dump，psql。这两个指令 在数据库的安装目录下，比如：C:\Program Files\PostgreSQL\10\;然后进入到bin文件夹，会看到不少的exe文件，这就是PostgreSQL内置的工具了。里面找到 pg_dump.exe，psql.e...
pgbackrest：可靠的PostgreSQL备份和还原
01-31
**pgbackrest：可靠的PostgreSQL备份与还原** 在数据库管理中，备份和恢复是至关重要的环节，特别是对于像PostgreSQL这样高度可靠的企业级数据库系统。pgbackrest是一款专为PostgreSQL设计的开源备份工具，它提供了...
pghoard：PostgreSQL备份和还原服务
02-05
**pghoard：PostgreSQL备份与恢复神器** `pghoard` 是一个高效且功能强大的工具，专为PostgreSQL数据库设计，提供全面的备份和快速的恢复服务。它由Aiven公司开发，支持云对象存储，如Amazon S3、Google Cloud ...
Postgresql备份和增量恢复方案
09-09
因此，增量备份的概念在PostgreSQL中表现为基于基础备份加上WAL日志的重做恢复。 在设置增量备份时，首先要确保`wal_level`设置为`replica`或更高，这样会记录足够的信息以允许增量恢复。同时，启用`archive_mode`...
pg_rman：PostgreSQL备份和还原管理工具
02-05
《pg_rman：PostgreSQL备份与恢复的利器》 在PostgreSQL数据库管理中，数据的安全性和完整性至关重要。为了确保在面临系统故障、数据丢失或需要回溯到特定时间点时能够迅速恢复，备份和还原机制成为了必备的工具。...
linux存档文件格式分析
stonesharp的专栏
08-29 1192
出于好奇,想了解一下linux的存档文件（*.a）的格式。在网上大概找了一下，没找到比较好的（没细找），所以想自己摸索 一下，把它的文件格式弄清楚。结构发现这个文件的格式竟然那么简单。在这里和大家分享一下，有错误的地方希望大家指正。 1.存档文件（archive）简介  存档文件（*.a）文件相当于windows的静态链接库，他把很多的目标文件（*.o）文件打包为单一的库文件。在链接程序
【参赛作品43】openGauss/MogDB/PostgreSQL对比学习
Gauss松鼠会
12-17 2030
本文将从日常使用的角度对比学习国产数据库openGauss/MogDB与开源数据库PostgreSQL之间的差异点。
PostgreSQL执行pg_stop_backup()问题
luojinbai的专栏
02-03 4528
问题描述： 在使用“物理备份+WAL日志备份”的时候，执行pg_stop_backup()时候发生错误： postgres=# select pg_stop_backup(); NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to be archived WARNING: pg_stop_ba
pgsql日志管理和备份
黑乎乎的小升
06-01 1513
在线日志，相当于mysql的事务日志，存放的是正在发生的事务，提交或未提交都有记录，默认大小16M，仅初始化环境时更改。日志序列号，WAL日志全局唯一的标识，每个事件都有唯一的LSN号，多个事件组成一个动作，多个动作构成一个事务。只要发现WAL日志文件有更新切换就备份，正在使用的不会备份，远程与本地仅可选一方。记录的服务运行情况信息，12版本之前默认不开启。两台主机，一主master，一从standby。将pgsql新版本的运行日志存储到数据库。
PostgreSQL 8.1.22 开启归档模式及在线备份恢复
lk的窝
10-20 2194
以下内容参考自： http://www.kuqin.com/postgreSQL8.1_doc/backup-online.html https://www.postgresql.org/docs/8.1/static/backup-online.html 数据库版本： postgres=# select version();                            
postgresql数据库备份与恢复
05-09
postgresql（简称Postgres）是一个高性能的企业级开源关系型数据库管理系统。在实际运行过程中，由于各种原因，Postgres数据库可能会出现一些问题，比如数据损坏、硬盘故障、误删除等情况，这时候备份和恢复就显得尤为重要。 Postgres数据库备份 Postgres数据库备份主要包括两种方式：物理备份和逻辑备份。 1. 物理备份 物理备份是指备份整个Postgres数据库的物理文件，包括数据和日志等信息。这种备份方式具有非常高的恢复效率和完整性，备份后可以快速地还原到任何一个时间点的状态。但是，物理备份存在一些限制，例如，如果备份时数据库正在运行，会存在锁定文件的问题，同时备份后的数据不太容易人工查看和修改。 实现物理备份的两种方式： （1）基于文件系统备份 这种备份方式是直接备份Postgres数据库的文件目录，包括数据文件（Data File）、事务日志文件（WAL File）和配置文件等。使用类似于cp、tar等常见的文件操作命令完成备份，简单方便。 （2）基于pg_basebackup工具备份 pg_basebackup是PostgreSQL自带的备份工具，可以很方便地进行物理备份。只需指定备份目录，即可将整个PostgreSQL数据库备份到指定目录下。 2. 逻辑备份 逻辑备份是指备份Postgres数据库中的逻辑数据，比如表、视图、函数、触发器等，备份后数据可以人工查看和编辑。但是，逻辑备份的恢复效率没有物理备份高，同时在备份和恢复过程中需要注意一些事项。 实现逻辑备份的两种方式： （1）基于pg_dump工具备份 pg_dump工具是PostgreSQL自带的备份工具，可以对数据库进行逻辑备份。备份时可以指定备份的对象（比如表、视图、函数等），也可以备份整个数据库。备份完成后，可以通过pg_restore工具进行恢复。 （2）基于导出/导入工具备份 除了pg_dump工具，还有其他的导出/导入工具可以进行逻辑备份，比如psql、SQL Shell等。其中，psql是PostgreSQL自带的终端用户控制台工具，可以对数据库进行交互式的管理和操作，包括导出/导入数据等。 Postgres数据库恢复 Postgres数据库恢复需要根据备份类型进行相应的恢复操作。 1. 对于物理备份 （1）基于文件系统备份的恢复 只需要将备份文件还原到需要恢复的PostgreSQL数据库目录下即可，恢复后启动PostgreSQL即可。 （2）基于pg_basebackup工具备份的恢复 指定备份目录为数据目录，启动PostgreSQL即可。在恢复完成后，还可以通过pg_archivecleanup工具清理归档日志。 2. 对于逻辑备份 （1）基于pg_dump工具备份的恢复 首先需要创建一个新的数据库（如果原数据库已经损坏）。然后使用pg_restore工具将备份文件恢复到指定数据库中即可。 （2）基于导出/导入工具备份的恢复 与pg_dump工具类似，需要先创建一个新的数据库，然后使用导入工具恢复备份文件到指定数据库中。 总结 Postgres数据库备份和恢复是保证数据安全、保障系统稳定的重要手段。无论是物理备份还是逻辑备份，都有各自的优势和限制，需要根据实际需求进行选择和应用。同时，备份和恢复操作也需要密切结合数据库实际情况和业务需求，综合考虑一些因素，如备份周期、备份策略、备份类型、备份存储空间、恢复时间等，才能够达到最佳效果。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值