柚子快報(bào)激活碼778899分享：數(shù)據(jù)庫 PostgreSQL

http://yzkb.51969.com/

一、Windows系統(tǒng)下安裝

1.下載安裝包

登錄PostgreSQL: Downloads官網(wǎng)：

選擇14.12版本，點(diǎn)擊下載：

2.安裝PostgrSQL14.12

雙擊exe安裝包程序，準(zhǔn)備安裝：

選擇安裝路徑：

選擇想安裝的工具：

?選擇數(shù)據(jù)存儲(chǔ)路徑：

設(shè)置超管密鑰：

?設(shè)置端口，一般是5432：

Locale建議使用C的本地化規(guī)則：

確認(rèn)配置信息：

開始執(zhí)行安裝：

3.調(diào)整PostgreSQL配置

3.1?pg_hba.conf 客戶端身份驗(yàn)證規(guī)則配置

在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.2?postgresql.conf 服務(wù)參數(shù)配置

在postgresql.conf中主要調(diào)整以下配置：

listen_addresses = '*' # 監(jiān)聽地址

port = 5432 # 端?號(hào)

max_connections = 1000 # 最?連接數(shù)

superuser_reserved_connections = 10 # 預(yù)留給超管?戶的連接數(shù)

password_encryption = scram-sha-256 # 密碼加密?式

shared_buffers = 1024MB # 允許使?的內(nèi)存，通常設(shè)置為物理內(nèi)存的25%

timezone = 'Asia/Shanghai' # 時(shí)區(qū)，根據(jù)實(shí)際項(xiàng)?地理位置修改

log_timezone = 'Asia/Shanghai' # ?志時(shí)區(qū)，根據(jù)實(shí)際項(xiàng)?地理位置修改

4.重啟PostgreSQL服務(wù)

二、Linux系統(tǒng)下安裝（以CentOS7為例）?

1.安裝依賴環(huán)境

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.規(guī)劃存儲(chǔ)路徑

mkdir /opt/pgsql/source -p # pgsql源碼包存放路徑

mkdir /opt/pgsql/extensions -p # pgsql插件存放路徑

mkdir -p /mnt/data/pgsql/pgsql5432 # pgsql數(shù)據(jù)?錄

mkdir -p /mnt/data/pgsql/backup/{backup-db,backup-tmp} # pgsql備份?錄

3.PG環(huán)境配置

3.1?創(chuàng)建用戶和用戶組、主目錄

useradd -d /home/postgres -s /bin/bash -U -m postgres

3.2?配置用戶環(huán)境變量

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?授權(quán)相關(guān)目錄

chmod 700 /mnt/data/pgsql/pgsql5432

chmod -R 700 /mnt/data/pgsql/backup

chown postgres:postgres -R /opt/pgsql /mnt/data/pgsql

4.下載二進(jìn)制安裝包

wget -P /opt/pgsql/source https://ftp.postgresql.org/pub/source/v14.8/postgresql-14.8.tar.gz --no-check-certificate

5.包完整性校驗(yàn)

md5sum postgresql-14.8.tar.gz

# MD5校驗(yàn)值：05a8078ee17d4f00779138767b802065

sha256sum postgresql-14.8.tar.gz

# SHA256校驗(yàn)值：a3c32ff8168832d9637eb870f6e98f98506797fe5942555d70cd77558949a844

操作如下圖所示：

6.解壓與編譯安裝

tar xf postgresql-14.8.tar.gz #解壓

cd postgresql-14.8 #進(jìn)到主目錄下

#下面進(jìn)行編譯與安裝

./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-world

ln -s /opt/pgsql/pgsql-14.8 /usr/local/pgsql //創(chuàng)建軟鏈接

7.安裝檢查與驗(yàn)證

su - postgres #進(jìn)到postgres用戶下

cd /opt/pgsql/source/postgresql-14.8/ #進(jìn)入pg主目錄

make check # 安裝成功后，測(cè)試?下編譯的功能是否正常，全部正常會(huì)在末尾輸出ALL tests passed

8.初始化數(shù)據(jù)庫

建議：字符編碼使?UTF8，本地化使?C，認(rèn)證?式使?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

創(chuàng)建兩個(gè)同名文件，配置如下：

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 將日志信息輸出到標(biāo)準(zhǔn)錯(cuò)誤輸出流

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 //自動(dòng)回收

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服務(wù)托管

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.啟動(dòng)pgsql.service

systemctl daemon-reload

systemctl enable pgsql

systemctl start pgsql

systemctl status pgsql

12.連接postgresql數(shù)據(jù)庫測(cè)試

su - postgres #進(jìn)入postgres用戶下

psql -h 127.0.0.1 -p 5432 postgres #連接數(shù)據(jù)庫

這里執(zhí)行? pqsql 和? psql -h 127.0.0.1 -p 5432 postgres? ?命令是同樣的效果

如果想修改postgres用戶的密碼，可以使用以下語句進(jìn)行修改：

postgres=# alter user postgres with password '123';

ALTER ROLE

postgres=# \quit

柚子快報(bào)激活碼778899分享：數(shù)據(jù)庫 PostgreSQL

http://yzkb.51969.com/

好文閱讀