一、postgresql 下載
pg官網：postgres.org
一般推薦用原始碼安裝，下載 .tar.gz 包
二、安裝
本文以12.6版本安裝為例：2.1、安裝前要求和環境配置
# 1、要求GNU make版本3.80或以上（GNU make有時以名字gmake安裝），要測試make版本可以使用以下命令（如果是安裝其他版本的pg具體要求make的版本詳情見官網對應版本的安裝文件）：
[root@xl001 ~]# make --version
GNU Make 3.82
# 2、安裝依賴包
yum install -y gcc gcc-c++ epel-release llvm5.0 llvm5.0-devel clang libicu-devel perl-ExtUtils-Embed readline readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel openldap-devel systemd-devel tcl-devel python-devel
正常情況下，OS已經幫我們把一些包已經裝好了，只需要安裝以下三個包就好
yum -y install readline-devel zlib-devel gcc
# 3、關閉防火牆
systemctl disable firewalld
systemctl stop firewalld
# 4、建立postgres使用者
useradd postgres
passwd postgres # 設定密碼
# 5、系統引數調優
# 5.1、修改系統核心引數
vi /etc/sysctl.conf
# 主要是修改三個引數
kernel.shmall=expr `free |grep Mem|awk '{print $2 *1024}'` / `getconf PAGE_SIZE`
kernel.shmmax=free |grep Mem|awk '{print $2 *1024}'
vm.nr_hugepages
引數解釋：
kernel.shmall 表示核心可以為共享記憶體分配的最大頁數（pages）,這裡的“頁”是指核心的頁面大小，預設通常是 4KB。
kernel.shmmax 設定了單個共享記憶體段的最大最大位元組數
vm.nr_hugepages 引數用於指定系統中可以分配的大頁數量
# 其他引數
fs.file-max = 6815744
kernel.sem = 10000 10240000 10000 1024
kernel.shmmni = 819200
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 40960000
vm.dirty_ratio=20
vm.dirty_background_ratio=3
vm.dirty_writeback_centisecs=3000
vm.dirty_expire_centisecs=500
vm.swappiness=10
vm.min_free_kbytes=524288
sysctl -p #即時生效
# 修改最大程序數和檔案控制代碼數
vim /etc/security/limits.conf
* soft nofile 65535
* hard nofile 65535
* soft nproc 65535
* hard nproc 65535
postgres soft nofile 65535
postgres hard nofile 65535
postgres soft nproc 65535
postgres hard nproc 65535
# 6、建立目錄
mkdir -p /opt/pgsql-12.6
mkdir -p /opt/pgdata/data
mkdir -p /opt/pgdata/wal
mkdir -p /opt/pgdata/archive
mkdir -p /opt/pgdata/logs
chown -R postgres:postgres /opt/pgsql-12.6/ /opt/pgdata
# 7、配置postgres環境變數
su - postgres
vim .bash_profile
export PGHOME=/opt/pgsql-12.6
export PGDATA=/opt/pgdata/data
export PGHOST=/opt/pgdata/data
export PGUSER=postgres
export PGPORT=5432
export PATH=$HOME/bin:$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
source .bash_profile
2.2、安裝
# 1、上傳安裝包並解壓
tar -zxvf postgresql-12.6.tar.gz
cd postgresql-12.6
./configure --prefix=/opt/pgsql-12.6 --with-pgport=5432 # 設定安裝目錄和埠
# 解釋
./configure 這一步可以設定一些引數，具體可./configure -h 檢視
# 2、安裝
gmake world #這個命令是全部功能安裝，也可以精細化部署，具體可以參考官網
gmake install-world
chown -R postgres:postgres /opt/pgsql-12.6
# 3、初始化資料庫
/opt/pgsql-12.6/bin/initdb -D /opt/pgdata/data
# 4、編輯配置檔案pg_hba.conf，此配置控制的是連線資料庫相關，檔案中配置的ip才可以連線資料庫，沒配置的都不能連線
vim /opt/pgdata/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust # 此行ADDRESS沒有值，代表是socket連線
# IPv4 local connections:
host all all 127.0.0.1/32 trust #若這一行沒有，則表示在本地不能用tcp/ip方式連線
host all all 0.0.0.0/0 trust
###如果出現兩個相同的配置，則下面的不起作用，比如
host all all 192.168.225.0/24 reject
host all all 192.168.225.201/24 MD5 #例如這兩個配置，比如有個連線從客戶端192.168.225.201過來，讀到上面的配置，
#因為192.168.225.201滿足192.168.225.0/24，所以連線被拒絕，即使在讀到下面一條可以連線，也不會生效了，最終就是連線被拒絕
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
# 5、修改配置檔案postgresql.conf
vim /opt/pgdata/dataa/postgresql.con
listen_addresses = 'localhost' #把localhost改為*，預設是隻能本地連線
# 6、啟動資料庫
pg_ctl -D /opt/pgdata start
# pg修改配置檔案後可以動態載入，不用重啟資料庫
pg_ctl -D /opt/pgdata/data reload
# 7、關閉資料庫
pg_ctl -D /opt/pgdata stop -m smart|fast|immediate #smart,會等到所有客戶端連線都斷開始之後才關閉，fast會kill掉所有連線回滾所有還未完成的事務然後關閉（預設方式），immediate相當於直接kill -9程序，不建議
pg_ctl -D /opt/pgdata/data stop -mf
# 8、設定密碼
[postgres@xl001 data]$ psql -Upostgres -h127.0.0.1 -dpostgres -p5432 -W
psql (12.6)
Type "help" for help.
postgres=# \password
Enter new password:
Enter it again:
postgres=#
2.3、附一份常用的postgresql.conf配置檔案
點選檢視程式碼
listen_addresses = '0.0.0.0'
port = 5432
max_connections = 8000
unix_socket_directories ='/opt/pgdata/data'
unix_socket_group = 'postgres'
unix_socket_permissions = 0700
shared_buffers = 32GB
dynamic_shared_memory_type = posix
wal_level = logical
max_wal_size = 10GB
min_wal_size = 1GB
archive_mode = always
archive_command = 'cp %p /opt/pgdata/mytest/archive/%f'
max_wal_senders = 10
wal_keep_segments = 5120
wal_buffers = -1
wal_log_hints = on
temp_buffers = 8GB
work_mem = 32MB
maintenance_work_mem = 8GB
effective_io_concurrency = 200
max_worker_processes = 16
max_parallel_workers = 16
max_replication_slots = 8
hot_standby = on
synchronous_commit = on
logging_collector = on
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min
log_destination = csvlog
log_directory = '/opt/pgdata/logs'
log_filename = 'postgresql-%Y-%m-%d.log'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
log_min_messages = warning
log_min_duration_statement = 30s
log_checkpoints = on
log_duration = off
log_lock_waits = on
log_statement = 'ddl'
log_timezone = 'PRC'
track_activities = on
track_counts = on
track_io_timing = on
track_functions = all
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'en_US.utf8'
lc_monetary = 'en_US.utf8'
lc_numeric = 'en_US.utf8'
lc_time = 'en_US.utf8'
random_page_cost = 1.1
autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_vacuum_cost_delay = 0
autovacuum_max_workers = 4
autovacuum_naptime = 15
default_text_search_config = 'pg_catalog.english'
shared_preload_libraries = 'pg_stat_statements,pgstattuple,pg_buffercache,postgres_fdw'
至此，postgresql 安裝完畢