postgres 流复制集群配置（一）

一，环境准备

 

	主机名	ip
master	kafka01	192.168.0.188
slave	pg_standby	192.168.0.187

 

　　　　以及两主机里面安装好postgres软件

二，环境操作

　　01，hosts文件添加解析(主备)

 

 

 　　02，创建集群使用的环境用户(主)

[postgres@kafka01 ~]$ psql
psql (10.12)
Type "help" for help.

postgres=# CREATE USER replica replication LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD '123456';
CREATE ROLE

　　03，配置pg_hba.conf(主)

 

 

 

 　　04，配置postgressql.conf(主)

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

　　05,启动主库，备库拉取数据(主备)

--启动主库
[postgres@kafka01 psql]$ cat stop.sh
PGPORT=5432
PGDATA=/data/pgdata
/data/postgres/bin/pg_ctl stop -D ${PGDATA} -s -m fast

[postgres@kafka01 psql]$ ./stop.sh

[postgres@kafka01 psql]$ cat start.sh
PGPORT=5432
PGDATA=/data/pgdata
/data/postgres/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300 -l /tmp/logfile

[postgres@kafka01 psql]$ ./start.sh

--备库拉取数据
[postgres@pg_standby data]$ pg_basebackup -h 192.168.0.188 -U replica -F p -X stream -P -R -D /data/pgdata/ -l back20200609 -w
4565171/4565171 kB (100%), 2/2 tablespaces


--注意拉取的时候注意假如存在目录有数据的话会报错，清理即可

　　查看slave节点的pg_hba.conf 看是否正确

　　06，配置recovery.conf (主备)

　　这个文件在安装目录下面的share，

---主库配置
[postgres@kafka01 pgdata]$ cp ../postgres/share/recovery.conf.sample recovery.done
[postgres@kafka01 pgdata]$ vim recovery.done
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=pg_standby port=5432 user=replica password=123456'
trigger_file = '/data/trigger_file'

---备库配置
[postgres@pg_standby pgdata]$ cp ../postgres/share/recovery.conf.sample recovery.done
[postgres@pg_standby pgdata]$ vim recovery.done

recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=master port=5432 user=repuser password=repuser'
trigger_file = '/home/postgres/data/trigger_file'

　　　　07,密码文件配置（主备）

--主
[postgres@kafka01 data]$ cat ~/.pgpass
pg_standby:5432:postgres:replica:12345
[postgres@pg_standby pgdata]$ chmod 0600 ~/.pgpass
--备

[postgres@pg_standby pgdata]$ cat ~/.pgpass
kafka01:5432:postgres:replica:123456

[postgres@pg_standby pgdata]$ chmod 0600 ~/.pgpass

　　08，启动，同步测试

---分别启动主库，然后启动备库

--主库测试

[postgres@kafka01 pgdata]$ psql -d kingledb -U kingle
psql (10.12)
Type "help" for help.

kingledb=> create table tmp01 (id int);
CREATE TABLE
kingledb=> insert into tmp01 values(1);
INSERT 0 1
kingledb=> select * from tmp01
kingledb-> ;
 id
----
  1
(1 row)

kingledb=>


--备库查询



[postgres@pg_standby psql]$ psql -d kingledb -U kingle
psql (10.12)
Type "help" for help.

kingledb=>  select * from tmp01;
 id
----
  1
(1 row)

kingledb=>



--完美-