跳转到主要内容

这是本节的多页打印视图。 .

返回本页常规视图.

Pigsty 默认值

Pigsty OLTP、OLAP、CRIT、TINY 模板相对 PostgreSQL 上游 boot 默认值的完整差异表。

本页使用固定夹具 8 vCPU, 32 GiB RAM, 100 GiB SSD, normal primary, role defaults，实际渲染 Pigsty 四套完整 Patroni 模板。PG18 中共有 94 个核心参数至少在一套模板中不同于上游 boot_val。

重要

这是模板建群时写入 Patroni DCS 或节点本地层的事实快照，不代表存量集群的当前有效值。pg_parameters 还可通过 postgresql.auto.conf 覆盖。

PG18 差异总表

参数 上游 boot OLTP OLAP CRIT TINY
archive_command "" (empty string) pgbackrest --stanza=fixture archive-push %p Δ pgbackrest --stanza=fixture archive-push %p Δ pgbackrest --stanza=fixture archive-push %p Δ pgbackrest --stanza=fixture archive-push %p Δ
archive_mode off on Δ on Δ on Δ on Δ
archive_timeout 0 s 300 Δ 300 Δ 300 Δ 300 Δ
autovacuum_analyze_scale_factor 0.1 0.04 Δ 0.04 Δ 0.04 Δ —
autovacuum_analyze_threshold 50 250 Δ 500 Δ 250 Δ 250 Δ
autovacuum_freeze_max_age 200000000 1000000000 Δ 1000000000 Δ 1000000000 Δ 1000000000 Δ
autovacuum_max_workers 3 3 = 3 = 3 = 2 Δ
autovacuum_vacuum_cost_delay 2 ms -1 Δ -1 Δ -1 Δ -1 Δ
autovacuum_vacuum_scale_factor 0.2 0.08 Δ 0.08 Δ 0.08 Δ —
autovacuum_vacuum_threshold 50 500 Δ 1000 Δ 500 Δ 500 Δ
bgwriter_delay 200 ms 10ms Δ 10ms Δ 10ms Δ 10ms Δ
bgwriter_lru_maxpages 100 800 Δ 800 Δ 800 Δ 800 Δ
bgwriter_lru_multiplier 2 5.0 Δ 5.0 Δ 5.0 Δ 5.0 Δ
checkpoint_completion_target 0.9 0.95 Δ 0.95 Δ 0.95 Δ 0.95 Δ
checkpoint_timeout 300 s (5 min) 15min Δ 15min Δ 15min Δ 15min Δ
commit_delay 0 20 Δ 20 Δ 20 Δ 20 Δ
commit_siblings 5 10 Δ 10 Δ 10 Δ 10 Δ
deadlock_timeout 1000 ms (1 s) 50ms Δ 50ms Δ 50ms Δ 50ms Δ
default_statistics_target 100 400 Δ 1000 Δ 400 Δ 200 Δ
effective_cache_size 524288 8kB (4 GiB (524288 × 8kB)) 24576MB Δ 24576MB Δ 24576MB Δ 24576MB Δ
effective_io_concurrency 16 200 Δ 200 Δ 200 Δ 200 Δ
enable_partitionwise_aggregate off — on Δ — —
enable_partitionwise_join off — on Δ — —
file_copy_method copy clone Δ clone Δ clone Δ clone Δ
hash_mem_multiplier 2 8.0 Δ 8.0 Δ 8.0 Δ 8.0 Δ
hot_standby_feedback off on Δ on Δ on Δ on Δ
idle_in_transaction_session_timeout 0 ms 10min Δ 0 = 1min Δ 10min Δ
idle_replication_slot_timeout 0 s 7d Δ 7d Δ 3d Δ 7d Δ
io_workers 3 4 Δ 4 Δ 4 Δ 3 =
log_autovacuum_min_duration 600000 ms (10 min) 1s Δ 1s Δ 1s Δ 1s Δ
log_connections "" (empty string) authorization Δ authorization Δ receipt,authentication,authorization Δ —
log_destination stderr csvlog Δ csvlog Δ csvlog Δ csvlog Δ
log_directory log /pg/log/postgres Δ /pg/log/postgres Δ /pg/log/postgres Δ /pg/log/postgres Δ
log_disconnections off — — on Δ —
log_file_mode 384 0640 Δ 0640 Δ 0640 Δ 0640 Δ
log_filename postgresql-%Y-%m-%d_%H%M%S.log postgresql-%a.log Δ postgresql-%a.log Δ postgresql-%a.log Δ postgresql-%a.log Δ
log_lock_failures off on Δ — on Δ —
log_lock_waits off on Δ on Δ on Δ on Δ
log_min_duration_statement -1 ms 100 Δ 1000 Δ 100 Δ 100 Δ
log_replication_commands off on Δ on Δ on Δ on Δ
log_rotation_size 10240 kB (10 MiB) 0 Δ 0 Δ 0 Δ 0 Δ
log_statement none ddl Δ ddl Δ ddl Δ ddl Δ
log_temp_files -1 kB 1024 Δ 1024 Δ 1024 Δ 1024 Δ
log_timezone GMT UTC Δ UTC Δ UTC Δ UTC Δ
log_truncate_on_rotation off on Δ on Δ on Δ on Δ
logging_collector off on Δ on Δ on Δ on Δ
maintenance_io_concurrency 16 100 Δ 100 Δ 100 Δ 100 Δ
maintenance_work_mem 65536 kB (64 MiB) 2048MB Δ 4096MB Δ 2048MB Δ 2048MB Δ
max_connections 100 500 Δ 500 Δ 500 Δ 250 Δ
max_locks_per_transaction 64 500 Δ 1000 Δ 500 Δ 250 Δ
max_logical_replication_workers 4 8 Δ 8 Δ 8 Δ 8 Δ
max_parallel_maintenance_workers 2 3 Δ 3 Δ 3 Δ 2 =
max_parallel_workers 8 4 Δ 7 Δ 4 Δ 4 Δ
max_parallel_workers_per_gather 2 2 = 4 Δ 0 Δ 0 Δ
max_replication_slots 10 50 Δ 50 Δ 50 Δ 50 Δ
max_slot_wal_keep_size -1 MB 30GB Δ 30GB Δ 30GB Δ 30GB Δ
max_standby_archive_delay 30000 ms (30 s) 10min Δ 10min Δ 10min Δ 10min Δ
max_standby_streaming_delay 30000 ms (30 s) 3min Δ 3min Δ 3min Δ 3min Δ
max_sync_workers_per_subscription 2 6 Δ 6 Δ 6 Δ 6 Δ
max_wal_senders 10 50 Δ 50 Δ 50 Δ 50 Δ
max_wal_size 1024 MB (1 GiB) 20GB Δ 20GB Δ 20GB Δ 20GB Δ
max_worker_processes 8 24 Δ 28 Δ 24 Δ 20 Δ
min_parallel_index_scan_size 64 8kB (512 KiB (64 × 8kB)) 2MB Δ — 2MB Δ —
min_parallel_table_scan_size 1024 8kB (8 MiB (1024 × 8kB)) 32MB Δ — 32MB Δ —
min_wal_size 80 MB (80 MiB) 5GB Δ 5GB Δ 5GB Δ 5GB Δ
output_plugin_libraries pgoutput, test_decoding pgoutput, test_decoding, wal2json Δ pgoutput, test_decoding, wal2json Δ pgoutput, test_decoding, wal2json Δ pgoutput, test_decoding, wal2json Δ
parallel_setup_cost 1000 2000 Δ — 2000 Δ —
parallel_tuple_cost 0.1 0.2 Δ — 0.2 Δ —
random_page_cost 4 1.1 Δ 1.1 Δ 1.1 Δ 1.1 Δ
shared_buffers 16384 8kB (128 MiB (16384 × 8kB)) 8192MB Δ 8192MB Δ 8192MB Δ 8192MB Δ
shared_preload_libraries "" (empty string) pg_stat_statements, auto_explain Δ pg_stat_statements, auto_explain Δ pg_stat_statements, auto_explain Δ pg_stat_statements, auto_explain Δ
ssl off on Δ on Δ on Δ on Δ
ssl_ca_file "" (empty string) /pg/cert/ca.crt Δ /pg/cert/ca.crt Δ /pg/cert/ca.crt Δ /pg/cert/ca.crt Δ
ssl_cert_file server.crt /pg/cert/server.crt Δ /pg/cert/server.crt Δ /pg/cert/server.crt Δ /pg/cert/server.crt Δ
ssl_key_file server.key /pg/cert/server.key Δ /pg/cert/server.key Δ /pg/cert/server.key Δ /pg/cert/server.key Δ
superuser_reserved_connections 3 10 Δ 10 Δ 10 Δ 10 Δ
sync_replication_slots off true Δ true Δ true Δ true Δ
temp_file_limit -1 kB 5GB Δ 20GB Δ 5GB Δ 5GB Δ
track_activity_query_size 1024 B (1 KiB) 8192 Δ 8192 Δ 32768 Δ 8192 Δ
track_commit_timestamp off on Δ on Δ on Δ on Δ
track_cost_delay_timing off — on Δ on Δ —
track_functions none all Δ all Δ all Δ all Δ
track_io_timing off on Δ on Δ on Δ on Δ
unix_socket_directories /var/run/postgresql /var/run/postgresql, /tmp Δ /var/run/postgresql, /tmp Δ /var/run/postgresql, /tmp Δ /var/run/postgresql, /tmp Δ
vacuum_cost_delay 0 ms 20ms Δ 10ms Δ 20ms Δ 20ms Δ
vacuum_cost_limit 200 2000 Δ 10000 Δ 2000 Δ 2000 Δ
wal_buffers -1 8kB 16MB Δ 16MB Δ 16MB Δ 16MB Δ
wal_compression off lz4 Δ lz4 Δ lz4 Δ lz4 Δ
wal_level replica logical Δ logical Δ logical Δ logical Δ
wal_log_hints off on Δ on Δ on Δ on Δ
wal_receiver_status_interval 10 s 1s Δ 1s Δ 1s Δ 1s Δ
wal_writer_delay 200 ms 20ms Δ 20ms Δ 10ms Δ 20ms Δ
wal_writer_flush_after 128 8kB (1 MiB (128 × 8kB)) 1MB = 1MB = 0 Δ 1MB =
work_mem 4096 kB (4 MiB) 64MB Δ 64MB Δ 64MB Δ 32MB Δ

