centOS7安裝postgresql12.2

參考：http://www.postgres.cn/v2/download

• 用戶db01-安裝postgresql12.2

[db01@centos1 ~]$ pwd
/home/db01
[db01@centos1 ~]$ ll
-rw-r--r--. 1 db01 db01 20363545 2月  10 2021 postgresql-12.2.tar.bz2 #下載到的安裝包
[db01@centos1 ~]$ tar xjvf postgresql*.bz2 #解壓
[db01@centos1 ~]$ ll
drwxrwxr-x. 6 db01 db01      273 2月  11 2020 postgresql-12.2
-rw-r--r--. 1 db01 db01 20363545 2月  10 2021 postgresql-12.2.tar.bz2
[db01@centos1 ~]$ cd postgresql-12.2/
[db01@centos1 postgresql-12.2]$ ll
總用量 744
-rw-r--r--.  1 db01 db01    522 2月  11 2020 aclocal.m4
drwxrwxr-x.  2 db01 db01   4096 2月  11 2020 config
-rwxr-xr-x.  1 db01 db01 572134 2月  11 2020 configure
-rw-r--r--.  1 db01 db01  83465 2月  11 2020 configure.in
drwxrwxr-x. 56 db01 db01   4096 2月  11 2020 contrib
-rw-r--r--.  1 db01 db01   1192 2月  11 2020 COPYRIGHT
drwxrwxr-x.  3 db01 db01     87 2月  11 2020 doc
-rw-r--r--.  1 db01 db01   3909 2月  11 2020 GNUmakefile.in
-rw-r--r--.  1 db01 db01    284 2月  11 2020 HISTORY
-rw-r--r--.  1 db01 db01  61606 2月  11 2020 INSTALL
-rw-r--r--.  1 db01 db01   1665 2月  11 2020 Makefile
-rw-r--r--.  1 db01 db01   1212 2月  11 2020 README
drwxrwxr-x. 16 db01 db01   4096 2月  11 2020 src
[db01@centos1 postgresql-12.2]$ pwd
/home/db01/postgresql-12.2
[db01@centos1 postgresql-12.2]$ sudo yum install readline-devel #安裝依賴包readline-devel
[db01@centos1 postgresql-12.2]$ sudo yum -y install zlib-devel #安裝依賴包zlib-devel
[db01@centos1 postgresql-12.2]$ ./configure --prefix=/home/db01/pgsql #configure 指定安裝路徑
[db01@centos1 postgresql-12.2]$ make world #編譯
，，，，，，
PostgreSQL, contrib, and documentation successfully made. Ready to install.
[db01@centos1 postgresql-12.2]$ make install-world #安裝
，，，，，，
PostgreSQL, contrib, and documentation installation complete.
[db01@centos1 postgresql-12.2]$ cd .. 
[db01@centos1 ~]$ ll
總用量 19892
drwxrwxr-x. 6 db01 db01       56 2月  10 19:44 pgsql
drwxrwxr-x. 6 db01 db01     4096 2月  10 19:31 postgresql-12.2
-rw-r--r--. 1 db01 db01 20363545 2月  10 2021 postgresql-12.2.tar.bz2
[db01@centos1 ~]$ rm -rf postgresql-12.2 #刪掉解壓出的安裝包
[db01@centos1 ~]$ cd pgsql/
[db01@centos1 pgsql]$ ll
總用量 16
drwxrwxr-x. 2 db01 db01 4096 2月  10 19:44 bin
drwxrwxr-x. 6 db01 db01 4096 2月  10 19:44 include
drwxrwxr-x. 4 db01 db01 4096 2月  10 19:44 lib
drwxrwxr-x. 8 db01 db01 4096 2月  10 19:44 share
[db01@centos1 pgsql]$ mkdir data #建資料存放目錄
[db01@centos1 pgsql]$ mkdir log #建日志存放目錄
[db01@centos1 pgsql]$ cd data/
[db01@centos1 data]$ pwd
/home/db01/pgsql/data
[db01@centos1 data]$ cd
[db01@centos1 ~]$ ~/pgsql/bin/initdb -D /home/db01/pgsql/data #初始化資料庫，指定資料目錄
The files belonging to this database system will be owned by user "db01".
This user must also own the server process.

The database cluster will be initialized with locale "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".

Data page checksums are disabled.

fixing permissions on existing directory /home/db01/pgsql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /home/db01/pgsql/bin/pg_ctl -D /home/db01/pgsql/data -l logfile start

[db01@centos1 ~]$ /home/db01/pgsql/bin/pg_ctl -D /home/db01/pgsql/data -l /home/db01/pgsql/log/log.log start #啟動資料庫
waiting for server to start.... done
server started
[db01@centos1 ~]$ /home/db01/pgsql/bin/pg_ctl status -D /home/db01/pgsql/data #查看資料庫是否在指定的資料目錄運行
pg_ctl: server is running (PID: 67624)
/home/db01/pgsql/bin/postgres "-D" "/home/db01/pgsql/data"
[db01@centos1 ~]$ 

用戶db01-修改為md5認證方式

[db01@centos1 ~]$ cd pgsql/data/
[db01@centos1 data]$ sudo vi pg_hba.conf
，，，，，，
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5 # trust改為md5
，，，，，，
[db01@centos1 data]$
#trust：凡是能連接到服務器的，都是可信任的，只需要提供資料庫用戶名，可以沒有對應的作業系統同名用戶；
#password 和 md5：對于外部訪問，需要提供 psql 用戶名和密碼，對于本地連接，提供 psql 用戶名密碼之外，還需要有作業系統訪問權（用作業系統同名用戶驗證），password 和 md5 的區別就是外部訪問時傳輸的密碼是否用 md5 加密；
#ident：對于外部訪問，從 ident 服務器獲得客戶端作業系統用戶名，然后把作業系統作為資料庫用戶名進行登錄；對于本地連接，實際上使用了peer；
#peer：通過客戶端作業系統內核來獲取當前系統登錄的用戶名，并作為psql用戶名進行登錄，

• 用戶db01-允許遠程登錄

[db01@centos1 data]$ cp postgresql.conf postgresql.conf.bak 
[db01@centos1 data]$ cat postgresql.conf.bak |grep -Ev '#|^$'>postgresql.conf
[db01@centos1 data]$ vi postgresql.conf
listen_addresses = '*' #增加1行
[db01@centos1 data]$vi pg_hba.conf
host all all 0.0.0.0/0 password #增加1行
[db01@centos1 data]$

• 用戶db01-重啟

[db01@centos1 bin]$ pwd
/home/db01/pgsql/bin
[db01@centos1 bin]$ ./pg_ctl stop -D  /home/db01/pgsql/data
waiting for server to shut down.... done
server stopped
[db01@centos1 bin]$ /home/db01/pgsql/bin/pg_ctl -D /home/db01/pgsql/data -l /home/db01/pgsql/log/log.log start

• 用戶db01-解決其他主機無法登錄問題

  [db01@centos1 bin]$ /home/db01/pgsql/bin/psql -h 127.0.0.1 -p 5432 -U db01 -d thingsboard #這樣不能登錄
  Password for user db01: 
  psql: error: could not connect to server: FATAL:  password authentication failed for user "db01"
  [db01@centos1 bin]$ /home/db01/pgsql/bin/psql -U db01 -d thingsboard  #這樣才能登錄
  psql (12.2)
  Type "help" for help.
  
  thingsboard=# \password #修改密碼，使密碼用上md5，這樣一來，其他主機也能登錄了
  Enter new password: 
  Enter it again: 
  thingsboard=# exit
  [db01@centos1 bin]$