阿里云 RDS PostgreSQL 11 自建异地从库（standby） - pg_basebackup

digoal 2019-11-12

720

作者

digoal

日期

2019-11-12

背景

自建异地rds pg 11从库，不管是同机房，还是异地，只要网络通就可以。利用PG的流复制。

以异地pg 从库 on ecs为例。首先要确保ecs的存储，内存与rds pg规格相当。同事建议

例子

1、centos 7.x x64

2、os 配置

```
vi /etc/sysctl.conf

add by digoal.zhou

fs.aio-max-nr = 1048576
fs.file-max = 76724600

可选：kernel.core_pattern = /data01/corefiles/core_%e_%u_%t_%s.%p

/data01/corefiles 事先建好，权限777，如果是软链接，对应的目录修改为777

kernel.sem = 4096 2147483647 2147483646 512000

信号量, ipcs -l 或 -u 查看，每16个进程一组，每组信号量需要17个信号量。

kernel.shmall = 107374182

所有共享内存段相加大小限制 (建议内存的80%)，单位为页。

kernel.shmmax = 274877906944

最大单个共享内存段大小 (建议为内存一半), >9.2的版本已大幅降低共享内存的使用，单位为字节。

kernel.shmmni = 819200

一共能生成多少共享内存段，每个PG数据库集群至少2个共享内存段

net.core.netdev_max_backlog = 10000
net.core.rmem_default = 262144

The default setting of the socket receive buffer in bytes.

net.core.rmem_max = 4194304

The maximum receive socket buffer size in bytes

net.core.wmem_default = 262144

The default setting (in bytes) of the socket send buffer.

net.core.wmem_max = 4194304

The maximum send socket buffer size in bytes.

net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_keepalive_intvl = 20
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_time = 60
net.ipv4.tcp_mem = 8388608 12582912 16777216
net.ipv4.tcp_fin_timeout = 5
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syncookies = 1

开启SYN Cookies。当出现SYN等待队列溢出时，启用cookie来处理，可防范少量的SYN攻击

net.ipv4.tcp_timestamps = 1

减少time_wait

net.ipv4.tcp_tw_recycle = 0

如果=1则开启TCP连接中TIME-WAIT套接字的快速回收，但是NAT环境可能导致连接失败，建议服务端关闭它

net.ipv4.tcp_tw_reuse = 1

开启重用。允许将TIME-WAIT套接字重新用于新的TCP连接

net.ipv4.tcp_max_tw_buckets = 262144
net.ipv4.tcp_rmem = 8192 87380 16777216
net.ipv4.tcp_wmem = 8192 65536 16777216

net.nf_conntrack_max = 1200000
net.netfilter.nf_conntrack_max = 1200000

vm.dirty_background_bytes = 409600000

系统脏页到达这个值，系统后台刷脏页调度进程 pdflush（或其他）自动将(dirty_expire_centisecs/100）秒前的脏页刷到磁盘

默认为10%，大内存机器建议调整为直接指定多少字节

vm.dirty_expire_centisecs = 3000

比这个值老的脏页，将被刷到磁盘。3000表示30秒。

vm.dirty_ratio = 95

如果系统进程刷脏页太慢，使得系统脏页超过内存 95 % 时，则用户进程如果有写磁盘的操作（如fsync, fdatasync等调用），则需要主动把系统脏页刷出。

有效防止用户进程刷脏页，在单机多实例，并且使用CGROUP限制单实例IOPS的情况下非常有效。

vm.dirty_writeback_centisecs = 100

pdflush（或其他）后台刷脏页进程的唤醒间隔， 100表示1秒。

vm.swappiness = 0

不使用交换分区

vm.mmap_min_addr = 65536
vm.overcommit_memory = 0

在分配内存时，允许少量over malloc, 如果设置为 1, 则认为总是有足够的内存，内存较少的测试环境可以使用 1 .

vm.overcommit_ratio = 90

当overcommit_memory = 2 时，用于参与计算允许指派的内存大小。

vm.swappiness = 0

关闭交换分区

vm.zone_reclaim_mode = 0

禁用 numa, 或者在vmlinux中禁止.

net.ipv4.ip_local_port_range = 40000 65535

本地自动分配的TCP, UDP端口号范围

fs.nr_open=20480000

单个进程允许打开的文件句柄上限

以下参数请注意

vm.extra_free_kbytes = 4096000
vm.min_free_kbytes = 2097152 # vm.min_free_kbytes 建议每32G内存分配1G vm.min_free_kbytes

如果是小内存机器，以上两个值不建议设置

vm.nr_hugepages = 66536

建议shared buffer设置超过64GB时使用大页，页大小 /proc/meminfo Hugepagesize

vm.lowmem_reserve_ratio = 1 1 1

对于内存大于64G时，建议设置，否则建议默认值 256 256 32

sysctl -p
```

