我的书签
添加书签
移除书签TINY
Patroni TINY模板
Patroni TINY模板主要针对极低配置的虚拟机进行优化，
此模板针对的典型机型是1核/1GB的虚拟机节点。您可以根据自己的实际机型进行调整。
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 }} (tiny)
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 : Tiny Database Cluster Template
11. # Doc : https://patroni.readthedocs.io/en/latest/SETTINGS.html
12. # Copyright (C) 2018-2021 Ruohang Feng
13. #==============================================================#
15. # TINY database are optimized for low-resource situation (e.g 1 Core 1G)
16. # typical spec: 1 Core | 1-4 GB RAM | Normal SSD 10x GB
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 }}
47. #------------------------------------------------------------------------------
48. # api
49. #------------------------------------------------------------------------------
50. # how to expose patroni service
51. # listen on all ipv4, connect via public ip, use same credential as dbuser_monitor
52. restapi:
53. listen: 0.0.0.0:{{ patroni_port }}
54. connect_address: {{ inventory_hostname }}:{{ patroni_port }}
55. authentication:
56. verify_client: none # none|optional|required
57. username: {{ pg_monitor_username }}
58. password: '{{ pg_monitor_password }}'
61. #------------------------------------------------------------------------------
62. # ctl
63. #------------------------------------------------------------------------------
64. ctl:
65. optional:
66. insecure: true
67. # cacert: '/path/to/ca/cert'
68. # certfile: '/path/to/cert/file'
69. # keyfile: '/path/to/key/file'
71. #------------------------------------------------------------------------------
72. # tags
73. #------------------------------------------------------------------------------
74. tags:
75. nofailover: false
76. clonefrom: true
77. noloadbalance: false
78. nosync: false
79. {% if pg_upstream is defined %}
80. replicatefrom: {{ pg_upstream }} # clone from another replica rather than primary
81. {% endif %}
83. #------------------------------------------------------------------------------
84. # watchdog
85. #------------------------------------------------------------------------------
86. # available mode: off|automatic|required
87. watchdog:
88. mode: {{ patroni_watchdog_mode }}
89. device: /dev/watchdog
90. # safety_margin: 10s
92. #------------------------------------------------------------------------------
93. # bootstrap
94. #------------------------------------------------------------------------------
95. bootstrap:
97. #----------------------------------------------------------------------------
98. # bootstrap method
99. #----------------------------------------------------------------------------
100. method: initdb
101. # add custom bootstrap method here
103. # default bootstrap method: initdb
104. initdb:
105. - locale: C
106. - encoding: UTF8
107. - data-checksums # enable data-checksum
110. #----------------------------------------------------------------------------
111. # bootstrap users
112. #---------------------------------------------------------------------------
113. # additional users which need to be created after initializing new cluster
114. # replication user and monitor user are required
115. users:
116. {{ pg_replication_username }}:
117. password: '{{ pg_replication_password }}'
118. {{ pg_monitor_username }}:
119. password: '{{ pg_monitor_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. # customization
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: 10
176. # import: candidate will not be promoted if replication lag is higher than this
177. # maximum RPO: 1MB
178. maximum_lag_on_failover: 1048576
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: 30
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: false
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: 50 # default 100 -> 50
205. superuser_reserved_connections: 10 # reserve 10 connection for su
206. max_locks_per_transaction: 64 # default 64
207. max_prepared_transactions: 0 # 0 disable 2PC
208. track_commit_timestamp: on # enabled xact timestamp
209. max_worker_processes: 1 # default 8 -> 1 (set to cpu core)
210. wal_level: logical # logical
211. wal_log_hints: on # wal log hints to support rewind
212. max_wal_senders: 10 # default 10
213. max_replication_slots: 10 # default 10
214. wal_keep_size: 1GB # keep at least 1GB 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: 4MB # default 4MB
224. huge_pages: try # try huge pages
225. temp_file_limit: 40GB # 0 -> 40GB (according to your disk)
226. vacuum_cost_delay: 5ms # wait 5ms 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: 15min # checkpoint 5min -> 15min
243. checkpoint_completion_target: 0.80 # 0.5 -> 0.8
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 -> 2 (set according to your cpu core)
259. max_sync_workers_per_subscription: 8 # 4 -> 2
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: 2GB # max mem - shared buffer
268. default_statistics_target: 200 # stat bucket 100 -> 200
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: 8192 # max query length in pg_stat_activity
291. #----------------------------------------------------------------------
292. # AUTOVACUUM
293. #----------------------------------------------------------------------
294. log_autovacuum_min_duration: 1s # log autovacuum activity take more than 1s
295. autovacuum_max_workers: 1 # default autovacuum worker 3 -> 1
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: 10min # 10min timeout for idle in transaction
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: 3000 # 5000 -> 3000 queries
324. pg_stat_statements.track: all # track all statements (all|top|none)
325. pg_stat_statements.track_utility: off # do not track query other than CRUD
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
403. ...
Last modified 2022-05-27: init commit (1e3e284)
