linux下postgresql的主从备份 - cytggit/Map-openlayers GitHub Wiki

linux下postgresql的主从备份（pg_basebackup ）

准备

防火墙

iptables -I INPUT -p tcp --dport 5432 -j ACCEPT

版本

主从的postgres的版本、环境等最好都一致，否则可能产生奇奇怪怪的问题。

centos、postgresql-9.6

安装postgresql

参考另外一篇，可能需要先安装依赖库epel，从库只需安装，不需要install以下步骤

postgresql 的主配置

添加环境变量

echo 'PATH=$PATH:/usr/pgsql-9.6/bin' >> /etc/profile
echo 'PGDATA=/var/lib/pgsql/9.6/data' >> /etc/profile
source /etc/profile   #使其生效

新建主从同步的专用账号

postgres# CREATE ROLE replica login replication encrypted password 'replica'

配置pg_hba.conf（只列出主从备份相关配置）

host    replication     replica     192.168.1.200/32                 md5

配置postgresql.conf （只列出主从备份相关配置）

max_connections = 900 # 这个设置要注意下，从库的max_connections必须要大于主库的

wal_level = hot_standby  # 这个是设置主为wal的主机
fsync = on 
synchronous_commit = on
wal_sync_method = fsync

checkpoint_segments = 3
checkpoint_timeout = 5min

max_wal_senders = 2 # 这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个
wal_keep_segments = 256 ＃ 设置流复制保留的最多的xlog数目
replication_timeout = 60s
synchronous_standby_names='standby01,standby02'

重启数据库

切换到postgres后 pg_ctl -D $PGDATA start （如果有报错，可直接看到报错的具体信息）或者直接service postgresql-9.6 restart

postgresql 的从配置

添加环境变量

echo 'PATH=$PATH:/usr/pgsql-9.6/bin' >> /etc/profile
echo 'PGDATA=/var/lib/pgsql/9.6/data' >> /etc/profile
source /etc/profile   #使其生效

暂停postgresql服务

切换到postgres后 pg_ctl -D $PGDATA stop

检查/var/lib/pgsql/9.6/data目录，清空就好

拷贝主库数据（root下执行）

pg_basebackup -D $PGDATA -F p -h 192.168.1.141 -p 5432 -U replica -W

配置recovery.conf

进/var/lib/pgsql/9.6/data目录

   cp -pir /usr/pgsql-9.6/share/recovery.conf.sample recovery.conf

编辑

   standby_mode = on  # 这个说明这台机器为从库
   primary_conninfo = 'host=192.168.1.141 port=5432 user=replica password=replica'  # 这个说明这台机器对应主库的信息
   recovery_target_timeline = 'latest' # 这个说明这个流复制同步到最新的数据

配置postgresql.conf

max_connections = 1000 ＃ 一般查多于写的应用从库的最大连接数要比较大

hot_standby = on  ＃ 说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间
wal_receiver_status_interval = 10s  # 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback = on # 如果有错误的数据复制，是否向主进行反馈

查看data下文件属主（改为postgres）
```
chown -R postgres:postgres *
```
重启从库

检查配置是否成功

查看进程

ps -ef |grep postgre

主库有sender进程，从库有receiver进程

查看复制状态

主库中执行：postgres=# select * from pg_stat_replication;

   pid              | 8467       # sender的进程
   usesysid         | 44673      # 复制的用户id
   usename          | replica    # 复制的用户用户名
   application_name | walreceiver  
   client_addr      | 10.12.12.12 # 复制的客户端地址
   client_hostname  |
   client_port      | 55804  # 复制的客户端端口
   backend_start    | 2015-05-12 07:31:16.972157+08  # 这个主从搭建的时间
   backend_xmin     |
   state            | streaming  # 同步状态 startup: 连接中、catchup: 同步中、streaming: 同步
   sent_location    | 3/CF123560 # Master传送WAL的位置
   write_location   | 3/CF123560 # Slave接收WAL的位置
   flush_location   | 3/CF123560 # Slave同步到磁盘的WAL位置
   replay_location  | 3/CF123560 # Slave同步到数据库的WAL位置
   sync_priority    | 0  #同步Replication的优先度
                         0: 异步、1～?: 同步(数字越小优先度越高)
   sync_state       | async  # 有三个值，async: 异步、sync: 同步、potential: 表示是一个潜在的同步 standby

目前是异步模式，需要同步模式的话，修改主库postgresql.conf 的 synchronous_standby_names = walreceiver ;

重启之后才能转化为同步模式