PostgresSQL 主从搭建

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介:

由于工作需要,最近开始接触各种数据库,并尝试各种数据库产品的高可用方案。

今天分享的是postgresSQL的主从配置,其实还是蛮简单的,跟随本文的步骤,保证能实现PG主从的搭建。


1. 安装环境

1
2
3
4
192.168.0.136 主库
192.168.0.160 从库
PORT: 5432
USR: postgres

2. 主库已经运行一段时间,检查主库的version,保证主从数据库的version相同。

1
2
3
4
5
6
7
# psql  --version
psql (PostgreSQL) 9.4.11
 
# rpm -qa|grep postgres
postgresql94-libs-9.4.11-1PGDG.rhel6.x86_64
postgresql94-server-9.4.11-1PGDG.rhel6.x86_64
postgresql94-9.4.11-1PGDG.rhel6.x86_64

3. 在从库上安装相同版本的软件

检查安装情况,已经安装和primary相同的软件版本

1
2
3
4
# rpm -qa|grep postgres
postgresql94-libs-9.4.11-1PGDG.rhel6.x86_64
postgresql94-server-9.4.11-1PGDG.rhel6.x86_64
postgresql94-9.4.11-1PGDG.rhel6.x86_64

4. 查询主库的数据库位置

1
2
3
4
5
6
7
8
9
10
# su - postgres
$  echo $PGDATA
/var/lib/pgsql/9.4/data
$ cd /var/lib/pgsql/9.4/data
$ ls
base    pg_clog      pg_hba.conf    pg_log      pg_multixact 
pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION  
postgresql.auto.conf  postmaster.opts  global   pg_dynshmem  pg_ident.conf  
  pg_logical  pg_notify     pg_serial    pg_stat       pg_subtrans  
  pg_twophase  pg_xlog     postgresql.conf       postmaster.pid

确认配置文件位置

1
2
3
4
postgres=# show config_file;
   config_file               
-----------------------------------------
  /var/lib/pgsql/9.4/data/postgresql.conf

查看数据文件目录

1
2
3
4
postgres=# show data_directory;
      data_directory      
-------------------------
  /var/lib/pgsql/9.4/data

5. 准备修改主库的参数文件,先查询一下pg_hba.conf已有的参数内容

1
2
3
4
$ cat pg_hba.conf|grep -v  '^#'
local    all              all                                      peer
host     all              all              0.0.0.0/0            trust
host     all              all              ::1/128             ident

6. 在主库的pg_hba.conf中添加

1
2
$ more pg_hba.conf
host    replication     replica     192.168.0.160                 md5

这样,就设置了replica这个用户可以从192.168.0.160 对应的网段进行流复制请求。


7. 在主库给postgres设置密码,登录和备份权限。

1
2
$psql
postgres#  CREATE  ROLE replica login replication encrypted  password  'replica123'

8. 修改postgresql.conf,注意设置下下面几个地方:

1
2
3
4
5
6
7
wal_level = hot_standby       # 这个是设置主为wal的主机
max_wal_senders = 10           # 这个设置了可以最多有几个流复制连接,差不多有几个从,就设置几个
wal_keep_segments = 256        # 设置流复制保留的最多的xlog数目
wal_sender_timeout = 60s       # 设置流复制主机发送数据的超时时间
max_connections = 100           # 这个设置要注意下,从库的max_connections必须要大于主库的
archive_mode =  on
archive_command =  'cp %p /var/lib/pgsql/9.4/archive/%f'

9. 创建对应archive log存放路径

1
mkdir -p /var/lib/pgsql/9.4/archive/

10. 重启主库,让配置生效

1
2
# service postgresql-9.4 start
Starting postgresql-9.6 service:                           [  OK  ]

11. 在从库远程连接主数据库,验证replica用户是否可以正常访问

1
2
  psql -h IP-address -p 5432 dbname usename  
  psql -h 192.168.0.136 -p 5432 postgres replica

12. 然后在主库做一次基础备份(后面的Hot-standby主要使用data目录下文件):

