浮雲雅築: [研究] PostgreSQL 10.17 安裝 + pgAdmin4 v5.5 (yum) (CentOS 8.4 x64)

2021年7月19日星期一

[研究] PostgreSQL 10.17 安裝 + pgAdmin4 v5.5 (yum) (CentOS 8.4 x64)

[研究] PostgreSQL 10.17 安裝 + pgAdmin 4 - 5.5 (yum) (CentOS 8.4 x64)

2021-07-19

PostgreSQL 各版本 (最新 13.3 和 14 beta 2)

https://www.postgresql.org/ftp/source/

PostgreSQL: Linux downloads (Red Hat family)
https://www.postgresql.org/download/linux/redhat/

PostgreSQL RPM Building Project - Yum Repository
https://yum.postgresql.org/

PostgreSQL RPM Building Project - Repository Packages
https://yum.postgresql.org/repopackages/

18.3. Connections and Authentication
https://www.postgresql.org/docs/13/runtime-config-connection.html

說 port 為 5432

檢查，發現 CentOS 8.4 x64 目前 yum 提供 PostgreSQL Database 10.17

[john@localhost ~]$ yum list | grep postgre

freeradius-postgresql.x86_64        3.0.20-3.module_el8.3.0+476+0982bc20   appstream 
pcp-pmda-postgresql.x86_64          5.2.5-4.el8                            appstream 
postgresql.x86_64                   10.17-1.module_el8.4.0+823+f0dbe136    appstream 
postgresql-contrib.x86_64           10.17-1.module_el8.4.0+823+f0dbe136    appstream 
postgresql-docs.x86_64              10.17-1.module_el8.4.0+823+f0dbe136    appstream 
postgresql-jdbc.noarch              42.2.3-3.el8_2                         appstream 
postgresql-jdbc-javadoc.noarch      42.2.3-3.el8_2                         appstream 
postgresql-odbc.x86_64              10.03.0000-2.el8                       appstream 
postgresql-odbc-tests.x86_64        10.03.0000-2.el8                       appstream 
postgresql-plperl.x86_64            10.17-1.module_el8.4.0+823+f0dbe136    appstream 
postgresql-plpython3.x86_64         10.17-1.module_el8.4.0+823+f0dbe136    appstream 
postgresql-pltcl.x86_64             10.17-1.module_el8.4.0+823+f0dbe136    appstream 
postgresql-server.x86_64            10.17-1.module_el8.4.0+823+f0dbe136    appstream 
postgresql-server-devel.x86_64      10.17-1.module_el8.4.0+823+f0dbe136    appstream 
postgresql-static.x86_64            10.17-1.module_el8.4.0+823+f0dbe136    appstream 
postgresql-test.x86_64              10.17-1.module_el8.4.0+823+f0dbe136    appstream 
postgresql-test-rpm-macros.x86_64   10.17-1.module_el8.4.0+823+f0dbe136    appstream 
postgresql-upgrade.x86_64           10.17-1.module_el8.4.0+823+f0dbe136    appstream 
postgresql-upgrade-devel.x86_64     10.17-1.module_el8.4.0+823+f0dbe136    appstream 
qt5-qtbase-postgresql.i686          5.12.5-8.el8                           appstream 
qt5-qtbase-postgresql.x86_64        5.12.5-8.el8                           appstream 
[john@localhost ~]$

安裝

yum -y install postgresql-server

[john@localhost ~]$ sudo yum install -y postgresql-server

We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these three things:

    #1) Respect the privacy of others.
    #2) Think before you type.
    #3) With great power comes great responsibility.

[sudo] password for john: 
CentOS Linux 8 - AppStream                      636 kB/s | 8.1 MB     00:13    
CentOS Linux 8 - BaseOS                         3.9 MB/s | 3.6 MB     00:00    
CentOS Linux 8 - Extras                          19 kB/s | 9.8 kB     00:00    
Dependencies resolved.
================================================================================
 Package           Arch   Version                               Repo       Size
================================================================================
Installing:
 postgresql-server x86_64 10.17-1.module_el8.4.0+823+f0dbe136   appstream 5.1 M
Installing dependencies:
 libpq             x86_64 13.3-1.el8_4                          appstream 197 k
 postgresql        x86_64 10.17-1.module_el8.4.0+823+f0dbe136   appstream 1.5 M
Enabling module streams:
 postgresql               10                                                   

Transaction Summary
================================================================================
Install  3 Packages

