欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

PostgrelSql9.4hotstandby配置

程序员文章站 2022-04-26 11:12:33
----配置同步流复制 **************************************************** 1.在主库增加同步的用户名与密码 *...
----配置同步流复制

****************************************************
1.在主库增加同步的用户名与密码
****************************************************

$ psql -h 192.168.50.110  -d postgres -U postgres 
Password for user postgres: 
psql (9.4.4)
Type "help" for help.

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

postgres=# \c postgres
You are now connected to database "postgres" as user "postgres".

 
postgres=# SELECT rolname from pg_roles ;
 rolname  
----------
 postgres
 repluser
(2 rows)

postgres=# SELECT usename from pg_user; 
 usename  
----------
 postgres
 repluser
(2 rows)


postgres=# CREATE ROLE repluser REPLICATION LOGIN PASSWORD '123';
CREATE ROLE

postgres=#

****************************************************
2.新建测试数据库
****************************************************

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

postgres=# create database wind;
CREATE DATABASE
postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 wind      | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
(4 rows)

postgres=# \c wind;
You are now connected to database "wind" as user "postgres".
wind=# \d
No relations found.
wind=# create table t1(sid int not null primary key,sname varchar(10));
CREATE TABLE
wind=# insert into t1 values(101,'ocpyang');
INSERT 0 1
wind=# insert into t1 values(102,'yzw');
INSERT 0 1
wind=# select * from t1;
 sid |  sname  
-----+---------
 101 | ocpyang
 102 | yzw
(2 rows)



****************************************************
3.在主库pg01上进行配置
****************************************************

--3.1
$ vi /data/pg/data/pg_hba.conf
增加以下:
host    replication     repluser        192.168.50.0/24           md5


--3.2 在主库pg01的/data/pg/data/postgresql.conf中设置如下三个参数
$ vi /data/pg/data/postgresql.conf
 
59  listen_addresses = '*'
175 wal_level = hot_standby
224 max_wal_senders = 5 
226 wal_keep_segments = 64  




--3.3 在主数据上指定同步复制的Standby名称,在主库pg01的/data/pg/data/postgresql.conf中设置:
vi /data/pg/data/postgresql.conf
 
236 synchronous_standby_names = 'pg02'


--3.4 修改上述参数后需要重启数据库.

pg_ctl -m "immediate" restart


****************************************************
4.在备库pg02上进行配置
****************************************************

$pg_ctl -m "immediate" stop 

-----4.1 备份数据

$rm -rf /data/pg/data/*

$pg_basebackup -h 192.168.50.110 -U repluser -F p -P -x -R -D /data/pg/data/ -l backup20150921
Password: 
54491/54491 kB (100%), 1/1 tablespace

--4.2 修改备库的pg02的recovery.conf 

$ cat /data/pg/data/recovery.conf 
standby_mode = 'on'
primary_conninfo = 'user=repluser password=123 host=192.168.50.110 port=5432 sslmode=disable sslcompression=1'


$vi /data/pg/data/recovery.conf 

#增加application_name,这个参数和主库中synchronous_standby_names指定的对应值一样.
standby_mode = 'on'
primary_conninfo = 'application_name=pg02 user=repluser password=123 host=192.168.50.110 port=5432 sslmode=disable sslcompression=1'
recovery_target_timeline = 'latest' # 这个说明这个流复制同步到最新的数据


---4.3 修改备库的pg02的postgresql.conf 

vi /data/pg/data/postgresql.conf 

64 max_connections = 1000 # 一般查多于写的应用从库的最大连接数要比较大
245 hot_standby = on 
255 hot_standby_feedback = on  


---4.4 备库pg02启动

pg_ctl  start


$ cat  /data/pg/data/pg_log/pg01-2015-09-21_151458.log 
LOG:  database system was shut down in recovery at 2015-09-21 15:14:29 CST
LOG:  entering standby mode
LOG:  redo starts at 0/30002E8
LOG:  consistent recovery state reached at 0/30003C0
LOG:  database system is ready to accept read only connections
LOG:  record with zero length at 0/30003C0
LOG:  started streaming WAL from primary at 0/3000000 on timeline 1


****************************************************
5.复制监控
****************************************************

select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;

 pid  |   state   |  client_addr   | sync_priority | sync_state 
-------+-----------+----------------+---------------+------------
 65392 | streaming | 192.168.50.120 |             1 | sync
(1 row)


+-----+----------+----------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------+
| pid | usesysid | usename  | application_name | client_addr    | client_hostname | client_port | backend_start                 | backend_xmin | state     | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state |
+-----+----------+----------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------+
| 632 | 16384    | repluser | pg02             | 192.168.50.120 | NULL            |       36746 | 2015-09-21 17:17:41.730857+08 | 1826         | streaming | 0/3002718     | 0/3002718      | 0/3002718      | 0/3002718       |             1 | sync       |
+-----+----------+----------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------+
1 row in set




pid              | 8467       # sender的进程
usesysid         | 44673      # 复制的用户id
usename          | replica    # 复制的用户用户名
application_name | walreceiver  
client_addr      | 10.12.12.12 # 复制的客户端地址
client_hostname  |
client_port      | 55804  # 复制的客户端端口
backend_start    | 2015-05-12 07:31:16.972157+08  # 这个主从搭建的时间
backend_xmin     |
state            | streaming  # 同步状态 startup: 连接中、catchup: 同步中、streaming: 同步
sent_location    | 3/CF123560 # Master传送WAL的位置
write_location   | 3/CF123560 # Slave接收WAL的位置
flush_location   | 3/CF123560 # Slave同步到磁盘的WAL位置
replay_location  | 3/CF123560 # Slave同步到数据库的WAL位置
sync_priority    | 0  #同步Replication的优先度
                      0: 异步、1~?: 同步(数字越小优先度越高)
sync_state       | async  # 有三个值,async: 异步、sync: 同步、potential: 虽然现在是异步模式,但是有可能升级到同步模式