# UTF-8 encoding ## 此份配置适合小内存的开发环境 ## [xx]假定用于4G内存的实际环境。 # 内存单位: kB MB GB # 时间单位: ms s min h d # 这个文件的位置可以在 postgres 命令行上通过 # "-c config_file=/path/to/postgresql.conf" 指定 #--------------------------------------------------------------------------- # 文件位置 #--------------------------------------------------------------------------- data_directory = '/data' hba_file = '/etc/postgresql/pg_hba.conf' ident_file = '/etc/postgresql/pg_ident.conf' external_pid_file = '/var/run/postgresql.pid' #--------------------------------------------------------------------------- # 连接和认证 #--------------------------------------------------------------------------- # 连接设置 listen_addresses = '127.0.0.1' # 自动清理使用 INET socket 进行链接，phpPGadmin也使用它。 port = 5432 max_connections = 5 #[xx]max_connections = 1000 #300kB #建议设为与apache的MaxClients值相等(假定每个接入请求平均使用一个数据库链接) superuser_reserved_connections = 3 #至少要为自动清理留出1个槽位 unix_socket_directory = '/dev/shm/pg_socket' # 常规PHP程序使用 UNIX socket 进行链接。 #unix_socket_group = 'www' unix_socket_permissions = 0770 # 安全和认证 authentication_timeout = 5 ssl = off password_encryption = on db_user_namespace = off #--------------------------------------------------------------------------- # 资源消耗 总内存消耗?: max_connections + max_prepared_transactions + shared_buffers + wal_buffers + max_fsm_relations + max_fsm_pages #--------------------------------------------------------------------------- # 内存 shared_buffers = 32MB #[xx]shared_buffers = 512MB temp_buffers = 8MB #[xx]temp_buffers = 64MB max_prepared_transactions = 5 #[xx]max_prepared_transactions = 250 #70kB work_mem = 8MB maintenance_work_mem = 16MB #[xx]maintenance_work_mem = 512MB max_stack_depth = 3MB #[xx]max_stack_depth = 4MB # 自由空间映射 max_fsm_pages = 1638400 #[xx]max_fsm_pages = 13107200 #75MB max_fsm_relations = 1000 #[xx]max_fsm_relations = 8000 #625kB # 内核资源 max_files_per_process = 1000 shared_preload_libraries = '/lib/postgresql/pgcrypto.so' # 基于开销的清理延迟 vacuum_cost_delay = 1s vacuum_cost_limit = 2000 # 后端写进程 bgwriter_delay = 1s bgwriter_lru_maxpages = 500 bgwriter_lru_multiplier = 2.5 #--------------------------------------------------------------------------- # 预写式日志 #--------------------------------------------------------------------------- # 设置 fsync = on synchronous_commit = off full_page_writes = on wal_buffers = 256kB wal_writer_delay = 317ms commit_delay = 0 # 检查点 checkpoint_segments = 6 checkpoint_timeout = 20min checkpoint_completion_target = 0.5 checkpoint_warning = 10min # WAL 归档 archive_mode = off #--------------------------------------------------------------------------- # 查询规划 #--------------------------------------------------------------------------- # 规划器开销常量 seq_page_cost = 10.0 random_page_cost = 20.0 cpu_tuple_cost = 1.0 cpu_index_tuple_cost = 0.4 cpu_operator_cost = 0.2 effective_cache_size = 64MB #[xx]effective_cache_size = 512MB # 基因查询优化器 geqo = on geqo_threshold = 11 geqo_effort = 6 # 其它规划器选项 default_statistics_target = 250 constraint_exclusion = off from_collapse_limit = 8 join_collapse_limit = 8 #--------------------------------------------------------------------------- # 错误报告和日志 #--------------------------------------------------------------------------- # 日志记录位置 log_destination = 'stderr' logging_collector = on log_directory = '/var/log/postgresql' log_filename = 'postgresql-%M.log' #[xx]log_filename = 'postgresql-%d.log' log_rotation_age = 10min #[xx]log_rotation_age = 1d log_rotation_size = 0 log_truncate_on_rotation = on # 记录什么 client_min_messages = 'notice' log_min_messages = 'notice' log_error_verbosity = 'default' log_min_error_statement = 'warning' log_min_duration_statement = 200ms silent_mode = on log_checkpoints = on log_connections = off log_disconnections = off log_duration = off log_hostname = off log_line_prefix = '<%u:%d>' log_lock_waits = on log_statement = 'mod' log_temp_files = 0 #[xx]log_temp_files = 50MB log_timezone = 'UTC' #--------------------------------------------------------------------------- # 运行时统计与监控 #--------------------------------------------------------------------------- track_activities = on track_counts = on update_process_title = off #--------------------------------------------------------------------------- # 自动清理 [依赖于本地回环接口和/etc/hosts] #--------------------------------------------------------------------------- autovacuum = on log_autovacuum_min_duration = 3s autovacuum_max_workers = 3 autovacuum_naptime = 5min autovacuum_vacuum_threshold = 100 autovacuum_analyze_threshold = 70 autovacuum_vacuum_scale_factor = 0.3 autovacuum_analyze_scale_factor = 0.2 autovacuum_freeze_max_age = 100000000 autovacuum_vacuum_cost_delay = 200 autovacuum_vacuum_cost_limit = 1000 #--------------------------------------------------------------------------- # 客户端连接缺省 #--------------------------------------------------------------------------- # 语句行为 search_path = '"$user",public' default_tablespace = '' temp_tablespaces = '' check_function_bodies = on default_transaction_isolation = 'read committed' default_transaction_read_only = off statement_timeout = 0 #session_replication_role = 'origin' vacuum_freeze_min_age = 50000000 #xmlbinary = 'base64' #xmloption = 'content' # 区域和格式化 datestyle = 'iso, ymd' timezone = 'UTC' timezone_abbreviations = 'Default' extra_float_digits = 2 client_encoding = 'UTF-8' lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' #default_text_search_config = 'pg_catalog.simple' # 其它缺省 explain_pretty_print = on dynamic_library_path = '/lib/postgresql' gin_fuzzy_search_limit = 500 local_preload_libraries = '' #--------------------------------------------------------------------------- # 锁管理 #--------------------------------------------------------------------------- deadlock_timeout = 4s max_locks_per_transaction = 96 #--------------------------------------------------------------------------- # 版本和平台兼容性 #--------------------------------------------------------------------------- add_missing_from = off array_nulls = on backslash_quote = off default_with_oids = off escape_string_warning = on regex_flavor = advanced sql_inheritance = on standard_conforming_strings = on transform_null_equals = off #--------------------------------------------------------------------------- # 自定义的选项[由附加模块添加(比如pgcrypto)的选项] #--------------------------------------------------------------------------- #custom_variable_classes = 'pgcrypto' #因为pgcrypto没有指令可用，所以就直接注释掉了