测试要求：
准备虚机：操作系统centos 7.9 + 数据库postgresql v16.3
https://www.postgresql.org/download/
实现目标：创建一个test数据库，建个建个t表，并随意插入几条记录，再建个t1表，最后删掉t表，记录删掉前的时间，恢复到删掉前的状态。
利用pg_rman做备份和恢复操作：对库做备份后，可以删掉test表，利用备份做恢复。
安装pg和pg_rman
root下的操作：
mount /dev/cdrom /mnt
df -hT
cd /mnt
ll
vi /etc/yum.repos.d/dvd.repo
yum install -y cmake make gcc zlib gcc-c++ perl readline readline-devel zlib zlib-devel perl python36 tcl openssl ncurses-devel perl-IPC-Run libicu-devel
su - pgsql
cd /root
ll
mv postgresql-16.3 /postgresql/soft/
chown -R pgsql:pgsql /postgresql
su - pgsql
cat > /etc/systemd/system/PG16.service <<"EOF"
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
After=network.target
[Service]
Type=forking
User=pgsql
Group=pgsql
Environment=PGPORT=5432
Environment=PGDATA=/postgresql/pgdata
OOMScoreAdjust=-1000
ExecStart=/postgresql/pg16/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/postgresql/pg16/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/postgresql/pg16/bin/pg_ctl reload -D ${PGDATA} -s
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0
[Install]
WantedBy=multi-user.target
EOF
systemctl daemon-reload
systemctl enable PG16
systemctl start PG16
systemctl status PG16.service
systemctl stop PG16.service
systemctl status PG16.service
systemctl start PG16.service
journalctl -xe
systemctl status PG16.service
ps -ef|grep pgsql
su - pgsql
systemctl status PG16.service
systemctl start PG16.service
systemctl status PG16.service
su - pgsql
pwd
wget -O pg_rman-1.3.16.tar.gz https://github.com/ossc-db/pg_rman/archive/refs/tags/V1.3.16.tar.gz
ll
unzip pg_rman-1.3.16.tar.gz
tar -zxvf pg_rman-1.3.16.tar.gz
ll
rm pg_rman-1.3.16.tar.gz
wget -O pg_rman-1.3.16.tar.gz https://github.com/ossc-db/pg_rman/archive/refs/tags/V1.3.16.tar.gz --no-check-certificate
ll
tar -zxvf pg_rman-1.3.16.tar.gz
cd pg_rman-1.3.16
ll
find /postgresql/ -name pg_config
make USE_PGXS=1 PG_CONFIG=/postgresql/pg16/bin/pg_config
make USE_PGXS=1 PG_CONFIG=/postgresql/pg16/bin/pg_config install
/postgresql/pg16/bin/pg_rman --version
pgsql下的操作：
cd /postgresql/soft
ll
pwd
exit
cd soft
cd /postgresql/soft/
ll
cd postgresql-16.3/
pwd
ll ./
pwd
ll /postgresql/
pwd
ll
./configure --prefix=/postgresql/pg16
make -j 8 && make install
make world -j 8 && make install-world
pwd
cd /home/pgsql/
cat >> ~/.bash_profile <<"EOF"
export LANG=en_US.UTF-8
export PS1="[\u@\h \W]\$ "
export PGPORT=5432
export PGDATA=/postgresql/pgdata
export PGHOME=/postgresql/pg16
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH:.
export DATE=`date +"%Y%m%d%H%M"`
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
EOF
source ~/.bash_profile
su - pgsql
/postgresql/pg16/bin/initdb -D /postgresql/pgdata -E UTF8 --locale=en_US.utf8 -U postgres --data-checksums
cat /postgresql/pgdata/postgresql.conf
cat >> /postgresql/pgdata/postgresql.conf <<"EOF"
listen_addresses = '*'
port=5432
unix_socket_directories='/postgresql/pgdata'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
EOF
cat >> /postgresql/pgdata/pg_hba.conf << EOFlocal all all trust
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5
host replication all 0.0.0.0/0 md5
EOF
pg_ctl status
pg_ctl start
备份恢复的简要过程如下：
检查备份： pg_rman show -B /home/pgsql/pgrman
对数据库做操作。
全备命令：
pg_rman backup --port=5432 --backup-mode=full --backup-path=/home/pgsql/pgrman --with-serverlog
增备命令：
pg_rman backup --port=5432 --backup-mode=incremental --backup-path=/home/pgsql/pgrman --keep-data-generations=3 --keep-data-days=7 --arclog-path=/postgresql/archive/16
备份归档命令： pg_rman backup --port=5432 --backup-mode=archive --pgdata=/postgresql/pgdata --backup-path=/home/pgsql/pgrman --keep-arclog-files=1000 --keep-arclog-days=30
校验归档备份： pg_rman validate --backup-path=/home/pgsql/pgrman
登录库，对库表做dml操作变更
psql -d test
停库：
pg_ctl stop
做恢复操作：
pg_rman restore --pgdata=/postgresql/pgdata --backup-path=/home/pgsql/pgrman --progress
有报错目录不为空：
ll /postgresql/pgdata/pg_xact/
ll /postgresql/pgdata/pg_xact/0000
tail -10 postgresql.conf
rm -rf /postgresql/pgdata/pg_xact/0000
再次做恢复：
pg_rman restore --pgdata=/postgresql/pgdata --backup-path=/home/pgsql/pgrman --progress
检查配置文件，
tail -10 postgresql.conf
发现自动增加了参数include = 'pg_rman_recovery.conf'
修改文件pg_rman_recovery.conf添加恢复的时间点：例如
recovery_target_time = '2024-07-05 10:20:08'
然后启动 pg_ctl start，此时是只读的。
第一次启动后，检查数据是否恢复满足要求，确认后执行select pg_wal_replay_resume() ;确认恢复终止，并可以读写。
然后再次停止库pg_ctl stop，修改vi postgresql.conf去掉恢复的参数#include = ‘pg_rman_recovery.conf’ # added by pg_rman 1.3.16
最后启动后正常库运行。
[pgsql@11g pgdata]$ pg_ctl start
waiting for server to start....2024-07-05 10:30:46.865 CST [2556] LOG: redirecting log output to logging collector process
2024-07-05 10:30:46.865 CST [2556] HINT: Future log output will appear in directory "pg_log".
done
server started
[pgsql@11g pgdata]$ tail -f ./pg_log/postgresql-Fri.log
2024-07-05 10:30:46.877 CST [2560] LOG: restored log file "00000003.history" from archive
2024-07-05 10:30:46.886 CST [2560] LOG: restored log file "00000003000000000000000C" from archive
2024-07-05 10:30:46.903 CST [2560] LOG: redo starts at 0/C000028
2024-07-05 10:30:46.911 CST [2560] LOG: restored log file "00000003000000000000000D" from archive
cp: cannot stat ‘/postgresql/archive/16/00000003000000000000000E’: No such file or directory
2024-07-05 10:30:46.928 CST [2560] LOG: consistent recovery state reached at 0/D000210
2024-07-05 10:30:46.928 CST [2560] LOG: recovery stopping before commit of transaction 758, time 2024-07-05 10:20:14.00828+08
2024-07-05 10:30:46.928 CST [2560] LOG: pausing at the end of recovery
2024-07-05 10:30:46.928 CST [2560] HINT: Execute pg_wal_replay_resume() to promote.
2024-07-05 10:30:46.928 CST [2556] LOG: database system is ready to accept read-only connections
^C
[pgsql@11g pgdata]$ psql -d test
psql (16.3)
Type "help" for help.
test=# insert into t2 values(7);
ERROR: cannot execute INSERT in a read-only transaction
test=# select pg_wal_replay_resume() ;
pg_wal_replay_resume
----------------------
(1 row)
test=# insert into t2 values(7);
INSERT 0 1
test=# select * from t1;
id
----
1
2
3
4
5
6
(6 rows)
test=# select * from t2;
id
----
1
2
3
4
5
7
(6 rows)
test=# \q
[pgsql@11g pgdata]$ tail -f ./pg_log/postgresql-Fri.log
2024-07-05 10:32:58.978 CST [2560] LOG: redo done at 0/D000210 system usage: CPU: user: 0.00 s, system: 0.01 s, elapsed: 132.07 s
2024-07-05 10:32:58.978 CST [2560] LOG: last completed transaction was at log time 2024-07-05 10:18:23.772754+08
2024-07-05 10:32:58.988 CST [2560] LOG: restored log file "00000003000000000000000D" from archive
cp: cannot stat ‘/postgresql/archive/16/00000004.history’: No such file or directory
2024-07-05 10:32:59.006 CST [2560] LOG: selected new timeline ID: 4
2024-07-05 10:32:59.027 CST [2560] LOG: restored log file "00000003.history" from archive
2024-07-05 10:32:59.029 CST [2560] LOG: archive recovery complete
2024-07-05 10:32:59.029 CST [2558] LOG: checkpoint starting: end-of-recovery immediate wait
2024-07-05 10:32:59.033 CST [2558] LOG: checkpoint complete: wrote 4 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.001 s, sync=0.002 s, total=0.005 s; sync files=3, longest=0.001 s, average=0.001 s; distance=16384 kB, estimate=16384 kB; lsn=0/D000210, redo lsn=0/D000210
2024-07-05 10:32:59.037 CST [2556] LOG: database system is ready to accept connections
^C
[pgsql@11g pgdata]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[pgsql@11g pgdata]$ tail -10 postgresql.conf
listen_addresses = '*'
port=5432
unix_socket_directories='/postgresql/pgdata'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
archive_mode=on
archive_command = 'cp %p /postgresql/archive/16/%f'
include = 'pg_rman_recovery.conf' # added by pg_rman 1.3.16
[pgsql@11g pgdata]$ cat pg_rman_recovery.conf
# added by pg_rman 1.3.16
restore_command = 'cp /postgresql/archive/16/%f %p'
recovery_target_timeline = '3'
recovery_target_time = '2024-07-05 10:20:08'
[pgsql@11g pgdata]$ vi postgresql.conf
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The "=" is optional.) Whitespace may be used. Comments are introduced with
# "#" anywhere on a line. The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal. If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, run "pg_ctl reload", or execute
# "SELECT pg_reload_conf()". Some parameters, which are marked below,
# require a server shutdown and restart to take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on". Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units: B = bytes Time units: us = microseconds
# kB = kilobytes ms = milliseconds
# MB = megabytes s = seconds
# GB = gigabytes min = minutes
# TB = terabytes h = hours
# d = days
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.
#data_directory = 'ConfigDir' # use data in another directory
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
# (change requires restart)
# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '' # write an extra PID file
# (change requires restart)
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
#port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
#reserved_connections = 0 # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directories = '/tmp' # comma-separated list of directories
"postgresql.conf" 830L, 30015C
#max_pred_locks_per_transaction = 64 # min 10
# (change requires restart)
#max_pred_locks_per_relation = -2 # negative values mean
# (max_pred_locks_per_transaction
# / -max_pred_locks_per_relation) - 1
#max_pred_locks_per_page = 2 # min 0
#------------------------------------------------------------------------------
# VERSION AND PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------
# - Previous PostgreSQL Versions -
#array_nulls = on
#backslash_quote = safe_encoding # on, off, or safe_encoding
#escape_string_warning = on
#lo_compat_privileges = off
#quote_all_identifiers = off
#standard_conforming_strings = on
#synchronize_seqscans = on
# - Other Platforms and Clients -
#transform_null_equals = off
#------------------------------------------------------------------------------
# ERROR HANDLING
#------------------------------------------------------------------------------
#exit_on_error = off # terminate session on any error?
#restart_after_crash = on # reinitialize after backend crash?
#data_sync_retry = off # retry or panic on failure to fsync
# data?
# (change requires restart)
#recovery_init_sync_method = fsync # fsync, syncfs (Linux 5.8+)
#------------------------------------------------------------------------------
# CONFIG FILE INCLUDES
#------------------------------------------------------------------------------
# These options allow settings to be loaded from files other than the
# default postgresql.conf. Note that these are directives, not variable
# assignments, so they can usefully be given more than once.
#include_dir = '...' # include files ending in '.conf' from
# a directory, e.g., 'conf.d'
#include_if_exists = '...' # include file only if it exists
#include = '...' # include file
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
# Add settings for extensions here
listen_addresses = '*'
port=5432
unix_socket_directories='/postgresql/pgdata'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
archive_mode=on
archive_command = 'cp %p /postgresql/archive/16/%f'
#include = 'pg_rman_recovery.conf' # added by pg_rman 1.3.16
"postgresql.conf" 830L, 30016C written
[pgsql@11g pgdata]$ pg_ctl start
waiting for server to start....2024-07-05 10:34:14.317 CST [2753] LOG: redirecting log output to logging collector process
2024-07-05 10:34:14.317 CST [2753] HINT: Future log output will appear in directory "pg_log".
done
server started
[pgsql@11g pgdata]$ tail -f ./pg_log/postgresql-Fri.log
2024-07-05 10:33:35.499 CST [2558] LOG: shutting down
2024-07-05 10:33:35.521 CST [2558] LOG: checkpoint starting: shutdown immediate
2024-07-05 10:33:35.526 CST [2558] LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 1 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.007 s; sync files=3, longest=0.001 s, average=0.001 s; distance=16383 kB, estimate=16384 kB; lsn=0/E000028, redo lsn=0/E000028
2024-07-05 10:33:35.530 CST [2556] LOG: database system is shut down
2024-07-05 10:34:14.317 CST [2753] LOG: starting PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2024-07-05 10:34:14.317 CST [2753] LOG: listening on IPv4 address "0.0.0.0", port 5432
2024-07-05 10:34:14.317 CST [2753] LOG: listening on IPv6 address "::", port 5432
2024-07-05 10:34:14.319 CST [2753] LOG: listening on Unix socket "/postgresql/pgdata/.s.PGSQL.5432"
2024-07-05 10:34:14.323 CST [2757] LOG: database system was shut down at 2024-07-05 10:33:35 CST
2024-07-05 10:34:14.325 CST [2753] LOG: database system is ready to accept connections
^C
[pgsql@11g pgdata]$ tail -10 postgresql.conf
listen_addresses = '*'
port=5432
unix_socket_directories='/postgresql/pgdata'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
archive_mode=on
archive_command = 'cp %p /postgresql/archive/16/%f'
#include = 'pg_rman_recovery.conf' # added by pg_rman 1.3.16
[pgsql@11g pgdata]$ cat pg_rman_recovery.conf
# added by pg_rman 1.3.16
restore_command = 'cp /postgresql/archive/16/%f %p'
recovery_target_timeline = '3'
recovery_target_time = '2024-07-05 10:20:08'
[pgsql@11g pgdata]$ psql -d test
psql (16.3)
Type "help" for help.
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
public | t2 | table | postgres
(2 rows)
test=# select * from t1;
id
----
1
2
3
4
5
6
(6 rows)
test=# select * from t2;
id
----
1
2
3
4
5
7
(6 rows)
test=# \q
[pgsql@11g pgdata]$
参考文档：
PostgreSQL 16数据库的各种安装方式汇总（yum、编译、docker等）
pg_rman 1.3.16与PostgreSQL 16实践
postgresql数据库PITR,数据增量恢复
![[氮化镓]Kevin J. Chen组新作—肖特基p-GaN HEMTs正栅ESD机理研究](https://i-blog.csdnimg.cn/direct/0a07bae20af740d9b21f2847fc9d28e8.png)
