Hello, today I will explain the installation of high availability patroni.<\/p>\n
1-PostgreSQL software installation
\n2-Patroni software installation
\n3-etcd software installation, etcd conf.
\n4-patroni conf.
\n5-pgbackrest setup and conf.
\n6-haproxy and keepalived
\n7-All other conf.<\/p>\n
PostgreSQL Software<\/p>\n
yum install -y postgresql12-server postgresql12-client postgresql12-contrib postgresql12-devel<\/pre>\nPatroni Software<\/p>\n
export PATH=\"$PATH:\/usr\/pgsql-12\/bin\"\r\n\r\nyum install -y python3-pip-9.0.3-5.el7.noarch gcc python3-devel\r\n\r\npip3 install --upgrade pip wheel\r\npip3 install psycopg2-binary\r\npip3 install psycopg2>=2.5.4\r\npip3 install patroni[etcd]<\/pre>\netcd installation and conf<\/p>\n
yum install -y etcd<\/pre>\n--vi \/etc\/etcd\/etcd.conf\r\n##--pgdb-1\r\nETCD_INITIAL_CLUSTER=\"etcd1=http:\/\/xxx.xxx.xx.xx:2380,etcd2=http:\/\/xxx.xxx.xx.xx:2380\"\r\nETCD_INITIAL_CLUSTER_STATE=\"new\"\r\nETCD_INITIAL_CLUSTER_TOKEN=\"etcd-cluster-01\"\r\nETCD_INITIAL_ADVERTISE_PEER_URLS=\"http:\/\/xxx.xxx.xx.xx:2380\"\r\nETCD_DATA_DIR=\"\/var\/lib\/etcd\/default.etcd\"\r\nETCD_LISTEN_PEER_URLS=\"http:\/\/xxx.xxx.xx.xx:2380\"\r\nETCD_LISTEN_CLIENT_URLS=\"http:\/\/xxx.xxx.xx.xx:2379,http:\/\/127.0.0.1:2379\"\r\nETCD_ADVERTISE_CLIENT_URLS=\"http:\/\/xxx.xxx.xx.xx:2379\"\r\nETCD_NAME=\"etcd1\"\r\n\r\n##--pgdb-2\r\nETCD_INITIAL_CLUSTER=\"etcd1=http:\/\/xxx.xxx.xx.xx:2380,etcd2=http:\/\/xxx.xxx.xx.xx:2380\"\r\nETCD_INITIAL_CLUSTER_STATE=\"new\"\r\nETCD_INITIAL_CLUSTER_TOKEN=\"etcd-cluster-01\"\r\nETCD_INITIAL_ADVERTISE_PEER_URLS=\"http:\/\/xxx.xxx.xx.xx:2380\"\r\nETCD_DATA_DIR=\"\/var\/lib\/etcd\/default.etcd\"\r\nETCD_LISTEN_PEER_URLS=\"http:\/\/xxx.xxx.xx.xx:2380\"\r\nETCD_LISTEN_CLIENT_URLS=\"http:\/\/xxx.xxx.xx.xx:2379,http:\/\/127.0.0.1:2379\"\r\nETCD_ADVERTISE_CLIENT_URLS=\"http:\/\/xxx.xxx.xx.xx:2379\"\r\nETCD_NAME=\"etcd2\"\r\n\r\nsystemctl enable etcd\r\nsystemctl start etcd\r\nsystemctl status etcd\r\nsystemctl stop etcd<\/pre>\nPatroni conf.<\/p>\n
The directory is created for the patroni yaml file. and postgres is authorized<\/p>\n
mkdir \/etc\/patroni\/\r\nchown postgres:postgres \/etc\/patroni\/\r\n\r\nmkdir \/postgres\/\r\nchown postgres:postgres \/postgres\/<\/pre>\n–\/\/ yml file should be set according to server IP addresses and memory etc. according to server resources. settings must be made.<\/p>\n
scope: pgdb-patroni\r\nname: postgres1\r\nrestapi:\r\nlisten: xxx.xxx.xx.xx:8008\r\nconnect_address: xxx.xxx.xx.xx:8008\r\netcd:\r\nhosts: 1xxx.xxx.xx.xx:2379,xxx.xxx.xx.xx:2379\r\nbootstrap:\r\ndcs:\r\nttl: 30\r\nloop_wait: 10\r\nretry_timeout: 10\r\nmaximum_lag_on_failover: 1048576\r\npostgresql:\r\nuse_pg_rewind: true\r\nparameters:\r\nwal_level: hot_standby\r\nhot_standby: \"on\"\r\nwal_keep_segments: 8\r\nmax_wal_senders: 10\r\nmax_replication_slots: 10\r\nwal_log_hints: \"on\"\r\nmax_connections: 200\r\nshared_buffers: 1GB\r\neffective_cache_size: 3GB\r\nmaintenance_work_mem: 256MB\r\ncheckpoint_completion_target: 0.7\r\nwal_buffers: 16MB\r\ndefault_statistics_target: 100\r\nrandom_page_cost: 1.1\r\neffective_io_concurrency: 300\r\nwork_mem: 5242kB\r\nmin_wal_size: 1GB\r\nmax_wal_size: 4GB\r\nmax_worker_processes: 2\r\nmax_parallel_workers_per_gather: 1\r\nmax_parallel_workers: 2\r\nmax_parallel_maintenance_workers: 1\r\narchive_mode: \"on\"\r\narchive_timeout: 1800s\r\narchive_command: \"pgbackrest --stanza=pgdb-patroni archive-push %p\"\r\nrecovery_conf:\r\nrestore_command: \"pgbackrest --stanza=pgdb-patroni archive-get %f %p\"\r\nstandby_mode: \"on\"\r\n\r\ninitdb:\r\n- encoding: UTF8\r\n- data-checksums\r\npg_hba:\r\n- host replication replicator xxx.xxx.xx.xx\/24 md5\r\n- host all all 0.0.0.0\/0 md5\r\nusers:\r\nadmin:\r\npassword: admin\r\noptions:\r\n- createrole\r\n- createdb\r\npostgresql:\r\nlisten: xxx.xxx.xx.xx:5432\r\nconnect_address: xxx.xxx.xx.xx:5432\r\ndata_dir: \/postgres\/data\/\r\nbin_dir: \/usr\/pgsql-12\/bin\r\npgpass: \/tmp\/pgpass0\r\nauthentication:\r\nreplication:\r\nusername: replicator\r\npassword: rep-pass\r\nsuperuser:\r\nusername: postgres\r\npassword: postgres\r\nrewind:\r\nusername: rewind_user\r\npassword: rewind_password\r\nparameters:\r\nunix_socket_directories: '\/var\/run\/postgresql\/'\r\ntags:\r\nnofailover: false\r\nnoloadbalance: false\r\nclonefrom: false\r\nnosync: false\r\n\r\nscope: pgdb-patroni\r\nname: postgres2\r\nrestapi:\r\nlisten: xxx.xxx.xx.xx:8008\r\nconnect_address: xxx.xxx.xx.xx:8008\r\netcd:\r\nhosts: xxx.xxx.xx.xx:2379,xxx.xxx.xx.xx:2379\r\nbootstrap:\r\ndcs:\r\nttl: 30\r\nloop_wait: 10\r\nretry_timeout: 10\r\nmaximum_lag_on_failover: 1048576\r\npostgresql:\r\nuse_pg_rewind: true\r\nparameters:\r\nwal_level: hot_standby\r\nhot_standby: \"on\"\r\nwal_keep_segments: 8\r\nmax_wal_senders: 10\r\nmax_replication_slots: 10\r\nwal_log_hints: \"on\"\r\nmax_connections: 200\r\nshared_buffers: 1GB\r\neffective_cache_size: 3GB\r\nmaintenance_work_mem: 256MB\r\ncheckpoint_completion_target: 0.7\r\nwal_buffers: 16MB\r\ndefault_statistics_target: 100\r\nrandom_page_cost: 1.1\r\neffective_io_concurrency: 300\r\nwork_mem: 5242kB\r\nmin_wal_size: 1GB\r\nmax_wal_size: 4GB\r\nmax_worker_processes: 2\r\nmax_parallel_workers_per_gather: 1\r\nmax_parallel_workers: 2\r\nmax_parallel_maintenance_workers: 1\r\narchive_mode: \"on\"\r\narchive_timeout: 1800s\r\narchive_command: \"pgbackrest --stanza=pgdb-patroni archive-push %p\"\r\nrecovery_conf:\r\nrestore_command: \"pgbackrest --stanza=pgdb-patroni archive-get %f %p\"\r\nstandby_mode: \"on\"<\/pre>\ninitdb:\r\n- encoding: UTF8\r\n- data-checksums\r\npg_hba:\r\n- host replication replicator xxx.xxx.xx.xx\/24 md5\r\n- host all all 0.0.0.0\/0 md5\r\nusers:\r\nadmin:\r\npassword: admin\r\noptions:\r\n- createrole\r\n- createdb\r\npostgresql:\r\nlisten: xxx.xxx.xx.xx:5432\r\nconnect_address: xxx.xxx.xx.xx:5432\r\ndata_dir: \/postgres\/data\/\r\nbin_dir: \/usr\/pgsql-12\/bin\r\npgpass: \/tmp\/pgpass0\r\nauthentication:\r\nreplication:\r\nusername: replicator\r\npassword: rep-pass\r\nsuperuser:\r\nusername: postgres\r\npassword: postgres\r\nrewind:\r\nusername: rewind_user\r\npassword: rewind_password\r\nparameters:\r\nunix_socket_directories: '\/var\/run\/postgresql\/'\r\ntags:\r\nnofailover: false\r\nnoloadbalance: false\r\nclonefrom: false\r\nnosync: false<\/pre>\npatroni service is created\r\n\r\n\u00a0more \/etc\/systemd\/system\/patroni.service\r\n\r\n\r\n[Unit]\r\nDescription=Runners to orchestrate a high-availability PostgreSQL\r\nAfter=syslog.target network.target\r\n\r\n[Service]\r\nType=simple\r\nUser=postgres\r\nGroup=postgres\r\nExecStart=\/usr\/local\/bin\/patroni \/etc\/patroni\/patroni.yml\r\nKillMode=process\r\nTimeoutSec=30\r\nRestart=no\r\n\r\n[Install]\r\nWantedBy=multi-user.target\r\n\r\n<\/pre>\nsystemctl enable patroni\r\nsystemctl start patroni\r\nsystemctl status patroni\r\nsystemctl stop patroni\r\njournalctl -xe -f -u patron<\/pre>\nThe same confs are made in other servers and the service is run.<\/p>\n
patronictl -d xxx.xxx.xx.xx list pgdb-patroni\r\n\r\n+ Cluster: pgdb-patroni (xxx.xxx.xx.xx) -+----+-----------+\r\n| Member | Host | Role | State | TL | Lag in MB |\r\n+-----------+---------------+--------+---------+----+-----------+\r\n| postgres1 | xxx.xxx.xx.xx | Leader | running | 1 | |\r\n| postgres2 | xxx.xxx.xx.xx | | running | 1 | 0 |\r\n+-----------+---------------+--------+---------+----+-----------+\r\n\r\nNote: Since we haven't installed pgbackrest until now, we may get an error when installing postgresql via patroni on 2.\r\nIf we restart patroni services after installing backrest, the problem will be solved. Or you can set up a backrest before starting these patrons.<\/pre>\npatronictl -d xxx.xxx.xx.xx list pgdb-patroni\r\n\r\npatronictl -d xxx.xxx.xx.xx reinit pgdb-patroni\r\n\r\npatronictl -c \/etc\/patroni\/patroni.yml list\r\n\r\n\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Hello, today I will explain the installation of high availability patroni. 1-PostgreSQL software installation 2-Patroni software installation 3-etcd software installation, etcd conf. 4-patroni conf. 5-pgbackrest setup and conf. 6-haproxy and keepalived 7-All other conf. PostgreSQL Software yum install -y postgresql12-server postgresql12-client postgresql12-contrib postgresql12-devel Patroni Software export PATH=”$PATH:\/usr\/pgsql-12\/bin” yum install -y python3-pip-9.0.3-5.el7.noarch gcc python3-devel pip3 install …<\/p>\n","protected":false},"author":36,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3896],"tags":[19814],"class_list":["post-34866","post","type-post","status-publish","format-standard","","category-postgresql","tag-patroni-haproxy"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/34866","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/36"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=34866"}],"version-history":[{"count":2,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/34866\/revisions"}],"predecessor-version":[{"id":34941,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/34866\/revisions\/34941"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=34866"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=34866"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=34866"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}