背景
最近有项目要用到热备功能，timescaledb只能兼容pg的流复制，不能兼容其他的复制策略，所以这里我们采用pg的流复制功能镜像部署，并进行了一些测试
timescaleDB安装（两台机器都安装）
1.添加postgresql源
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -c -s)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo apt-get update
2.安装timescaledb
sudo add-apt-repository ppa:timescale/timescaledb-ppa sudo apt-get update sudo apt install timescaledb-1.7.5-postgresql-11
3.安装postgis
sudo apt install postgresql-11-postgis-2.5
4.进行数据库调优
sudo timescaledb-tune -yes
5.创建一个300M的占位文件，避免数据库占满硬盘空间，系统无法调试
dd if=/dev/zero of=./useless-placeholder bs=1M count=300
timescaledb流复制配置(主节点配置)
修改配置文件sudo nano /etc/postgresql/11/main/postgresql.conf
# postgresql.conf wal_level = replica max_wal_senders = 16 # 最多多少各流复制链接 wal_keep_segments = 256 # 流复制保留最多的xlog数 wal_sender_timeout = 60s # 流复制主机发送数据超时时间 max_connections = 1000 # 从库的max_connections必须大于主库的 full_page_writes = on # 使用pg_rewind命令同步数据库要用 wal_log_hints = on # 使用pg_rewind命令同步数据库要用 hot_standby = on # 使用pg_rewind命令同步数据库要用 listen_addresses = '*' # 修改监听 archive_mode = on # 开启归档模式 archive_command = 'arch.sh %f %p'
创建replica用户，密码replica123
sudo -u postgres psql CREATE ROLE replica login replication encrypted password 'replica123';
在/var/lib/postgresql/11/main创建arch.sh,用于定时删除超过7天的归档文件内容如下
PGDATA=/var/lib/postgresql/11/main #test ! -f $PGDATA/arch/$1 && cp -r --preserve=timestamps $2 $PGDATA/arch/$1 ; find $PGDATA/arch/ -type f -mtime +1 -exec rm -f {} \; test ! -f $PGDATA/arch/$1 && cp -r --preserve=timestamps $2 $PGDATA/arch/$1 ; # 一天的日志量太大，还是删除最近20个的吧 num=`ls -l $PGDATA/arch | grep '^-' | wc -l` cd $PGDATA/arch if [ $num -gt 20 ]; then #计算超过5个多少 num=`expr $num - 20` clean=`ls -tr | head -$num | xargs` echo "will delete file:" echo ${clean} #-n1 每次处理1个文件 ls -tr | head -$num | xargs -i -n1 rm -rf {} fi
在/var/lib/postgresql/11/main创建arch目录，赋权给arch和arch.sh
mkdir /var/lib/postgresql/11/main/arch chown -R postgres:postgres arch*
确认归档功能开启
root@database-master:/var/lib/postgresql/11/main# ps -ef|grep archiver postgres 29921 29916 0 08:36 ? 00:00:00 postgres: 11/main: archiver root 29953 29477 0 08:36 pts/0 00:00:00 grep --color=auto archiver
配置sudo nano /etc/postgresql/11/main/pg_hba.conf
备注：192.168.0.31 是备节点的IP
# 在配置文件末尾添加 pg_hba.conf host all all 0.0.0.0/0 md5 host replication replica 192.168.0.31/32 md5
重启数据库
sudo systemctl restart postgresql
timescaledb流复制配置(从节点配置)
配置sudo nano /etc/postgresql/11/main/pg_hba.conf
# 在配置文件末尾添加 pg_hba.conf host all all 0.0.0.0/0 md5 host replication replica 192.168.0.31/32 md5
删除数据目录
sudo rm -rf /var/lib/postgresql/11/main sudo pg_basebackup -D /var/lib/postgresql/11/main -Fp -Xs -v -P -h 192.168.0.30 -U replica -W
复制之后，注意设置main目录的权限为postgres
sudo chown -R postgres:postgres /var/lib/postgresql/11/main sudo systemctl restart postgresql
在 /var/lib/postgresql/11/main 下添加recovery.conf文件，内容如下：
注意192.168.0.30是主节点的IP
standby_mode = on primary_conninfo = 'host=192.168.0.30 port=5432 user=replica password=replica123' recovery_target_timeline = 'latest'
修改配置文件sudo nano /etc/postgresql/11/main/postgresql.conf
# postgresql.conf max_connections = 10000 # 从库的max_connections必须大于主库的 max_standby_streaming_delay = 30s wal_receiver_status_interval = 10s hot_standby_feedback = on full_page_writes = on # 使用pg_rewind命令同步数据库要用 wal_log_hints = on # 使用pg_rewind命令同步数据库要用 hot_standby = on # 使用pg_rewind命令同步数据库要用 listen_addresses = '*'
重启数据库
sudo systemctl restart postgresql
场景测试
功能说明：
主库可读可写，备库只可读。备库的数据会通过异步的方式实时从主库同步。
备库关闭，不影响主库的使用，备库重启之后，能自动从主库同步这段时间缺失的数据。
主库关闭，备库不会自动切换成主库，等主库开机之后，主备功能自动恢复。
场景1：确认foreign table可兼容双击热备
步骤1：在master机器上创建两个库，并对其中的一些表之间建立起foreign table。
步骤2：检查slver机器上也自动生成了两个库和对应的外表
步骤3：在测试环境上安装应用程序，进行sql insert操作
步骤4：检查master和slaver上的4张表数据是否一致
结论：foreign table功能能兼容双击热备，不会丢数据
场景2：在小数据量时，如果备库关闭，过一段时间再重启，会有什么后果？
步骤1：在完成场景1的情况下，修改插入频率，提高到每间隔1秒500条数据
步骤2：持续了2分钟之后，突然在slaver机器上运行systemctl stop postgresql
步骤3：master机器继续工作，master上的数据持续增加
步骤4：1小时后，在slaver机器上运行systemctl start postgresql 启动数据库
步骤5：检查slaver机器上缺少的数据是否会补充回来
结论：slaver机器关闭再重启，这段时间缺失的数据会补充回来
场景3：在大数据量时，如果备库关闭，过一段时间再重启，会有什么后果？
步骤1：在完成场景1的情况下，修改插入频率，提高到每间隔1秒500000个条数据
步骤2：持续了2分钟之后，突然在slaver机器上运行systemctl stop postgresql
步骤3：master机器继续工作，master上的数据持续增加
步骤4：1小时候在slaver机器上运行systemctl start postgresql 启动数据库
步骤5：检查slaver机器上缺少的数据是否会补充回来
结论：slaver机器关闭再重启，这段时间缺失的数据会通过wal日志补充回来，补充速度看备机的硬盘io
场景4：在大数据量时，如果主库关闭，过一段时间再重启，主备能自动恢复连接吗？
步骤1：在完成场景1的情况下，修改遥测频率，提高到每间隔30秒50000个遥测数据
步骤2：持续了一段时间之后，突然在master机器上运行systemctl stop postgresql
步骤3：此时应用程序出错，遥测无法上传
步骤4：过60分钟之后，在master机器上运行systemctl start postgresql 启动数据库
步骤5：检查连接，和主备数据库，删除和增加数据，检查是否自动同步
select client_addr,sync_state from pg_stat_replication;
结论：master机器关闭再重启，能够自动与备库建立连接，并且不影响后续使用,但是这里要注意了，如果关闭时间过长，会产生巨量的pg_wal日志，速度很快，一天可能100G+此时不能删除pg_wal日志，还是需要把备库完全起来，等备库完成了数据同步之后，通过
pg_controldata /var/lib/postgresql/11/main命令找到【Latest checkpoint location】，然后通过pg_archivecleanup -d /var/lib/postgresql/11/main/pg_wal 【Latest checkpoint location】来清理日志文件，若备库没有起来，千万不要提前删除，会导致备库数据不同步，只能通过pg_rewind重做备库。在没有出问题的情况下。pg_wal的日志会根据max_wal_size配置进行自动清理
场景5：主库关闭，备库切换成主库，原主库能改成备库吗，程序能继续使用吗？
步骤1：关闭主库systemctl stop postgresql
步骤2：在备库上linux用户切换到postgres，然后添加pg_ctl到环境变量
$ cd ~ $ vim .profile PATH=$PATH:/usr/lib/postgresql/11/bin export PATH $ . ~/.profile
步骤3：在备库上输入pg_ctl promote -D /var/lib/postgresql/11/main。此时会发现/var/lib/postgresql/11/main下的recovery.conf变成了recovery.done
postgres@database-slaver:/home/sfere$ pg_ctl promote -D /var/lib/postgresql/11/main waiting for server to promote.... done server promoted
步骤4：在老的主库上，使用postgres用户登录，使用pg_rewind同步数据
pg_rewind -D /var/lib/postgresql/11/main --source-server='hostaddr=192.168.0.31 port=5432 user=postgres password=postgres'
步骤5：修改应用程序的数据库连接配置到新的主库，继续进行sql insert操作
结论：主库备库切换完成之后，修改应用程序数据库连接池配置，不影响后续使用
