Skip to content

Commit faf10f0

Browse files
VonngVonng
committed
adjust config/0130-pg_setting refactor for PG10-18 compatibility
- Add 13 new metrics: max_parallel_workers, max_parallel_workers_per_gather, max_parallel_maintenance_workers, shared_buffers, maintenance_work_mem, effective_cache_size, fsync, full_page_writes, autovacuum, autovacuum_max_workers, checkpoint_timeout, checkpoint_completion_target, hot_standby, synchronous_commit, io_method (PG18) - Use missing_ok=true for version-specific parameters (PG11+/PG13+/PG14+/PG18+) - Rename work_memory_size to work_mem - Change min_version from 9.6 to 10 - Add explicit ::int type casting for all numeric settings - Fix hugepage_status ELSE clause from -2 to NULL
1 parent cea3f44 commit faf10f0

File tree

1 file changed

+79
-41
lines changed

1 file changed

+79
-41
lines changed

‎config/0130-pg_setting.yml‎

Lines changed: 79 additions & 41 deletions
Original file line numberDiff line numberDiff line change
@@ -1,56 +1,94 @@
11
#==============================================================#
22
# 0130 pg_setting
33
#==============================================================#
4+
# Key PostgreSQL configuration parameters
5+
# All parameters use current_setting(name, missing_ok) for version safety
6+
# Parameters introduced after PG10 use missing_ok=true to return NULL on older versions
47
pg_setting:
58
name: pg_setting
6-
desc: Important postgres setting parameters
9+
desc: PostgreSQL shared configuration parameters (shared across all databases)
710
query: |
811
SELECT
9-
current_setting('max_connections') AS max_connections,
10-
current_setting('max_prepared_transactions') AS max_prepared_transactions,
11-
current_setting('max_locks_per_transaction') AS max_locks_per_transaction,
12-
current_setting('max_worker_processes') AS max_worker_processes,
13-
current_setting('max_replication_slots') AS max_replication_slots,
14-
current_setting('max_wal_senders') AS max_wal_senders,
15-
current_setting('block_size') AS block_size,
16-
current_setting('wal_block_size') AS wal_block_size,
17-
pg_size_bytes(current_setting('segment_size')) AS segment_size,
18-
pg_size_bytes(current_setting('wal_segment_size')) AS wal_segment_size,
12+
current_setting('max_connections')::int AS max_connections,
13+
current_setting('max_prepared_transactions')::int AS max_prepared_transactions,
14+
current_setting('max_locks_per_transaction')::int AS max_locks_per_transaction,
15+
current_setting('max_worker_processes')::int AS max_worker_processes,
16+
current_setting('max_parallel_workers')::int AS max_parallel_workers,
17+
current_setting('max_parallel_workers_per_gather')::int AS max_parallel_workers_per_gather,
18+
current_setting('max_parallel_maintenance_workers', true)::int AS max_parallel_maintenance_workers,
19+
current_setting('max_replication_slots')::int AS max_replication_slots,
20+
current_setting('max_wal_senders')::int AS max_wal_senders,
21+
current_setting('block_size')::int AS block_size,
22+
current_setting('wal_block_size')::int AS wal_block_size,
23+
pg_size_bytes(current_setting('segment_size')) AS segment_size,
24+
pg_size_bytes(current_setting('wal_segment_size')) AS wal_segment_size,
1925
CASE current_setting('data_checksums') WHEN 'on' THEN 1 ELSE 0 END AS data_checksums,
2026
CASE current_setting('wal_log_hints') WHEN 'on' THEN 1 ELSE 0 END AS wal_log_hints,
27+
CASE current_setting('fsync') WHEN 'on' THEN 1 ELSE 0 END AS fsync,
28+
CASE current_setting('full_page_writes') WHEN 'on' THEN 1 ELSE 0 END AS full_page_writes,
2129
CASE current_setting('wal_level') WHEN 'logical' THEN 3 WHEN 'replica' THEN 2 WHEN 'minimal' THEN 1 ELSE 0 END AS wal_level,
22-
pg_size_bytes(current_setting('work_mem', true)) AS work_memory_size,
23-
pg_size_bytes(current_setting('shared_memory_size', true)) AS shared_memory_size,
24-
CASE current_setting('huge_pages_status', true) WHEN 'on' THEN 1 WHEN 'off' THEN 0 WHEN 'unknown' THEN -1 ELSE -2 END AS hugepage_status,
25-
current_setting('shared_memory_size_in_huge_pages', true) AS hugepage_count,
26-
pg_size_bytes(current_setting('min_wal_size')) AS min_wal_size,
27-
pg_size_bytes(current_setting('max_wal_size')) AS max_wal_size,
28-
pg_size_bytes(current_setting('max_slot_wal_keep_size', true)) AS max_slot_wal_keep_size,
29-
CASE current_setting('archive_mode') WHEN 'off' THEN 0 WHEN 'on' THEN 1 WHEN 'always' THEN 2 ELSE -1 END AS archive_mode;
30+
pg_size_bytes(current_setting('min_wal_size')) AS min_wal_size,
31+
pg_size_bytes(current_setting('max_wal_size')) AS max_wal_size,
32+
pg_size_bytes(current_setting('max_slot_wal_keep_size', true)) AS max_slot_wal_keep_size,
33+
pg_size_bytes(current_setting('shared_buffers')) AS shared_buffers,
34+
pg_size_bytes(current_setting('work_mem')) AS work_mem,
35+
pg_size_bytes(current_setting('maintenance_work_mem')) AS maintenance_work_mem,
36+
pg_size_bytes(current_setting('effective_cache_size')) AS effective_cache_size,
37+
pg_size_bytes(current_setting('shared_memory_size', true)) AS shared_memory_size,
38+
CASE current_setting('huge_pages_status', true) WHEN 'on' THEN 1 WHEN 'off' THEN 0 WHEN 'unknown' THEN -1 ELSE NULL END AS hugepage_status,
39+
current_setting('shared_memory_size_in_huge_pages', true)::int AS hugepage_count,
40+
CASE current_setting('archive_mode') WHEN 'off' THEN 0 WHEN 'on' THEN 1 WHEN 'always' THEN 2 ELSE -1 END AS archive_mode,
41+
CASE current_setting('autovacuum') WHEN 'on' THEN 1 ELSE 0 END AS autovacuum,
42+
current_setting('autovacuum_max_workers')::int AS autovacuum_max_workers,
43+
extract(epoch from current_setting('checkpoint_timeout')::interval)::int AS checkpoint_timeout,
44+
current_setting('checkpoint_completion_target')::float AS checkpoint_completion_target,
45+
CASE current_setting('hot_standby') WHEN 'on' THEN 1 ELSE 0 END AS hot_standby,
46+
CASE current_setting('synchronous_commit')
47+
WHEN 'off' THEN 0 WHEN 'local' THEN 1 WHEN 'remote_write' THEN 2
48+
WHEN 'on' THEN 3 WHEN 'remote_apply' THEN 4 ELSE -1 END AS synchronous_commit,
49+
CASE current_setting('io_method', true)
50+
WHEN 'sync' THEN 0 WHEN 'worker' THEN 1 WHEN 'io_uring' THEN 2 ELSE NULL END AS io_method;
51+
3052
ttl: 10
31-
min_version: 090600
53+
min_version: 100000
3254
tags: [ cluster ]
3355
metrics:
34-
- max_connections: { usage: GAUGE ,description: number of concurrent connections to the database server }
35-
- max_prepared_transactions: { usage: GAUGE ,description: maximum number of transactions that can be in the prepared state simultaneously }
36-
- max_locks_per_transaction: { usage: GAUGE ,description: no more than this many distinct objects can be locked at any one time }
37-
- max_worker_processes: { usage: GAUGE ,description: maximum number of background processes that the system can support }
38-
- max_replication_slots: { usage: GAUGE ,description: maximum number of replication slots }
39-
- max_wal_senders: { usage: GAUGE ,description: maximum number of concurrent connections from standby servers }
40-
- block_size: { usage: GAUGE ,description: pg page block size, 8192 by default }
41-
- wal_block_size: { usage: GAUGE ,description: block size in WAL files }
42-
- segment_size: { usage: GAUGE ,description: segment size for database files }
43-
- wal_segment_size: { usage: GAUGE ,description: segment size for WAL files }
44-
- data_checksums: { usage: GAUGE ,description: whether data checksum is enabled, 1 enabled 0 disabled }
45-
- wal_log_hints: { usage: GAUGE ,description: whether wal_log_hints is enabled, 1 enabled 0 disabled }
46-
- wal_level: { usage: GAUGE ,description: WAL level, 1=minimal, 2=replica, 3=logical }
47-
- work_memory_size: { usage: GAUGE ,description: size of work memory in bytes }
48-
- shared_memory_size: { usage: GAUGE ,description: size of shared memory segment in bytes }
49-
- hugepage_count: { usage: GAUGE ,description: Reports the number of huge pages that are needed for the main shared memory area }
50-
- hugepage_status: { usage: GAUGE ,description: huge pages configuration status, 0=off, 1=on, -1=unknown }
51-
- min_wal_size: { usage: GAUGE ,description: minimum size that the WAL will shrink to in bytes }
52-
- max_wal_size: { usage: GAUGE ,description: maximum size that the WAL will grow to in bytes }
53-
- max_slot_wal_keep_size: { usage: GAUGE ,description: maximum WAL size that replication slots are allowed to retain in bytes, -1 means no limit }
54-
- archive_mode: { usage: GAUGE ,description: archive mode status, 0=off, 1=on, 2=always, -1=other }
56+
- max_connections: { usage: GAUGE ,description: maximum number of concurrent connections to the database server }
57+
- max_prepared_transactions: { usage: GAUGE ,description: maximum number of transactions that can be in the prepared state simultaneously }
58+
- max_locks_per_transaction: { usage: GAUGE ,description: maximum number of locks per transaction }
59+
- max_worker_processes: { usage: GAUGE ,description: maximum number of background processes }
60+
- max_parallel_workers: { usage: GAUGE ,description: maximum number of parallel workers that can be active at one time }
61+
- max_parallel_workers_per_gather: { usage: GAUGE ,description: maximum number of parallel workers per Gather node }
62+
- max_parallel_maintenance_workers: { usage: GAUGE ,description: maximum number of parallel maintenance workers (PG11+, NULL on older) }
63+
- max_replication_slots: { usage: GAUGE ,description: maximum number of replication slots }
64+
- max_wal_senders: { usage: GAUGE ,description: maximum number of concurrent WAL sender connections }
65+
- block_size: { usage: GAUGE ,description: database block size in bytes (default 8192) }
66+
- wal_block_size: { usage: GAUGE ,description: WAL block size in bytes }
67+
- segment_size: { usage: GAUGE ,description: database file segment size in bytes }
68+
- wal_segment_size: { usage: GAUGE ,description: WAL segment size in bytes }
69+
- data_checksums: { usage: GAUGE ,description: data checksums enabled, 1=on 0=off }
70+
- wal_log_hints: { usage: GAUGE ,description: WAL log hints enabled, 1=on 0=off }
71+
- fsync: { usage: GAUGE ,description: fsync enabled (CRITICAL for data safety), 1=on 0=off }
72+
- full_page_writes: { usage: GAUGE ,description: full page writes enabled, 1=on 0=off }
73+
- wal_level: { usage: GAUGE ,description: WAL level, 1=minimal 2=replica 3=logical }
74+
- min_wal_size: { usage: GAUGE ,description: minimum WAL size in bytes }
75+
- max_wal_size: { usage: GAUGE ,description: maximum WAL size in bytes }
76+
- max_slot_wal_keep_size: { usage: GAUGE ,description: maximum WAL size retained by replication slots in bytes (PG13+, NULL on older) }
77+
- shared_buffers: { usage: GAUGE ,description: shared buffer size in bytes }
78+
- work_mem: { usage: GAUGE ,description: work memory size in bytes }
79+
- maintenance_work_mem: { usage: GAUGE ,description: maintenance work memory size in bytes }
80+
- effective_cache_size: { usage: GAUGE ,description: planner's assumption about effective OS cache size in bytes }
81+
- shared_memory_size: { usage: GAUGE ,description: total shared memory size in bytes (PG13+, NULL on older) }
82+
- hugepage_status: { usage: GAUGE ,description: huge pages status, 1=on 0=off -1=unknown NULL=unavailable (PG14+) }
83+
- hugepage_count: { usage: GAUGE ,description: number of huge pages needed for shared memory (PG14+, NULL on older) }
84+
- archive_mode: { usage: GAUGE ,description: archive mode, 0=off 1=on 2=always }
85+
- autovacuum: { usage: GAUGE ,description: autovacuum enabled, 1=on 0=off }
86+
- autovacuum_max_workers: { usage: GAUGE ,description: maximum number of autovacuum worker processes }
87+
- checkpoint_timeout: { usage: GAUGE ,description: checkpoint timeout in seconds }
88+
- checkpoint_completion_target: { usage: GAUGE ,description: checkpoint completion target (0.0-1.0) }
89+
- hot_standby: { usage: GAUGE ,description: hot standby mode enabled, 1=on 0=off }
90+
- synchronous_commit: { usage: GAUGE ,description: synchronous commit level, 0=off 1=local 2=remote_write 3=on 4=remote_apply }
91+
- io_method: { usage: GAUGE ,description: I/O method (PG18+), 0=sync 1=worker 2=io_uring NULL=unavailable }
92+
5593

5694

0 commit comments

Comments
 (0)