阮胜昌的技术记录站-LinuxMySQL数据库运维

全量备份与恢复

① 模拟环境：

# 创建数据：
create table t1(id int not null primary key,name varchar(20) not null);
insert into t1 values(1,'zhnagsan'),(2,'lisi');

配置postgresql.conf监听所有的ip:
cat postgresql.conf
listen_addresses = '*'          # what IP address(es) to listen on;

编辑pg_hba.conf添加允许主机以IP地址来连接并备份
[postgres@pca data]$ tail pg_hba.conf
host    replication     all             172.16.1.222            trust

重新加载配置文件：
[postgres@pca data]$ pg_ctl reload
server signaled

③   进行备份：
[postgres@pca data]$ pg_basebackup -h 172.16.1.222 -D /data/backup -P -p 5432 -U postgres
24287/24287 kB (100%), 1/1 tablespace

③ 模拟数据库故障：
pg_ctl -D $PGDATA stop

[postgres@pca data]$ pwd
/data/postgresql/data
[postgres@pca data]$ rm -rf ./*

④ 进行数据恢复：
cd /data/backup
[postgres@pca backup]$ pwd
/data/backup
[postgres@pca backup]$ cp -a * $PGDATA

启动数据库服务：
[postgres@pca data]$ pg_ctl start

恢复完成

全备结合时间点进行恢复

# 创建归档目录，这个目录不能跟$PGDATA在同一个目录下，要单独一个目录 ：
mkdir -p /data/archive
chown -R postgres. /data/
# 开启归档，并重启数据库：
vim $PGDATA/postgresql.conf
wal_level = 'replica'
archive_mode = 'on'
archive_command = 'cp %p /data/archive/%f'
# wal_level参数说明：
·  minimal记录wal最少，记录数据库异常关闭需要恢复的wal外，其它操作都不记
录。
·  replica在minimal的基础上还支持wal归档、复制和备库中启用只读查询等操作所
需的wal信息。
·  logical记录wal日志信息最多，包含了支持逻辑解析（10版本的新特性，逻辑复制
使用这种模式）所需的wal，此参数包含了minimal和replica所有的记录。

# 重启数据库：
pg_ctl restart

# 执行全量备份:
pg_basebackup -D /data/backup  -P  -p 5432 -U postgres

# 如果此时的数据有变化，也可以执行增量备份:
pg_receivewal -D /data/backup/ -p 5432

# 插入数据：
create table a(id int);
insert into a values (2);
select current_timestamp;
select pg_switch_wal();
checkpoint;

# 模拟数据丢失：
postgres=# drop table a;
DROP TABLE
postgres=# \d
Did not find any relations.
原来的实例先停止：
[postgres@pca backup]$ pg_ctl stop

# 启动备份实例，进行数据恢复：
[postgres@pca ~]$ cd /data/backup/
[postgres@pca backup]$ pwd
/data/backup

vim postgresql.conf
添加以下参数，其它新增的全部删除：
recovery_target_time = '2021-07-03 11:55:41.543904+08'
restore_command='cp /data/archive/%f  %p'
recovery_target_action = 'promote'
# 创建备库标签：
[postgres@pca backup]$ pwd
/data/backup
[postgres@pca backup]$ touch recovery.signal

# 启动数据库：
修改目录的权限：
[postgres@pca backup]$ pwd
/data/backup
[postgres@pca backup]$ chmod 0750 ./* -R

pg_ctl -D /data/backup start

登录查看表数据：
[postgres@pca ~]$ psql
psql (13.10)
Type "help" for help.

postgres=# \d
        List of relations
Schema | Name | Type  |  Owner  
--------+------+-------+----------
public | a    | table | postgres
(1 row)

postgres=# select * from a;
id
----
  2
(1 row)

全量结合还原点进行恢复

# 创建归档目录：
mkdir -p /data/archive
chown -R postgres. /data/
# 开启归档，并重启数据库：
wal_level = 'replica'
archive_mode = 'on'
archive_command = 'cp %p /data/archive/%f'

# 重启数据库：
pg_ctl restarat

# 执行全量备份:
[postgres@pca ~]$ pg_basebackup -h 172.16.1.222 -D /data/backup  -P  -p 5432 -U postgres
24289/24289 kB (100%), 1/1 tablespace

如果有变化的数据，可以执行增量备份:
pg_receivewal -D /data/backup/ -p 5432

# 插入数据：
create table a(id int);
insert into a values (2);

# 创建还原点：
postgres=# select pg_create_restore_point('huanyuan');
pg_create_restore_point
-------------------------
0/9010608
(1 row)

# 模拟数据丢失：
drop table a;

关闭原实例：
[postgres@pca ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped

# 启动备份实例，进行数据恢复：
[postgres@pca backup]$ pwd
/data/backup
[postgres@pca backup]$ vim postgresql.conf
 删除上面新加的所有配置
新增以下的配置：
recovery_target_name = 'huanyuan'
restore_command='cp /data/archive/%f %p'
recovery_target_action = 'promote'

[postgres@pca backup]$ pwd
/data/backup
[postgres@pca backup]$ touch recovery.signal

# 启动数据库：
pg_ctl -D /data/backup start

验证数据：
[postgres@pca ~]$ psql
psql (13.10)
Type "help" for help.

postgres=# \d
        List of relations
Schema | Name | Type  |  Owner  
--------+------+-------+----------
public | a    | table | postgres
(1 row)

postgres=# select * from a;
id
----
  2
(1 row) 

全备结合LSN号码进行恢复

# 创建归档目录：
mkdir -p /data/archive
chown -R postgres. /data/
# 开启归档，并重启数据库：
wal_level = 'replica'
archive_mode = 'on'
archive_command = 'cp %p /data/archive/%f'

# 重启数据库：
pg_ctl restarat

# 执行全量备份:
pg_basebackup -h 172.16.1.222 -D /data/backup  -P  -p 5432 -U postgres

# 如果有变化的数据，可以执行增量备份:
pg_receivewal -D /data/backup/ -p 5432

# 登录数据库并插入数据：
create table a(id int);
insert into a values (2);
postgres=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/100117E0
(1 row)

select pg_switch_wal();checkpoint;

# 模拟数据丢失：
drop table a; 

关闭原来的实例：
[postgres@pca data]$ pg_ctl stop
waiting for server to shut down.... done
server stopped

# 启动备份实例，进行数据恢复：
[postgres@pca backup]$ pwd
/data/backup

[postgres@pca backup]$ vim postgresql.conf         #删除原来增加的配置
在文件最后添加以下配置：
recovery_target_lsn = '0/100117E0'
restore_command='cp /data/archive/%f %p'
recovery_target_action = 'promote'

[postgres@pca backup]$ pwd
/data/backup
[postgres@pca backup]$ touch recovery.signal

# 启动数据库：
pg_ctl -D /data/backup start

#验证数据：
[postgres@pca ~]$ psql
psql (13.10)
Type "help" for help.

postgres=# \d
        List of relations
Schema | Name | Type  |  Owner  
--------+------+-------+----------
public | a    | table | postgres
(1 row)

postgres=# select * from a;
id
----
  2
(1 row)

全备结合xid进行恢复

# 创建归档目录：
mkdir -p /data/archive
chown -R postgres. /data/

# 开启归档，并重启数据库：
wal_level = 'replica'
archive_mode = 'on'
archive_command = 'cp %p /data/archive/%f'

# 重启数据库：
pg_ctl restarat

# 执行全量备份:
pg_basebackup -h 172.16.1.222 -D /data/backup  -P  -p 5432 -U postgres

# 如果有变化的数据，可以执行增量备份:
pg_receivewal -D /data/backup/ -p 5432

# 登录数据库，插入数据：
psql
create table a(id int);
insert into a values (2);
postgres=# select pg_current_xact_id();
pg_current_xact_id
--------------------
                500
(1 row)

select pg_switch_wal();
checkpoint;

# 模拟数据丢失：
drop table a;

关闭原实例：
[postgres@pca ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped

# 启动备份实例，进行数据恢复：
[postgres@pca backup]$ pwd
/data/backup
[postgres@pca backup]$ vim postgresql.conf         #原来新增的配置文件全部删除
recovery_target_xid = '500'
restore_command='cp /data/archive/%f %p'
recovery_target_action = 'promote'

[postgres@pca backup]$ pwd
/data/backup
[postgres@pca backup]$ touch recovery.signal

# 启动数据库：
pg_ctl -D /data/backup start

#验证数据：
[postgres@pca data]$ psql
psql (13.10)
Type "help" for help.

postgres=# \d
        List of relations
Schema | Name | Type  |  Owner  
--------+------+-------+----------
public | a    | table | postgres
(1 row)

postgres=# select * from a;
id
----
  2
(1 row)