我的书签
添加书签
移除书签CRIT
Patroni CRIT模板，针对金融场景、不允许数据丢失错漏的场景进行优化。
Patroni CRIT模板主要针对RPO进行优化，采用同步复制，发生故障时确保不会有数据丢失。
此模板针对的机型是Dell R740 64核/400GB内存，使用PCI-E SSD的节点。用户可以根据自己的实际机型进行调整。
1. #!/usr/bin/env patroni
2. #==============================================================#
3. # File : patroni.yml
4. # Ctime : 2020-04-08
5. # Mtime : 2020-12-22
6. # Desc : patroni cluster definition for {{ pg_cluster }} (crit)
7. # Path : /pg/bin/patroni.yml
8. # Real Path : /pg/conf/{{ pg_instance }}.yml
9. # Link : /pg/bin/patroni.yml -> /pg/conf/{{ pg_instance}}.yml
10. # Note : Critical Database Cluster Template
11. # Doc : https://patroni.readthedocs.io/en/latest/SETTINGS.html
12. # Copyright (C) 2018-2021 Ruohang Feng
13. #==============================================================#
15. # CRIT database are optimized for security, integrity, RPO
16. # typical spec: 64 Core | 400 GB RAM | PCI-E SSD xTB
18. ---
19. #------------------------------------------------------------------------------
20. # identity
21. #------------------------------------------------------------------------------
22. namespace: {{ pg_namespace }}/ # namespace
23. scope: {{ pg_cluster }} # cluster name
24. name: {{ pg_instance }} # instance name
26. #------------------------------------------------------------------------------
27. # log
28. #------------------------------------------------------------------------------
29. log:
30. level: INFO # NOTEST|DEBUG|INFO|WARNING|ERROR|CRITICAL
31. dir: /pg/log/ # default log file: /pg/log/patroni.log
32. file_size: 100000000 # 100MB log triggers a log rotate
33. # format: '%(asctime)s %(levelname)s: %(message)s'
35. #------------------------------------------------------------------------------
36. # dcs
37. #------------------------------------------------------------------------------
38. consul:
39. host: 127.0.0.1:8500
40. consistency: default # default|consistent|stale
41. register_service: true
42. service_check_interval: 15s
43. service_tags:
44. - {{ pg_cluster }}
46. #------------------------------------------------------------------------------
47. # api
48. #------------------------------------------------------------------------------
49. # how to expose patroni service
50. # listen on all ipv4, connect via public ip, use same credential as dbuser_monitor
51. restapi:
52. listen: 0.0.0.0:{{ patroni_port }}
53. connect_address: {{ inventory_hostname }}:{{ patroni_port }}
54. authentication:
55. verify_client: none # none|optional|required
56. username: {{ pg_monitor_username }}
57. password: '{{ pg_monitor_password }}'
59. #------------------------------------------------------------------------------
60. # ctl
61. #------------------------------------------------------------------------------
62. ctl:
63. optional:
64. insecure: true
65. # cacert: '/path/to/ca/cert'
66. # certfile: '/path/to/cert/file'
67. # keyfile: '/path/to/key/file'
69. #------------------------------------------------------------------------------
70. # tags
71. #------------------------------------------------------------------------------
72. tags:
73. nofailover: false
74. clonefrom: true
75. noloadbalance: false
76. nosync: false
77. {% if pg_upstream is defined %}
78. replicatefrom: {{ pg_upstream }} # clone from another replica rather than primary
79. {% endif %}
81. #------------------------------------------------------------------------------
82. # watchdog
83. #------------------------------------------------------------------------------
84. # available mode: off|automatic|required
85. watchdog:
86. mode: {{ patroni_watchdog_mode }}
87. device: /dev/watchdog
88. # safety_margin: 10s
90. #------------------------------------------------------------------------------
91. # bootstrap
92. #------------------------------------------------------------------------------
93. bootstrap:
95. #----------------------------------------------------------------------------
96. # bootstrap method
97. #----------------------------------------------------------------------------
98. method: initdb
99. # add custom bootstrap method here
101. # default bootstrap method: initdb
102. initdb:
103. - locale: C
104. - encoding: UTF8
105. # - data-checksums # enable data-checksum
108. #----------------------------------------------------------------------------
109. # bootstrap users
110. #---------------------------------------------------------------------------
111. # additional users which need to be created after initializing new cluster
112. # replication user and monitor user are required
113. users:
114. {{ pg_replication_username }}:
115. password: '{{ pg_replication_password }}'
116. {{ pg_monitor_username }}:
117. password: '{{ pg_monitor_password }}'
118. {{ pg_admin_username }}:
119. password: '{{ pg_admin_password }}'
121. # bootstrap hba, allow local and intranet password access & replication
122. # will be overwritten later
123. pg_hba:
124. - local all postgres ident
125. - local all all md5
126. - host all all 0.0.0.0/0 md5
127. - local replication postgres ident
128. - local replication all md5
129. - host replication all 0.0.0.0/0 md5
132. #----------------------------------------------------------------------------
133. # template
134. #---------------------------------------------------------------------------
135. # post_init: /pg/bin/pg-init
137. #----------------------------------------------------------------------------
138. # bootstrap config
139. #---------------------------------------------------------------------------
140. # this section will be written to /{{ pg_namespace }}/{{ pg_cluster }}/config
141. # if will NOT take any effect after cluster bootstrap
142. dcs:
144. {% if pg_role == 'primary' and pg_upstream is defined %}
145. #----------------------------------------------------------------------------
146. # standby cluster definition
147. #---------------------------------------------------------------------------
148. standby_cluster:
149. host: {{ pg_upstream }}
150. port: {{ pg_port }}
151. # primary_slot_name: patroni # must be create manually on upstream server, if specified
152. create_replica_methods:
153. - basebackup
154. {% endif %}
156. #----------------------------------------------------------------------------
157. # important parameters
158. #---------------------------------------------------------------------------
159. # constraint: ttl >: loop_wait + retry_timeout * 2
161. # the number of seconds the loop will sleep. Default value: 10
162. # this is patroni check loop interval
163. loop_wait: 10
165. # the TTL to acquire the leader lock (in seconds). Think of it as the length of time before initiation of the automatic failover process. Default value: 30
166. # config this according to your network condition to avoid false-positive failover
167. ttl: 30
169. # timeout for DCS and PostgreSQL operation retries (in seconds). DCS or network issues shorter than this will not cause Patroni to demote the leader. Default value: 10
170. retry_timeout: 10
172. # the amount of time a master is allowed to recover from failures before failover is triggered (in seconds)
173. # Max RTO: 2 loop wait + master_start_timeout
174. master_start_timeout: 120 # more patient on critical database
176. # import: candidate will not be promoted if replication lag is higher than this
177. # maximum RPO: 0 for critical database
178. maximum_lag_on_failover: 1
180. # The number of seconds Patroni is allowed to wait when stopping Postgres and effective only when synchronous_mode is enabled
181. master_stop_timeout: 10 # more patient on critical database
183. # turns on synchronous replication mode. In this mode a replica will be chosen as synchronous and only the latest leader and synchronous replica are able to participate in leader election
184. # set to true for RPO mode
185. synchronous_mode: true # use sync replication on critical database
187. # prevents disabling synchronous replication if no synchronous replicas are available, blocking all client writes to the master
188. synchronous_mode_strict: false
191. #----------------------------------------------------------------------------
192. # postgres parameters
193. #---------------------------------------------------------------------------
194. postgresql:
195. use_slots: true
196. use_pg_rewind: true
197. remove_data_directory_on_rewind_failure: true
200. parameters:
201. #----------------------------------------------------------------------
202. # IMPORTANT PARAMETERS
203. #----------------------------------------------------------------------
204. max_connections: 400 # 100 -> 400
205. superuser_reserved_connections: 10 # reserve 10 connection for su
206. max_locks_per_transaction: 128 # 64 -> 128
207. max_prepared_transactions: 0 # 0 disable 2PC
208. track_commit_timestamp: on # enabled xact timestamp
209. max_worker_processes: 8 # default 8, set to cpu core
210. wal_level: logical # logical
211. wal_log_hints: on # wal log hints to support rewind
212. max_wal_senders: 16 # 10 -> 16
213. max_replication_slots: 16 # 10 -> 16
214. wal_keep_size: 100GB # keep at least 100GB WAL
215. password_encryption: md5 # use traditional md5 auth
217. #----------------------------------------------------------------------
218. # RESOURCE USAGE (except WAL)
219. #----------------------------------------------------------------------
220. # memory: shared_buffers and maintenance_work_mem will be dynamically set
221. shared_buffers: {{ pg_shared_buffers }}
222. maintenance_work_mem: {{ pg_maintenance_work_mem }}
223. work_mem: 32MB # 4MB -> 32MB
224. huge_pages: try # try huge pages
225. temp_file_limit: 100GB # 0 -> 100GB
226. vacuum_cost_delay: 2ms # wait 2ms per 10000 cost
227. vacuum_cost_limit: 10000 # 10000 cost each round
228. bgwriter_delay: 10ms # check dirty page every 10ms
229. bgwriter_lru_maxpages: 800 # 100 -> 800
230. bgwriter_lru_multiplier: 5.0 # 2.0 -> 5.0 more cushion buffer
232. #----------------------------------------------------------------------
233. # WAL
234. #----------------------------------------------------------------------
235. wal_buffers: 16MB # max to 16MB
236. wal_writer_delay: 20ms # wait period
237. wal_writer_flush_after: 1MB # max allowed data loss
238. min_wal_size: 100GB # at least 100GB WAL
239. max_wal_size: 400GB # at most 400GB WAL
240. commit_delay: 20 # 200ms -> 20ms, increase speed
241. commit_siblings: 10 # 5 -> 10
242. checkpoint_timeout: 60min # checkpoint 5min -> 1h
243. checkpoint_completion_target: 0.95 # 0.5 -> 0.95
244. archive_mode: on
245. archive_command: 'wal_dir=/pg/arcwal; [[ $(date +%H%M) == 1200 ]] && rm -rf ${wal_dir}/$(date -d"yesterday" +%Y%m%d); /bin/mkdir -p ${wal_dir}/$(date +%Y%m%d) && /usr/bin/lz4 -q -z %p > ${wal_dir}/$(date +%Y%m%d)/%f.lz4'
247. #----------------------------------------------------------------------
248. # REPLICATION
249. #----------------------------------------------------------------------
250. # synchronous_standby_names: ''
251. vacuum_defer_cleanup_age: 50000 # 0->50000 last 50000 xact changes will not be vacuumed
252. promote_trigger_file: promote.signal # default promote trigger file path
253. max_standby_archive_delay: 10min # max delay before canceling queries when reading WAL from archive;
254. max_standby_streaming_delay: 3min # max delay before canceling queries when reading streaming WAL;
255. wal_receiver_status_interval: 1s # send replies at least this often
256. hot_standby_feedback: on # send info from standby to prevent query conflicts
257. wal_receiver_timeout: 60s # time that receiver waits for
258. max_logical_replication_workers: 8 # 4 -> 8
259. max_sync_workers_per_subscription: 8 # 4 -> 8
261. #----------------------------------------------------------------------
262. # QUERY TUNING
263. #----------------------------------------------------------------------
264. # planner
265. # enable_partitionwise_join: on
266. random_page_cost: 1.1 # 4 for HDD, 1.1 for SSD
267. effective_cache_size: 320GB # max mem - shared buffer
268. default_statistics_target: 1000 # stat bucket 100 -> 1000
270. #----------------------------------------------------------------------
271. # REPORTING AND LOGGING
272. #----------------------------------------------------------------------
273. log_destination: csvlog # use standard csv log
274. logging_collector: on # enable csvlog
275. log_directory: log # default log dir: /pg/data/log
276. # log_filename: 'postgresql-%a.log' # weekly auto-recycle
277. log_filename: 'postgresql-%Y-%m-%d.log' # YYYY-MM-DD full log retention
278. log_checkpoints: on # log checkpoint info
279. log_lock_waits: on # log lock wait info
280. log_replication_commands: on # log replication info
281. log_statement: ddl # log ddl change
282. log_min_duration_statement: 100 # log slow query (>100ms)
284. #----------------------------------------------------------------------
285. # STATISTICS
286. #----------------------------------------------------------------------
287. track_io_timing: on # collect io statistics
288. track_functions: all # track all functions (none|pl|all)
289. track_activity_query_size: 32768 # show full query on critical database
291. #----------------------------------------------------------------------
292. # AUTOVACUUM
293. #----------------------------------------------------------------------
294. log_autovacuum_min_duration: 1s # log autovacuum activity take more than 1s
295. autovacuum_max_workers: 3 # default autovacuum worker 3
296. autovacuum_naptime: 1min # default autovacuum naptime 1min
297. autovacuum_vacuum_scale_factor: 0.08 # fraction of table size before vacuum 20% -> 8%
298. autovacuum_analyze_scale_factor: 0.04 # fraction of table size before analyze 10% -> 4%
299. autovacuum_vacuum_cost_delay: -1 # default vacuum cost delay: same as vacuum_cost_delay
300. autovacuum_vacuum_cost_limit: -1 # default vacuum cost limit: same as vacuum_cost_limit
301. autovacuum_freeze_max_age: 100000000 # age > 1 billion triggers force vacuum
303. #----------------------------------------------------------------------
304. # CLIENT
305. #----------------------------------------------------------------------
306. deadlock_timeout: 50ms # 50ms for deadlock
307. idle_in_transaction_session_timeout: 1min # 1min timeout for idle in transaction in critical database
309. #----------------------------------------------------------------------
310. # CUSTOMIZED OPTIONS
311. #----------------------------------------------------------------------
312. # extensions
313. shared_preload_libraries: '{{ pg_shared_libraries | default("pg_stat_statements, auto_explain") }}'
315. # auto_explain
316. auto_explain.log_min_duration: 1s # auto explain query slower than 1s
317. auto_explain.log_analyze: true # explain analyze
318. auto_explain.log_verbose: true # explain verbose
319. auto_explain.log_timing: true # explain timing
320. auto_explain.log_nested_statements: true
322. # pg_stat_statements
323. pg_stat_statements.max: 10000 # 5000 -> 10000 queries
324. pg_stat_statements.track: all # track all statements (all|top|none)
325. pg_stat_statements.track_utility: on # TRACK all queries on critical database
326. pg_stat_statements.track_planning: off # do not track planning metrics
329. #------------------------------------------------------------------------------
330. # postgres
331. #------------------------------------------------------------------------------
332. postgresql:
334. #----------------------------------------------------------------------------
335. # how to connect to postgres
336. #----------------------------------------------------------------------------
337. bin_dir: {{ pg_bin_dir }}
338. data_dir: {{ pg_data }}
339. config_dir: {{ pg_data }}
340. pgpass: {{ pg_dbsu_home }}/.pgpass
341. listen: {{ pg_listen }}:{{ pg_port }}
342. connect_address: {{ inventory_hostname }}:{{ pg_port }}
343. use_unix_socket: true # default: /var/run/postgresql, /tmp
345. #----------------------------------------------------------------------------
346. # who to connect to postgres
347. #----------------------------------------------------------------------------
348. authentication:
349. superuser:
350. username: {{ pg_dbsu }}
351. replication:
352. username: {{ pg_replication_username }}
353. password: '{{ pg_replication_password }}'
354. rewind:
355. username: {{ pg_replication_username }}
356. password: '{{ pg_replication_password }}'
358. #----------------------------------------------------------------------------
359. # how to react to database operations
360. #----------------------------------------------------------------------------
361. # event callback script log: /pg/log/callback.log
362. callbacks:
363. on_start: /pg/bin/pg-failover-callback
364. on_stop: /pg/bin/pg-failover-callback
365. on_reload: /pg/bin/pg-failover-callback
366. on_restart: /pg/bin/pg-failover-callback
367. on_role_change: /pg/bin/pg-failover-callback
369. # rewind policy: data checksum should be enabled before using rewind
370. use_pg_rewind: true
371. remove_data_directory_on_rewind_failure: true
372. remove_data_directory_on_diverged_timelines: false
374. #----------------------------------------------------------------------------
375. # how to create replica
376. #----------------------------------------------------------------------------
377. # create replica method: default pg_basebackup
378. create_replica_methods:
379. - basebackup
380. basebackup:
381. - max-rate: '1000M'
382. - checkpoint: fast
383. - status-interva: 1s
384. - verbose
385. - progress
387. #----------------------------------------------------------------------------
388. # ad hoc parameters (overwrite with default)
389. #----------------------------------------------------------------------------
390. # parameters:
392. #----------------------------------------------------------------------------
393. # host based authentication, overwrite default pg_hba.conf
394. #----------------------------------------------------------------------------
395. # pg_hba:
396. # - local all postgres ident
397. # - local all all md5
398. # - host all all 0.0.0.0/0 md5
399. # - local replication postgres ident
400. # - local replication all md5
401. # - host replication all 0.0.0.0/0 md5
402. ...
最后修改 2022-05-27: init commit (1e3e284)
