postgresql 高可用 etcd + patroni 之二 patroni

os: centos 7.4
postgresql: 9.6.9
etcd: 3.2.18
patroni: 1.4.4

patroni + etcd 是在一個postgrsql 開源大會上 亞信的一個哥們講解的高可用方案。
依然是基于 postgreql stream replication。

ip規劃
192.168.56.101 node1 master
192.168.56.102 node2 slave
192.168.56.103 node3 slave

安裝postgresql并配置好stream

node1、node2、node3 節點上注意設置如下幾個參數

synchronous_commit = on
full_page_writes = on
wal_log_hints = on
synchronous_standby_names = '*'
max_replication_slots = 10

主要是為了使用 pg_rewind,盡量不用 synchronous 方式復制數據，性能影響太大。

node1上創建復制槽，至關重要，patroni 用到了這個玩意

postgres=# create user replicator replication login encrypted password '1qaz2wsx';postgres=# select * from pg_create_physical_replication_slot('pgsql96_node1');
postgres=# select * from pg_create_physical_replication_slot('pgsql96_node2');
postgres=# select * from pg_create_physical_replication_slot('pgsql96_node3');

node2、node3 配置stream replication

$ /usr/pgsql-9.6/bin/pg_ctl stop -m fast -D /var/lib/pgsql/9.6/main$ cd /var/lib/pgsql/9.6/main
$ rm -rf ./*
$ /usr/pgsql-9.6/bin/pg_basebackup -h 192.168.56.101 -D /var/lib/pgsql/9.6/main -U replicator -v -P -R$ vi recovery.conf
recovery_target_timeline = 'latest'
standby_mode = 'on'
primary_conninfo = 'host=192.168.56.101 port=5432 user=replicator password=1qaz2wsx'
primary_slot_name = 'pgsql96_node1'
trigger_file = '/tmp/postgresql.trigger.5432'$ /usr/pgsql-9.6/bin/pg_ctl start -D /var/lib/pgsql/9.6/main -o "-c config_file=/etc/postgresql/9.6/main/postgresql.conf"

注意 recovery.conf 的 primary_slot_name 在不同節點值會不同。

添加復制功能條目 pg_hba.conf

$ vi pg_hba.conf# Database administrative login by Unix domain socket
local   all             postgres                                peer# TYPE  DATABASE        USER            ADDRESS                 METHOD# "local" is for Unix domain socket connections only
local   all             all                                     peer# IPv4 local connections:
host    all             postgres        127.0.0.1/32            trust
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.56.0/24         md5# IPv6 local connections:
host    all             all             ::1/128                 md5# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     replicator                              peer
host    replication     replicator      127.0.0.1/32            md5
host    replication     replicator      ::1/128                 md5host	replication	    replicator	    192.168.56.101/32	         md5
host	replication	    replicator	    192.168.56.102/32	         md5
host	replication	    replicator	    192.168.56.103/32	         md5$ psql -c "select pg_reload_conf();"

查看復制狀態

postgres=#  select client_addr,pg_xlog_location_diff(sent_location, write_location) as write_delay,pg_xlog_location_diff(sent_location, flush_location) as flush_delay,pg_xlog_location_diff(sent_location, replay_location) as replay_delay from pg_stat_replication;client_addr   | write_delay | flush_delay | replay_delay 
----------------+-------------+-------------+--------------192.168.56.102 |           0 |           0 |            0192.168.56.103 |           0 |           0 |            0
(2 row)

安裝etcd

參考上一篇blog安裝好etcd

下載、安裝 patroni

用戶也可以參考 https://www.linode.com/docs/databases/postgresql/create-a-highly-available-postgresql-cluster-using-patroni-and-haproxy/

個人覺得上篇文章中 etcd 做成單點不太合適，當然作為參考完全沒有問題。

# cd /tmp
# curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py
# python get-pip.py
# pip install patroni[dependencies]

patroni的一些依賴

urllib3>=1.19.1,!=1.21
boto
psycopg2>=2.5.4
PyYAML
requests
six>=1.7
kazoo>=1.3.1
python-etcd>=0.4.3,<0.5
python-consul>=0.7.0
click>=4.1
prettytable>=0.7
tzlocal
python-dateutil
psutil
cdiff
kubernetes>=2.0.0,<=6.0.0,!=4.0.*,!=5.0.*

patroni 的配置

# which patroni
/usr/bin/patroni# patroni --help
/usr/lib64/python2.7/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.""")
Usage: /usr/bin/patroni config.ymlPatroni may also read the configuration from the PATRONI_CONFIGURATION environment variable

錯誤提示 please use “pip install psycopg2-binary” instead

# pip install psycopg2-binary

patroni 配置文件

# mkdir -p /usr/patroni/conf
# cd /usr/patroni/conf/# vi patroni_postgresql.ymlscope: pgsql96
namespace: /pgsql/
name: pgsql96_node1restapi:listen: 192.168.56.101:8008connect_address: 192.168.56.101:8008etcd:host: 192.168.56.101:2379bootstrap:# this section will be written into Etcd:/<namespace>/<scope>/config after initializing new cluster# and all other cluster members will use it as a `global configuration`dcs:ttl: 30loop_wait: 10retry_timeout: 10maximum_lag_on_failover: 1048576master_start_timeout: 300
#    synchronous_mode: falsepostgresql:use_pg_rewind: trueuse_slots: trueparameters:listen_addresses: "*"port: 5432wal_level: logicalhot_standby: "on"wal_keep_segments: 1000max_wal_senders: 10max_replication_slots: 10wal_log_hints: "on"
#        archive_mode: "on"
#        archive_timeout: 1800s
#        archive_command: gzip < %p > /data/backup/pgwalarchive/%f.gz
#      recovery_conf:
#        restore_command: gunzip < /data/backup/pgwalarchive/%f.gz > %ppostgresql:listen: 0.0.0.0:5432connect_address: 192.168.56.101:5432data_dir: /var/lib/pgsql/9.6/databin_dir: /usr/pgsql-9.6/bin
#  config_dir:authentication:replication:username: replicatorpassword: 1qaz2wsxsuperuser:username: postgrespassword: 1qaz2wsx#watchdog:
#  mode: automatic # Allowed values: off, automatic, required
#  device: /dev/watchdog
#  safety_margin: 5tags:nofailover: falsenoloadbalance: falseclonefrom: falsenosync: false

上面的配置和后面的輸出信息有細微差異，是因為當時實驗完成后又對這個配置文件做了好幾次修正，方便大家直接copy使用。

手動啟動 patroni

參數將按以下順序應用(運行時被賦予最高優先級)：

1、從文件加載參數postgresql.base.conf(或從自定義conf文件(如果已設置)
2、從文件加載參數postgresql.conf
3、從文件加載參數postgresql.auto.conf
4、運行時參數使用-o-name=value

node1、node2、node3 三個節點依次啟動

$ patroni /usr/patroni/conf/patroni_postgresql.yml

node1 的日志如下

2018-07-11 18:17:22,402 INFO: Lock owner: pg96_101; I am pg96_101
2018-07-11 18:17:22,430 INFO: no action.  i am the leader with the lock
2018-07-11 18:17:32,403 INFO: Lock owner: pg96_101; I am pg96_101
2018-07-11 18:17:32,432 INFO: no action.  i am the leader with the lock

node2 的日志如下

2018-07-11 18:17:22,421 INFO: Lock owner: pg96_101; I am pg96_102
2018-07-11 18:17:22,421 INFO: does not have lock
2018-07-11 18:17:22,435 INFO: no action.  i am a secondary and i am following a leader
2018-07-11 18:17:32,426 INFO: Lock owner: pg96_101; I am pg96_102
2018-07-11 18:17:32,426 INFO: does not have lock
2018-07-11 18:17:32,436 INFO: no action.  i am a secondary and i am following a leader

node3 的日志如下

2018-07-11 18:17:22,409 INFO: Lock owner: pg96_101; I am pg96_103
2018-07-11 18:17:22,410 INFO: does not have lock
2018-07-11 18:17:22,423 INFO: no action.  i am a secondary and i am following a leader
2018-07-11 18:17:32,415 INFO: Lock owner: pg96_101; I am pg96_103
2018-07-11 18:17:32,415 INFO: does not have lock
2018-07-11 18:17:32,425 INFO: no action.  i am a secondary and i am following a leader

查看集群狀態

查看 patroni 集群狀態

$ patronictl -c /usr/patroni/conf/patroni_postgresql.yml list pg96
+---------+----------+----------------+--------+---------+-----------+
| Cluster |  Member  |      Host      |  Role  |  State  | Lag in MB |
+---------+----------+----------------+--------+---------+-----------+
|   pg96  | pg96_101 | 192.168.56.101 | Leader | running |       0.0 |
|   pg96  | pg96_102 | 192.168.56.102 |        | running |       0.0 |
|   pg96  | pg96_103 | 192.168.56.103 |        | running |       0.0 |
+---------+----------+----------------+--------+---------+-----------+$ patronictl -c /usr/patroni/conf/patroni_postgresql.yml show-config pg96
loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:parameters:listen_addresses: '*'port: 5432use_pg_rewind: true
retry_timeout: 10
ttl: 30

查看 etcd 的 信息

$ etcdctl ls /pg96/pg96/
/pg96/pg96/members
/pg96/pg96/initialize
/pg96/pg96/leader
/pg96/pg96/config
/pg96/pg96/optime

$ etcdctl get /pg96/pg96/members/pg96_101
{"conn_url":"postgres://192.168.56.101:5432/postgres","api_url":"http://127.0.0.1:8008/patroni","timeline":1,"state":"running","role":"master","xlog_location":50378640}
$ etcdctl get /pg96/pg96/members/pg96_102
{"conn_url":"postgres://192.168.56.102:5432/postgres","api_url":"http://127.0.0.1:8008/patroni","timeline":1,"state":"running","role":"replica","xlog_location":50378640}
$ etcdctl get /pg96/pg96/members/pg96_103
{"conn_url":"postgres://192.168.56.103:5432/postgres","api_url":"http://127.0.0.1:8008/patroni","timeline":1,"state":"running","role":"replica","xlog_location":50378640}

$ etcdctl get /pg96/pg96/initialize
6576484813966394513

$ etcdctl get /pg96/pg96/leader
pg96_101

$ etcdctl get /pg96/pg96/config
{"ttl":30,"maximum_lag_on_failover":1048576,"retry_timeout":10,"postgresql":{"use_pg_rewind":true,"parameters":{"listen_addresses":"*","port":5432}},"loop_wait":10}
$ etcdctl get /pg96/pg96/optime/leader
50378640

connction

using jdbc:

jdbc:postgresql://node1,node2,node3/postgres?targetServerType=master

libpq starting from postgresql 10:

postgresql://node1:port,node2:port,node3:port/?target_session_attrs=read-write

配置隨OS啟動

# vi /etc/rc.local
su - postgres -c "/usr/bin/patroni /usr/patroni/conf/patroni_postgresql.yml >> /var/log/postgresql/patroni.log  2>&1 &"

或者配置成 patroni.service

# vi /etc/systemd/system/patroni.service[Unit]
Description=Runners to orchestrate a high-availability PostgreSQL
After=syslog.target network.target etcd.target[Service]
Type=simpleUser=postgres
Group=postgresExecStart=/usr/bin/patroni /usr/patroni/conf/patroni_postgresql.ymlKillMode=processTimeoutSec=30Restart=no[Install]
WantedBy=multi-user.targ

# systemctl status patroni
# systemctl start patroni
# systemctl enable patroni# systemctl status postgresql
# systemctl disable postgresql# systemctl status etcd
# systemctl enable etcd

禁止 postgresql 的自啟動，通過 patroni 來管理 postgresql。

總結：
個人感覺 etcd + patroni 還是相當不錯的，會繼續對patroni 研究下。

參考：
https://github.com/zalando/patroni
https://patroni.readthedocs.io/en/latest/
https://pypi.org/project/patroni/

https://github.com/zalando/patroni/blob/master/docs/replication_modes.rst
https://postgresconf.org/system/events/document/000/000/228/Patroni_tutorial_4x3-2.pdf

轉載于:https://www.cnblogs.com/ctypyb2002/p/9792939.html

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/249972.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/249972.shtml
英文地址，請注明出處：http://en.pswp.cn/news/249972.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

vue對象偵測

vue對象偵測

http://blog.csdn.net/yihanzhi/article/details/74200618 數組&#xff1a;this.$set(this.arr,index,value) 轉載于:https://www.cnblogs.com/smzd/p/8390626.html
閱讀更多...
Laravel 5.4 migrate時報錯: Specified key was too long error

Laravel 5.4 migrate時報錯: Specified key was too long error

Laravel 5.4默認使用utf8mb4字符編碼&#xff0c;而不是之前的utf8編碼。因此運行php artisan migrate 會出現如下錯誤&#xff1a; [Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key leng…
閱讀更多...
springboot工具類

springboot工具類

ClassPathResource 在類路徑下讀取資源 public final String getPath() public boolean exists() public InputStream getInputStream() WebUtils 獲取web資源工具類 public static String getRealPath(ServletContext servletContext, String path) public static Object g…
閱讀更多...
MySQL中事物的詳解

MySQL中事物的詳解

1. 事物的定義及特性 事務是一組操作數據庫的SQL語句組成的工作單元&#xff0c;該工作單元中所有操作要么同時成功&#xff0c;要么同時失敗。事物有如下四個特性&#xff0c;ACID簡稱“酸性”。 1&#xff09;原子性&#xff1a;工作單元中所有的操作要么都成功&#xff0c;要…
閱讀更多...
記了老是忘記那就寫下來吧宏任務微任務

記了老是忘記那就寫下來吧宏任務微任務

宏任務&#xff1a;script 定時器 微任務&#xff1a;promiss process.nexttick new Promise(function(resolve){console.log(3);//此為同步程序resolve();//同步 是否異步 由內部函數決定console.log(4); }).then(function(){ //。then 異步console.log(5); });async function…
閱讀更多...
SPRING自定義注入CONTROLLER變量

SPRING自定義注入CONTROLLER變量

問題描述 在SpringMVC中默認可以注入Model&#xff0c;ModelAndView&#xff0c;RequestParam&#xff0c;PathVariable 等&#xff0c;那么這個是怎么實現的&#xff0c;以及怎么注入一個自定義的參數呢 HandlerMethodArgumentResolver 在SpringMVC中有一個接口HandlerMethod…
閱讀更多...
進程，線程

進程，線程

import os, timeif __name__ __main__:print(the calling process id:%d % os.getpid())# 創建進程pid os.fork()if pid 0:# 子進程print(the child pid is %d % os.getpid())time.sleep(3)elif pid > 0:# 父進程os.wait() # 等待子進程終止print([%d]bye-bye % os.getpi…
閱讀更多...
livebos--iframe使用

livebos--iframe使用

新建一個方法。建一個參數&#xff0c;iframe控件&#xff0c;虛擬列。然后使用以下信息 <% livebos languagejavascript %>var url LB_ObjURI("Lb_lbOrganization",0,[],["NoTitle"]);var v {"edit" : "url ", "view"…
閱讀更多...
單行溢出 和多行溢出

單行溢出 和多行溢出

/*單行溢出*/.one_txt_cut{overflow: hidden;white-space: nowrap;text-overflow: ellipsis;}.txt_cut{overflow : hidden;text-overflow: ellipsis;display: -webkit-box;-webkit-line-clamp: 2;-webkit-box-orient: vertical;}轉載于:https://www.cnblogs.com/smzd/p/8491583…
閱讀更多...
Spring方法注入 @Lookup注解使用

