postgres备份

| | 备份速度|备份范围|恢复范围|操作影响|备份原理|恢复成本|
| ------- |:------:|:-----:|:------:|:------:|:-----:|:-----:|:-------|
| 流复制 | 实时 | 全备 | 当前时刻|备库只读 | 预写日志 | 高可用切换|
| pg_dump(all) | 慢 |全备(库表) | 备份时刻|无,互斥锁,DDL|SQL访问|停库恢复|
| pg_basebackup|快| 全备 | 备份时刻|无|文件复制|停库恢复|
|连续归档(PITR)|快 | 增量 |上一次全备到当前任意时刻|无|归档日志|停库恢复

postgres安装

• centos自带postgres，通过yum list | grep postgresql查看，安装最新版的postgres,安装yum源:yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

• 安装PostgreSQL： yum install postgresql96-server postgresql96-contrib

• 可执行文件在 /usr/pgsql-9.6/bin/添加到环境变量， 数据和配置文件在 /var/lib/pgsql/9.6/data/

export POSTGRES_HOME=/usr/pgsql-9.6
export PGDATA=/var/lib/pgsql/9.6/data/
export PATH=$PATH:$POSTGRES_HOME/bin


TZ='Asia/Shanghai'; export TZ

• 初始化数据库, 在数据文件目录/var/lib/pgsql/9.6/data/ 下执行

  • su - postgres
  • initdb ./

• 支持密码登录修改相关文件：

  • vi /var/lib/pgsql/9.6/data/pg_hba.conf添加host all all 0.0.0.0 0.0.0.0 md5
  • 开启远程访问，vi postgresql.conf修改 listen_addresses='*'
  • 重启postgres，su postgres -c 'pg_ctl restart'，如果没有设置$PGDATA环境变量，执行该命令需要指定数据库目录-D /data

• 登录，创建用户

  • 执行su postgres，输入psql进入postgres命令行
  • 执行ALTER USER postgres WITH PASSWORD '123456' 设置postgres用户的密码
  • 查看角色\du，创建用户并分配权限create user test_user password '123456' Superuser CreateDB; 删除用户drop user test_user;
  • 退出\q

postgres备份

• SQL转储
• 文件系统级别备份
• 流复制
• 在线增量备份与任意时间点恢复

一、SQL转储

SQL 转储方法的思想是创建一个由SQL命令组成的文件，当把这个文件回馈给服务器时，服务器将利用其中的SQL命令重建与转储时状态一样的数据库。pg_dump创建的备份在内部是一致的， 也就是说，转储表现了pg_dump开始运行时刻的数据库快照，且在pg_dump运行过程中发生的更新将不会被转储。pg_dump工作的时候并不阻塞其他的对数据库的操作。 （但是会阻塞那些需要排它锁的操作，比如大部分形式的ALTER TABLE）

• 导出数据库数据 pg_dump -U postgres -f /home/pg1.sql pg
• 恢复数据 psql -U postgres -f /home/pg1.sql pg
• pg_dump 表备份 pg_dump databasename –t tablename1 –t tablename2 >filename
• pg_dumpall备份所有数据库及角色 pg_dumpall > /home/pg_all.dmp， 恢复 psql –f /home/pg_all.dmp postgres
• 备份压缩(处理大型数据库)
  • 备份 pg_dump dbname | gzip > filename.gz
  • 恢复 gunzip -c filename.gz | psql dbname

二、文件系统级别备份

• 连续归档基础备份pg_basebackup，被用于获得一个正在运行的PostgreSQL数据库集簇的基础备份，备份通过一个常规PostgreSQL连接制作，并且使用复制协议。获得这些备份不会影响连接到该数据库的其他客户端,

• 直接复制PostgreSQL用于存储数据库中数据的文件,要求主库关闭。
  tar -cf backup.tar /usr/local/pgsql/data

• 创建一个数据目录的"一致快照"，创建一个包含数据库的卷的"冻结快照"，然后从该快照复制整个数据目录（如上，不能是部分复制）到备份设备，最后释放冻结快照。即使在数据库服务器运行时，这种方式也有效。但是，以这种方式创建的备份保存的文件看起来就像数据库没有被正确关闭时的状态。因此，当你从备份数据上启动数据库服务器时，它会认为上一次的服务器实例崩溃了并尝试重放WAL日志。这不是问题，只是需要注意（当然WAL文件必须要包括在备份中）。你可以在拍摄快照之前执行一次CHECKPOINT以便节省恢复时间。

• 使用rsync来执行一次文件系统备份。其做法是先在数据库服务器运行时执行rsync，然后关闭数据库服务器足够长时间来做一次rsync --checksum （--checksum是必需的，因为rsync的文件修改 时间粒度只能精确到秒）。

三、流复制

