如何建立基于RDS PostgreSQL的备库

用户在阿里云购买了RDS PostgreSQL，如何在自己的机房或者ECS上建立备库？关于如何构建逻辑备库，在我以前的文章有详细的讲解，所谓逻辑备库，是可以跨版本，甚至仅仅同步一部分相同步的表的备库。 https://yq.aliyun.com/articles/7240
如果用户需要构建一个和RDS PostgreSQL一模一样的备库，则可以通过流复制或者归档来完成。同时用户可以通过这种方法搭建自己的备库，在RDS没有提供异地容灾服务前，进行异地容灾。

步骤如下

1. 准备备库环境安装64位Linux安装与RDS PostgreSQL大版本一致的PostgreSQL软件空间规划
2. 准备一个拥有replication角色的用户
3. 配置外网地址（可选）
4. 配置白名单，测试连通性正常
5. 下载全量备份集，测试归档的下载接口是否正常
6. 配置postgresql.conf, recovery.conf
7. 启动备库，检查是否同步

详细步骤



1. 准备备库环境

• 
  安装CentOS 6.x x64，步骤略。
  线上RDS PostgreSQL版本为9.4.x，所以备库环境也需要安装9.4的大版本，我们可以安装9.4.6，关注一下release notes，确保兼容性。

  
  PS:目前阿里RDS PostgreSQL软件还没有下载或开源，如果将来开放下载或开源的话，建议安装阿里云提供的PostgreSQL版本，可以保证兼容性，以及出问题可以找到阿里云的PostgreSQL内核团队修复。

• 
  确保与线上版本编译参数一致，包括插件版本。只需要关注如下select name,setting from pg_settings;  
• block_size                          | 8192  
• wal_block_size                      | 8192  
• rds_available_extensions            | plpgsql,pg_stat_statements,btree_gin,btree_gist,chkpass,citext,cube,dblink,dict_int,earthdistance,hstore,intagg,intarray,isn,ltree,pgcrypto,pgrowlocks,pg_prewarm,pg_trgm,postgres_fdw,sslinfo,tablefunc,tsearch2,unaccent,postgis,postgis_topology,fuzzystrmatch,postgis_tiger_geocoder,plperl,pltcl,plv8,plls,plcoffee,"uuid-ossp",zhparser,pgrouting,rdkit,pg_hint_plan,pgstattuple
• 
  
• 
  安装软件wget https://ftp.postgresql.org/pub/source/v9.4.6/postgresql-9.4.6.tar.bz2  
• tar -jxvf postgresql-9.4.6.tar.bz2  
• cd postgresql-9.4.6  
• ./configure --prefix=/home/postgres/pgsql9.4.6 --with-blocksize=8 --with-wal-blocksize=8  
• gmake -j 32 world  
• gmake install-world
• 
  
• 
  配置环境变量 # vi ~/env_pg.sh
•   export PS1="$USER@\`/bin/hostname -s\`-> "  
•   export PGPORT=1921  
•   export PGDATA=/data01/pgdata  
•   export LANG=en_US.utf8  
•   export PGHOME=/home/postgres/pgsql9.4.6  
•   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  
•   export PGDATABASE=postgres  
•   export PGUSER=postgres  
•   alias rm='rm -i'  
•   alias ll='ls -lh'  
•   unalias vi  
•   postgres@digoal-> . ./env_pg.sh  
•   -bash: unalias: vi: not found  
•   postgres@digoal-> psql -V  
•   psql (PostgreSQL) 9.4.6
• 
  
• 
  以下插件如果没有用到可以不安装，否则需要手动安装，安装方法见相应的插件官网：postgis
• plv8
• plls
• plcoffee
• zhparser
• pgrouting
• rdkit
• pg_hint_plan
• 
  
• 
  规划空间
  目录空间至少要大于你所购买的RDS的容量规格，例如我买的是5G的实例，那么我本地的单个目录的空间要大于5GB。

  
  PS：目前RDS PostgreSQL不支持自定义表空间，所以所有的数据都是放在默认表空间的，也即是需要单个目录的空间大于购买规格的空间的原因。将来如果RDS开放了创建表空间的权限，可以重新规划本地的目录。 [root@digoal ~]# df -h  

• Filesystem      Size  Used Avail Use% Mounted on  
• /dev/sda2        39G   22G   15G  61% /  
• tmpfs           3.9G     0  3.9G   0% /dev/shm  
• /dev/sdb         20G   44M   19G   1% /data01
• 
  

