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

---

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 --preserve=timestamps $2 $PGDATA/arch/$1 ; find /arch/ -type f -mtime +7 -exec rm -f {} \;

在/var/lib/postgresql/11/main创建archive目录，赋权给archive和arch.sh

  mkdir /var/lib/postgresql/11/main/archive
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机器关闭再重启，能够自动与备库建立连接，并且不影响后续使用

场景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.done变成了recovery.conf

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

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