Spring方法注入 @Lookup注解使用

情景分析 在Spring的諸多應用場景中bean都是單例形式&#xff0c;當一個單利bean需要和一個非單利bean組合使用或者一個非單利bean和另一個非單利bean組合使用時&#xff0c;我們通常都是將依賴以屬性的方式放到bean中來引用&#xff0c;然后以Autowired來標記需要注入的屬性。…
閱讀更多...
Jupyter配置步驟

Jupyter配置步驟

Jupyter是基于瀏覽器的可交互式開發工具&#xff0c;在數據科學界非常受歡迎&#xff0c;它功能齊全&#xff0c;使用方便&#xff0c;是一款數據分析和建模挖掘的利器。 本文簡介Jupyter的配置和使用過程 一、修改添加國內鏡像 通常我會先安裝Anaconda&#xff0c;再安裝Jupyt…
閱讀更多...
edittext 屬性

edittext 屬性

1.去掉edittext的底線&#xff0c;設置&#xff0c;不管是edittext&#xff0c;還是appcompatEdittext都是這個屬性 轉載于:https://www.cnblogs.com/hechangshou/p/9301004.html
閱讀更多...
定義高亮顏色

定義高亮顏色

/*怎么定義高亮的顏色*/-webkit-tap-highlight-color: transparent;/*透明 其實就是不顯示顏色*/-webkit-tap-highlight-color: red; 轉載于:https://www.cnblogs.com/smzd/p/8491587.html
閱讀更多...
springboot 配置webservice接口