Total download size: 6.8 M
Installed size: 26 M
Downloading Packages:
(1/3): libpq-13.3-1.el8_4.x86_64.rpm            435 kB/s | 197 kB     00:00    
(2/3): postgresql-10.17-1.module_el8.4.0+823+f0 479 kB/s | 1.5 MB     00:03    
(3/3): postgresql-server-10.17-1.module_el8.4.0 463 kB/s | 5.1 MB     00:11    
--------------------------------------------------------------------------------
Total                                           596 kB/s | 6.8 MB     00:11     
warning: /var/cache/dnf/appstream-a520ed22b0a8a736/packages/libpq-13.3-1.el8_4.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 8483c65d: NOKEY
CentOS Linux 8 - AppStream                      1.6 MB/s | 1.6 kB     00:00    
Importing GPG key 0x8483C65D:
 Userid     : "CentOS (CentOS Official Signing Key) <security@centos.org>"
 Fingerprint: 99DB 70FA E1D7 CE22 7FB6 4882 05B5 55B3 8483 C65D
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                        1/1 
  Installing       : libpq-13.3-1.el8_4.x86_64                              1/3 
  Installing       : postgresql-10.17-1.module_el8.4.0+823+f0dbe136.x86_6   2/3 
  Running scriptlet: postgresql-server-10.17-1.module_el8.4.0+823+f0dbe13   3/3 
  Installing       : postgresql-server-10.17-1.module_el8.4.0+823+f0dbe13   3/3 
  Running scriptlet: postgresql-server-10.17-1.module_el8.4.0+823+f0dbe13   3/3 
[/usr/lib/tmpfiles.d/postgresql.conf:1] Line references path below legacy directory /var/run/, updating /var/run/postgresql → /run/postgresql; please update the tmpfiles.d/ drop-in file accordingly.

  Verifying        : libpq-13.3-1.el8_4.x86_64                              1/3 
  Verifying        : postgresql-10.17-1.module_el8.4.0+823+f0dbe136.x86_6   2/3 
  Verifying        : postgresql-server-10.17-1.module_el8.4.0+823+f0dbe13   3/3 
Installed products updated.

Installed:
  libpq-13.3-1.el8_4.x86_64                                                     
  postgresql-10.17-1.module_el8.4.0+823+f0dbe136.x86_64                         
  postgresql-server-10.17-1.module_el8.4.0+823+f0dbe136.x86_64                  

Complete!
[john@localhost ~]$

初始化資料庫

xxx

[john@localhost ~]$ service   postgresql   initdb
Hint: the preferred way to do this is now "/usr/bin/postgresql-setup --initdb --unit postgresql"
ERROR: The /var/lib/pgsql directory has wrong permissions.
       Please make sure the directory is writable by john.

[john@localhost ~]$ sudo service   postgresql   initdb
Hint: the preferred way to do this is now "/usr/bin/postgresql-setup --initdb --unit postgresql"
 * Initializing database in '/var/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
[john@localhost ~]$

立刻啟動 (但是重新啟動後，不會自動啟動)

[john@localhost ~]$ sudo service   postgresql   start
Redirecting to /bin/systemctl start postgresql.service
[john@localhost ~]$

確認啟動