2. 准备一个拥有replication角色的用户


用户创建的初始账号拥有replication角色。用户可以根据 快速入门-创建数据库和账号的介绍来创建初始账号。

3. 配置公网连接(可选)


如果你需要将RDS复制到阿里云以外的主机，或者RDS和ECS在不同的可用区，则需要通过公网来连接。那么需要用户配置RDS的公网地址，同样在阿里云管理控制台的RDS实例管理中可以申请公网地址。

4. 配置白名单


在阿里云管理控制台的RDS 实例管理 -> 数据安全 配置白名单，测试连通性正常例如备库的出口IP是固定的，则将这个IP添加到白名单，如果不是固定的IP，则需要添加0.0.0.0。

5. 下载全量备份集


在控制台下载最近的一次全量备份集。

6. 测试归档的下载接口是否正常


归档文件的下载需要通过调用API完成。 https://help.aliyun.com/document_detail/rds/OpenAPI-manual/RDS-OpenAPI-LogManagement/DescribeBinlogFiles.html?spm=5176.docrds/OpenAPI-manual/RDS-OpenAPI-BackupRecovery/DescribeBackups.6.217.tqV3VW
什么情况下需要用到API呢？当备库需要的XLOG文件已经被主库删除时。这种情况通常发生在自建的备库和主库网络异常，或者自建的备库由于某些原因停库后，长时间没有接收来自RDS PostgreSQL的XLOG，这些XLOG在RDS归档后就会从线上数据库的WAL日志中清除。
如果发现自建的备库报需要获取的XLOG不存在的错误，这个时候就需要从OSS下载归档了。
如果连OSS中都无法找到需要的归档，说明归档也清除了，那么就需要重建备库，回到第五步骤。

7. 测试数据库的流复制连通性


请替换成您自己的RDS实例连接信息进行测试 postgres@digoal-> psql "replication=true" -h xxxx.pg.rds.aliyuncs.com -p 3433 -U digoal  
Password for user digoal:  
psql (9.4.6, server 9.4.1)  
Type "help" for help.  
postgres=> IDENTIFY_SYSTEM;  
      systemid       | timeline |  xlogpos   | dbname  
---------------------+----------+------------+--------  
6165616856935119759 |        3 | 0/6B3A0180 |  
(1 row)


8. 配置postgresql.conf, recovery.conf


解压全量备份集到规划好的目录。/data01/pgdata
配置 postgresql.conf在文件末尾追加如下： # add by digoal  
port=1921  
unix_socket_directories='.'  
tcp_keepalives_idle = 70  
tcp_keepalives_interval = 10  
tcp_keepalives_count = 10  
log_destination='csvlog'  
logging_collector=on  
log_truncate_on_rotation=on  
log_line_prefix = ''  
log_checkpoints = on  
log_connections = on  
log_disconnections = on  
log_error_verbosity = verbose  
hot_standby = on  
max_standby_archive_delay = 300s  
max_standby_streaming_delay = 300s  
wal_receiver_status_interval = 1s  
hot_standby_feedback = on  
log_statement='none'  
archive_mode=on  
archive_command = '/bin/date'  
track_io_timing=off  
listen_addresses='0.0.0.0'

配置 recovery.conf请替换成您自己的RDS实例连接信息 standby_mode = 'on'  
primary_conninfo = 'host=xxxxxx.pg.rds.aliyuncs.com user=digoal password=xxxx port=3433'  
recovery_target_timeline = 'latest'


9. 启动备库，检查是否同步


RDS中操作 postgres@digoal-> psql -h xxxx.pg.rds.aliyuncs.com -p 3433 -U digoal postgres
Type "help" for help.  
postgres=> create table test(id timestamp);  
postgres=> insert into test values (now());  
postgres=> update test set id=now() returning *;  
postgres=> \watch 1

备库中操作 postgres@digoal-> psql -h 127.0.0.1 -p 1921 -U digoal postgres
Type "help" for help.  
postgres=> select * from test;  
postgres=> \watch 1

查看是否能同步

风险点评估


建议不要使用replication slot, 因为slot会导致主节点不删除XLOG, 从而可能因为网络堵塞，备库异常等无法实时接收XLOG的情况下导致主节点因为保留pg_xlog而把空间用满。