1
2
3
postgres=#  SELECT  pg_start_backup( 'bak20170905' );
$tar czvf /var/lib/pgsql/9.4/backups/backup_data.tar.gz.20170905 /var/lib/pgsql/9.4/data
postgres=#  SELECT  pg_stop_backup();

13. 将备份文件sftp到从库,并解压,替换原有的data目录

1
2
3
4
cd  /var/lib/pgsql/9.4/
mv data data_bk
mv backup_data.tar.gz.20170905 backup_data.tar.gz
tar -xzvf backup_data.tar.gz

14. 删除一些就的身份信息,归档日志文件等

1
2
3
rm -rf data/pg_xlog/
mkdir -p data/pg_xlog/archive_status
rm data/postmaster.pid

15. 查找并拷贝recovery.conf.sample文件到data目录下

1
2
3
4
5
find / - name  recovery.conf.sample
/root/postgresql/postgresql-9.2.20/src/backend/access/transam/recovery.conf.sample 
/usr/pgsql-9.4/share/recovery.conf.sample
复制
cp /usr/pgsql-9.4/share/recovery.conf.sample /var/lib/pgsql/9.4/data/recovery.conf

16. 然后编辑recovery.conf:

1
2
3
4
standby_mode =  on
restore_command =  'cp /var/lib/pgsql/9.4/archive/%f %p'    #这个参数,我还需要确认具体含义
primary_conninfo =  'host=192.168.0.136 port=5432 user=replica password=replica123'                       # 主服务器的信息以及连接的用户,这条信息最重要
recovery_target_timeline =  'latest'

17. 拷贝下面配置文件

1
2
cp  /var/lib/pgsql/9.4/data.bk/postgresql.conf  /var/lib/pgsql/9.4/data/postgresql.conf
cp  /var/lib/pgsql/9.4/data.bk/pg_hba.conf  /var/lib/pgsql/9.4/data/pg_hba.conf

18. 然后编辑pstgresql.conf:

1
hot_standby =  on

19. 启动Hot-Standby:

1
/usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data  --port=5432

20. 验证是否部署成功

在主节点上执行,验证已经成功搭建,说明5.160是从服务器,在接收流,而且是异步流复制。

1
2
3
4
postgres=#  select  client_addr,sync_state  from  pg_stat_replication;
  client_addr | sync_state 
-------------+------------
  192.168.0.160 | async

21. 查询更多数据同步信息:

1
2
3
4
postgres=#  select  usename,application_name,client_addr,client_port,backend_start,backend_xmin,state,sent_location,write_location,flush_location,replay_location,sync_priority,sync_state   from  pg_stat_replication;
  usename | application_name | client_addr | client_port |         backend_start         | backend_xmin |   state   | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state 
---------+------------------+-------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
  replica | walreceiver      | 192.168.0.160 |       39375 | 2017-09-05 17:49:22.512393+08 |              | streaming |         5/1049488     | 5/1049488        | 5/1049488      |   5/1049488       |             0 |          async

22. 此外,还可以分别在主、从节点上运行 ps aux | grep postgres 来查看进程:

主服务器上,可以看到有一个 wal sender 进程,还有archiver进程等

1
2
3
4
5
# ps aux | grep postgres
postgres  1104  0.0  0.1 324652 15120 ?        S    14:26   0:00 /usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data                      
postgres  1111  0.0  0.0 324652  5844 ?        Ss   14:26   0:00 postgres: wal writer process                              
postgres  1113  0.0  0.0 179796  1544 ?        Ss   14:26   0:00 postgres: archiver process    last  was 000000010000000500000000.00000060.backup
postgres  8515  0.0  0.0 325448  3108 ?        Ss   17:49   0:00 postgres: wal sender process replica 192.168.0.160(39375) streaming 5/103A1D0

从服务器上,可以看到 wal receiver 进程,和recovering进程正在恢复archive log

