1. 增量备份

增量备份和任意时间点恢复：
image.png

  1. 实现增量备份的思路是
  2. 1. 记录数据库系统的操作记录（WAL归档）
  3. 2. 在某一时刻进行一次完整的数据库备份
  4. 3. 需要恢复时,还原上一次完整的数据库备份,根据操作记录恢复数据库至指定的某个时刻(即可以实现PITR 时间点恢复)

概述 操作

2. 全量备份

2.1 全量备份：

  1. 1. 模拟环境：
  2. # 创建数据：
  3. create table t1(id int not null primary key,name varchar(20) not null);
  4. insert into t1 values(1,'zhnagsan'),(2,'lisi');
  6. 2. 进行备份：
  7. pg_basebackup -Ft -Pv -Xf -z -Z5 -p 5432 -D /usr/local/pgsql/backup/
  9. 3. 模拟数据库故障：
  10. pg_ctl -D /usr/local/pgsql/data/ stop
  11. rm -rf /usr/local/pgsql/data/*
  13. 4. 进行数据恢复：
  14. cd /usr/local/pgsql/backup/
  15. tar xf base.tar.gz -C ../data

2.2 全备结合时间点恢复：

  1. # 创建归档目录：
  2. mkdir -p /pgccc/archive
  3. chown -R postgres. /pgccc/
  5. # 开启归档，并重启数据库：
  6. wal_level = 'replica'
  7. archive_mode = 'on'
  8. archive_command = 'cp %p /pgccc/archive/%f'
  10. # 重启数据库：
  11. pg_ctl restart
  13. # 执行全量备份:
  14. pg_basebackup -D /pgccc/backup  -P  -p 5432 -U postgres
  15. # 如果此时的数据有变化，也可以执行增量备份:
  16. pg_receivewal -D /pgccc/backup/ -p 5432
  18. # 插入数据：
  19. create table a(id int);
  20. insert into a values (2);
  21. select current_timestamp;
  22. select pg_switch_wal();checkpoint;
  24. # 模拟数据丢失：
  25. drop table a;
  27. # 启动备份实例，进行数据恢复：
  28. vim postgresql.conf
  29. recovery_target_time = '2021-07-03 11:55:41.543904+08'
  30. restore_command='cp /pgccc/archive/%f %p'
  31. recovery_target_action = 'promote'
  33. # 创建备库标签：
  34. touch recovery.signal
  36. # 启动数据库：
  37. pg_ctl -D /pgccc/backup start
  39. # 关闭读模式：
  40. select pg_wal_replay_resume();

2.3 全备结合还原点恢复：

  1. # 创建归档目录：
  2. mkdir -p /pgccc/archive
  3. chown -R postgres. /pgccc/
  5. # 开启归档，并重启数据库：
  6. wal_level = 'replica'
  7. archive_mode = 'on'
  8. archive_command = 'cp %p /pgccc/archive/%f'
  10. # 重启数据库：
  11. pg_ctl restarat
  13. # 执行全量备份:
  14. pg_basebackup -D /pgccc/backup  -P  -p 5432 -U postgres
  15. 如果有变化的数据，可以执行增量备份:
  16. pg_receivewal -D /pgccc/backup/ -p 5432
  18. # 插入数据：
  19. create table a(id int);
  20. insert into a values (2);
  22. # 创建还原点：
  23. select pg_create_restore_point('huanyuan');
  25. # 模拟数据丢失：
  26. drop table a;
  28. # 启动备份实例，进行数据恢复：
  29. vim postgresql.conf
  30. recovery_target_name = 'huanyuan'
  31. restore_command='cp /pgccc/archive/%f %p'
  32. recovery_target_action = 'promote'
  34. touch recovery.signal
  36. # 启动数据库：
  37. pg_ctl -D /pgccc/backup start
  39. # 关闭读模式：
  40. select pg_wal_replay_resume();

2.4 全备结合指定事务恢复：

  1. # 创建归档目录：
  2. mkdir -p /pgccc/archive
  3. chown -R postgres. /pgccc/
  5. # 开启归档，并重启数据库：
  6. wal_level = 'replica'
  7. archive_mode = 'on'
  8. archive_command = 'cp %p /pgccc/archive/%f'
  10. # 重启数据库：
  11. pg_ctl restarat
  13. # 执行全量备份:
  14. pg_basebackup -D /pgccc/backup  -P  -p 5432 -U postgres
  15. # 如果有变化的数据，可以执行增量备份:
  16. pg_receivewal -D /pgccc/backup/ -p 5432
  18. # 插入数据：
  19. create table a(id int);
  20. insert into a values (2);
  21. select pg_current_xact_id();
  22. select pg_switch_wal();checkpoint;
  24. # 模拟数据丢失：
  25. drop table a;
  27. # 启动备份实例，进行数据恢复：
  28. vim postgresql.conf
  29. recovery_target_xid = '487'
  30. restore_command='cp /pgccc/archive/%f %p'
  31. recovery_target_action = 'promote'
  33. touch recovery.signal
  35. # 启动数据库：
  36. pg_ctl -D /pgccc/backup start
  38. # 关闭读模式：
  39. select pg_wal_replay_resume();

2.5 全备结合LSN号码恢复：

  1. # 创建归档目录：
  2. mkdir -p /pgccc/archive
  3. chown -R postgres. /pgccc/
  5. # 开启归档，并重启数据库：
  6. wal_level = 'replica'
  7. archive_mode = 'on'
  8. archive_command = 'cp %p /pgccc/archive/%f'
  10. # 重启数据库：
  11. pg_ctl restarat
  13. # 执行全量备份:
  14. pg_basebackup -D /pgccc/backup  -P  -p 5432 -U postgres
  15. # 如果有变化的数据，可以执行增量备份:
  16. pg_receivewal -D /pgccc/backup/ -p 5432
  18. # 插入数据：
  19. create table a(id int);
  20. insert into a values (2);
  21. select pg_current_wal_lsn();
  22. select pg_switch_wal();checkpoint;
  24. # 模拟数据丢失：
  25. drop table a;
  27. # 启动备份实例，进行数据恢复：
  28. vim postgresql.conf
  29. recovery_target_lsn = '0/4011BF8'
  30. restore_command='cp /pgccc/archive/%f %p'
  31. recovery_target_action = 'promote'
  33. touch recovery.signal
  35. # 启动数据库：
  36. pg_ctl -D /pgccc/backup start
  38. # 关闭读模式：
  39. select pg_wal_replay_resume();

3. SQL转储和文件系统级别备份

2.1 pg_dump的使用：

  1. 1. 备份及恢复库下所有的数据：
  2. # 普通格式备份及恢复：
  3. # 备份：
  4. pg_dump -U postgres test >test.sql
  5. # 恢复：
  6. psql
  7. create database test;
  8. psql -U postgres test < test.sql
  10. # 指定格式进行备份：
  11. # 备份：
  12. pg_dump -Fc -U postgres test >test.dmp
  13. # 恢复：
  14. psql
  15. create database test;
  16. pg_restore -d test test.dmp
  18. 2. 备份库下某个模式所有的表：
  19. # 备份：
  20. pg_dump -U postgres -t 'schema1.t*' test >test.sql
  21. # 恢复：
  22. psql -U postgres test < test.sql
  23. # 在备份过程中也可以排除某个表，例如排除zhang表：
  24. pg_dump -U postgres -t 'schema1.t*' -T 'schema1.zhang' test >test.sql
  26. 3. 备份单个表：
  27. # 例如备份test库下的zhang表：
  28. # 备份：
  29. pg_dump -t zhang test >dump.sql
  30. # 恢复：
  31. create database test;
  32. psql -U test < dump.sql

2.2. pg_dumpall的使用：

  1. # 全库级别备份：
  2. # 备份：
  3. pg_dumpall -U postgres > dumpall.sql
  4. # 恢复：
  5. psql -U postgres < dumpall.sql

4. 其他备份工具

url