install
官网网站
https://www.postgresql.org/download/
https://www.postgresql.org/ftp/source/
centos
先安装PostgreSQL的YUM源
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
安装server
yum install -y postgresql13-server
初始化数据库
/usr/pgsql-13/bin/postgresql-13-setup initdb
PostgreSQL 初始化数据库之后，默认的数据目录是在/var/lib/pgsql
移动原始数据目录
mv /var/lib/pgsql /data/
创建软连
cd /var/lib && ln -s /data/databases/pgsql pgsql
默认密码设置为“postgres”
配置文件
/etc/postgresql/13/main/postgresql.conf
postgresql.conf
关键参数
#connection control
listen_addresses = '*'
max_connections = 2000
superuser_reserved_connections = 10
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
password_encryption = md5
#memory management
shared_buffers = 16GB #推荐操作系统物理内存的1/4
max_prepared_transactions = 2000
work_mem = 8MB
maintenance_work_mem = 2GB
autovacuum_work_mem = 1GB
dynamic_shared_memory_type = posix
max_files_per_process = 24800
effective_cache_size = 32GB #推荐操作系统物理内存的1/2
#write optimization
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 10.0
bgwriter_flush_after = 512kB
effective_io_concurrency = 0
max_worker_processes = 256
max_parallel_maintenance_workers = 6
max_parallel_workers_per_gather = 0
max_parallel_workers = 28
#wal optimization
synchronous_commit = remote_write
full_page_writes = on
wal_compression = on
wal_writer_delay = 10ms
wal_writer_flush_after = 1MB
commit_delay = 10
commit_siblings = 5
checkpoint_timeout = 30min
max_wal_size = 32GB
min_wal_size = 16GB
archive_mode = on
max_wal_senders = 64
wal_keep_segments = 15
wal_sender_timeout = 60s
max_replication_slots = 64
hot_standby_feedback = off
#log optimization
log_destination = 'csvlog'
logging_collector = on
log_directory = '/pg12.4/logs' # 日志存放路径，提前规划在系统上创建好
log_filename = 'postgresql-%a.log'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 1GB
#audit settings
log_min_duration_statement = 5s
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose
log_line_prefix = '%m [%p] %q %u %d %a %r %e '
log_statement = 'ddl'
log_timezone = 'PRC'
track_io_timing = on
track_activity_query_size = 2048
#autovacuum
autovacuum = on
vacuum_cost_delay = 0
old_snapshot_threshold = 6h
log_autovacuum_min_duration = 0
autovacuum_max_workers = 8
autovacuum_vacuum_scale_factor = 0.02
autovacuum_analyze_scale_factor = 0.01
autovacuum_freeze_max_age = 1200000000
autovacuum_multixact_freeze_max_age = 1250000000
autovacuum_vacuum_cost_delay = 0ms
#system environment
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'en_US.utf8'
lc_monetary = 'en_US.utf8'
lc_numeric = 'en_US.utf8'
lc_time = 'en_US.utf8'
default_text_search_config = 'pg_catalog.english'
