Install postgreSQL on CentOS7

refer to： https://www.postgresql.org/download/linux/redhat/

# Install the repository RPM:
[root@localhost ~]# sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Loaded plugins: fastestmirror
pgdg-redhat-repo-latest.noarch.rpm
Examining /var/tmp/yum-root-gaTqoz/pgdg-redhat-repo-latest.noarch.rpm: pgdg-redhat-repo-42.0-32.noarch
Marking /var/tmp/yum-root-gaTqoz/pgdg-redhat-repo-latest.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package pgdg-redhat-repo.noarch 0:42.0-32 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

========================================================================================================================================================================
Package Arch Version Repository
========================================================================================================================================================================
Installing:
pgdg-redhat-repo noarch 42.0-32 /pgdg-redhat-repo-latest.noarch

Transaction Summary
========================================================================================================================================================================
Install 1 Package

Total size: 13 k
Installed size: 13 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : pgdg-redhat-repo-42.0-32.noarch
Verifying : pgdg-redhat-repo-42.0-32.noarch

Installed:
pgdg-redhat-repo.noarch 0:42.0-32

Complete!

# Install PostgreSQL:
[root@localhost ~]# sudo yum install -y postgresql15-server
Loaded plugins: fastestmirror
Determining fastest mirrors

* base: mirrors.ustc.edu.cn
* extras: mirrors.ustc.edu.cn
* updates: mirrors.ustc.edu.cn
base
extras
pgdg-common/7/x86_64/signature
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-32.noarch (@/pgdg-redhat-repo-latest.noarch)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg-common/7/x86_64/signature
pgdg11/7/x86_64/signature
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-32.noarch (@/pgdg-redhat-repo-latest.noarch)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg11/7/x86_64/signature
pgdg12/7/x86_64/signature
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-32.noarch (@/pgdg-redhat-repo-latest.noarch)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg12/7/x86_64/signature
pgdg13/7/x86_64/signature
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-32.noarch (@/pgdg-redhat-repo-latest.noarch)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg13/7/x86_64/signature
pgdg14/7/x86_64/signature
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-32.noarch (@/pgdg-redhat-repo-latest.noarch)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg14/7/x86_64/signature
pgdg15/7/x86_64/signature
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-32.noarch (@/pgdg-redhat-repo-latest.noarch)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg15/7/x86_64/signature
updates
(1/12): pgdg12/7/x86_64/group_gz
(2/12): pgdg13/7/x86_64/group_gz
(3/12): pgdg-common/7/x86_64/primary_db
(4/12): pgdg12/7/x86_64/primary_db
(5/12): pgdg14/7/x86_64/group_gz
(6/12): pgdg11/7/x86_64/group_gz
(7/12): pgdg14/7/x86_64/primary_db
(8/12): pgdg11/7/x86_64/primary_db
(9/12): pgdg15/7/x86_64/group_gz
(10/12): pgdg13/7/x86_64/primary_db
(11/12): updates/7/x86_64/primary_db
(12/12): pgdg15/7/x86_64/primary_db
Resolving Dependencies
--> Running transaction check
---> Package postgresql15-server.x86_64 0:15.2-1PGDG.rhel7 will be installed
--> Processing Dependency: postgresql15-libs(x86-64) = 15.2-1PGDG.rhel7 for package: postgresql15-server-15.2-1PGDG.rhel7.x86_64
--> Processing Dependency: postgresql15(x86-64) = 15.2-1PGDG.rhel7 for package: postgresql15-server-15.2-1PGDG.rhel7.x86_64
--> Processing Dependency: libzstd.so.1()(64bit) for package: postgresql15-server-15.2-1PGDG.rhel7.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql15-server-15.2-1PGDG.rhel7.x86_64
--> Processing Dependency: libicuuc.so.50()(64bit) for package: postgresql15-server-15.2-1PGDG.rhel7.x86_64
--> Processing Dependency: libicui18n.so.50()(64bit) for package: postgresql15-server-15.2-1PGDG.rhel7.x86_64
--> Running transaction check
---> Package libicu.x86_64 0:50.2-4.el7_7 will be installed
---> Package postgresql15.x86_64 0:15.2-1PGDG.rhel7 will be installed
--> Processing Dependency: libzstd >= 1.4.0 for package: postgresql15-15.2-1PGDG.rhel7.x86_64
--> Processing Dependency: libzstd.so.1()(64bit) for package: postgresql15-15.2-1PGDG.rhel7.x86_64
---> Package postgresql15-libs.x86_64 0:15.2-1PGDG.rhel7 will be installed
---> Package postgresql15-server.x86_64 0:15.2-1PGDG.rhel7 will be installed
--> Processing Dependency: libzstd.so.1()(64bit) for package: postgresql15-server-15.2-1PGDG.rhel7.x86_64
--> Finished Dependency Resolution
Error: Package: postgresql15-15.2-1PGDG.rhel7.x86_64 (pgdg15)
Requires: libzstd >= 1.4.0
Error: Package: postgresql15-15.2-1PGDG.rhel7.x86_64 (pgdg15)
Requires: libzstd.so.1()(64bit)
Error: Package: postgresql15-server-15.2-1PGDG.rhel7.x86_64 (pgdg15)
Requires: libzstd.so.1()(64bit)
You could try using --skip-broken to work around the problem
You could try running: rpm -Va --nofiles --nodigest

