postgresql 主从　patroni-白红宇

postgresql 主从　patroni

阅读量：5221 次

发布时间：2019-06-14

本文共 4189 字，大约阅读时间需要 13 分钟。

1 安装基础包

1.1 postgres

yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm　gcc -y

yum install -y postgresql10-contrib.x86_64 postgresql10-server.x86_64

yum install postgis25_10.x86_64 -y

1.2 patroni

yum install python36 python36-devel -y

curl https://bootstrap.pypa.io/get-pip.py -o /tmp/get-pip.py

python3.6 /tmp/get-pip.py

pip3 install patroni[etcd] -i https://pypi.douban.com/simple

pip install psycopg2-binary

2 配置, 简单demo

2.1 配置　pg 超级用户密码，流复制用户权限及密码，pg_hba.conf

create user replicator replication login encrypted password 'replicator';

local   all             all                                     trusthost   all             all              127.0.0.1/32            trust# IPv4 local connections:host    all             all             10.1.0.0/16             md5# IPv6 local connections:host    all             all             ::1/128                 md5# Allow replication connections from localhost, by a user with the# replication privilege.local   replication     all                                     trusthost   replication      all             127.0.0.1/32             trusthost    replication     all             10.1.0.0/16             md5host    replication     all             ::1/128                 trust

2.2 patroni 配置文件

cat /etc/patroni/patroni_postgresql.yml scope: pgha    #集群名namespace: /pgsql/　　＃etcd pathname: pg_node1  # node 名称　每个节点不同restapi:  listen: 10.1.88.82:8008  connect_address: 10.1.88.82:8008etcd:  hosts: 10.1.88.81:2379,10.1.88.82:2379,10.1.88.83:2379  #host: ip:port  #single etd serverbootstrap:  # this section will be written into Etcd:/
     
      /
      
       /config after initializing new cluster  # and all other cluster members will use it as a `global configuration`  dcs:    ttl: 30    loop_wait: 10    retry_timeout: 10    maximum_lag_on_failover: 20485760    master_start_timeout: 300#    synchronous_mode: false    postgresql:      use_pg_rewind: true      use_slots: false      parameters:        listen_addresses: "*"        port: 5432        wal_level: logical        hot_standby: "on"        wal_keep_segments: 1000        max_wal_senders: 10        #synchronous_standby_names: '*'        max_connections: 150        max_replication_slots: 10        wal_log_hints: "on"postgresql:  listen: 0.0.0.0:5432  connect_address: 10.1.88.82:5432 # local ip  data_dir: /var/lib/pgsql/10/data  bin_dir: /usr/pgsql-10/bin#  config_dir:  authentication:    replication:      username: replicator      password: replicator    superuser:      username: postgres      password: postgres#watchdog:#  mode: automatic # Allowed values: off, automatic, required#  device: /dev/watchdog#  safety_margin: 5tags:    nofailover: false    noloadbalance: false    clonefrom: false    nosync: false

2.3 　服务配置

cat /usr/lib/systemd/system/patroni.service[Unit]Description=Patroni serverDocumentation='https://github.com/zalando/patroni,https://www.opsdash.com/blog/postgres-getting-started-patroni.html'After=syslog.targetAfter=network.target[Service]Type=simpleUser=postgresGroup=postgres# StandardOutput=syslogWorkingDirectory=/etc/patroni/ExecStart=/usr/local/bin/patroni /etc/patroni/patroni_postgresql.ymlExecReload=/bin/kill -HUP $MAINPIDKillMode=mixedKillSignal=SIGINT# Do not set any timeout value, so that systemd will not kill postmaster# during crash recovery.TimeoutSec=0[Install]WantedBy=multi-user.target

基本操作

patronictl -c /etc/patroni/patroni_postgresql.yml list

patronictl -c /etc/patroni/patroni_postgresql.yml show-config

patronictl -c /etc/patroni/patroni_postgresql.yml edit-config

删除　属性

patronictl -c /etc/patroni/patroni_postgresql.yml edit-config -s postgresql.parameters.synchronous_standby_names=null

错误　解决

data dir for the cluster is not empty, but system ID is invalid; consider doing reinitalize

patronictl -c /etc/patroni/patroni_postgresql.yml reinit pgha pg_node81

利用Restful API　动态修改数据库属性

https://patroni.readthedocs.io/en/latest/dynamic_configuration.html#dynamic-configuration

重启数据库　