一、物理备份
1、全量备份
pg_basebackup
2、增量备份
pg_basebackup 全量备份
加wal归档
3、恢复
全备结果时间点进行恢复
全备结合还原点进行恢复
全备结合指定事务进行恢复

二、模拟环境【全量备份与恢复】
1、创建数据：
create table calming1 (id int not null primary key,name varchar(20) not null);
insert into calming1 values(1,‘ANDY’),(2,‘JACK’);
实际操作：
postgres=# create table calming1 (id int not null primary key,name varchar(20) not null);
CREATE TABLE
postgres=#
postgres=# insert into t1 values(1,‘ANDY’),(2,‘JACK’);
INSERT 0 2
postgres=# select * from calming1;
id | name
----±-----
1 | ANDY
2 | JACK
(2 rows)

2、增加本地复制的权限
cat /postgresql/pgdata/pg_hba.conf
host replication all 192.168.2.0/24 trust

3、需要重新加载服务
pg_ctl reload

4、 进行备份
pg_basebackup -h 192.168.2.61 -D /postgresql/backup -P -p 5432 -U postgres
[pgsql@pgsql1:/postgresql]pg_basebackup -h 192.168.2.61 -D /postgresql/backup -P -p 5432 -U postgres 120097/120097 kB (100%), 2/2 tablespaces [pgsql@pgsql1:/postgresql]
[pgsql@pgsql1:/postgresql]$[pgsql@pgsql1:/postgresql]$
[pgsql@pgsql1:/postgresql]$

5、停止数据库服务
[pgsql@pgsql1:/postgresql]pg_ctl stop waiting for server to shut down.... done server stopped 6、删除数据目录、启动肯定报错 [pgsql@pgsql1:/postgresql]cd pgdata
[pgsql@pgsql1:/postgresql/pgdata]$ls
！ global pg_hba.conf pg_multixact pg_snapshots pg_tblspc pg_xact
backup_label.old hhh.signal pg_ident.conf pg_notify pg_stat pg_twophase postgresql.auto.conf
base pg_commit_ts pg_log pg_replslot pg_stat_tmp PG_VERSION postgresql.conf
current_logfiles pg_dynshmem pg_logical pg_serial pg_subtrans pg_wal postmaster.opts

[pgsql@pgsql1:/postgresql/pgdata]$rm -rf * [pgsql@pgsql1:/postgresql/pgdata]$cd …
[pgsql@pgsql1:/postgresql]$pg_ctl start
pg_ctl: directory “/postgresql/pgdata” is not a database cluster directory

7、开始进行恢复cp -a * /postgresql/pgdata/
[pgsql@pgsql1:/postgresql]$cd /postgresql/backup/ [pgsql@pgsql1:/postgresql/backup]$ls
！ current_logfiles pg_dynshmem pg_logical pg_serial pg_subtrans pg_wal
backup_label global pg_hba.conf pg_multixact pg_snapshots pg_tblspc pg_xact
backup_label.old hhh.signal pg_ident.conf pg_notify pg_stat pg_twophase postgresql.auto.conf
base pg_commit_ts pg_log pg_replslot pg_stat_tmp PG_VERSION postgresql.conf

cp -a * /postgresql/pgdata/ 拷贝备份的文件到数据目录
pg_ctl start 启动服务
[pgsql@pgsql1:/postgresql/backup]$cp -a * /postgresql/pgdata/ [pgsql@pgsql1:/postgresql/backup]$pg_ctl start
waiting for server to start…2023-11-26 09:33:13.623 CST [13554] LOG: starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2023-11-26 09:33:13.626 CST [13554] LOG: listening on IPv4 address “0.0.0.0”, port 5432
2023-11-26 09:33:13.626 CST [13554] LOG: listening on IPv6 address “::”, port 5432
2023-11-26 09:33:13.628 CST [13554] LOG: listening on Unix socket “/tmp/.s.PGSQL.5432”
2023-11-26 09:33:13.796 CST [13554] LOG: could not open directory “pg_tblspc/16428/PG_12_201909212”: No such file or directory 【这是之前创建表空间，然后删除、该目录没有对应删除】
2023-11-26 09:33:13.797 CST [13554] LOG: redirecting log output to logging collector process
2023-11-26 09:33:13.797 CST [13554] HINT: Future log output will appear in directory “pg_log”.
done
server started

