鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 centos換源+安裝postgresql http://mirror.nsc.liu.se/centos-store/8.2.2004/isos/x86_64/ 鏡像安裝網站 https://mirrors.edge.kernel.org/pu ...
鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站
centos換源+安裝postgresql
http://mirror.nsc.liu.se/centos-store/8.2.2004/isos/x86_64/ 鏡像安裝網站
https://mirrors.edge.kernel.org/pub/software/scm/git/git-2.24.0.tar.xz git版本網站
1 更換阿裡源
[ivanlee@localhost Desktop]$ cd /etc/yum.repos.d
[ivanlee@localhost yum.repos.d]$ ls
CentOS-AppStream.repo CentOS-Devel.repo CentOS-PowerTools.repo
CentOS-Base.repo CentOS-Extras.repo CentOS-Sources.repo
CentOS-centosplus.repo CentOS-fasttrack.repo CentOS-Vault.repo
CentOS-CR.repo CentOS-HA.repo
CentOS-Debuginfo.repo CentOS-Media.repo
首先全部刪除源的所有文件，遇到所有的詢問都是y
[root@localhost yum.repos.d]# rm *.repo
rm: remove regular file 'CentOS-AppStream.repo'? y
·························
安裝新的阿裡源
[root@localhost yum.repos.d]# wget -O /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-vault-8.5.2111.repo
--2022-03-22 13:53:56-- https://mirrors.aliyun.com/repo/Centos-vault-8.5.2111.repo
Resolving mirrors.aliyun.com (mirrors.aliyun.com)... 139.227.225.136, 2408:8744:803:2:3::3fe, 2408:8744:803:2:3::3fd
Connecting to mirrors.aliyun.com (mirrors.aliyun.com)|139.227.225.136|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 2495 (2.4K) [application/octet-stream]
Saving to: ‘/etc/yum.repos.d/CentOS-Base.repo’
/etc/yum.repos.d/CentOS-Base.repo 100%[==========================================================================================================>] 2.44K --.-KB/s in 0s
2022-03-22 13:54:01 (60.0 MB/s) - ‘/etc/yum.repos.d/CentOS-Base.repo’ saved [2495/2495]
[root@localhost yum.repos.d]# yum makecache
CentOS-8.5.2111 - Base - mirrors.aliyun.com 437 kB/s | 4.6 MB 00:10
CentOS-8.5.2111 - Extras - mirrors.aliyun.com 1.0 kB/s | 10 kB 00:10
CentOS-8.5.2111 - AppStream - mirrors.aliyun.com 739 kB/s | 8.4 MB 00:11
Metadata cache created.
如果遇到無法解析網址的情況：
[root@localhost yum.repos.d]# wget -O /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-vault-8.5.2111.repo
--2022-05-07 15:42:13-- https://mirrors.aliyun.com/repo/Centos-vault-8.5.2111.repo
Resolving mirrors.aliyun.com (mirrors.aliyun.com)... failed: Name or service not known.
wget: unable to resolve host address 'mirrors.aliyun.com'
那就對本地/etc/resolv.conf文件進行修改，添加如下欄位
nameserver 8.8.8.8
nameserver 8.8.4.4
再繼續就好了
手動安裝pgsql
[root@localhost yum.repos.d]# dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Last metadata expiration check: 0:00:35 ago on Tue 22 Mar 2022 01:54:51 PM CST.
pgdg-redhat-repo-latest.noarch.rpm 2.0 kB/s | 13 kB 00:06
Dependencies resolved.
=====================================================================================================================================================================================================
Package Architecture Version Repository Size
=====================================================================================================================================================================================================
Installing:
pgdg-redhat-repo noarch 42.0-24 @commandline 13 k
Transaction Summary
=====================================================================================================================================================================================================
Install 1 Package
Total size: 13 k
Installed size: 12 k
Is this ok [y/N]: y
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : pgdg-redhat-repo-42.0-24.noarch 1/1
Verifying : pgdg-redhat-repo-42.0-24.noarch 1/1
Installed products updated.
Installed: pgdg-redhat-repo-42.0-24.noarch Complete!
[root@localhost yum.repos.d]# dnf module list postgresql
PostgreSQL common RPMs for RHEL / Rocky 8 - x86_64 15 B/s | 195 B 00:12
PostgreSQL common RPMs for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: y
PostgreSQL common RPMs for RHEL / Rocky 8 - x86_64 30 kB/s | 619 kB 00:20
PostgreSQL 14 for RHEL / Rocky 8 - x86_64 15 B/s | 195 B 00:12
PostgreSQL 14 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: y
PostgreSQL 14 for RHEL / Rocky 8 - x86_64 11 kB/s | 208 kB 00:19
PostgreSQL 13 for RHEL / Rocky 8 - x86_64 16 B/s | 195 B 00:12
PostgreSQL 13 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: y
PostgreSQL 13 for RHEL / Rocky 8 - x86_64 26 kB/s | 526 kB 00:19
PostgreSQL 12 for RHEL / Rocky 8 - x86_64 15 B/s | 195 B 00:12
PostgreSQL 12 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: y
PostgreSQL 12 for RHEL / Rocky 8 - x86_64 36 kB/s | 694 kB 00:19
PostgreSQL 11 for RHEL / Rocky 8 - x86_64 16 B/s | 195 B 00:12
PostgreSQL 11 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: y
PostgreSQL 11 for RHEL / Rocky 8 - x86_64 41 kB/s | 885 kB 00:21
PostgreSQL 10 for RHEL / Rocky 8 - x86_64 16 B/s | 195 B 00:12
PostgreSQL 10 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: y
PostgreSQL 10 for RHEL / Rocky 8 - x86_64 29 kB/s | 588 kB 00:20
CentOS-8.5.2111 - AppStream - mirrors.aliyun.com
Name Stream Profiles Summary
postgresql 9.6 client, server [d] PostgreSQL server and client module
postgresql 10 [d] client, server [d] PostgreSQL server and client module
postgresql 12 client, server [d] PostgreSQL server and client module
postgresql 13 client, server [d] PostgreSQL server and client module
Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled
[root@localhost ~]# dnf module disable postgresql
PostgreSQL common RPMs for RHEL / Rocky 8 - x86_64 16 B/s | 195 B 00:12
PostgreSQL 14 for RHEL / Rocky 8 - x86_64 16 B/s | 195 B 00:12
PostgreSQL 13 for RHEL / Rocky 8 - x86_64 16 B/s | 195 B 00:12
PostgreSQL 12 for RHEL / Rocky 8 - x86_64 16 B/s | 195 B 00:12
Dependencies resolved.
=====================================================================================================================================================================================================
Package Architecture Version Repository Size
=====================================================================================================================================================================================================
Disabling modules:
postgresql
Transaction Summary
=====================================================================================================================================================================================================
Is this ok [y/N]: y
Complete!
安裝pgsql12
[root@localhost ~]# dnf install postgresql12
Last metadata expiration check: 0:08:39 ago on Tue 22 Mar 2022 02:00:31 PM CST.
All matches were filtered out by modular filtering for argument: postgresql12
Error: Unable to find a match: postgresql12
[root@localhost ~]# dnf config-manager --disable pgdg11
[root@localhost ~]# dnf config-manager --disable pgdg10
[root@localhost ~]# dnf config-manager --disable pgdg96
Error: No matching repo to modify: pgdg96.
[root@localhost ~]# dnf module disable postgresql
PostgreSQL common RPMs for RHEL / Rocky 8 - x86_64 16 B/s | 195 B 00:12
PostgreSQL 14 for RHEL / Rocky 8 - x86_64 16 B/s | 195 B 00:12
PostgreSQL 13 for RHEL / Rocky 8 - x86_64 16 B/s | 195 B 00:12
PostgreSQL 12 for RHEL / Rocky 8 - x86_64 16 B/s | 195 B 00:12
Dependencies resolved.
=====================================================================================================================================================================================================
Package Architecture Version Repository Size
=====================================================================================================================================================================================================
Disabling modules:
postgresql
Transaction Summary
=====================================================================================================================================================================================================
Is this ok [y/N]: y
Complete!
[root@localhost ~]# dnf install postgresql12
Last metadata expiration check: 0:01:29 ago on Tue 22 Mar 2022 02:10:45 PM CST.
Dependencies resolved.
=====================================================================================================================================================================================================
Package Architecture Version Repository Size
=====================================================================================================================================================================================================
Installing:
postgresql12 x86_64 12.10-1PGDG.rhel8 pgdg12 1.7 M
Installing dependencies:
postgresql12-libs x86_64 12.10-1PGDG.rhel8 pgdg12 400 k
Transaction Summary
=====================================================================================================================================================================================================
Install 2 Packages
Total download size: 2.1 M
Installed size: 10 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): postgresql12-libs-12.10-1PGDG.rhel8.x86_64.rpm 50 kB/s | 400 kB 00:08
(2/2): postgresql12-12.10-1PGDG.rhel8.x86_64.rpm 172 kB/s | 1.7 MB 00:09
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 213 kB/s | 2.1 MB 00:09
warning: /var/cache/dnf/pgdg12-64e2194e85277659/packages/postgresql12-12.10-1PGDG.rhel8.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
PostgreSQL 12 for RHEL / Rocky 8 - x86_64 1.6 MB/s | 1.7 kB 00:00
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <[email protected]>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: y
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : postgresql12-libs-12.10-1PGDG.rhel8.x86_64 1/2
Running scriptlet: postgresql12-libs-12.10-1PGDG.rhel8.x86_64 1/2
Installing : postgresql12-12.10-1PGDG.rhel8.x86_64 2/2
Running scriptlet: postgresql12-12.10-1PGDG.rhel8.x86_64 2/2
Verifying : postgresql12-12.10-1PGDG.rhel8.x86_64 1/2
Verifying : postgresql12-libs-12.10-1PGDG.rhel8.x86_64 2/2
Installed products updated.
Installed:
postgresql12-12.10-1PGDG.rhel8.x86_64 postgresql12-libs-12.10-1PGDG.rhel8.x86_64
Complete!
安裝服務端
[root@localhost ~]# dnf install postgresql12-server
Last metadata expiration check: 0:05:33 ago on Tue 22 Mar 2022 02:10:45 PM CST.
Dependencies resolved.
=====================================================================================================================================================================================================
Package Architecture Version Repository Size
=====================================================================================================================================================================================================
Installing:
postgresql12-server x86_64 12.10-1PGDG.rhel8 pgdg12 5.2 M
Transaction Summary
=====================================================================================================================================================================================================
Install 1 Package
Total download size: 5.2 M
Installed size: 21 M
Is this ok [y/N]: y
Downloading Packages:
postgresql12-server-12.10-1PGDG.rhel8.x86_64.rpm 390 kB/s | 5.2 MB 00:13
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 390 kB/s | 5.2 MB 00:13
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Running scriptlet: postgresql12-server-12.10-1PGDG.rhel8.x86_64 1/1
Installing : postgresql12-server-12.10-1PGDG.rhel8.x86_64 1/1
Running scriptlet: postgresql12-server-12.10-1PGDG.rhel8.x86_64 1/1
Verifying : postgresql12-server-12.10-1PGDG.rhel8.x86_64 1/1
Installed products updated.
Installed:
postgresql12-server-12.10-1PGDG.rhel8.x86_64
Complete!
[root@localhost ~]# dnf install postgresql12-contrib
Last metadata expiration check: 0:09:45 ago on Tue 22 Mar 2022 02:10:45 PM CST.
Dependencies resolved.
=====================================================================================================================================================================================================
Package Architecture Version Repository Size
=====================================================================================================================================================================================================
Installing:
postgresql12-contrib x86_64 12.10-1PGDG.rhel8 pgdg12 645 k
Transaction Summary
=====================================================================================================================================================================================================
Install 1 Package
Total download size: 645 k
Installed size: 2.3 M
Is this ok [y/N]: y
Downloading Packages:
postgresql12-contrib-12.10-1PGDG.rhel8.x86_64.rpm 84 kB/s | 645 kB 00:07
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 84 kB/s | 645 kB 00:07
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : postgresql12-contrib-12.10-1PGDG.rhel8.x86_64 1/1
Running scriptlet: postgresql12-contrib-12.10-1PGDG.rhel8.x86_64 1/1
Verifying : postgresql12-contrib-12.10-1PGDG.rhel8.x86_64 1/1
Installed products updated.
Installed:
postgresql12-contrib-12.10-1PGDG.rhel8.x86_64
Complete!
註意：程式安裝目錄是"/usr/pgsql-12"，程式運行目錄是"/var/run/postgresql"，程式運行用戶和組是"postgres:postgres"，"postgres"用戶和組安裝時預設創建。
pgsql的配置
1 設置資料庫預設的數據存儲目錄
資料庫實例的預設存儲目錄是/var/lib/pgsql/12/data/,/var是一個系統目錄，不適合存放大量的業務數據，因此要在初始化資料庫實例之前創建一個存儲目錄
mkdir -p /data/pgsql12-data：因為創建了多級目錄，所以需要-p
[root@localhost /]# mkdir -p /data/pgsql12-data
[root@localhost /]# ls
bin boot data dev etc home lib lib64 lost+found media mnt opt proc root run sbin srv sys tmp usr var
[root@localhost /]# cd data
[root@localhost data]# ll
total 4
drwxr-xr-x. 2 root root 4096 Mar 22 14:27 pgsql12-data
設置數據存儲目錄的所有者用戶和組為postgres:postgres，postgres用戶和組在安裝PostgreSQL12時已創建
[root@localhost data]# chown postgres:postgres /data/pgsql12-data
修改postgreSQL12開機啟動服務配置文件，設置為新的數據存儲目錄
[root@localhost data]# vim /usr/lib/systemd/system/postgresql-12.service <服務配置文件路徑！>
將其中的enviroment變數註釋掉，改為新的（vim如果卡死，按鍵ctrl+q）
2 初始化資料庫實例
進入程式安裝目錄下的bin目錄下，執行postgresql-12-setup initdb命令。
[root@localhost data]# cd /usr/pgsql-12/bin
[root@localhost bin]# ./postgresql-12-setup initdb
Initializing database ... OK
3 啟動資料庫實例服務，並設置為開機自動啟動
[root@localhost bin]# systemctl enable postgresql-12.service
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-12.service → /usr/lib/systemd/system/postgresql-12.service.
[root@localhost bin]# systemctl start postgresql-12.service
4 設置資料庫實例超級管理員賬戶postgres的口令
postgreSQL12安裝完成後postgres的預設口令為空，為空時無法使用該用戶登錄資料庫。
[root@localhost bin]# passwd postgres
Changing password for user postgres.
New password: prs@2018
Retype new password: prs@2018
passwd: all authentication tokens updated successfully.
[root@localhost bin]# su postgres
bash-4.4$ psql
psql (12.10)
Type "help" for help.
postgres=# alter user postgres with password 'xxxxx';
ALTER ROLE
postgres=# \q
bash-4.4$ exit
exit
[root@localhost bin]#
5 設置資料庫實例的遠程訪問策略
postgreSQL12安裝完成後預設只允許本地訪問
設置資料庫實例訪問策略，可以設置多個由主機類型、資料庫、用戶、IP地址組成的策略。
在文件的"# IPv4 local connections"策略中追加一條“允許全部用戶，通過全部網路地址訪問全部資料庫”的策略並保存，策略定義如 下：
vim /data/pgsql12-data/pg_hba.conf "配置文件conf"
image-20220507152653413
設置資料庫實例的監聽埠
監聽地址：*預設是全部地址，預設是localhost ｜｜監聽埠：預設是5432
vim /data/pgsql12-data/postgresql.conf
設置防火牆埠
centOS8預設安裝firewall防火牆，允許5432埠（PostgreSQL預設埠）訪問伺服器。
[root@localhost bin]# firewall-cmd --zone=public --add-port=5432/tcp --permanent
success
[root@localhost bin]# firewall-cmd --reload
success
重新啟動資料庫服務實例
systemctl restart postgresql-12.service
資料庫的運維和管理
資料庫啟動、停止、重啟、查看狀態、開機自啟動、禁用開機自啟動
[root@localhost bin]# systemctl start postgresql-12.service #開啟
[root@localhost bin]# systemctl stop postgresql-12.service #停止
[root@localhost bin]# systemctl restart postgresql-12.service #重啟
[root@localhost bin]# systemctl status postgresql-12.service #查看狀態
● postgresql-12.service - PostgreSQL 12 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2022-03-22 15:02:49 CST; 7s ago
Docs: https://www.postgresql.org/docs/12/static/
Process: 41843 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 41849 (postmaster)
Tasks: 8 (limit: 11432)
Memory: 17.8M
CGroup: /system.slice/postgresql-12.service
├─41849 /usr/pgsql-12/bin/postmaster -D /data/pgsql12-data/
├─41853 postgres: logger
├─41855 postgres: checkpointer
├─41856 postgres: background writer
├─41857 postgres: walwriter
├─41858 postgres: autovacuum launcher
├─41859 postgres: stats collector
└─41860 postgres: logical replication launcher
Mar 22 15:02:49 localhost.localdomain systemd[1]: Starting PostgreSQL 12 database server...
Mar 22 15:02:49 localhost.localdomain postmaster[41849]: 2022-03-22 15:02:49.320 CST [41849] LOG: starting PostgreSQL 12.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8>
Mar 22 15:02:49 localhost.localdomain postmaster[41849]: 2022-03-22 15:02:49.321 CST [41849] LOG: listening on IPv4 address "0.0.0.0", port 5432
Mar 22 15:02:49 localhost.localdomain postmaster[41849]: 2022-03-22 15:02:49.321 CST [41849] LOG: listening on IPv6 address "::", port 5432
Mar 22 15:02:49 localhost.localdomain postmaster[41849]: 2022-03-22 15:02:49.324 CST [41849] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Mar 22 15:02:49 localhost.localdomain postmaster[41849]: 2022-03-22 15:02:49.328 CST [41849] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
Mar 22 15:02:49 localhost.localdomain postmaster[41849]: 2022-03-22 15:02:49.338 CST [41849] LOG: redirecting log output to logging collector process
Mar 22 15:02:49 localhost.localdomain postmaster[41849]: 2022-03-22 15:02:49.338 CST [41849] HINT: Future log output will appear in directory "log".
Mar 22 15:02:49 localhost.localdomain systemd[1]: Started PostgreSQL 12 database server.
[root@localhost bin]# systemctl enable postgresql-12.service #開機自動開啟
創建用戶
[root@localhost pgsql12-data]# su postgres
bash-4.4$ psql
psql (12.10)
Type "help" for help.
postgres=# CREATE DATABASE pvp_msf;
CREATE DATABASE
postgres=# CREATE DATABASE pvp_strike_db;
CREATE DATABASE
離線安裝需要的包
rpms包：
postgresql12-contrib-12.10-1PGDG.rhel8.x86_64.rpm
postgresql12-devel-12.10-1PGDG.rhel8.x86_64.rpm
postgresql12-docs-12.10-1PGDG.rhel8.x86_64.rpm
postgresql12-libs-12.10-1PGDG.rhel8.x86_64.rpm
postgresql12-llvmjit-12.10-1PGDG.rhel8.x86_64.rpm
postgresql12-plperl-12.10-1PGDG.rhel8.x86_64.rpm
postgresql12-plpython-12.10-1PGDG.rhel8.x86_64.rpm
postgresql12-plpython3-12.10-1PGDG.rhel8.x86_64.rpm
postgresql12-pltcl-12.10-1PGDG.rhel8.x86_64.rpm
postgresql12-server-12.10-1PGDG.rhel8.x86_64.rpm
postgresql12-test-12.10-1PGDG.rhel8.x86_64.rpm
靜態資源包:postgresql-12.10 -tar.gz
本文轉自：https://www.cnblogs.com/ivanlee717/p/16242784.html