```
vi /etc/security/limits.conf

soft nofile 1024000
hard nofile 1024000
soft nproc unlimited
hard nproc unlimited
soft core unlimited
hard core unlimited
soft memlock unlimited
hard memlock unlimited
```

3、pg 11 软件

```
rpm -ivh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

yum -y install coreutils glib2 lrzsz dstat sysstat e4fsprogs xfsprogs ntp readline-devel zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc gcc-c++ make smartmontools flex bison perl-devel perl-ExtUtils* openldap-devel jadetex openjade bzip2 git iotop

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

yum install -y postgresql11*
```

4、数据存储

parted -s /dev/vdb mklabel gpt parted -s /dev/vdb mkpart primary 1MiB 100%

```
mkfs.ext4 /dev/vdb1 -m 0 -O extent,uninit_bg -E lazy_itable_init=1 -T largefile -L data01

vi /etc/fstab
LABEL=data01 /data01 ext4 defaults,noatime,nodiratime,nodelalloc,barrier=0,data=writeback 0 0

mkdir /data01

mount -a

standby存储目录

mkdir /data01/std
chown -R postgres:postgres /data01/std
```

5、环境变量

```
su - postgres

vi ~/.bash_profile

export PS1="$USER@/bin/hostname -s-> "
export PGDATA=/data01/std
export LANG=en_US.utf8
export PGHOME=/usr/pgsql-11
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=date +"%Y%m%d%H%M"
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA

alias rm='rm -i'
alias ll='ls -lh'
unalias vi
```

6、创建rds pg流复制用户

```
开源版本(需要replication 角色)：
create role stduser login replication encrypted password 'pwd';

rds pg 版本使用rds_superuser代替（如下）：
create role stduser login rds_superuser encrypted password 'pwd';
```

7、配置rds pg白名单

8、rds pg地址：

公网 pgm-xxxx.pg.rds.aliyuncs.com:3433

内网 pgm-xxxxxxx.pg.rds.aliyuncs.com:3433

9、配置环境变量

export PGPORT=3433 export PGUSER=stduser export PGDATABASE=postgres export PGPASSWORD=pwd

10、使用pg_basebackup创建rds pg standby

https://www.postgresql.org/docs/11/app-pgbasebackup.html

pg_basebackup -D /data01/std -F p -r 10M -R -X s -h pgm-xxx.pg.rds.aliyuncs.com -p 3433 -U stduser -s 5

-D /data01/std 数据目录 -F p 通过流复制协议拷贝数据文件 -r 10M 限速 10MB/s -R 生成recovery.conf -X s 通过流复制协议拷贝wal日志 -h xxx rds pg数据库地址 -p 3433 rds pg数据库port -U stduser rds_superuser用户 -s 5 每5秒报告

11、查看配置standby recovery.conf

```
standby_mode = 'on'
primary_conninfo = 'user=stduser password=''pwd'' host=''pgm-xxx.pg.rds.aliyuncs.com'' port=3433 application_name=custom_client'

sslmode可以改成off，如果不需要ssl链接的话

指定application_name，用于识别客户

```

12、配置standby postgresql.conf

注释rds独有参数，以及本地未安装插件的preload library（如果需要使用这些插件的话，必须要在ecs上安装这些插件的软件），同时修改监听。

```
vi $PGDATA/postgresql.conf

port=3433

shared_preload_libraries='pg_stat_statements,auth_delay,auto_explain,zhparser,timescaledb,pg_pathman'

shared_preload_libraries='pg_stat_statements,auth_delay,auto_explain'

rds_max_log_files=20

rds_sync_replication_timeout=0

```

13、配置standby pg_hba.conf

可以不配置

14、启动standby

su - postgres pg_ctl start

15、监控延迟，链接到rds pg主库查询

```
psql -h pgm-xxx.pg.rds.aliyuncs.com -p 3433 -U stduser postgres

查看延迟

```
postgres=> select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_insert_lsn(), sent_lsn)),
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_insert_lsn(), replay_lsn)),*
from pg_stat_replication
where application_name='custom_client';

参考