8、验证数据
[pgsql@pgsql1:/postgresql/backup]$psql
psql (12.2)
Type “help” for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------±---------±---------±-----------±-----------±----------------------
fgdb | fgedu | UTF8 | en_US.utf8 | en_US.utf8 |
hcj | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
itpux | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
itpux01 | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
itpuxdb | itpux | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
testdb2 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =T/postgres +
| | | | | postgres=CTc/postgres+
| | | | | u1=Cc/postgres
(10 rows)

postgres=# select * from calming1;
id | name
----±-----
1 | ANDY
2 | JACK
(2 rows)

postgres=#

三、增量备份恢复
1、创建归档目录：
mkdir -p /postgres/archive
授权
chown -R pgsql:pgsql /postgres/archive
参数中配置
/postgresql/pgdata/postgresql.conf 增加下面信息
wal_level = replica
archive_mode = on
archive_command=‘test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f’

wal_level参数说明
minnimal记录wal最小，记录数据库异常关闭需要的wal外，其它操作都不记录
replica在minimal的基础上还支持wal归档，复制和备库中启用只读查询等操作所需要的wal信息
logical 记录wal日志信息最多，包含了支持逻辑解析(10g版本的新特性，逻辑复制使用这种模式）所需的wal，此参数包含minimal和replica所有的记录
/postgresql/pgdata/postgresql.conf 中内容修改后，需要重新启动数据库
pg_ctl restart

2、执行全量备份
pg_basebackup -D /postgresql/pdbackup -P -p 5432 -U postgres

备注：下面是修改密码
ALTER USER postgres WITH PASSWORD ‘postgres’;
postgres=# ALTER USER postgres WITH PASSWORD ‘postgres’;
ALTER ROLE
postgres=# exit
实际备份的操作截图：
[pgsql@pgsql1:/postgresql]pg_basebackup -D /postgresql/pdbackup -P -p 5432 -U postgres Password: 120150/120150 kB (100%), 1/1 tablespace [pgsql@pgsql1:/postgresql]

3、在数据库中插入新数据
create table a1(id int);
insert into a1 values (2);
select current_timestamp;
select pg_switch_wal();checkpoint;
操作截图：
postgres=# create table a1(id int);
CREATE TABLE
postgres=# insert into a1 values (2);
INSERT 0 1
postgres=# select * from a1;
id

2
(1 row)

查询时间
postgres=# select current_timestamp;
current_timestamp

2023-11-26 10:44:58.801437+08
(1 row)

切换归档日志
postgres=# select pg_switch_wal();checkpoint;
pg_switch_wal

0/32011B30
(1 row)
查看归档目录
[pgsql@pgsql1:/home/pgsql]$cd /postgresql/archive/ [pgsql@pgsql1:/postgresql/archive]$ls
00000002000000000000002D 00000002000000000000002F 000000020000000000000031
00000002000000000000002E 000000020000000000000030 000000020000000000000031.00000028.backup

4、 模拟数据丢失：
drop table a1;
postgres=# drop table a1;
DROP TABLE
postgres=# select current_timestamp;
current_timestamp

2023-11-26 10:16:08.440343+08
(1 row)

postgres=#

5、启动备份实例，进行数据恢复：
vim postgresql.conf
recovery_target_time = ‘2023-11-26 10:44:58.801437+08’
restore_command=‘cp /postgresql/archive/%f %p’
recovery_target_action = ‘promote’
6、 创建恢复标签：
touch recovery.signal
7、 启动数据库：
pg_ctl -D /postgres/pgbackup start

关闭只读模式：

select pg_wal_replay_resume();
8、启动备份的实例、并且查看数据
[pgsql@pgsql1:/postgresql/pdbackup]$pg_ctl -D /postgresql/pdbackup start waiting for server to start....2023-11-26 10:50:04.902 CST [15439] LOG: starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit 2023-11-26 10:50:04.904 CST [15439] LOG: listening on IPv4 address "0.0.0.0", port 15432 2023-11-26 10:50:04.904 CST [15439] LOG: listening on IPv6 address "::", port 15432 2023-11-26 10:50:04.906 CST [15439] LOG: listening on Unix socket "/tmp/.s.PGSQL.15432" 2023-11-26 10:50:04.937 CST [15439] LOG: redirecting log output to logging collector process 2023-11-26 10:50:04.937 CST [15439] HINT: Future log output will appear in directory "pg_log". done server started [pgsql@pgsql1:/postgresql/pdbackup]$$psql -h 192.168.2.61 -p 15432 -U postgres bash: -h: command not found... [pgsql@pgsql1:/postgresql/pdbackup]$psql -h 192.168.2.61 -p 15432 -U postgres
psql (12.2)
Type “help” for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------±---------±---------±-----------±-----------±----------------------
fgdb | fgedu | UTF8 | en_US.utf8 | en_US.utf8 |
hcj | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
itpux | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
itpux01 | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
itpuxdb | itpux | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
testdb2 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =T/postgres +
| | | | | postgres=CTc/postgres+
| | | | | u1=Cc/postgres
(10 rows)

postgres=# \d
List of relations
Schema | Name | Type | Owner
--------±-------------------±---------±---------
public | a1 | table | postgres
public | calming1 | table | postgres
public | t1 | table | postgres
public | test_boolean | table | postgres
public | test_char | table | postgres
public | test_serial | table | postgres
public | test_serial_id_seq | sequence | postgres
(7 rows)

postgres=# select * from a1;
id

2
(1 row)

postgres=#

9、关闭备份的实例，同时启动原有生产实例
[pgsql@pgsql1:/postgresql/pdbackup]$pg_ctl -D /postgresql/pdbackup stop waiting for server to shut down.... done server stopped [pgsql@pgsql1:/postgresql]$pg_ctl start
waiting for server to start…2023-11-26 10:52:31.037 CST [15498] LOG: starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2023-11-26 10:52:31.039 CST [15498] LOG: listening on IPv4 address “0.0.0.0”, port 5432
2023-11-26 10:52:31.039 CST [15498] LOG: listening on IPv6 address “::”, port 5432
2023-11-26 10:52:31.041 CST [15498] LOG: listening on Unix socket “/tmp/.s.PGSQL.5432”
2023-11-26 10:52:31.072 CST [15498] LOG: redirecting log output to logging collector process
2023-11-26 10:52:31.072 CST [15498] HINT: Future log output will appear in directory “pg_log”.
done
server started

[pgsql@pgsql1:/postgresql]$psql
psql (12.2)
Type “help” for help.

postgres=# \d
List of relations
Schema | Name | Type | Owner
--------±-------------------±---------±---------
public | calming1 | table | postgres
public | t1 | table | postgres
public | test_boolean | table | postgres
public | test_char | table | postgres
public | test_serial | table | postgres
public | test_serial_id_seq | sequence | postgres
(6 rows)

postgres=#

四、 结合还原点的恢复
1、创建归档目录：
mkdir -p /postgres/archive
授权
chown -R pgsql:pgsql /postgres/archive
参数中配置
/postgresql/pgdata/postgresql.conf 增加下面信息
wal_level = replica
archive_mode = on
archive_command=‘test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f’
wal_level参数说明
minnimal记录wal最小，记录数据库异常关闭需要的wal外，其它操作都不记录
replica在minimal的基础上还支持wal归档，复制和备库中启用只读查询等操作所需要的wal信息
logical 记录wal日志信息最多，包含了支持逻辑解析(10g版本的新特性，逻辑复制使用这种模式）所需的wal，此参数包含minimal和replica所有的记录
/postgresql/pgdata/postgresql.conf 中内容修改后，需要重新启动数据库
pg_ctl restart

2、进行全量备份
pg_basebackup -h 192.168.1.44 -D /postgresql/backup2 -P -p 5432 -U postgres
如果有变化的数据，可以执行增量备份:
pg_receivewal -D /postgresql/backup2/ -p 5432
3、插入数据：
create table a2(id int);
insert into a2 values (2);
4、 创建还原点：
select pg_create_restore_point(‘huanyuan1’);
5、 模拟数据丢失：
drop table a;

实际操作：
[pgsql@pgsql1:/postgresql]pg_basebackup -h 192.168.1.44 -D /postgresql/backup2 -P -p 5432 -U postgres 120152/120152 kB (100%), 1/1 tablespacee [pgsql@pgsql1:/postgresql]
[pgsql@mysql1:/postgresql]$psql
psql (12.2)
Type “help” for help.

postgres=# create table a2(id int);
CREATE TABLE
postgres=# insert into a2 values (2);
INSERT 0 1
postgres=# select pg_create_restore_point(‘huanyuan1’);
pg_create_restore_point

0/5012F28
(1 row)

postgres=# select pg_create_restore_point(‘huanyuan1’);
pg_create_restore_point

0/5012F28
(1 row)

postgres=# drop table a2;
DROP TABLE
postgres=#
postgres=#
postgres=# select current_timestamp;
current_timestamp

2023-11-27 19:00:28.261509+08
(1 row)
postgres=#

6、 启动备份实例，进行数据恢复：
vim postgresql.conf
recovery_target_name = ‘huanyuan1’
restore_command=‘cp /postgresql/archive/%f %p’
recovery_target_action = ‘promote’

7、创建恢复标签
cd /postgresql/backup2
touch recovery.signal
8、启动数据库：
pg_ctl -D /postgresql/backup start
操作如下：
[pgsql@mysql1:/postgresql/backup2]$pg_ctl -D /postgresql/backup2 start
waiting for server to start…2023-11-27 19:01:56.598 CST [12604] LOG: starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
2023-11-27 19:01:56.599 CST [12604] LOG: listening on IPv4 address “0.0.0.0”, port 15432
2023-11-27 19:01:56.599 CST [12604] LOG: listening on IPv6 address “::”, port 15432
2023-11-27 19:01:56.600 CST [12604] LOG: listening on Unix socket “/tmp/.s.PGSQL.15432”
2023-11-27 19:01:56.621 CST [12605] LOG: database system was interrupted; last known up at 2023-11-27 18:58:20 CST
cp: cannot stat ‘/postgresql/archive/00000002.history’: No such file or directory
2023-11-27 19:01:56.649 CST [12605] LOG: starting point-in-time recovery to “huanyuan1”
2023-11-27 19:01:56.737 CST [12605] LOG: restored log file “000000010000000000000004” from archive
2023-11-27 19:01:56.764 CST [12605] LOG: redo starts at 0/4000028
2023-11-27 19:01:56.768 CST [12605] LOG: consistent recovery state reached at 0/4000100
2023-11-27 19:01:56.769 CST [12604] LOG: database system is ready to accept read only connections
done
server started
[pgsql@mysql1:/postgresql/backup2]$2023-11-27 19:01:56.894 CST [12605] LOG: restored log file “000000010000000000000005” from archive
2023-11-27 19:01:57.012 CST [12605] LOG: recovery stopping at restore point “huanyuan1”, time 2023-11-27 18:59:03.321959+08
2023-11-27 19:01:57.012 CST [12605] LOG: redo done at 0/5012EC0
2023-11-27 19:01:57.012 CST [12605] LOG: last completed transaction was at log time 2023-11-27 18:58:52.38568+08
cp: cannot stat ‘/postgresql/archive/00000002.history’: No such file or directory
2023-11-27 19:01:57.026 CST [12605] LOG: selected new timeline ID: 2
2023-11-27 19:01:57.214 CST [12605] LOG: archive recovery complete
cp: cannot stat ‘/postgresql/archive/00000001.history’: No such file or directory
2023-11-27 19:01:57.393 CST [12604] LOG: database system is ready to accept connections

[pgsql@mysql1:/postgresql/backup2]$[pgsql@mysql1:/postgresql/backup2]$
[pgsql@mysql1:/postgresql/backup2] 查看进程 [pgsql@mysql1:/postgresql/backup2]ps -ef|grep postgres
pgsql 12604 1 0 19:01 ? 00:00:00 /postgresql/pg12/bin/postgres -D /postgresql/backup2
pgsql 12608 12604 0 19:01 ? 00:00:00 postgres: checkpointer
pgsql 12609 12604 0 19:01 ? 00:00:00 postgres: background writer
pgsql 12611 12604 0 19:01 ? 00:00:00 postgres: stats collector
pgsql 12614 12604 0 19:01 ? 00:00:00 postgres: walwriter
pgsql 12615 12604 0 19:01 ? 00:00:00 postgres: autovacuum launcher
pgsql 12616 12604 0 19:01 ? 00:00:00 postgres: archiver last was 00000002.history
pgsql 12617 12604 0 19:01 ? 00:00:00 postgres: logical replication launcher
pgsql 12627 11481 0 19:02 pts/2 00:00:00 grep --color=auto postgres

9、登陆数据库，查询还原点恢复那个时间的数据
[pgsql@mysql1:/postgresql/backup2]$psql psql: error: could not connect to server: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"? [pgsql@mysql1:/postgresql/backup2]$psql -h 192.168.1.44 -p 15432 -U postgres
psql (12.2)
Type “help” for help.
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------±-----±------±---------
public | a2 | table | postgres
(1 row)
postgres=# select * from a2;
id

2
(1 row)
postgres=#

10、关闭只读模式：
select pg_wal_replay_resume();

11、关闭备库的实例
[pgsql@mysql1:/postgresql/backup2]$pg_ctl -D /postgresql/backup2 stop
waiting for server to shut down…2023-11-27 20:20:45.902 CST [12604] LOG: received fast shutdown request
2023-11-27 20:20:45.904 CST [12604] LOG: aborting any active transactions
2023-11-27 20:20:45.911 CST [12604] LOG: background worker “logical replication launcher” (PID 12617) exited with exit code 1
2023-11-27 20:20:45.911 CST [12608] LOG: shutting down
2023-11-27 20:20:46.085 CST [12604] LOG: database system is shut down
done
server stopped

12、启动原生产实例【这个表是不存在】
[pgsql@mysql1:/postgresql/backup2]$pg_ctl start waiting for server to start....2023-11-27 20:20:57.837 CST [15484] LOG: starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit 2023-11-27 20:20:57.838 CST [15484] LOG: listening on IPv4 address "0.0.0.0", port 5432 2023-11-27 20:20:57.838 CST [15484] LOG: listening on IPv6 address "::", port 5432 2023-11-27 20:20:57.840 CST [15484] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2023-11-27 20:20:57.871 CST [15485] LOG: database system was shut down at 2023-11-27 19:00:34 CST 2023-11-27 20:20:57.879 CST [15484] LOG: database system is ready to accept connections done server started [pgsql@mysql1:/postgresql/backup2]$psql
psql (12.2)
Type “help” for help.
postgres=# \d
Did not find any relations.
postgres=#

备注：登陆数据库
psql -h 192.168.2.61 -p 15432 -U postgres