timescaleDB双机热备流复制与测试
背景
最近有项目要用到热备功能,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操作
结论:主库备库切换完成之后,修改应用程序数据库连接池配置,不影响后续使用