[root@localhost ~]# yum install wget
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.ustc.edu.cn
* extras: mirrors.ustc.edu.cn
* updates: mirrors.ustc.edu.cn
Resolving Dependencies
--> Running transaction check
---> Package wget.x86_64 0:1.14-18.el7_6.1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

Total download size: 547 k
Installed size: 2.0 M
Is this ok [y/d/N]: y
Downloading packages:
wget-1.14-18.el7_6.1.x86_64.rpm | 547 kB 00:00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : wget-1.14-18.el7_6.1.x86_64 1/1
Verifying : wget-1.14-18.el7_6.1.x86_64 1/1

Installed:
wget.x86_64 0:1.14-18.el7_6.1

Complete!
[root@localhost ~]# wget https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/libzstd-1.5.2-1.el7.x86_64.rpm
--2023-02-22 02:00:36-- https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/libzstd-1.5.2-1.el7.x86_64.rpm
Resolving download-ib01.fedoraproject.org (download-ib01.fedoraproject.org)... 152.19.134.145, 2600:2701:4000:5211:dead:beef:fe:fed6
Connecting to download-ib01.fedoraproject.org (download-ib01.fedoraproject.org)|152.19.134.145|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 289072 (282K) [application/x-rpm]
Saving to: ‘libzstd-1.5.2-1.el7.x86_64.rpm’

100%[=============================================================================================================================>] 289,072 30.8KB/s in 9.2s

2023-02-22 02:00:47 (30.8 KB/s) - ‘libzstd-1.5.2-1.el7.x86_64.rpm’ saved [289072/289072]

[root@localhost ~]# yum install libzstd-1.5.2-1.el7.x86_64.rpm
Loaded plugins: fastestmirror
Examining libzstd-1.5.2-1.el7.x86_64.rpm: libzstd-1.5.2-1.el7.x86_64
Marking libzstd-1.5.2-1.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package libzstd.x86_64 0:1.5.2-1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================================================================================
Package Arch Version Repository Size
=======================================================================================================================================================================
Installing:
libzstd x86_64 1.5.2-1.el7 /libzstd-1.5.2-1.el7.x86_64 743 k

Total size: 743 k
Installed size: 743 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : libzstd-1.5.2-1.el7.x86_64 1/1
Verifying : libzstd-1.5.2-1.el7.x86_64 1/1

Installed:
libzstd.x86_64 0:1.5.2-1.el7

