timescaleDB双机热备流复制与测试

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操作

结论:主库备库切换完成之后,修改应用程序数据库连接池配置,不影响后续使用


苏ICP备18047533号-1