postgresql恢复备份主从
84593973 2020-03-06
参考
http://www.postgres.cn/docs/11/app-pg-dumpall.html
http://m.blog.chinaunix.net/uid-20665047-id-5817656.html
http://www.postgres.cn/docs/11/continuous-archiving.html#BACKUP-ARCHIVING-WAL
https://blog.csdn.net/yaoqiancuo3276/article/details/80826073
全量备份
我们要做实时恢复，就需要对数据库做增量备份，也就是记录每次数据库的操作。恢复的时候，就按照记录，一条条的把数据还原到正确的地方。但是不管怎么样，都需要有一个基准，也就是从一个初始状态开始，按照操作记录，一条条的恢复。
pg_basebackup
pg_basebackup的作用相当于是把数据库目录下的相关数据文件拷贝到一个地方。
只能用这个做全量备份或是自己拷贝所有的数据库文件，不可以使用pg_dump、pg_dumpall或者navicat的备份工具备份。因为pg_dump等只是把数据库的表备份出来。postgresql的还原需要数据库目录中的一些信息。这也是与mysql不一样的地方。
mysql是在当前数据的基础上，一条条的执行binlog中的命令。而postgresql是在数据库文件记录的当前状态下，向前同步到对应的状态。比如同步了第几条wal日志信息，还有哪些信息没同步，需要同步到第几个。如果仅仅是把数据库的表导出来，这个无法做还原，因为数据库当前状态一直是最新的状态，就算把数据改回原来的数据，对于数据库来说，只不过是更新了新的数据，并不认为是回退到了原来的某个时间点。
pg_basebackup -D /home/postgres/basebackup/
把数据全备份到指定目录
pg_dump
pg_dump dbname > dbback1
把数据库名字是dbname的数据备份到dbback1中，上面的语句并没有指定用户名和密码，是因为在postgres自己的用户下，默认配置是peer访问权限，也就是在自己用户下，不需要输入密码。
psql db_payserver < dbback1
把原来备份的数据还原。
db_dumpall
db_dumpall备份的数据更多。这个是把整个数据库备份。每个单独的数据库调用的是db_dump。但是它还额外备份了一些其他信息，其中包括
所有数据库公用的全局对象（pg_dump不保存这些对象），数据库角色和表空间都会被转储。包括适数据库用户和组、表空间以及适合所有数据库的访问权限等属性。
pg_dumpall需要多次连接到PostgreSQL服务器（每个数据库一次）。如果你使用口令认证，可能每次都会要求口令。这种情况下使用一个~/.pgpass会比较方便。
pg_dumpall > dball
psql -f dball
打开归档
与mysql的binlog类似，postgresql也会保留对数据库的操作日志，这也是时间点恢复的基础。同样需要我们配置相关参数打开。
修改配置
vim /etc/postgresql/11/main/postgresql.conf
wal_level = logical # minimal, replica, or logical
# (change requires restart)
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = ‘test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f‘ # command to use to archive a logfile segment
# placeholders: %p = path of file to archive
# %f = file name only
# e.g. ‘test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f‘配置解释
wal_level-设置wal log需要记录哪些信息，minimal记录的信息不全，不能恢复数据。所以信息需要是replica或是更高的logical。这个等级就是记录的信息越来越多。
archive_mode-是否打开归档
archive_command-这个是当postgresql完成一个归档的时候，运行的命令。postgresql与mysql一样会记录每一次数据库操作，放到一个文件中。这个文件是有大小限制的，默认16m的时候就会换一个文档。当发生这个操作的时候，就会运行archive_command指定的命令。我们可以把当前的文档拷贝到一个地方备份起来。test的目的是不要覆盖已有的文件
wal_level是设置wal日志的模式，postgresql不管有没有设置，都会有wal日志。
postgresql产生wal日志后，会不断的删除原来的wal文档，为了可以恢复到以前更老的时间点，需要每次postgresql完成一个wal日志文件创建下一个文件的时候，把当前的文件拷贝到一个地方保存起来。归档的作用就是postgresql给我们提供的一个借口，可以自动的完成这个操作。
注意事项
windows下创建的目录，用来拷贝保存wal日志。权限需要增加network service用户组，不然会报错没有权限，无法拷贝数据。
重启服务或是系统
我们可以调用pg_switch_wal命令来刷新强制产生一次归档，看看数据是否拷贝到我们命令设置的地方。
数据还原测试
通过pg_basebackup全量本分数据库
这就相当于做了一个还原点，从现在这个点往后的数据都可以还原
确定打开了wal日志，并设定为replica或logical
确定打开了归档
修改数据
第一条数据改为111
第二条数据改为222
第三条数据改为333
查看当前wal日志
SELECT pg_walfile_name(pg_current_wal_lsn())
查看当前wal详细信息
/usr/lib/postgresql/11/bin/pg_controldata /var/lib/postgresql/11/main
手动归档当前wal日志
select pg_switch_wal();
手动把当前的wal刷新一下，切换到另一个新的日志。这样就可以分析当前的日志了。因为分析正在使用的wal日志有可能会报错。
运行这个命令或是数据库在重启（也就是关闭的时候）或是在系统重启（也就是数据库关闭的时候），都会切换日志，运行归档命令。
这个也可以不做，因为恢复数据的时候需要停止数据库，这是也会归档wal日志。
停止数据库
把数据库的data目录拷贝到一个地方，防止恢复失败的时候导致更多数据丢失
C:\Program Files\PostgreSQL\11\data
/var/lib/postgresql/11/main
上面是windows和linux下默认安装的data目录
清空原来的data目录下的数据
把上面pg_basebackup备份的数据拷贝到data目录
就相当于从备份那个点开始还原
分析日志
pg_waldump是用来分析wal日志的命令
pg_waldump 000000040000000000000010
这个命令就是导出日志的详细信息。这个命令有可能不在系统的环境中，直接执行会报找不到命令的错误，可以通过find找到对应的位置。
postgresql的wal日志有一个很大的问题，就是不知道执行的是什么sql语句。这个与mysql的binlog比起来就麻烦了很多。虽然有一些方法可以导出来，但是太麻烦了。希望postgresql以后可以提供直接在日志中显示sql语句的功能。
上面就是wal日志的详细信息，我们可以看到COMMIT信息，还有对应的日期。COMMIT信息上面描述了这次操作的类型，比如HOT_UPDATE，就是运行了update命令，更新了数据。
我们看到中间有running xid 1205; online等信息，xid 1205就是这次运行的事务id。也就是我们修改数据库，默认数据库也会开启事务，与我们自己写的事务一样，为了保证这次修改要么成功，要么没修改，不存在中间状态。
我们还原的时候可以根据这个事务id还原，就可以保证精确度。
上面修改了三次，我们看到这里有三个事务id，1205、1206、1207。所以如果还原到第二条修改，那就是1206.
拷贝wal日志到data目录下的pg_wal
拷贝recovery.conf.sample到data目录下，并且修改名字为recovery.conf
这里面有注释，可以根据注释修改，我这里只打开了两个
restore_command = ‘‘ recovery_target_xid = ‘1206‘
第一个是必须要开的，就是把原来的归档，wal日志，拷贝到pg_wal目录，如果提前自己拷贝进来，直接是空就好了。
第二个是按照xid的模式还原到1206个xid
启动数据库
我们看到数据还原到第二条修改，第三条修改还是原来的值