springboot 配置webservice接口

導入依賴的jar <!-- webservice cxf --><dependency><groupId>org.apache.cxf</groupId><artifactId>cxf-rt-frontend-jaxws</artifactId><version>3.1.6</version></dependency><dependency><groupId>org…
閱讀更多...
【Django】認證系統

【Django】認證系統

目錄 #. auth模塊1. 認證 authenticate()2. 登陸 login(HttpRequest, user)3. 注銷 logout(request)4. 認證判斷 is_authenticated()5. 登陸校驗 login_requierd()6. 創建普通用戶 create_user()7. 創建超級用戶 create_superuser()8. 密碼校驗 check_password(password)9. 修改…
閱讀更多...
學習的目的是什么？

學習的目的是什么？

學習的目的是為了掌握生存的常識和技能&#xff0c;以便獨立地面對世界&#xff1b; 學習的目的是為了遵從生活的規范和律則&#xff0c;以便和諧地與人相處&#xff1b; 學習的目的是為了探索生命的價值和意義&#xff0c;以便有尊嚴地立于天地之間。 你覺得為什么要學習呢&am…
閱讀更多...
span里面插入文字

span里面插入文字

.text-box span::before{ content:attr(data-text);} 轉載于:https://www.cnblogs.com/smzd/p/8491664.html
閱讀更多...
Spring Boot 動態注入的兩種方式

Spring Boot 動態注入的兩種方式

通過Profilespring.profiles.active spring.profiles.active&#xff1a;官方解釋是激活不同環境下的配置文件&#xff0c;但是實際測試發現沒有對應的配置文件也是可以正常執行的。那就可以把這個key當作一個參數來使用 Profile&#xff1a;spring.profiles.active中激活某配…
閱讀更多...
kernel devel 安裝與卸載

kernel devel 安裝與卸載

1、查看系統內核 uname -r 2、查看已安裝kernel-devel uname -a ; rpm -qa kernel\* | sort 3、下載對應的rpm wget xxx/kernel-devel-2.6.32-754.el6.x86_64.rpm 或者 $ sudo yum install "kernel-devel-uname-r $(uname -r)" 4、卸載已安裝的內核 yum remove ker…
閱讀更多...
彈性布局

彈性布局

/* 開啟彈性布局的換行 */ flex-wrap: wrap;/* 變為多行了 無法使用 align-items 進行位置設置 align-content 在多行的時候 設置屬性 跟 justify-content 一模一樣如果只有 一行時 無法生效 *//* 調整元素 在主軸上的 排布方式flex-end 到主軸的末尾flex-start 默認值center…
閱讀更多...
最新文章

Copyright @ 2022~2023