[john@localhost ~]$ systemctl status postgresql
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor>
   Active: active (running) since Sun 2021-07-18 23:54:34 EDT; 14s ago
  Process: 34624 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (>
 Main PID: 34627 (postmaster)
    Tasks: 8 (limit: 23364)
   Memory: 15.9M
   CGroup: /system.slice/postgresql.service
           ├─34627 /usr/bin/postmaster -D /var/lib/pgsql/data
           ├─34628 postgres: logger process   
           ├─34630 postgres: checkpointer process   
           ├─34631 postgres: writer process   
           ├─34632 postgres: wal writer process   
           ├─34633 postgres: autovacuum launcher process   
           ├─34634 postgres: stats collector process   
           └─34635 postgres: bgworker: logical replication launcher   

Jul 18 23:54:34 localhost.localdomain systemd[1]: Starting PostgreSQL database >
Jul 18 23:54:34 localhost.localdomain postmaster[34627]: 2021-07-18 23:54:34.95>
Jul 18 23:54:34 localhost.localdomain postmaster[34627]: 2021-07-18 23:54:34.95>
Jul 18 23:54:34 localhost.localdomain postmaster[34627]: 2021-07-18 23:54:34.95>
Jul 18 23:54:34 localhost.localdomain postmaster[34627]: 2021-07-18 23:54:34.95>
Jul 18 23:54:34 localhost.localdomain postmaster[34627]: 2021-07-18 23:54:34.96>
Jul 18 23:54:34 localhost.localdomain postmaster[34627]: 2021-07-18 23:54:34.96>
Jul 18 23:54:34 localhost.localdomain systemd[1]: Started PostgreSQL database s>
[john@localhost ~]$

或

[john@localhost ~]$ ps aux | grep pg
postgres   34627  0.0  0.5 245664 21508 ?        Ss   23:54   0:00 /usr/bin/postmaster -D /var/lib/pgsql/data
john       34683  0.0  0.0  12136  1148 pts/0    S+   23:56   0:00 grep --color=auto pg
[john@localhost ~]$

設定每次重新開機後，自動啟動 (但不會立刻啟動)

xxx

[john@localhost ~]$ sudo chkconfig   postgresql   on
Note: Forwarding request to 'systemctl enable postgresql.service'.
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.

[john@localhost ~]$

或

[john@localhost ~]$ sudo systemctl enable postgresql   
[john@localhost ~]$

檢查

[john@localhost ~]$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor >
   Active: active (running) since Sun 2021-07-18 23:54:34 EDT; 5min ago
 Main PID: 34627 (postmaster)
    Tasks: 8 (limit: 23364)
   Memory: 16.0M
   CGroup: /system.slice/postgresql.service
           ├─34627 /usr/bin/postmaster -D /var/lib/pgsql/data
           ├─34628 postgres: logger process   
           ├─34630 postgres: checkpointer process   
           ├─34631 postgres: writer process   
           ├─34632 postgres: wal writer process   
           ├─34633 postgres: autovacuum launcher process   
           ├─34634 postgres: stats collector process   
           └─34635 postgres: bgworker: logical replication launcher   

Jul 18 23:54:34 localhost.localdomain systemd[1]: Starting PostgreSQL database >
Jul 18 23:54:34 localhost.localdomain postmaster[34627]: 2021-07-18 23:54:34.95>
Jul 18 23:54:34 localhost.localdomain postmaster[34627]: 2021-07-18 23:54:34.95>
Jul 18 23:54:34 localhost.localdomain postmaster[34627]: 2021-07-18 23:54:34.95>
Jul 18 23:54:34 localhost.localdomain postmaster[34627]: 2021-07-18 23:54:34.95>
Jul 18 23:54:34 localhost.localdomain postmaster[34627]: 2021-07-18 23:54:34.96>
Jul 18 23:54:34 localhost.localdomain postmaster[34627]: 2021-07-18 23:54:34.96>
Jul 18 23:54:34 localhost.localdomain systemd[1]: Started PostgreSQL database s>

[john@localhost ~]$

開啟防火牆 port 5432

[john@localhost ~]$ firewall-cmd --add-port=5432/tcp --permanent
Authorization failed.
    Make sure polkit agent is running or run the application as superuser.
[john@localhost ~]$ sudo firewall-cmd --add-port=5432/tcp --permanent
success
[john@localhost ~]$

重新載入防火牆設定，讓剛剛設定生效

[john@localhost ~]$ firewall-cmd --reload
Authorization failed.
    Make sure polkit agent is running or run the application as superuser.
[john@localhost ~]$ sudo firewall-cmd --reload
[sudo] password for john: 
success
[john@localhost ~]$

列出防火牆全部設定

[john@localhost ~]$ sudo firewall-cmd --list-all   
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: ens33
  sources: 
  services: cockpit dhcpv6-client ssh
  ports: 5432/tcp
  protocols: 
  masquerade: no
  forward-ports: 
  source-ports: 
  icmp-blocks: 
  rich rules: 
[john@localhost ~]$

設定檔案

postgresql.conf 及 pg_hba.conf

如果不知道在哪

/etc/postgresql-setup/upgrade/postgresql.conf
/var/lib/pgsql/data/postgresql.conf
/usr/lib/tmpfiles.d/postgresql.conf

[john@localhost ~]$ sudo find / -name pg_hba.conf   
find: ‘/run/user/1000/gvfs’: Permission denied
/var/lib/pgsql/data/pg_hba.conf
[john@localhost ~]$

測試

[john@localhost ~]$ su root
Password: 
[root@localhost john]# su - postgres
[postgres@localhost ~]$ psql
psql (10.17)
Type "help" for help.

postgres=# create database TestDB;
CREATE DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileg
es   
-----------+----------+----------+-------------+-------------+------------------
-----
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres      
    +
           |          |          |             |             | postgres=CTc/post
gres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres      
    +
           |          |          |             |             | postgres=CTc/post
gres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

postgres=# \c TestDB
FATAL:  database "TestDB" does not exist
Previous connection kept
postgres=# \c testdb;
You are now connected to database "testdb" as user "postgres".
testdb=# create table table1 (id serial, title text, body text);
CREATE TABLE
testdb=# insert into table1 (title, body) values ('pgpool', 'test');
INSERT 0 1
testdb=# select * from table1;
 id | title  | body 
----+--------+------
  1 | pgpool | test
(1 row)

testdb=# \q
[postgres@localhost ~]$ exit
logout
[root@localhost john]#

停止服務

[root@localhost ~]# systemctl  stop  postgresql   
[root@localhost ~]#

變更 postgtes 密碼

# su - postgres
$ psql -c "ALTER USER postgres WITH PASSWORD 'password1234567';"
$ exit

********************************************************************************

總結

安裝、啟動、設定防火牆

yum  -y  install  postgresql-server
postgresql-setup   initdb
systemctl   restart   postgresql
systemctl   status   postgresql
firewall-cmd --add-port=5432/tcp --permanent  
firewall-cmd --reload
firewall-cmd --list-all

********************************************************************************

PgAdmin4 安裝

參考

https://www.pgadmin.org/download/pgadmin-4-rpm/

安裝

yum install -y epel-release
dnf install -y https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-2-1.noarch.rpm
yum   -y   install  pgadmin4

[root@localhost ~]# sudo yum install -y pgadmin4-desktop
Last metadata expiration check: 0:07:01 ago on Mon 19 Jul 2021 01:59:57 AM EDT.  
Package pgadmin4-desktop-5.5-1.el8.x86_64 is already installed.
Dependencies resolved.
Nothing to do.
Complete!
[root@localhost ~]#

[root@localhost ~]# sudo yum install -y pgadmin4-web
Last metadata expiration check: 0:08:35 ago on Mon 19 Jul 2021 01:59:57 AM EDT.
Package pgadmin4-web-5.5-1.el8.noarch is already installed.
Dependencies resolved.
Nothing to do.
Complete!

[root@localhost ~]# sudo /usr/pgadmin4/bin/setup-web.sh
Setting up pgAdmin 4 in web mode on a Redhat based platform...
Creating configuration database...
NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

Email address: lu@nccst.nat.gov.tw
Password: 
Retype password:
pgAdmin 4 - Application Initialisation
======================================

Creating storage and log directories...
Configuring SELinux...
The Apache web server is not running. We can enable and start the web server for you to finish pgAdmin 4 installation. Continue (y/n)? y
Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → /usr/lib/systemd/system/httpd.service.
Apache successfully enabled.
Apache successfully started.
You can now start using pgAdmin 4 in web mode at http://127.0.0.1/pgadmin4
[root@localhost ~]#

修改 PostgreSQL 設定檔

19.1. The pg_hba.conf File
https://www.postgresql.org/docs/10/auth-pg-hba-conf.html

20.1. 設定檔：pg_hba.conf - PostgreSQL 正體中文使用手冊

https://docs.postgresql.tw/server-administration/client-authentication/the-pg_hba.conf-file

vi /var/lib/pgsql/data/pg_hba.conf

找到 ident

# TYPE  DATABASE        USER            ADDRESS                 METHOD  

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

改成 trust

# TYPE  DATABASE        USER            ADDRESS                 METHOD  

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

最後加上3行 (後2行非必須)

host    all         all         0.0.0.0/0             trust   
listen_addresses = '*'
max_connections = 60

或直接都用下面命令執行

cp  /usr/share/pgsql/pg_hba.conf.sample   /var/lib/pgsql/data/pg_hba.conf
sed  -i -e  "s@ident@trust@"    /var/lib/pgsql/data/pg_hba.conf
echo  "host    all         all         0.0.0.0/0             trust"  >>  /var/lib/pgsql/data/pg_hba.conf  
#echo "listen_addresses = '*'" >>  /var/lib/pgsql/9.2/data/pg_hba.conf
#echo "max_connections = 60"  >> /var/lib/pgsql/9.2/data/pg_hba.conf
tail -n 10  /var/lib/pgsql/data/pg_hba.conf

設定檔生效 ( 重新啟動 PostgreSQL )

一定要用 sudo systemctl status postgresql 檢查

[john@localhost ~]$ sudo systemctl restart postgresql   
[sudo] password for john: 
[john@localhost ~]$ sudo systemctl status postgresql

建立帳號

[john@localhost ~]$ su root
Password: 
[root@localhost john]# su - postgres
[postgres@localhost ~]$ createuser user3  
[postgres@localhost ~]$ exit
logout
[root@localhost john]#