创建相关管理目录
# mkdir –p /data/pgsql/
chown –R postgres: postgres /data/pgsql/
su - postgres
mkdir -p /data/pgsql/backup
mkdir -p /data/pgsql/archive
backup目录用来存放基础备份
archive目录用来存放归档
接下来我们修改我们的postgresql.conf文件的相关设置
wal_level = replica
archive_mode = on
archive_command = ' test ! -f /data/pgsql/archive/%f && cp %p /data/pgsql/archive/%f '
archive_command 参数的默认值是个空字符串,它的值可以是一条shell命令或者一个复杂的shell脚本。
在archive_command的shell命令或脚本中可以用 %p 表示将要归档的WAL文件的包含完整路径信息的文件名,用 %f 代表不包含路径信息的WAL文件的文件名。
修改wal_level和archive_mode参数都需要重新启动数据库才可以生效,修改archive_command不需要重启,只需要reload即可,例如:
postgres=# SELECT pg_reload_conf();
postgres=# show wal_level;
wal_level
-----------
replica
(1 row)
postgres=# show archive_mode;
archive_mode
--------------
on
(1 row)
postgres=# show archive_command;
archive_command
------------------------------------------------------------------
test ! -f /data/pgsql/archive/%f && cp %p /data/pgsql/archive/%f
(1 row)
postgres=#
创建基础备份
$ -bash-4.2$ pg_basebackup -D /data/pgsql/backup/20190714151000 -v
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/D000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_62982"
pg_basebackup: write-ahead log end point: 0/D000130
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
-bash-4.2$
创建测试表:
CREATE TABLE test(
id SERIAL PRIMARY KEY,
time TIMESTAMPTZ NOT NULL DEFAULT now()
);
初始化一些测试数据
INSERT INTO test values(default,default);
INSERT INTO test values(default,default);
INSERT INTO test values(default,default);
INSERT INTO test values(default,default);
INSERT INTO test values(default,default);
INSERT INTO test values(default,default);
INSERT INTO test values(default,default);
INSERT INTO test values(default,default);
INSERT INTO test values(default,default);
INSERT INTO test values(default,default);
select * from test
id|time |
--|-------------------|
1|2019-07-15 00:39:45|
2|2019-07-15 00:39:48|
3|2019-07-15 00:39:49|
4|2019-07-15 00:39:49|
5|2019-07-15 00:39:50|
6|2019-07-15 00:39:51|
7|2019-07-15 00:39:51|
8|2019-07-15 00:39:52|
9|2019-07-15 00:39:53|
10|2019-07-15 00:39:54|
并且按照上文的方法创建一个基础备份。如果是测试,有一点需要注意,由于WAL文件是写满16MB才会进行归档,测试阶段可能写入会非常少,可以在执行完 基础备份之后,手动进行一次WAL切换。例如:
postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/1D01B858
(1 row)
或者通过设置archive_timeout参数,在达到timeout阈值时强行切换到新的WAL段。
show archive_timeout
archive_timeout|
---------------|
0 |
alter system set archive_timeout=600;
SELECT pg_reload_conf();
show archive_timeout
archive_timeout|
---------------|
10min |
接下来我们对数据做一些变更, 我们删除test的所有数据:
select now()
now |
-------------------|
2019-07-15 00:42:10|
delete from test;
select * from test;
id|time|
--|----|
下面进行恢复到时间还原点测试
停止数据库
#service postgresql-11 stop
Redirecting to /bin/systemctl stop postgresql-11.service
netstat -an | grep 5432
移除旧的数据目录
ls
backups data initdb.log
mv data data_old
ls
backups data_old initdb.log
mv 20190714151000 /var/lib/pgsql/11/data
cp /usr/pgsql-11/share/recovery.conf.sample /var/lib/pgsql/11/data/recovery.conf
修改 recovery.conf, 修改以下配置信息:
restore_command = ' cp /data/pgsql/archive/%f %p '
recovery_target_time = '2019-07-14 23:19:34+08'
然后启动数据库进入恢复状态,观察日志,如下所示:
bash-4.2$ pg_ctl start -D /var/lib/pgsql/11/data
waiting for server to start....2019-07-15 00:07:52.168 CST [65634] LOG: listening on IPv4 address "0.0.0.0", port 5432
2019-07-15 00:07:52.168 CST [65634] LOG: listening on IPv6 address "::", port 5432
2019-07-15 00:07:52.170 CST [65634] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-07-15 00:07:52.173 CST [65634] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-07-15 00:07:52.188 CST [65634] LOG: redirecting log output to logging collector process
2019-07-15 00:07:52.188 CST [65634] HINT: Future log output will appear in directory "log".
done
server started
-bash-4.2$
重启后,我们对test表进行查询,看数据是否正常恢复:
select * from test
id|time |
--|-------------------|
1|2019-07-15 00:39:45|
2|2019-07-15 00:39:48|
3|2019-07-15 00:39:49|
4|2019-07-15 00:39:49|
5|2019-07-15 00:39:50|
6|2019-07-15 00:39:51|
7|2019-07-15 00:39:51|
8|2019-07-15 00:39:52|
9|2019-07-15 00:39:53|
10|2019-07-15 00:39:54|
可以看到数据已经被恢复了
当前数据库状态为pause状态(recovery_target_action),我们可以判断当前的数据是否满足我们的预期值,如果不满足,那么可以继续向后推,直到达到我们的预期值
解除暂停状态
postgres=# select pg_wal_replay_resume();
pg_wal_replay_resume
----------------------
(1 row)
postgres=#
这时,recovery.conf文件名字变成了recovery.done可以移除
查看postgreSQL日志:
more /var/lib/pgsql/11/data/log/postgresql-Sun.log
2019-07-14 18:09:34.161 CST [11188] LOG: database system was interrupted; last known up at 2019-07-14 18:00:03 CST
2019-07-14 18:09:34.194 CST [11188] LOG: starting point-in-time recovery to 2019-07-14 17:40:34+08
2019-07-14 18:09:34.201 CST [11188] LOG: restored log file "00000002.history" from archive
2019-07-14 18:09:34.230 CST [11188] LOG: restored log file "000000020000000000000013" from archive
2019-07-14 18:09:34.256 CST [11188] LOG: redo starts at 0/13000028
2019-07-14 18:09:34.257 CST [11188] LOG: consistent recovery state reached at 0/130000F8
2019-07-14 18:09:34.257 CST [11186] LOG: database system is ready to accept read only connections
2019-07-14 18:09:34.281 CST [11188] LOG: restored log file "000000020000000000000014" from archive
2019-07-14 18:09:34.295 CST [11188] LOG: recovery stopping before commit of transaction 732, time 2019-07-14 18:00:28.482788+08
2019-07-14 18:09:34.295 CST [11188] LOG: recovery has paused
2019-07-14 18:09:34.295 CST [11188] HINT: Execute pg_wal_replay_resume() to continue.
2019-07-14 18:10:34.239 CST [11186] LOG: received fast shutdown request
2019-07-14 18:10:34.240 CST [11186] LOG: aborting any active transactions
2019-07-14 18:10:34.244 CST [11191] LOG: shutting down
2019-07-14 18:10:34.257 CST [11186] LOG: database system is shut down
2019-07-14 18:11:39.995 CST [11261] LOG: database system was shut down in recovery at 2019-07-14 18:10:34 CST
2019-07-14 18:11:39.996 CST [11261] LOG: starting point-in-time recovery to 2019-07-14 18:02:34+08
2019-07-14 18:11:40.002 CST [11261] LOG: restored log file "00000002.history" from archive
2019-07-14 18:11:40.029 CST [11261] LOG: restored log file "000000020000000000000013" from archive
2019-07-14 18:11:40.052 CST [11261] LOG: redo starts at 0/13000028
2019-07-14 18:11:40.074 CST [11261] LOG: restored log file "000000020000000000000014" from archive
2019-07-14 18:11:40.092 CST [11261] LOG: consistent recovery state reached at 0/140009C0
2019-07-14 18:11:40.092 CST [11261] LOG: recovery stopping before commit of transaction 735, time 2019-07-14 18:04:28.992452+08
2019-07-14 18:11:40.092 CST [11261] LOG: recovery has paused
2019-07-14 18:11:40.092 CST [11261] HINT: Execute pg_wal_replay_resume() to continue.
2019-07-14 18:11:40.093 CST [11259] LOG: database system is ready to accept read only connections
2019-07-14 18:12:20.815 CST [11269] ERROR: cannot execute INSERT in a read-only transaction
2019-07-14 18:12:20.815 CST [11269] STATEMENT: INSERT INTO test values(default,default);
2019-07-14 18:13:31.877 CST [11259] LOG: received fast shutdown request
2019-07-14 18:13:31.878 CST [11259] LOG: aborting any active transactions
2019-07-14 18:13:31.881 CST [11265] LOG: shutting down
2019-07-14 18:13:31.893 CST [11259] LOG: database system is shut down
2019-07-14 18:13:38.425 CST [11294] LOG: database system was shut down in recovery at 2019-07-14 18:13:31 CST
2019-07-14 18:13:38.426 CST [11294] LOG: database system was not properly shut down; automatic recovery in progress
2019-07-14 18:13:38.428 CST [11294] LOG: redo starts at 0/13000028
2019-07-14 18:13:38.428 CST [11294] LOG: redo done at 0/14000D80
2019-07-14 18:13:38.428 CST [11294] LOG: last completed transaction was at log time 2019-07-14 18:04:28.992452+08
2019-07-14 18:13:38.471 CST [11292] LOG: database system is ready to accept connections
-bash-4.2$