一、Windows系統下安裝
1.下載安裝包
登錄PostgreSQL: Downloads官網：
選擇14.12版本，點擊下載：
2.安裝PostgrSQL14.12
雙擊exe安裝包程序，準備安裝：
選擇安裝路徑：
選擇想安裝的工具：
選擇數據存儲路徑：
設置超管密鑰：
設置端口，一般是5432：
Locale建議使用C的本地化規則：
確認配置信息：
開始執行安裝：
3.調整PostgreSQL配置
3.1pg_hba.conf 客戶端身份驗證規則配置
在pg_hba.conf文件中添加以下配置：
# postgres for localhost:
local all postgres scram-sha-256
host all postgres 127.0.0.1/32 scram-sha-256
host all postgres 0.0.0.0/0 reject
# remote connections:
host all all 0.0.0.0/0 scram-sha-256
3.2postgresql.conf 服務參數配置
在postgresql.conf中主要調整以下配置：
listen_addresses = '*' # 監聽地址
port = 5432 # 端?號
max_connections = 1000 # 最?連接數
superuser_reserved_connections = 10 # 預留給超管?戶的連接數
password_encryption = scram-sha-256 # 密碼加密?式
shared_buffers = 1024MB # 允許使?的內存，通常設置為物理內存的25%
timezone = 'Asia/Shanghai' # 時區，根據實際項?地理位置修改
log_timezone = 'Asia/Shanghai' # ?志時區，根據實際項?地理位置修改
4.重啟PostgreSQL服務
二、Linux系統下安裝（以CentOS7為例）
1.安裝依賴環境
yum install -y perl-devel perl-ExtUtils-Embed systemd-devel readline-devel uuid-devel zlib-devel clang-devel llvm-devel perlExtUtils-Embed tcl-devel libicu-devel libxml2-devel libxslt-devel python-devel python3-devel gcc gcc-c++ llvm3.9-devel openssl-devel lz4-devel pam-devel openldap-devel cmake bison flex
2.規劃存儲路徑
mkdir /opt/pgsql/source -p # pgsql源碼包存放路徑
mkdir /opt/pgsql/extensions -p # pgsql插件存放路徑
mkdir -p /mnt/data/pgsql/pgsql5432 # pgsql數據?錄
mkdir -p /mnt/data/pgsql/backup/{backup-db,backup-tmp} # pgsql備份?錄
3.PG環境配置
3.1創建用戶和用戶組、主目錄
useradd -d /home/postgres -s /bin/bash -U -m postgres
3.2配置用戶環境變量
cat >> /home/postgres/.bash_profile << EOF
# PostgreSQL
export PGHOME=/usr/local/pgsql
export PATH=$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PGDATA=/mnt/data/pgsql/pgsql5432
export PGHOST=/tmp
export PGPORT=5432
EOF
3.3授權相關目錄
chmod 700 /mnt/data/pgsql/pgsql5432
chmod -R 700 /mnt/data/pgsql/backup
chown postgres:postgres -R /opt/pgsql /mnt/data/pgsql
4.下載二進制安裝包
wget -P /opt/pgsql/source https://ftp.postgresql.org/pub/source/v14.8/postgresql-14.8.tar.gz --no-check-certificate
5.包完整性校驗
md5sum postgresql-14.8.tar.gz
# MD5校驗值：05a8078ee17d4f00779138767b802065
sha256sum postgresql-14.8.tar.gz
# SHA256校驗值：a3c32ff8168832d9637eb870f6e98f98506797fe5942555d70cd77558949a844
操作如下圖所示：
6.解壓與編譯安裝
tar xf postgresql-14.8.tar.gz #解壓
cd postgresql-14.8 #進到主目錄下
#下面進行編譯與安裝
./configure --with-systemd --with-uuid=ossp --with-perl --with-python --with-tcl --with-icu --with-openssl --with-libxml --with-libxslt --with-lz4 --prefix=/opt/pgsql/pgsql-14.8 make -j 4 world && make -j 4 install-worldln -s /opt/pgsql/pgsql-14.8 /usr/local/pgsql //創建軟鏈接
7.安裝檢查與驗證
su - postgres #進到postgres用戶下
cd /opt/pgsql/source/postgresql-14.8/ #進入pg主目錄
make check # 安裝成功后，測試?下編譯的功能是否正常，全部正常會在末尾輸出ALL tests passed
8.初始化數據庫
建議：字符編碼使UTF8，本地化使C，認證式使scram-sha-256
initdb -E UTF8 --locale=C -U postgres -W -A scram-sha-256 --data-checksums
9.初始化配置文件
cd /mnt/data/pgsql/pgsql5432/
mv postgresql.conf postgresql.conf.bak
mv pg_hba.conf pg_hba.conf.bak
touch postgresql.conf pg_hba.conf
創建兩個同名文件，配置如下：
pg_hba.conf：
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all scram-sha-256
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# replication connections:
local replication all scram-sha-256
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
# postgres for localhost:
local all postgres scram-sha-256
host all postgres 127.0.0.1/32 scram-sha-256
host all postgres 0.0.0.0/0 reject
# remote connections:
host all all 0.0.0.0/0 scram-sha-256
postgresql.conf：
listen_addresses = '*'
port = 5432
max_connections = 1000
superuser_reserved_connections = 10
unix_socket_directories = '/tmp'
tcp_keepalives_idle = 180
tcp_keepalives_interval = 10
tcp_keepalives_count = 3
password_encryption = scram-sha-256
shared_buffers = 1024MB
temp_buffers = 8MB
max_prepared_transactions = 50
work_mem = 4MB
maintenance_work_mem = 64MB
dynamic_shared_memory_type = posix
max_worker_processes = 8
wal_level = logical
fsync = on
synchronous_commit = remote_write
wal_sync_method = fsync
full_page_writes = on
max_wal_size = 5GB
min_wal_size = 80MB
max_wal_senders = 30
max_replication_slots = 10
hot_standby = on //允許只讀
max_logical_replication_workers = 4
log_destination = 'stderr' //表示 PostgreSQL 將日志信息輸出到標準錯誤輸出流
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_file_mode = 0600
log_rotation_age = 1d
log_truncate_on_rotation = on
log_checkpoints = on
log_timezone = 'Asia/Shanghai'
autovacuum = on //自動回收
idle_session_timeout = 1200000
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
default_text_search_config = 'pg_catalog.english'
10.配置systemd服務托管
cat > /usr/lib/systemd/system/pgsql.service << EOF
[Unit]
Description=PostgreSQL database server 14
After=network-online.target
Wants=network-online.target[Install]
WantedBy=multi-user.target[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGPORT=5432
Environment=PGDATA=/mnt/data/pgsql/pgsql5432
ExecStart=/usr/local/pgsql/bin/pg_ctl start -D ${PGDATA} -s -w -t 300
ExecStop=/usr/local/pgsql/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/local/pgsql/bin/pg_ctl reload -D ${PGDATA} -s
TimeoutSec=300
Restart=on-failure
RestartSec=3
OOMScoreAdjust=-1000
LimitNOFILE=65535
LimitNPROC=65535
EOF
11.啟動pgsql.service
systemctl daemon-reload
systemctl enable pgsql
systemctl start pgsql
systemctl status pgsql
12.連接postgresql數據庫測試
su - postgres #進入postgres用戶下
psql -h 127.0.0.1 -p 5432 postgres #連接數據庫
這里執行 pqsql 和 psql -h 127.0.0.1 -p 5432 postgres 命令是同樣的效果
如果想修改postgres用戶的密碼，可以使用以下語句進行修改：
postgres=# alter user postgres with password '123';
ALTER ROLE
postgres=# quit
