[实时数仓]玩转PostgreSQL主从流复制
实时postgresql 复制 玩转 主从 数仓
2023-06-13 09:17:36 时间
PostgreSQL 在 9.0 以后引入了流复制(Streaming Replication)。流复制提供了将 WAL 记录连续发送并应用到从服务器以使其保持最新状态的功能。通过流复制,从服务器不断从主服务器同步相应的数据,同时,从服务器作为主服务器的一个备份。
本文主要记录 PostgreSQL 主从流复制的部署。
服务器规划
角色 | 地址 | 版本 |
---|---|---|
主服务器 | 172.31.5.1 | Ubuntu 18.04,PostgreSQL 10 |
从服务器 | 172.31.5.2 | Ubuntu 18.04,PostgreSQL 10 |
PostgreSQL 安装
# Add PostgresSQL Repository to Ubuntu
sudo sh -c "echo 'deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -c -s`-pgdg main' >> /etc/apt/sources.list.d/pgdg.list"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# Update and Install PostgreSQL
sudo apt-get update
sudo apt-get install -y postgresql-10
复制代码
主服务器配置
进入数据库
sudo -u postgres psql
创建 replicator
用户用于登录和复制
create role replicator login replication encrypted password 'password';
配置 pg_hba.conf
sudo vim /etc/postgresql/10/main/pg_hba.conf
添加以下内容
host all all 172.31.0.0/16 trust # 允许连接
host replication replicator 172.31.0.0/16 trust # 允许replicator用户复制本机数据
配置 postgrsql.conf
sudo vim /etc/postgresql/10/main/postgresql.conf
按照以下内容配置
listen_addresses = '*' #监听所有ip
archive_mode = on #开启归档模式
archive_command = 'cp %p /var/lib/postgresql/10/main/%f' #归档命令
wal_level = replica # 决定多少信息写入WAL,此处为replica模式
max_wal_senders = 10 #最大流复制连接,一般和从服务相等
wal_sender_timeout = 60s #流复制超时时间
max_connections = 100 #最大连接数,必须不大于从库的配置
重启数据库
sudo service postgresql restart
从服务器配置
先测试一下能否连接主服务器
psql -h 172.31.5.1 -U postgres
配置 postgresql.conf
wal_level = replica #决定多少信息写入WAL,此处为replica模式
max_connections = 300 #最大连接数,必须不小于主库的配置
hot_standby = on #说明这台机器不仅用于数据归档,还可以用于数据查询
max_standby_streaming_delay = 30s #流备份的最大延迟时间
wal_receiver_status_interval = 10s #向主服务器汇报本机状态的间隔时间
hot_standby_feedback = on #是否向主服务器反馈错误的数据复制
首先清空 PostgreSQL 数据
sudo su - postgres # 切换到postgresl用户
rm -rf 10/main/* # 清空data目录数据
然后备份主服务器数据
pg_basebackup -D 10/main/ -h 172.31.5.1 -U replicator -X stream -P
配置 recovery.conf
vim 10/main/recovery.conf
standby_mode = on # 说明该节点是从服务器
primary_conninfo = 'host=172.31.5.1 port=5432 user=replicator password=password' # 主服务器的连接信息
recovery_target_timeline = 'latest'
退出用户,然后重启数据库
sudo service postgresql restart
验证部署
在主服务上执行,结果如下:
postgres=# select client_addr,sync_state from pg_stat_replication;
client_addr | sync_state
--------------+------------
172.31.5.2 | async
(1 row)
我们也可以在两台服务器上执行 ps aux | grep postgres
来验证部署成功,可以看到
主服务器上有一个 wal sender process
从服务器上有一个 wal receiver process
测试
主服务器创建数据库:
postgres=# create database test;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
(4 rows)
此时从服务器可以同步看到
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
(4 rows)
如果我们在从服务器上执行:
postgres=# drop database test;
ERROR: cannot execute DROP DATABASE in a read-only transaction
可以看到无法在从服务器上执行写操作,因为从服务器是只读的。
这样我们就完成了主从流复制的部署和验证测试。
相关文章
- 基于Flink+Hudi在兴盛优选营销域实时数仓的实践
- POSTGRESQL postgresql 升级的需求来自哪里
- Linux服务监控:实时把握系统运行状况(linux服务监控)
- 名称:Oracle表实时监控系统(oracle表监控)
- Oracle远程容灾:保障应用实时高可用(oracle远程容灾)
- 历程PostgreSQL: 从历史足迹到发展潮流(postgresql发展)
- 创建PostgreSQL数据库:一步一步操作指南(postgresql创建数据库)
- 引擎使用PostgreSQL实现规则引擎功能(postgresql规则)
- 安装及使用PostgreSQL数据库安装与应用指南(postgresql数据库)
- 大全PostgreSQL命令指南:开发者必备技能(postgresql命令)
- 基于Zabbix的PostgreSQL数据库监控(zabbix监控postgresql)
- 优势PostgreSQL归档:优势及其应用(postgresql归档)
- MySQL实时同步: 原理及应用(mysql实时同步原理)
- 解决PostgreSQL乱码问题(postgresql乱码)
- 数据的实时同步介绍
- PostgreSQL注入攻击:防范你的数据库(postgresql注入)
- 深入浅出:PostgreSQL编程指南(postgresql编程)
- 数据库PostgreSQL:开放源码的分布式数据库管理系统(postgresql开源)
- Linux文件变化监控:实时审视文件的状态(linux文件变化监控)
- 基于密码认证的PostgreSQL数据库安全设置技巧(postgresql认证)
- 深入解析PostgreSQL扩展:优化数据库性能与拓展功能(postgresql扩展)
- 使用 Redis 实现实时在线人数统计(redis在线人数统计)
- 深入Linux下查看实时流量情况(linux查看流量进程)
- MySQL技术获取实时分钟级数据解析(mysql得到分钟)
- 深入探索PostgreSQL开发技术(postgresql开发)
- PostgreSQL精彩模式:构建最高效率的数据库系统(postgresql模式)
- 管理PostgreSQL:优化内存管理提升数据库性能(postgresql内存)
- 放入Redis中的Hash值实时存取(往redis里放hash)
- 以毫秒计测从Redis中实时读取(每秒读取一次redis)