流复制允许一台后备服务器比使用基于文件的日志传送更能保持为最新的状态。 后备服务器连接到主服务器， 主服务器则在 WAL(write ahead log) 记录产生时即将它们以流式传送给后备服务器而不必等到 WAL文件被填充。在这种情况下主服务器上提交一个事务与该变化在后备服务器上变得可见之间存在短暂的延迟。 不过这种延迟比基于文件的日志传送方式中要小得多， 在后备服务器的能力足以跟得上负载的前提下延迟通常低于一秒。

默认情况下流复制是异步的

主库配置

• 创建复制用户： create user rep replication password '123456';

• 配置pg_hba.conf： host replication rep 10.0.12.5/8 md5

• 配置postgresql.conf：

 wal_level = replica
 hot_standby = on
 max_wal_senders = 2            #流复制最大连接数
 wal_keep_segments = 16        #xlog段的大小 

从库配置

• pg_basebackup基础备份： pg_basebackup -D $PGDATA -Fp -Xs -v -P -h 10.0.12.56 -p 5432 -U rep

• 配置recovery.conf

    cp /usr/pgsql-9.6/share/recovery.conf.sample  $PGDATA/recovery.conf
    vim $PGDATA/recovery.conf
    standby_mode = on
    primary_conninfo = 'host=10.0.12.56 port=5432 user=rep password=123456'

• 启动postgres： pg_ctl start

测试 流复制

查看相关进程

• 主库查找进程ps -ef |grep postgres，其中包含 postgres: wal sender process...
• 从库查找进程ps -ef |grep postgres，其中包含 postgres: wal receiver process...

查看记录点

• 返回主库记录点、备库记录点：postgres=# select txid_current_snapshot(); 主库每增加一条写入，记录点的值就会加1

查看主备库同步状态

• postgres=# select * from pg_stat_replication; 字段state显示的同步状态有：startup（连接中）、catchup（同步中）、streaming（同步）；字段sync_state显示的模式有：async（异步）、sync（同步）、potential（虽然现在是异步模式，但是有可能升级到同步模式）

• 此时在主库对数据的操作将会同步至备库，备库处于只读模式。参考主从切换，将备库升级为主库。

四、在线增量备份与任意时间点恢复

在任何时间，PostgreSQL在数据集簇目录的pg_xlog/子目录下都保持有一个预写式日志（WAL）。这个日志存在的目的是为了保证崩溃后的安全：如果系统崩溃，可以"重放"从最后一次检查点以来的日志项来恢复数据库的一致性。该日志的存在也使得第三种备份数据库的策略变得可能：我们可以把一个文件系统级别的备份和WAL文件的备份结合起来。当需要恢复时，我们先恢复文件系统备份，然后从备份的WAL文件中重放来把系统带到一个当前状态。这种方法比之前的方法管理起来要更复杂，但是可以实现数据任意时间点恢复功能。

主库设置(基于流复制中主库的配置)

• 配置postgresql.conf：

#开启归档模式
archive_mode = on
archive_command = 'ssh 10.0.12.5 test ! -f /data/postgres/pg_archive/%f && scp %p  10.0.12.5:/data/postgres/pg_archive/%f'

其中archive_command是归档命令，将归档文件传送至 10.0.12.5备份服务器的/data/postgres/pg_archive目录下

执行该命令前要保证 主备库服务器的免密登录, postgres用户目录在/var/lib/pgsql，切换postgres用户，执行ssh-keygen -t rsa，将生成的公钥/var/lib/pgsql/.ssh/id_rsa.pub添加到免密登录主机对应的/var/lib/pgsql/.ssh/authorized_keys文件中，注意公钥中的主机名需要在免密登录的主机上配置dns或直接改为ip地址。

重启主库pg_ctl restart

从库设置

• 创建存储归档文件的目录： mkdir -p /data/postgres/pg_archive，设置文件权限chown postgres:postgres /data/postgres/pg_archive

• 创建主库的基础备份目录 mkdir -p /data/postgres/pg_basebackup，执行pg_basebackup -D /data/postgres/pg_basebackup -Fp -Xs -v -P -h 10.0.12.56 -p 5432 -U rep将主库基础备份至该目录，然后需要修改基础备份中的postgresql.conf，关闭归档模式，注释掉archive_mode和archive_command

• 配置恢复文件，restore_command将归档日志复制过来，recovery_target_time是恢复到的时间点,将$PGDATA即/var/lib/pgsql/9.6/data链接到基础备份目录/data/postgres/pg_basebackup，设置权限chmod 0700 $PGDATA，启动postgres，pg_ctl start，即可恢复到指定时间点。

cp /usr/pgsql-9.6/share/recovery.conf.sample  /data/postgres/pg_basebackuprecovery.conf

vim recovery.conf
restore_command = 'cp /data/postgres/pg_basebackup/%f %p'
recovery_target_time = '2017-11-14 18:31:00'

---

参考资料：

http://www.postgres.cn/docs/9.6/backup.html

https://yq.aliyun.com/articles/59355#

http://blog.csdn.net/yanggd1987/article/details/51209344

https://www.2cto.com/database/201708/670383.html