Complete!
[root@localhost ~]# yum install -y postgresql15-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.ustc.edu.cn
* extras: mirrors.ustc.edu.cn
* updates: mirrors.ustc.edu.cn
Resolving Dependencies
--> Running transaction check
---> Package postgresql15-server.x86_64 0:15.2-1PGDG.rhel7 will be installed
--> Processing Dependency: postgresql15-libs(x86-64) = 15.2-1PGDG.rhel7 for package: postgresql15-server-15.2-1PGDG.rhel7.x86_64
--> Processing Dependency: postgresql15(x86-64) = 15.2-1PGDG.rhel7 for package: postgresql15-server-15.2-1PGDG.rhel7.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql15-server-15.2-1PGDG.rhel7.x86_64
--> Processing Dependency: libicuuc.so.50()(64bit) for package: postgresql15-server-15.2-1PGDG.rhel7.x86_64
--> Processing Dependency: libicui18n.so.50()(64bit) for package: postgresql15-server-15.2-1PGDG.rhel7.x86_64
--> Running transaction check
---> Package libicu.x86_64 0:50.2-4.el7_7 will be installed
---> Package postgresql15.x86_64 0:15.2-1PGDG.rhel7 will be installed
---> Package postgresql15-libs.x86_64 0:15.2-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================================================================================
Package Arch Version Repository Size
=======================================================================================================================================================================
Installing:
postgresql15-server x86_64 15.2-1PGDG.rhel7 pgdg15 5.7 M
Installing for dependencies:
libicu x86_64 50.2-4.el7_7 base 6.9 M
postgresql15 x86_64 15.2-1PGDG.rhel7 pgdg15 1.6 M
postgresql15-libs x86_64 15.2-1PGDG.rhel7 pgdg15 283 k

Total download size: 14 M
Installed size: 56 M
Downloading packages:
warning: /var/cache/yum/x86_64/7/pgdg15/packages/postgresql15-libs-15.2-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY9 MB 00:00:25 ETA
Public key for postgresql15-libs-15.2-1PGDG.rhel7.x86_64.rpm is not installed
(1/4): postgresql15-libs-15.2-1PGDG.rhel7.x86_64.rpm | 283 kB 00:00:02
(2/4): libicu-50.2-4.el7_7.x86_64.rpm | 6.9 MB 00:00:04
(3/4): postgresql15-15.2-1PGDG.rhel7.x86_64.rpm | 1.6 MB 00:00:06
(4/4): postgresql15-server-15.2-1PGDG.rhel7.x86_64.rpm | 5.7 MB 00:00:16
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 744 kB/s | 14 MB 00:00:19
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-32.noarch (@/pgdg-redhat-repo-latest.noarch)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql15-libs-15.2-1PGDG.rhel7.x86_64 1/4
Installing : libicu-50.2-4.el7_7.x86_64 2/4
Installing : postgresql15-15.2-1PGDG.rhel7.x86_64 3/4
Installing : postgresql15-server-15.2-1PGDG.rhel7.x86_64 4/4
Verifying : postgresql15-server-15.2-1PGDG.rhel7.x86_64 1/4
Verifying : postgresql15-15.2-1PGDG.rhel7.x86_64 2/4
Verifying : libicu-50.2-4.el7_7.x86_64 3/4
Verifying : postgresql15-libs-15.2-1PGDG.rhel7.x86_64 4/4

Installed:
postgresql15-server.x86_64 0:15.2-1PGDG.rhel7

Dependency Installed:
libicu.x86_64 0:50.2-4.el7_7 postgresql15.x86_64 0:15.2-1PGDG.rhel7 postgresql15-libs.x86_64 0:15.2-1PGDG.rhel7

Complete!

# Optionally initialize the database and enable automatic start:

[root@localhost ~]# sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
Initializing database ... OK

[root@localhost ~]# sudo systemctl enable postgresql-15
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-15.service to /usr/lib/systemd/system/postgresql-15.service.
[root@localhost ~]# sudo systemctl start postgresql-15
切换到postgres用户

[root@localhost ~]# su - postgres

进入psql,修改postgres用户密码

-bash-4.2$ psql
psql (15.2)
Type "help" for help.

postgres=# alter user postgres with password 'qq@@123';
ALTER ROLE

postgres=# \q
-bash-4.2$ exit

打开防火墙端口

[root@localhost ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man:firewalld(1)
[root@localhost ~]#

防火墙是关闭的，如果不是关闭，需要执行以下命令开通端口：

sudo firewall-cmd --add-port=5432/tcp --permanent
sudo firewall-cmd --reload

设置远程访问

[root@localhost ~]# vi /var/lib/pgsql/15/data/postgresql.conf

把listen_addresses改为‘*'

[root@localhost ~]# vi /var/lib/pgsql/15/data/pg_hba.conf

在末尾添加

host all all 0.0.0.0/0 md5

或

# IPv4 local connections:
#host all all 127.0.0.1/32 scram-sha-256
host all all 0.0.0.0/0 scram-sha-256

# IPv6 local connections:
#host all all ::1/128 scram-sha-256
host all all ::/0 scram-sha-256

taotao100

Install postgreSQL on CentOS7

公告