os: centos 7.4
db: postgresql 11.7
# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core)
#
#
# yum list installed |grep -i postgresql
postgresql11.x86_64 11.7-1PGDG.rhel7 @pgdg11
postgresql11-contrib.x86_64 11.7-1PGDG.rhel7 @pgdg11
postgresql11-debuginfo.x86_64 11.5-1PGDG.rhel7 @pgdg11
postgresql11-devel.x86_64 11.7-1PGDG.rhel7 @pgdg11
postgresql11-docs.x86_64 11.7-1PGDG.rhel7 @pgdg11
postgresql11-libs.x86_64 11.7-1PGDG.rhel7 @pgdg11
postgresql11-llvmjit.x86_64 11.7-1PGDG.rhel7 @pgdg11
postgresql11-odbc.x86_64 12.01.0000-1PGDG.rhel7 @pgdg11
postgresql11-plperl.x86_64 11.7-1PGDG.rhel7 @pgdg11
postgresql11-plpython.x86_64 11.7-1PGDG.rhel7 @pgdg11
postgresql11-plpython3.x86_64 11.7-1PGDG.rhel7 @pgdg11
postgresql11-pltcl.x86_64 11.7-1PGDG.rhel7 @pgdg11
postgresql11-server.x86_64 11.7-1PGDG.rhel7 @pgdg11
postgresql11-tcl.x86_64 2.4.0-2.rhel7.1 @pgdg11
postgresql11-test.x86_64 11.7-1PGDG.rhel7 @pgdg11
# su - postgres
Last login: Wed Jan 15 18:34:12 CST 2020 on pts/0
$
$
$ psql -c "select version();"
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
# pgbackrest
pgBackRest 2.26 - General help
Usage:
pgbackrest [options] [command]
Commands:
archive-get Get a WAL segment from the archive.
archive-push Push a WAL segment to the archive.
backup Backup a database cluster.
check Check the configuration.
expire Expire backups that exceed retention.
help Get help.
info Retrieve information about backups.
restore Restore a database cluster.
stanza-create Create the required stanza data.
stanza-delete Delete a stanza.
stanza-upgrade Upgrade a stanza.
start Allow pgBackRest processes to run.
stop Stop pgBackRest processes from running.
version Get version.
Use 'pgbackrest help [command]' for more information.
$ pgbackrest info
stanza: pg1-data
status: ok
cipher: none
db (current)
wal archive min/max (11-1): 00000001000000010000003F/000000010000000100000045
full backup: 20200503-234738F
timestamp start/stop: 2020-05-03 23:47:38 / 2020-05-03 23:48:02
wal start/stop: 000000010000000100000041 / 000000010000000100000041
database size: 141.6MB, backup size: 141.6MB
repository size: 15.1MB, repository backup size: 15.1MB
diff backup: 20200503-234738F_20200503-235423D
timestamp start/stop: 2020-05-03 23:54:23 / 2020-05-03 23:54:25
wal start/stop: 000000010000000100000043 / 000000010000000100000043
database size: 141.6MB, backup size: 2.5MB
repository size: 15.1MB, repository backup size: 269.5KB
backup reference list: 20200503-234738F
incr backup: 20200503-234738F_20200503-235906I
timestamp start/stop: 2020-05-03 23:59:06 / 2020-05-03 23:59:08
wal start/stop: 000000010000000100000045 / 000000010000000100000045
database size: 141.6MB, backup size: 2.4MB
repository size: 15.1MB, repository backup size: 230.7KB
backup reference list: 20200503-234738F, 20200503-234738F_20200503-235423D
# systemctl stop postgresql-11.service
# su - postgres
$ cd /var/lib/pgsql/11/data
$ rm -rf ./*
$ pgbackrest --stanza=pg1-data --log-level-console=info --delta \
--type=time --target="2020-05-04 01:00:00.507654+08" \
--target-action=pause restore
$ cat recovery.conf
# Recovery settings generated by pgBackRest restore on 2020-05-04 01:28:51
restore_command = 'pgbackrest --stanza=pg1-data archive-get %f "%p"'
recovery_target_time = '2020-05-04 01:00:00.507654+08'
给 recovery.conf 添加 recovery_target_action = ‘pause’
# systemctl start postgresql-11.service
#
# ps -ef|grep -i post
postgres 11558 1 0 01:40 ? 00:00:00 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
postgres 11564 11558 0 01:40 ? 00:00:00 postgres: logger
postgres 11569 11558 0 01:40 ? 00:00:00 postgres: checkpointer
postgres 11570 11558 0 01:40 ? 00:00:00 postgres: background writer
postgres 11572 11558 0 01:40 ? 00:00:00 postgres: stats collector
postgres 11581 11558 0 01:40 ? 00:00:00 postgres: walwriter
postgres 11582 11558 0 01:40 ? 00:00:00 postgres: autovacuum launcher
postgres 11583 11558 0 01:40 ? 00:00:00 postgres: archiver last was 00000003.history
postgres 11584 11558 0 01:40 ? 00:00:00 postgres: logical replication launcher
查看日志
2020-04-06 11:49:20.881 CST,,,3544,,5e8a1314.dd8,5,,2020-04-06 01:19:16 CST,,0,LOG,00000,"checkpoint starting: time",,,,,,,,,""
2020-04-06 11:49:49.241 CST,,,3544,,5e8a1314.dd8,6,,2020-04-06 01:19:16 CST,,0,LOG,00000,"checkpoint complete: wrote 131 buffers (0.8%); 0 WAL file(s) added, 0 removed, 0 recycled; write=27.889 s, sync=0.125 s, total=28.360 s; sync files=59, longest=0.097 s, average=0.002 s; distance=357 kB, estimate=357 kB",,,,,,,,,""
2020-05-04 01:45:44.388 CST,,,11703,,5eaf0348.2db7,1,,2020-05-04 01:45:44 CST,,0,LOG,00000,"ending log output to stderr",,"Future log output will go to log destination ""csvlog"".",,,,,,,""
2020-05-04 01:45:44.393 CST,,,11711,,5eaf0348.2dbf,1,,2020-05-04 01:45:44 CST,,0,LOG,00000,"database system was interrupted; last known up at 2020-05-03 23:59:06 CST",,,,,,,,,""
2020-05-04 01:45:45.371 CST,,,11711,,5eaf0348.2dbf,2,,2020-05-04 01:45:44 CST,,0,LOG,00000,"starting point-in-time recovery to 2020-05-04 01:00:00.507654+08",,,,,,,,,""
2020-05-04 01:45:45.416 CST,,,11711,,5eaf0348.2dbf,3,,2020-05-04 01:45:44 CST,,0,LOG,00000,"restored log file ""000000010000000100000045"" from archive",,,,,,,,,""
2020-05-04 01:45:45.491 CST,,,11711,,5eaf0348.2dbf,4,,2020-05-04 01:45:44 CST,1/0,0,LOG,00000,"redo starts at 1/45000028",,,,,,,,,""
2020-05-04 01:45:45.494 CST,,,11711,,5eaf0348.2dbf,5,,2020-05-04 01:45:44 CST,1/0,0,LOG,00000,"consistent recovery state reached at 1/450000F8",,,,,,,,,""
2020-05-04 01:45:45.494 CST,,,11703,,5eaf0348.2db7,2,,2020-05-04 01:45:44 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,,""
2020-05-04 01:45:45.544 CST,,,11711,,5eaf0348.2dbf,6,,2020-05-04 01:45:44 CST,1/0,0,LOG,00000,"restored log file ""000000010000000100000046"" from archive",,,,,,,,,""
2020-05-04 01:45:45.663 CST,,,11711,,5eaf0348.2dbf,7,,2020-05-04 01:45:44 CST,1/0,0,LOG,00000,"restored log file ""000000010000000100000047"" from archive",,,,,,,,,""
2020-05-04 01:45:45.741 CST,,,11711,,5eaf0348.2dbf,8,,2020-05-04 01:45:44 CST,1/0,0,LOG,00000,"redo done at 1/470000D0",,,,,,,,,""
2020-05-04 01:45:45.787 CST,,,11711,,5eaf0348.2dbf,9,,2020-05-04 01:45:44 CST,1/0,0,LOG,00000,"restored log file ""000000010000000100000047"" from archive",,,,,,,,,""
2020-05-04 01:45:45.875 CST,,,11711,,5eaf0348.2dbf,10,,2020-05-04 01:45:44 CST,1/0,0,LOG,00000,"restored log file ""00000002.history"" from archive",,,,,,,,,""
2020-05-04 01:45:45.885 CST,,,11711,,5eaf0348.2dbf,11,,2020-05-04 01:45:44 CST,1/0,0,LOG,00000,"restored log file ""00000003.history"" from archive",,,,,,,,,""
2020-05-04 01:45:45.894 CST,,,11711,,5eaf0348.2dbf,12,,2020-05-04 01:45:44 CST,1/0,0,LOG,00000,"selected new timeline ID: 4",,,,,,,,,""
2020-05-04 01:45:46.006 CST,,,11711,,5eaf0348.2dbf,13,,2020-05-04 01:45:44 CST,1/0,0,LOG,00000,"archive recovery complete",,,,,,,,,""
2020-05-04 01:45:46.024 CST,,,11713,,5eaf0349.2dc1,1,,2020-05-04 01:45:45 CST,,0,LOG,00000,"checkpoint starting: end-of-recovery immediate wait",,,,,,,,,""
2020-05-04 01:45:46.035 CST,,,11713,,5eaf0349.2dc1,2,,2020-05-04 01:45:45 CST,,0,LOG,00000,"checkpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.002 s, total=0.013 s; sync files=1, longest=0.002 s, average=0.002 s; distance=49152 kB, estimate=49152 kB",,,,,,,,,""
2020-05-04 01:45:46.153 CST,,,11703,,5eaf0348.2db7,3,,2020-05-04 01:45:44 CST,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,""
参考:
https://github.com/pgbackrest/pgbackrest
https://pgbackrest.org/
https://pgbackrest.org/configuration.html
https://pgbackrest.org/command.html
https://pgbackrest.org/user-guide-index.html