1
2
3
4
$ ps aux | grep postgres
postgres 11508  0.0  0.1 324684 15128 ?        S    17:49   0:00 /usr/pgsql-9.4/bin/postmaster -D /var/lib/pgsql/9.4/data
postgres 11510  0.0  0.0 324796  4336 ?        Ss   17:49   0:00 postgres: startup process   recovering 000000010000000500000001
postgres 11513  0.0  0.0 331892  3700 ?        Ss   17:49   0:00 postgres: wal receiver process   streaming 5/103A1D0

23. 可以在下面路径中,看到从库接收的archive log文件

1
2
3
4
# pwd
/var/lib/pgsql/9.4/data/pg_xlog
# ls
000000010000000500000001  000000010000000500000002  archive_status

至此,PostgreSQL主从流复制安装部署完成。

在主服务器上插入数据或删除数据,在从服务器上能看到相应的变化。从服务器上只能查询,不能插入或删除数据


24. 第12、13、14步骤,可以通过另一种拷贝主库到从库的方法,pg_basebackup命令拷贝文件

1
pg_basebackup -F p  --progress -D /var/lib/pgsql/9.4/data2 -h 192.168.0.136 -p 5432 -U replica --password

进入到/var/lib/pgsql/9.4/data2目录,修改recovery.conf,这个文件可以从pg的安装目录的share文件夹中获取,比如

1
cp /usr/pgsql-9.6/share/recovery.conf.sample /var/lib/pgsql/9.6/data2/recovery.conf

确保文件夹权限是700,这个很关键,其他权限,不能正常启动

1
$ chmod 0700 /var/lib/pgsql/9.6/data2

使用下面命令启动standby

1
$ /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data2










本文转自 hsbxxl 51CTO博客,原文链接:http://blog.51cto.com/hsbxxl/1963039,如需转载请自行联系原作者

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
4月前
|
负载均衡 关系型数据库 MySQL
MySQL主从架构的搭建
MySQL主从架构的搭建
57 3
|
SQL 关系型数据库 MySQL
MySQL集群-主从服务器搭建(一主多从)
MySQL集群-主从服务器搭建(一主多从)
440 0
MySQL集群-主从服务器搭建(一主多从)
|
5月前
|
SQL 算法 关系型数据库
微服务轮子项目(27) -MySQL 主从复制、主从切换、主主复制
微服务轮子项目(27) -MySQL 主从复制、主从切换、主主复制
51 0
|
7月前
|
存储 关系型数据库 MySQL
Mysql主从复制搭建
Mysql主从复制搭建
45 0
|
7月前
|
监控 关系型数据库 MySQL
MySQL 数据库复制与集群技术及实战演练(一)
MySQL 数据库复制与集群技术及实战演练(一)
|
SQL 负载均衡 关系型数据库
MySql主从复制的部署实现
🍅程序员小王的博客:程序员小王的博客 🍅 欢迎点赞 👍 收藏 ⭐留言 📝 🍅 如有编辑错误联系作者,如果有比较好的文章欢迎分享给我,我会取其精华去其糟粕 🍅java自学的学习路线:java自学的学习路线 🍅参考文献:mysql官网
197 0
MySql主从复制的部署实现
|
负载均衡 关系型数据库 MySQL
关于mysql集群主从服务器搭建
关于mysql集群主从服务器搭建
197 0
关于mysql集群主从服务器搭建
|
SQL 负载均衡 网络协议
关于Linux下Mysql集群同步(主从、一主多从、主从从)部署及同步策略的一些笔记
和小伙们分享一些Mysql集群主从同步部署相关的笔记 博文内容涉及: 为什么需要mysql集群主从同步 主从同步原理 部署不同主从结构的Mysql集群 一主一从 一主多从 主从从 主从同步使用的复制模式介绍配置 食用方式:了解Linux、Mysql即可 理解不足小伙伴帮忙指正
653 0
关于Linux下Mysql集群同步(主从、一主多从、主从从)部署及同步策略的一些笔记
|
关系型数据库 MySQL
MySQL主从复制搭建
MySQL主从复制搭建
149 0