How to Build a Remote Secondary Database on PostgreSQL Using pg_basebackup

In this article, the author explains how to run a self-built standby remote secondary database of ApsaraDB RDS for PostgreSQL 11 using PostgreSQL pg_basebackup.

By digoal

Background

You can access a self-built remote secondary database of ApsaraDB RDS for PostgreSQL 11, whether in the same data center or a remote place, as long as the network is connected. This is because the secondary database uses the stream replication of PostgreSQL.

For example, take the Elastic Compute Service (ECS) of the remote PostgreSQL secondary database. First, ensure the ECS instance has the same storage and memory capacity as the ApsaraDB RDS for PostgreSQL instance. Also, refer to the following examples.

Examples

1) CentOS 7.x x64

2) OS configuration:

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) PostgreSQL 11 software:

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) Data storage:

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) Environment variables:

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) Create an ApsaraDB RDS for PostgreSQL flow for stream replication.

开源版本(需要replication 角色)：  
create role stduser login replication encrypted password 'pwd';  
  
rds pg 版本 使用rds_superuser代替 （如下）：  
create role stduser login rds_superuser encrypted password 'pwd';

7) Configure the whitelist of ApsaraDB RDS for PostgreSQL.

8) ApsaraDB RDS for PostgreSQL address:

Internet pgm - xxxx.pg.rds.aliyuncs.com:3433

Intranet pgm - xxxxxxx.pg.rds.aliyuncs.com:3433

9) Configure environment variables.

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

10) Create ApsaraDB RDS for PostgreSQL standby using pg_basebackup

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) View the standby recovery.conf configuration.

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) Configure the standby postgresql.conf

Annotate unique parameters of the ApsaraDB RDS and the local preload library of uninstalled plug-ins. If you need to use these plug-ins, you must install the plug-ins on the ECS instance and modify the listener simultaneously.

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) Configure the standby pg_hba.conf

可以不配置

14) Start standby.

su - postgres
pg_ctl start

15) Link to the ApsaraDB RDS for PostgreSQL primary database to query monitoring latency.

psql -h pgm-xxx.pg.rds.aliyuncs.com -p 3433 -U stduser postgres  
  
postgres=> select * from pg_stat_replication ;  
-[ RECORD 1 ]----+------------------------------  
pid              | 94  
usesysid         | 16385  
usename          | replicator  
application_name | standby1  
client_addr      | 192.168.0.xxx  
client_hostname  |   
client_port      | 46234  
backend_start    | 2019-10-28 10:29:55.251335+08  
backend_xmin     |   
state            | streaming  
sent_lsn         | 11/7D0014F0  
write_lsn        | 11/7D0014F0  
flush_lsn        | 11/7D0014F0  
replay_lsn       | 11/7D0014F0  
write_lag        |   
flush_lag        |   
replay_lag       |   
sync_priority    | 0  
sync_state       | async  
-[ RECORD 2 ]----+------------------------------  
pid              | 12615  
usesysid         | 16398  
usename          | stduser  
application_name | custom_client  
client_addr      | xxx.xxx.xxx.xxx  
client_hostname  |   
client_port      | 64870  
backend_start    | 2019-11-12 21:21:50.036358+08  
backend_xmin     |   
state            | catchup  
sent_lsn         | 11/79F20000  
write_lsn        | 11/78F40000  
flush_lsn        | 11/78F40000  
replay_lsn       | 11/79000000  
write_lag        | 00:00:08.805876  
flush_lag        | 00:00:08.805876  
replay_lag       | 00:00:08.805876  
sync_priority    | 0  
sync_state       | async

View delay:

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';  
  
  
-[ RECORD 1 ]----+------------------------------  
pg_size_pretty   | 0 bytes  
pg_size_pretty   | 0 bytes  
pid              | 12615  
usesysid         | 16398  
usename          | stduser  
application_name | custom_client  
client_addr      | xxx.xxx.xxx.xxx 
client_hostname  |   
client_port      | 64870  
backend_start    | 2019-11-12 21:21:50.036358+08  
backend_xmin     |   
state            | streaming  
sent_lsn         | 11/7E0014C0  
write_lsn        | 11/7E0014C0  
flush_lsn        | 11/7E0014C0  
replay_lsn       | 11/7E0014C0  
write_lag        |   
flush_lag        |   
replay_lag       |   
sync_priority    | 0  
sync_state       | async

References

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

Community

How to Build a Remote Secondary Database on PostgreSQL Using pg_basebackup

Background

Examples

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

Database Backup

Database for FinTech Solution

Oracle Database Migration Solution