Δ 表示模板值不同于上游 boot 默认，= 表示显式声明但等于上游，— 表示未修改。

显式声明但未改变默认值

以下参数至少被一套 PG18 模板显式声明，但所有已声明值都等于上游 boot 默认。它们不是“未修改”，因此单独保留用于审计。

autovacuum_naptime · autovacuum_vacuum_cost_limit · huge_pages · io_method · log_checkpoints · log_rotation_age · max_prepared_transactions · password_encryption · wal_receiver_timeout

历代覆盖统计

PG OLTP OLAP CRIT TINY
PG9.0 58 / 53 58 / 53 61 / 56 56 / 52
PG9.1 61 / 56 61 / 56 64 / 59 59 / 55
PG9.2 66 / 61 66 / 61 69 / 64 64 / 60
PG9.3 68 / 62 68 / 62 71 / 65 66 / 61
PG9.4 72 / 65 72 / 65 75 / 68 70 / 64
PG9.5 76 / 69 76 / 69 79 / 72 74 / 68
PG9.6 81 / 73 79 / 70 84 / 76 77 / 69
PG10 86 / 77 82 / 73 89 / 82 80 / 73
PG11 87 / 78 85 / 76 90 / 83 81 / 73
PG12 87 / 78 85 / 76 90 / 83 81 / 73
PG13 90 / 81 88 / 79 93 / 86 84 / 76
PG14 91 / 81 89 / 79 94 / 86 85 / 76
PG15 92 / 81 90 / 79 95 / 86 86 / 76
PG16 92 / 81 90 / 79 94 / 85 86 / 76
PG17 93 / 82 91 / 80 95 / 86 87 / 77
PG18 99 / 87 97 / 85 101 / 91 91 / 79
PG19 Beta 3 98 / 85 96 / 83 100 / 89 90 / 78

每个单元格为“显式声明数 / 不同于上游 boot 数”。

来源与解释边界

注意

每个参数页面中的 Pigsty 理由属于 Advice，并在维护者确认前标记“待人工复核”；本页只汇总可重复渲染的取值事实。