postgresql-pgpool-流复制主从同步（一）----主从搭建

原创

流年_火星程序员 2020-06-12 14:26:19 博主文章分类：postgresql ©著作权

©著作权归作者所有：来自51CTO博客作者流年_火星程序员的原创作品，请联系作者获取转载授权，否则将追究法律责任

一主备机器规划

主机名	IP	角色	端口
master	192.168.0.108	Master	5432
slave	192.168.0.109	Slave	5432

二创建流复制 2.1 设置host master,slave两节点都要操作。 [root@bogon ~]# vim /etc/hosts #编辑内容如下： 192.168.43.127 master 192.168.43.243 slave 按esc，wq!保存退出。 2.2 初始化master数据库以下操作在master下执行： #切换到postgres账户 [root@bogon ~]# su - postgres #初始化data [postgres@bogon ~]$ initdb -D $PGDATA

启动master数据库

[postgres@bogon ~]$ pg_ctl start -D $PGDATA #创建流复制用户 [postgres@bogon ~]$ psql psql (9.6.1) Type "help" for help.

postgres=# CREATE USER repuser replication LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD 'repuser'; CREATE ROLE 2.3 配置pg_hba.conf 在master的pg_hba.conf最后一行增加如下: host all all 0.0.0.0/0 md5 host replication repuser slave md5 2.4 配置postgresql.conf 在master端配置如下: listen_addresses = '*' port = 5432 max_wal_senders = 1 wal_level = replica archive_mode = on archive_command = 'cd ./' hot_standby = on wal_keep_segments = 64 full_page_writes = on wal_log_hints = on 配置完成后，重启master数据库 [postgres@bogon ~]$ pg_ctl restart -D $PGDATA 2.5 pg_basebackup 创建备库在slave端的postgres账户下执行： #切换到postgres账户 [root@bogon ~]# su - postgres #从主库备份创建备库 [postgres@bogon ~]$ pg_basebackup -D $PGDATA -Fp -Xs -v -P -h master -p 5432 -U repuser transaction log start point: 0/2000060 on timeline 1 pg_basebackup: starting background WAL receiver 22806/22806 kB (100%), 1/1 tablespace
transaction log end point: 0/2000130 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: base backup completed

修改slave中data目录下的pg_hba.conf最后一行修改如下: host all all 0.0.0.0/0 md5 host replication repuser master md5 2.6 配置recovery.conf Master端配置如下： [postgres@bogon ~]$ ls bin data gdal geos include lib proj4 share [postgres@bogon ~]$ cp share/recovery.conf.sample data/recovery.done [postgres@bogon ~]$ vim data/recovery.done #编辑内容如下 recovery_target_timeline = 'latest' standby_mode = on primary_conninfo = 'host=slave port=5432 user=repuser password=repuser' trigger_file = '/home/postgres/data/trigger_file' Salve端配置如下： [postgres@bogon ~]$ ls bin data gdal geos include lib proj4 share [postgres@bogon ~]$ cp share/recovery.conf.sample data/recovery.conf [postgres@bogon ~]$ vim data/recovery.conf #编辑内容如下 recovery_target_timeline = 'latest' standby_mode = on primary_conninfo = 'host=master port=5432 user=repuser password=repuser' trigger_file = '/home/postgres/data/trigger_file' 2.7 配置.pgpass master上配置访问slave参数 [postgres@bogon ~]$ vim .pgpass master:5432:postgres:repuser:repuser slave:5432:postgres:repuser:repuser slave上配置访问master参数 [postgres@bogon ~]$ vim .pgpass slave:5432:postgres:repuser:repuser master:5432:postgres:repuser:repuser 2.8 流复制数据同步测试分别启动master，slave数据库在master上创建一个数据库和临时表 [postgres@bogon data]$ psql psql (9.6.1) Type "help" for help. postgres=# \password #创建数据库密码 #创建测试数据库 postgres=# create database test; CREATE DATABASE postgres=# \c test You are now connected to database "test" as user "postgres". test=# create table tt(id serial not null,name text); CREATE TABLE test=# insert into tt(name) values ('china'); INSERT 0 1 在slave上查询刚才创建的表和数据，判定是否有数据同步 [postgres@bogon data]$ psql psql (9.6.1) Type "help" for help.

postgres=# \c test You are now connected to database "test" as user "postgres". test=# select * from tt; id | name
----+------- 1 | china (1 row) 很明显，从库已经同步了主库的数据，到此可以说PG流复制热备已经创建结束了。以下对流复制做一些简单的应用。三主备切换一般可以通过若干命令查询数据库的主备属性，主数据库是读写的，备数据库是只读的。当主数据库宕机了，可以通过建立触发文件，备数据库将被提升为主数据库，实现一些基本的HA应用。 3.1 查询主备 3.1.1 pg_controldata 主机 [postgres@localhost ~]$ pg_controldata pg_control version number: 960 Catalog version number: 201608131 Database system identifier: 6362107256088627972 Database cluster state: in production 备机 pg_control version number: 960 Catalog version number: 201608131 Database system identifier: 6362107256088627972 Database cluster state: in archive recovery 主机的cluster state是in production，备机的cluster state是in archive recovery。 3.1.2 字典表pg_stat_replication 在主机字典表中是能查到记录，备机中是查询不到的。 postgres=# select pid,application_name,client_addr,client_port,state,sync_state from pg_stat_replication; pid | application_name | client_addr | client_port | state | sync_state -------+------------------+---------------+-------------+-----------+------------ 17131 | walreceiver | 192.168.0.105 | 55734 | streaming | async (1 row)