pgBackRest 是一个开源的 PostgreSQL 数据库备份和恢复解决方案。可以去官网 阅读完整的信息。下面根据实验环境做一个了本地的备份恢复测试。
实验环境
| 操作系统 | 数据库版本 | 工具版本 |
|---|---|---|
| CentOS 7.9 | PostgreSQL 15.7 | pgBackRest 2.52.1 |
pgBackRest 2.52.1下载 将文件上传到服务器/home/postgres目录下
源码安装
操作系统依赖包
[root@pg15 ~]# yum install -y libxml2-devel libyaml-devel bzip2-devel
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package bzip2-devel.x86_64 0:1.0.6-13.el7 will be installed
---> Package libxml2-devel.x86_64 0:2.9.1-6.el7_9.6 will be installed
--> Processing Dependency: xz-devel for package: libxml2-devel-2.9.1-6.el7_9.6.x86_64
---> Package libyaml-devel.x86_64 0:0.1.4-11.el7_0 will be installed
--> Running transaction check
---> Package xz-devel.x86_64 0:5.2.2-2.el7_9 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
===============================================================================================================================================================================================================================================================================
Package Arch Version Repository Size
===============================================================================================================================================================================================================================================================================
Installing:
bzip2-devel x86_64 1.0.6-13.el7 base 218 k
libxml2-devel x86_64 2.9.1-6.el7_9.6 updates 1.1 M
libyaml-devel x86_64 0.1.4-11.el7_0 base 82 k
Installing for dependencies:
xz-devel x86_64 5.2.2-2.el7_9 updates 46 k
Transaction Summary
===============================================================================================================================================================================================================================================================================
Install 3 Packages (+1 Dependent package)
Total download size: 1.4 M
Installed size: 10 M
Downloading packages:
(1/4): bzip2-devel-1.0.6-13.el7.x86_64.rpm | 218 kB 00:00:02
(2/4): xz-devel-5.2.2-2.el7_9.x86_64.rpm | 46 kB 00:00:02
(3/4): libyaml-devel-0.1.4-11.el7_0.x86_64.rpm | 82 kB 00:00:02
(4/4): libxml2-devel-2.9.1-6.el7_9.6.x86_64.rpm | 1.1 MB 00:00:03
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 428 kB/s | 1.4 MB 00:00:03
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : xz-devel-5.2.2-2.el7_9.x86_64 1/4
Installing : libxml2-devel-2.9.1-6.el7_9.6.x86_64 2/4
Installing : libyaml-devel-0.1.4-11.el7_0.x86_64 3/4
Installing : bzip2-devel-1.0.6-13.el7.x86_64 4/4
Verifying : bzip2-devel-1.0.6-13.el7.x86_64 1/4
Verifying : libyaml-devel-0.1.4-11.el7_0.x86_64 2/4
Verifying : xz-devel-5.2.2-2.el7_9.x86_64 3/4
Verifying : libxml2-devel-2.9.1-6.el7_9.6.x86_64 4/4
Installed:
bzip2-devel.x86_64 0:1.0.6-13.el7 libxml2-devel.x86_64 0:2.9.1-6.el7_9.6 libyaml-devel.x86_64 0:0.1.4-11.el7_0
Dependency Installed:
xz-devel.x86_64 0:5.2.2-2.el7_9
Complete!
[root@pg15 ~]# s
复制源码编译
[postgres@pg15 ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
#PostgreSQL settings
export PGPORT=5432
export PGUSER=postgres
export PGHOME=/opt/pg15
export PGDATA=/opt/pgdata
export LD_LIBRARY_PATH=$PGHOME/lib
export MANPATH=$PGHOME/share/man
export PATH=$PGHOME/bin:$PATH
export LANG="en_US.UTF-8"
[postgres@pg15 ~]$ tar -zxf pgbackrest-release-2.52.1.tar.gz
[postgres@pg15 ~]$ cd pgbackrest-release-2.52.1/src/
[postgres@pg15 src]$ ./configure --prefix=/opt/pg15
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether the compiler supports GNU C... yes
checking whether gcc accepts -g... yes
checking for gcc option to enable C11 features... -std=gnu11
checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
checking for stdio.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for strings.h... yes
checking for sys/stat.h... yes
checking for sys/types.h... yes
checking for unistd.h... yes
checking for backtrace_full in -lbacktrace... no
checking for pg_config... yes
checking for PQconnectdb in -lpq... yes
checking for libpq-fe.h... yes
checking for EVP_get_digestbyname in -lcrypto... yes
checking for SSL_new in -lssl... yes
checking for pkg-config... yes
checking for xmlSaveToBuffer in -lxml2... yes
checking for libxml/parser.h... yes
checking for yaml_parser_initialize in -lyaml... yes
checking for zlib.h... yes
checking for deflate in -lz... yes
checking for zlib.h... (cached) yes
checking for BZ2_bzCompress in -lbz2... yes
checking for bzlib.h... yes
checking for LZ4F_isError in -llz4... no
checking for libssh2_init in -lssh2... no
checking for libssh2_sftp_init in -lssh2... no
checking for ZSTD_isError in -lzstd... no
checking whether C compiler accepts -Wno-clobbered... yes
configure: creating ./config.status
config.status: creating Makefile
config.status: creating build.auto.h
[postgres@pg15 src]$ make && make install
gcc -std=gnu11 -std=c99 -Wall -Wextra -Wno-missing-field-initializers -Wno-implicit-fallthrough -Wno-clobbered -O2 -D_POSIX_C_SOURCE=200809L -I. -I/opt/pg15/include -I/usr/include/libxml2 -I. -I. -c -o .build/common/compress/bz2/common.o common/compress/bz2/common.c -MMD -MP -MF .build/common/compress/bz2/common.dep
。。。省略
.build/storage/s3/helper.o .build/storage/s3/read.o .build/storage/s3/storage.o .build/storage/s3/write.o .build/storage/sftp/helper.o .build/storage/sftp/read.o .build/storage/sftp/storage.o .build/storage/sftp/write.o .build/main.o -L/opt/pg15/lib -lbz2 -lz -lxml2 -lssl -lcrypto -lpq
install -d /opt/pg15/bin
install -m 755 pgbackrest /opt/pg15/bin
[postgres@pg15 src]$ ls -lh /opt/pg15/bin/pgbackrest
-rwxr-xr-x 1 postgres postgres 1.1M Jun 28 11:48 /opt/pg15/bin/pgbackrest
[postgres@pg15 src]$ pgbackrest version
pgBackRest 2.52.1
[postgres@pg15 src]$
复制准备测试环境
创建一个新的数据库集簇
[postgres@pg15 ~]$ mkdir -p /opt/pgdata
[postgres@pg15 ~]$ initdb -D /opt/pgdata -k -E UTF8 -U postgres -W
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".
Data page checksums are enabled.
Enter new superuser password:
Enter it again:
fixing permissions on existing directory /opt/pgdata ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /opt/pgdata -l logfile start
[postgres@pg15 ~]$
复制创建pgBackRest目录和conf文件
[postgres@pg15 ~]$ sudo mkdir -p -m 770 /var/log/pgbackrest
[postgres@pg15 ~]$ sudo chown postgres:postgres /var/log/pgbackrest
[postgres@pg15 ~]$ sudo mkdir -p /etc/pgbackrest
[postgres@pg15 ~]$ sudo mkdir -p /etc/pgbackrest/conf.d
[postgres@pg15 ~]$ sudo touch /etc/pgbackrest/pgbackrest.conf
[postgres@pg15 ~]$ sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
[postgres@pg15 ~]$ sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
[postgres@pg15 ~]$
复制启用 WAL 存档，启动数据库集簇
[postgres@pg15 ~]$ cat >> /opt/pgdata/postgresql.conf << "EOF"
>
> archive_command = 'pgbackrest --stanza=demo archive-push %p'
> archive_mode = on
> log_filename = 'postgresql.log'
> max_wal_senders = 3
> wal_level = replica
>
>
> EOF
[postgres@pg15 ~]$ pg_ctl start
waiting for server to start....2024-06-28 14:18:45.406 CST [22836] LOG: starting PostgreSQL 15.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2024-06-28 14:18:45.406 CST [22836] LOG: listening on IPv4 address "127.0.0.1", port 5432
2024-06-28 14:18:45.407 CST [22836] LOG: could not create IPv6 socket for address "::1": Address family not supported by protocol
2024-06-28 14:18:45.408 CST [22836] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-06-28 14:18:45.409 CST [22843] LOG: database system was shut down at 2024-06-28 14:06:47 CST
2024-06-28 14:18:45.411 CST [22836] LOG: database system is ready to accept connections
done
server started
[postgres@pg15 ~]$
复制创建 Stanza
在conf文件中设置pg1-path：pgdata的路径，repo1-path：备份文件的路径，备份保留的的数量：2，创建stanza，检查配置
[postgres@pg15 ~]$ cat > /etc/pgbackrest/pgbackrest.conf << "EOF"
>
> [demo]
> pg1-path=/opt/pgdata
>
> [global]
> repo1-path=/opt/backup/pgbackrest
> repo1-retention-full=2
>
> [global:archive-push]
> compress-level=3
>
> EOF
[postgres@pg15 ~]$
[postgres@pg15 ~]$ pgbackrest --stanza=demo --log-level-console=info stanza-create
2024-06-28 14:26:02.625 P00 INFO: stanza-create command begin 2.52.1: --exec-id=22937-10ae0781 --log-level-console=info --pg1-path=/opt/pgdata --repo1-path=/opt/backup/pgbackrest --stanza=demo
2024-06-28 14:26:03.233 P00 INFO: stanza-create for stanza 'demo' on repo1
2024-06-28 14:26:03.236 P00 INFO: stanza-create command end: completed successfully (612ms)
[postgres@pg15 ~]$ pgbackrest --stanza=demo --log-level-console=info check
2024-06-28 14:29:40.111 P00 INFO: check command begin 2.52.1: --exec-id=22978-05f3d592 --log-level-console=info --pg1-path=/opt/pgdata --repo1-path=/opt/backup/pgbackrest --stanza=demo
2024-06-28 14:29:40.717 P00 INFO: check repo1 configuration (primary)
2024-06-28 14:29:40.718 CST [22979] LOG: restore point "pgBackRest Archive Check" created at 0/177B318
2024-06-28 14:29:40.718 CST [22979] STATEMENT: select pg_catalog.pg_create_restore_point('pgBackRest Archive Check')::text
2024-06-28 14:29:40.920 P00 INFO: check repo1 archive for WAL (primary)
2024-06-28 14:29:41.021 P00 INFO: WAL segment 000000010000000000000001 successfully archived to '/opt/backup/pgbackrest/archive/demo/15-1/0000000100000000/000000010000000000000001-76284bc6e40e49da33431d1f8c9c804e4ba1bb02.gz' on repo1
2024-06-28 14:29:41.021 P00 INFO: check command end: completed successfully (911ms)
[postgres@pg15 ~]$
复制准备数据做备份
[postgres@pg15 ~]$ psql
psql (15.7)
Type "help" for help.
postgres=# create table t (id serial primary key,name varchar(20),createdate timestamp);
CREATE TABLE
postgres=# insert into t (name,createdate) values('zhangsan',now());
INSERT 0 1
postgres=# insert into t (name,createdate) values('lisi',now());
INSERT 0 1
postgres=# insert into t (name,createdate) values('wangwu',now());
INSERT 0 1
postgres=# select * from t;
id | name | createdate
----+----------+----------------------------
1 | zhangsan | 2024-06-28 14:33:18.473007
2 | lisi | 2024-06-28 14:33:48.402666
3 | wangwu | 2024-06-28 14:34:45.19109
(3 rows)
postgres=#
复制备份操作
全量备份
执行全备
[postgres@pg15 ~]$ pgbackrest --stanza=demo --log-level-console=info backup --type=full
2024-06-28 14:36:15.416 P00 INFO: backup command begin 2.52.1: --exec-id=23050-22491722 --log-level-console=info --pg1-path=/opt/pgdata --repo1-path=/opt/backup/pgbackrest --repo1-retention-full=2 --stanza=demo --type=full
2024-06-28 14:36:16.123 P00 INFO: execute non-exclusive backup start: backup begins after the next regular checkpoint completes
2024-06-28 14:36:16.338 CST [22841] LOG: checkpoint starting: force wait
2024-06-28 14:36:16.642 CST [22841] LOG: checkpoint complete: wrote 4 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.304 s, sync=0.001 s, total=0.305 s; sync files=4, longest=0.001 s, average=0.001 s; distance=16228 kB, estimate=16228 kB
2024-06-28 14:36:17.127 P00 INFO: backup start archive = 000000010000000000000003, lsn = 0/3000028
2024-06-28 14:36:17.127 P00 INFO: check archive for prior segment 000000010000000000000002
2024-06-28 14:36:19.149 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2024-06-28 14:36:19.350 P00 INFO: backup stop archive = 000000010000000000000003, lsn = 0/3000138
2024-06-28 14:36:19.351 P00 INFO: check archive for segment(s) 000000010000000000000003:000000010000000000000003
2024-06-28 14:36:19.355 P00 INFO: new backup label = 20240628-143616F
2024-06-28 14:36:19.368 P00 INFO: full backup size = 22MB, file total = 967
2024-06-28 14:36:19.368 P00 INFO: backup command end: completed successfully (3952ms)
2024-06-28 14:36:19.368 P00 INFO: expire command begin 2.52.1: --exec-id=23050-22491722 --log-level-console=info --repo1-path=/opt/backup/pgbackrest --repo1-retention-full=2 --stanza=demo
2024-06-28 14:36:19.369 P00 INFO: expire command end: completed successfully (1ms)
[postgres@pg15 ~]$
复制增量备份
再插入数据，执行增量备份
[postgres@pg15 ~]$ psql
psql (15.7)
Type "help" for help.
postgres=# insert into t (name,createdate) values('sunying',now());
INSERT 0 1
postgres=# insert into t (name,createdate) values('helloworld',now());
INSERT 0 1
postgres=# select * from t;
id | name | createdate
----+------------+----------------------------
1 | zhangsan | 2024-06-28 14:33:18.473007
2 | lisi | 2024-06-28 14:33:48.402666
3 | wangwu | 2024-06-28 14:34:45.19109
4 | sunying | 2024-06-28 14:38:56.688416
5 | helloworld | 2024-06-28 14:40:13.756512
(5 rows)
postgres=# \q
[postgres@pg15 ~]$ pgbackrest --stanza=demo --log-level-console=info backup --type=incr
2024-06-28 14:41:00.221 P00 INFO: backup command begin 2.52.1: --exec-id=23115-7b3a812a --log-level-console=info --pg1-path=/opt/pgdata --repo1-path=/opt/backup/pgbackrest --repo1-retention-full=2 --stanza=demo --type=incr
2024-06-28 14:41:00.933 P00 INFO: last backup label = 20240628-143616F, version = 2.52.1
2024-06-28 14:41:00.933 P00 INFO: execute non-exclusive backup start: backup begins after the next regular checkpoint completes
2024-06-28 14:41:01.148 CST [22841] LOG: checkpoint starting: force wait
2024-06-28 14:41:01.463 CST [22841] LOG: checkpoint complete: wrote 4 buffers (0.0%); 0 WAL file(s) added, 0 removed, 2 recycled; write=0.303 s, sync=0.001 s, total=0.315 s; sync files=4, longest=0.001 s, average=0.001 s; distance=32768 kB, estimate=32768 kB
2024-06-28 14:41:01.937 P00 INFO: backup start archive = 000000010000000000000005, lsn = 0/5000028
2024-06-28 14:41:01.937 P00 INFO: check archive for prior segment 000000010000000000000004
2024-06-28 14:41:02.514 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2024-06-28 14:41:02.716 P00 INFO: backup stop archive = 000000010000000000000005, lsn = 0/5000100
2024-06-28 14:41:02.717 P00 INFO: check archive for segment(s) 000000010000000000000005:000000010000000000000005
2024-06-28 14:41:02.721 P00 INFO: new backup label = 20240628-143616F_20240628-144100I
2024-06-28 14:41:02.734 P00 INFO: incr backup size = 48.3KB, file total = 967
2024-06-28 14:41:02.734 P00 INFO: backup command end: completed successfully (2513ms)
2024-06-28 14:41:02.734 P00 INFO: expire command begin 2.52.1: --exec-id=23115-7b3a812a --log-level-console=info --repo1-path=/opt/backup/pgbackrest --repo1-retention-full=2 --stanza=demo
2024-06-28 14:41:02.735 P00 INFO: expire command end: completed successfully (1ms)
[postgres@pg15 ~]$
复制差异备份
[postgres@pg15 ~]$ psql
psql (15.7)
Type "help" for help.
postgres=# insert into t (name,createdate) values('AAAAAA',now());
INSERT 0 1
postgres=# select * from t;
id | name | createdate
----+------------+----------------------------
1 | zhangsan | 2024-06-28 14:33:18.473007
2 | lisi | 2024-06-28 14:33:48.402666
3 | wangwu | 2024-06-28 14:34:45.19109
4 | sunying | 2024-06-28 14:38:56.688416
5 | helloworld | 2024-06-28 14:40:13.756512
6 | AAAAAA | 2024-06-28 14:44:36.273033
(6 rows)
postgres=# \q
[postgres@pg15 ~]$ pgbackrest --stanza=demo --log-level-console=info backup --type=diff
2024-06-28 14:45:22.769 P00 INFO: backup command begin 2.52.1: --exec-id=23172-8bf32680 --log-level-console=info --pg1-path=/opt/pgdata --repo1-path=/opt/backup/pgbackrest --repo1-retention-full=2 --stanza=demo --type=diff
2024-06-28 14:45:23.480 P00 INFO: last backup label = 20240628-143616F, version = 2.52.1
2024-06-28 14:45:23.480 P00 INFO: execute non-exclusive backup start: backup begins after the next regular checkpoint completes
2024-06-28 14:45:23.695 CST [22841] LOG: checkpoint starting: force wait
2024-06-28 14:45:24.000 CST [22841] LOG: checkpoint complete: wrote 4 buffers (0.0%); 0 WAL file(s) added, 0 removed, 2 recycled; write=0.304 s, sync=0.001 s, total=0.306 s; sync files=4, longest=0.001 s, average=0.001 s; distance=32768 kB, estimate=32768 kB
2024-06-28 14:45:24.485 P00 INFO: backup start archive = 000000010000000000000007, lsn = 0/7000028
2024-06-28 14:45:24.485 P00 INFO: check archive for prior segment 000000010000000000000006
2024-06-28 14:45:25.513 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2024-06-28 14:45:25.714 P00 INFO: backup stop archive = 000000010000000000000007, lsn = 0/7000100
2024-06-28 14:45:25.715 P00 INFO: check archive for segment(s) 000000010000000000000007:000000010000000000000007
2024-06-28 14:45:25.719 P00 INFO: new backup label = 20240628-143616F_20240628-144523D
2024-06-28 14:45:25.731 P00 INFO: diff backup size = 48.3KB, file total = 967
2024-06-28 14:45:25.731 P00 INFO: backup command end: completed successfully (2963ms)
2024-06-28 14:45:25.731 P00 INFO: expire command begin 2.52.1: --exec-id=23172-8bf32680 --log-level-console=info --repo1-path=/opt/backup/pgbackrest --repo1-retention-full=2 --stanza=demo
2024-06-28 14:45:25.732 P00 INFO: expire command end: completed successfully (1ms)
[postgres@pg15 ~]$
复制查看备份
[postgres@pg15 ~]$ pgbackrest info
stanza: demo
status: ok
cipher: none
db (current)
wal archive min/max (15): 000000010000000000000001/000000010000000000000007
full backup: 20240628-143616F
timestamp start/stop: 2024-06-28 14:36:16+08 / 2024-06-28 14:36:19+08
wal start/stop: 000000010000000000000003 / 000000010000000000000003
database size: 22MB, database backup size: 22MB
repo1: backup set size: 2.9MB, backup size: 2.9MB
incr backup: 20240628-143616F_20240628-144100I
timestamp start/stop: 2024-06-28 14:41:00+08 / 2024-06-28 14:41:02+08
wal start/stop: 000000010000000000000005 / 000000010000000000000005
database size: 22MB, database backup size: 48.3KB
repo1: backup set size: 2.9MB, backup size: 932B
backup reference list: 20240628-143616F
diff backup: 20240628-143616F_20240628-144523D
timestamp start/stop: 2024-06-28 14:45:23+08 / 2024-06-28 14:45:25+08
wal start/stop: 000000010000000000000007 / 000000010000000000000007
database size: 22MB, database backup size: 48.3KB
repo1: backup set size: 2.9MB, backup size: 972B
backup reference list: 20240628-143616F
[postgres@pg15 ~]$ psql -f /home/postgres/pgbackrest-release-2.52.1/doc/example/pgsql-pgbackrest-info.sql
CREATE SCHEMA
CREATE FUNCTION
[postgres@pg15 ~]$ psql -f /home/postgres/pgbackrest-release-2.52.1/doc/example/pgsql-pgbackrest-query.sql
name | last_successful_backup | last_archived_wal
--------+------------------------+--------------------------
"demo" | 2024-06-28 14:45:25+08 | 000000010000000000000007
(1 row)
[postgres@pg15 ~]$
复制恢复测试
全量恢复
恢复需要关闭数据库实例，rm删除/opt/pgdata数据库data目录，恢复到最新的时间线，查询恢复结果
[postgres@pg15 ~]$ pg_ctl stop
waiting for server to shut down....2024-06-28 14:54:19.314 CST [22836] LOG: received fast shutdown request
2024-06-28 14:54:19.314 CST [22836] LOG: aborting any active transactions
2024-06-28 14:54:19.315 CST [22836] LOG: background worker "logical replication launcher" (PID 22847) exited with exit code 1
2024-06-28 14:54:19.315 CST [22841] LOG: shutting down
2024-06-28 14:54:19.328 CST [22841] LOG: checkpoint starting: shutdown immediate
2024-06-28 14:54:19.329 CST [22841] LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.002 s; sync files=0, longest=0.000 s, average=0.000 s; distance=16218 kB, estimate=29653 kB
2024-06-28 14:54:19.384 CST [22836] LOG: database system is shut down
done
server stopped
[postgres@pg15 ~]$ rm -rf /opt/pgdata/
[postgres@pg15 ~]$ pgbackrest --stanza=demo --log-level-console=info restore
2024-06-28 14:55:07.680 P00 INFO: restore command begin 2.52.1: --exec-id=23321-50e1bf33 --log-level-console=info --pg1-path=/opt/pgdata --repo1-path=/opt/backup/pgbackrest --stanza=demo
2024-06-28 14:55:07.686 P00 INFO: repo1: restore backup set 20240628-143616F_20240628-144523D, recovery will start at 2024-06-28 14:45:23
2024-06-28 14:55:08.126 P00 INFO: write updated /opt/pgdata/postgresql.auto.conf
2024-06-28 14:55:08.128 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2024-06-28 14:55:08.128 P00 INFO: restore size = 22MB, file total = 967
2024-06-28 14:55:08.128 P00 INFO: restore command end: completed successfully (448ms)
[postgres@pg15 ~]$ pg_ctl start
waiting for server to start....2024-06-28 14:55:37.867 CST [23333] LOG: starting PostgreSQL 15.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2024-06-28 14:55:37.868 CST [23333] LOG: listening on IPv4 address "127.0.0.1", port 5432
2024-06-28 14:55:37.869 CST [23333] LOG: could not create IPv6 socket for address "::1": Address family not supported by protocol
2024-06-28 14:55:37.869 CST [23333] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-06-28 14:55:37.871 CST [23340] LOG: database system was interrupted; last known up at 2024-06-28 14:45:23 CST
2024-06-28 14:55:37.877 CST [23340] LOG: starting archive recovery
2024-06-28 14:55:37.877 CST [23340] LOG: starting backup recovery with redo LSN 0/7000028, checkpoint LSN 0/7000060, on timeline ID 1
2024-06-28 14:55:37.898 CST [23340] LOG: restored log file "000000010000000000000007" from archive
2024-06-28 14:55:37.908 CST [23340] LOG: redo starts at 0/7000028
2024-06-28 14:55:37.930 CST [23340] LOG: restored log file "000000010000000000000008" from archive
2024-06-28 14:55:37.941 CST [23340] LOG: completed backup recovery with redo LSN 0/7000028 and end LSN 0/7000100
2024-06-28 14:55:37.941 CST [23340] LOG: consistent recovery state reached at 0/7000100
2024-06-28 14:55:37.942 CST [23333] LOG: database system is ready to accept read-only connections
2024-06-28 14:55:37.949 CST [23340] LOG: redo done at 0/8029718 system usage: CPU: user: 0.00 s, system: 0.01 s, elapsed: 0.04 s
2024-06-28 14:55:37.949 CST [23340] LOG: last completed transaction was at log time 2024-06-28 14:49:04.726301+08
done
server started
[postgres@pg15 ~]$ 2024-06-28 14:55:37.970 CST [23340] LOG: restored log file "000000010000000000000008" from archive
2024-06-28 14:55:37.985 CST [23340] LOG: selected new timeline ID: 2
2024-06-28 14:55:38.002 CST [23340] LOG: archive recovery complete
2024-06-28 14:55:38.003 CST [23338] LOG: checkpoint starting: end-of-recovery immediate wait
2024-06-28 14:55:38.006 CST [23338] LOG: checkpoint complete: wrote 37 buffers (0.2%); 0 WAL file(s) added, 0 removed, 2 recycled; write=0.002 s, sync=0.001 s, total=0.004 s; sync files=29, longest=0.001 s, average=0.001 s; distance=32768 kB, estimate=32768 kB
2024-06-28 14:55:38.007 CST [23333] LOG: database system is ready to accept connections
[postgres@pg15 ~]$ psql
psql (15.7)
Type "help" for help.
postgres=# select * from t;
id | name | createdate
----+------------+----------------------------
1 | zhangsan | 2024-06-28 14:33:18.473007
2 | lisi | 2024-06-28 14:33:48.402666
3 | wangwu | 2024-06-28 14:34:45.19109
4 | sunying | 2024-06-28 14:38:56.688416
5 | helloworld | 2024-06-28 14:40:13.756512
6 | AAAAAA | 2024-06-28 14:44:36.273033
(6 rows)
postgres=#
复制基于时间点的恢复
[postgres@pg15 ~]$ pg_ctl stop
waiting for server to shut down....2024-06-28 14:57:15.416 CST [23333] LOG: received fast shutdown request
2024-06-28 14:57:15.416 CST [23333] LOG: aborting any active transactions
2024-06-28 14:57:15.417 CST [23333] LOG: background worker "logical replication launcher" (PID 23352) exited with exit code 1
2024-06-28 14:57:15.417 CST [23338] LOG: shutting down
2024-06-28 14:57:15.430 CST [23338] LOG: checkpoint starting: shutdown immediate
2024-06-28 14:57:15.431 CST [23338] LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.002 s; sync files=3, longest=0.001 s, average=0.001 s; distance=16384 kB, estimate=31129 kB
2024-06-28 14:57:15.483 CST [23333] LOG: database system is shut down
done
server stopped
[postgres@pg15 ~]$ pgbackrest --stanza=demo --delta --type=time "--target=2024-06-28 14:38:16+08" --log-level-console=info restore
2024-06-28 15:06:24.182 P00 INFO: restore command begin 2.52.1: --delta --exec-id=23474-14748c12 --log-level-console=info --pg1-path=/opt/pgdata --repo1-path=/opt/backup/pgbackrest --stanza=demo --target="2024-06-28 14:38:16+08" --type=time
2024-06-28 15:06:24.186 P00 INFO: repo1: restore backup set 20240628-143616F, recovery will start at 2024-06-28 14:36:16
2024-06-28 15:06:24.187 P00 INFO: remove invalid files/links/paths from '/opt/pgdata'
2024-06-28 15:06:24.399 P00 INFO: write updated /opt/pgdata/postgresql.auto.conf
2024-06-28 15:06:24.400 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2024-06-28 15:06:24.400 P00 INFO: restore size = 22MB, file total = 967
2024-06-28 15:06:24.401 P00 INFO: restore command end: completed successfully (220ms)
[postgres@pg15 ~]$ cat /opt/pgdata/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
# Recovery settings generated by pgBackRest restore on 2024-06-28 15:06:24
restore_command = 'pgbackrest --stanza=demo archive-get %f "%p"'
recovery_target_time = '2024-06-28 14:38:16+08'
[postgres@pg15 ~]$ ls -lh /opt/pgdata/recovery.signal
-rw------- 1 postgres postgres 0 Jun 28 15:06 /opt/pgdata/recovery.signal
[postgres@pg15 ~]$ pg_ctl start
waiting for server to start....2024-06-28 15:07:55.771 CST [23514] LOG: starting PostgreSQL 15.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2024-06-28 15:07:55.772 CST [23514] LOG: listening on IPv4 address "127.0.0.1", port 5432
2024-06-28 15:07:55.773 CST [23514] LOG: could not create IPv6 socket for address "::1": Address family not supported by protocol
2024-06-28 15:07:55.774 CST [23514] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-06-28 15:07:55.775 CST [23521] LOG: database system was interrupted; last known up at 2024-06-28 14:36:16 CST
2024-06-28 15:07:55.782 CST [23521] LOG: restored log file "00000002.history" from archive
2024-06-28 15:07:55.785 CST [23521] LOG: starting point-in-time recovery to 2024-06-28 14:38:16+08
2024-06-28 15:07:55.785 CST [23521] LOG: starting backup recovery with redo LSN 0/3000028, checkpoint LSN 0/3000098, on timeline ID 1
2024-06-28 15:07:55.788 CST [23521] LOG: restored log file "00000002.history" from archive
2024-06-28 15:07:55.809 CST [23521] LOG: restored log file "000000010000000000000003" from archive
2024-06-28 15:07:55.820 CST [23521] LOG: redo starts at 0/3000028
2024-06-28 15:07:55.841 CST [23521] LOG: restored log file "000000010000000000000004" from archive
2024-06-28 15:07:55.872 CST [23521] LOG: restored log file "000000010000000000000005" from archive
2024-06-28 15:07:55.903 CST [23521] LOG: restored log file "000000010000000000000006" from archive
2024-06-28 15:07:55.934 CST [23521] LOG: restored log file "000000010000000000000007" from archive
2024-06-28 15:07:55.964 CST [23521] LOG: restored log file "000000010000000000000008" from archive
2024-06-28 15:07:55.972 CST [23521] LOG: completed backup recovery with redo LSN 0/3000028 and end LSN 0/3000138
2024-06-28 15:07:55.972 CST [23521] LOG: consistent recovery state reached at 0/3000138
2024-06-28 15:07:55.973 CST [23521] LOG: recovery stopping before commit of transaction 740, time 2024-06-28 14:38:56.689142+08
2024-06-28 15:07:55.973 CST [23521] LOG: pausing at the end of recovery
2024-06-28 15:07:55.973 CST [23521] HINT: Execute pg_wal_replay_resume() to promote.
2024-06-28 15:07:55.973 CST [23514] LOG: database system is ready to accept read-only connections
done
server started
[postgres@pg15 ~]$ psql
psql (15.7)
Type "help" for help.
postgres=# select * from t;
id | name | createdate
----+----------+----------------------------
1 | zhangsan | 2024-06-28 14:33:18.473007
2 | lisi | 2024-06-28 14:33:48.402666
3 | wangwu | 2024-06-28 14:34:45.19109
(3 rows)
postgres=#
复制总结
pgBackRest作为一个功能强大且灵活的备份恢复解决方案，是可以在生产环境使用的，本次的简单测试希望能帮助到您。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
锁钥
9月前
评论
0pgBackRest作为一个功能强大且灵活的备份恢复解决方案，是可以在生产环境使用的
9月前
点赞 评论
筱悦星辰
9月前
评论
0接纳自己的不完美、不惧失败、勇敢面对一切挑战，拥有这样的品质才能让你成为人生真正的赢家。
9月前
点赞 评论
相关阅读
明明想执行的SQL是DELETE、UPDATE，但为什么看到的是SELECT（FDW的实现原理解析）
小满未满、
386次阅读
2025-03-19 23:11:26
PostgreSQL初/中/高级认证考试（3.15）通过考生公示
开源软件联盟PostgreSQL分会
368次阅读
2025-03-20 09:50:36
9.9 分高危漏洞，尽快升级到 pgAdmin 4 v9.2 进行修复
严少安
265次阅读
2025-04-11 10:43:23
openHalo问世，全球首款基于PostgreSQL兼容MySQL协议的国产开源数据库
严少安
227次阅读
2025-04-07 12:14:29
IvorySQL 4.4 发布 - 基于 PostgreSQL 17.4，增强平台支持
通讯员
226次阅读
2025-03-20 15:31:04
PG vs MySQL 执行计划解读的异同点
进击的CJR
147次阅读
2025-03-21 10:50:08
PostgreSQL分区管理扩展——pg_partman
chirpyli
118次阅读
2025-03-19 15:48:31
手把手教你在 openKylin 上部署 IvorySQL 4.4
严少安
115次阅读
2025-03-27 20:41:28
postgresql+patroni+etcd高可用安装
necessary
113次阅读
2025-03-28 10:11:23
从 Oracle 到 PostgreSQL迁移成本评估揭秘
梧桐
104次阅读
2025-03-27 17:21:42
