在linux的Ubuntu系統(tǒng)上安裝PostgreSQL數(shù)據(jù)庫與PostgreSQL數(shù)據(jù)庫簡單操作和使用
在linux的Ubuntu系統(tǒng)上安裝PostgreSQL數(shù)據(jù)庫與PostgreSQL數(shù)據(jù)庫簡單操作和使用
1. 安裝數(shù)據(jù)庫
先更新源：
sudo apt upgrade
sudo apt update
1.1. 安裝postgresql數(shù)據(jù)庫
安裝過程：
xd@wxd:~$ sudo apt install postgresql
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
libllvm10 libpq5 libxslt1.1 postgresql-12 postgresql-client-12 postgresql-client-common
postgresql-common ssl-cert
Suggested packages:
postgresql-doc postgresql-doc-12 libjson-perl openssl-blacklist
The following NEW packages will be installed:
libllvm10 libpq5 libxslt1.1 postgresql postgresql-12 postgresql-client-12
postgresql-client-common postgresql-common ssl-cert
0 upgraded, 9 newly installed, 0 to remove and 0 not upgraded.
Need to get 30.3 MB of archives.
After this operation, 120 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Get:1 http://mirrors.cloud.aliyuncs.com/ubuntu focal/main amd64 libllvm10 amd64 1:10.0.0-4ubuntu1 [15.3 MB]
Get:2 http://mirrors.cloud.aliyuncs.com/ubuntu focal-updates/main amd64 libpq5 amd64 12.9-0ubuntu0.20.04.1 [117 kB]
Get:3 http://mirrors.cloud.aliyuncs.com/ubuntu focal/main amd64 libxslt1.1 amd64 1.1.34-4 [152 kB]
Get:4 http://mirrors.cloud.aliyuncs.com/ubuntu focal-updates/main amd64 postgresql-client-common all 214ubuntu0.1 [28.2 kB]
Get:5 http://mirrors.cloud.aliyuncs.com/ubuntu focal-updates/main amd64 postgresql-client-12 amd64 12.9-0ubuntu0.20.04.1 [1,047 kB]
Get:6 http://mirrors.cloud.aliyuncs.com/ubuntu focal/main amd64 ssl-cert all 1.0.39 [17.0 kB]
Get:7 http://mirrors.cloud.aliyuncs.com/ubuntu focal-updates/main amd64 postgresql-common all 214ubuntu0.1 [169 kB]
Get:8 http://mirrors.cloud.aliyuncs.com/ubuntu focal-updates/main amd64 postgresql-12 amd64 12.9-0ubuntu0.20.04.1 [13.5 MB]
Get:9 http://mirrors.cloud.aliyuncs.com/ubuntu focal-updates/main amd64 postgresql all 12+214ubuntu0.1 [3,924 B]
Fetched 30.3 MB in 1s (25.9 MB/s)
Preconfiguring packages ...
Selecting previously unselected package libllvm10:amd64.
(Reading database ... 149019 files and directories currently installed.)
Preparing to unpack .../0-libllvm10_1%3a10.0.0-4ubuntu1_amd64.deb ...
Unpacking libllvm10:amd64 (1:10.0.0-4ubuntu1) ...
Selecting previously unselected package libpq5:amd64.
Preparing to unpack .../1-libpq5_12.9-0ubuntu0.20.04.1_amd64.deb ...
Unpacking libpq5:amd64 (12.9-0ubuntu0.20.04.1) ...
Selecting previously unselected package libxslt1.1:amd64.
Preparing to unpack .../2-libxslt1.1_1.1.34-4_amd64.deb ...
Unpacking libxslt1.1:amd64 (1.1.34-4) ...
Selecting previously unselected package postgresql-client-common.
Preparing to unpack .../3-postgresql-client-common_214ubuntu0.1_all.deb ...
Unpacking postgresql-client-common (214ubuntu0.1) ...
Selecting previously unselected package postgresql-client-12.
Preparing to unpack .../4-postgresql-client-12_12.9-0ubuntu0.20.04.1_amd64.deb ...
Unpacking postgresql-client-12 (12.9-0ubuntu0.20.04.1) ...
Selecting previously unselected package ssl-cert.
Preparing to unpack .../5-ssl-cert_1.0.39_all.deb ...
Unpacking ssl-cert (1.0.39) ...
Selecting previously unselected package postgresql-common.
Preparing to unpack .../6-postgresql-common_214ubuntu0.1_all.deb ...
Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Unpacking postgresql-common (214ubuntu0.1) ...
Selecting previously unselected package postgresql-12.
Preparing to unpack .../7-postgresql-12_12.9-0ubuntu0.20.04.1_amd64.deb ...
Unpacking postgresql-12 (12.9-0ubuntu0.20.04.1) ...
Selecting previously unselected package postgresql.
Preparing to unpack .../8-postgresql_12+214ubuntu0.1_all.deb ...
Unpacking postgresql (12+214ubuntu0.1) ...
Setting up postgresql-client-common (214ubuntu0.1) ...
Setting up libpq5:amd64 (12.9-0ubuntu0.20.04.1) ...
Setting up libllvm10:amd64 (1:10.0.0-4ubuntu1) ...
Setting up postgresql-client-12 (12.9-0ubuntu0.20.04.1) ...
update-alternatives: using /usr/share/postgresql/12/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up ssl-cert (1.0.39) ...
Setting up postgresql-common (214ubuntu0.1) ...
Adding user postgres to group ssl-cert
Creating config file /etc/postgresql-common/createcluster.conf with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service.
Setting up libxslt1.1:amd64 (1.1.34-4) ...
Setting up postgresql-12 (12.9-0ubuntu0.20.04.1) ...
Creating new PostgreSQL cluster 12/main ...
/usr/lib/postgresql/12/bin/initdb -D /var/lib/postgresql/12/main --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/12/main ... 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
Success. You can now start the database server using:
pg_ctlcluster 12 main start
Ver Cluster Port Status Owner Data directory Log file
12 main 5432 down postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
update-alternatives: using /usr/share/postgresql/12/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Setting up postgresql (12+214ubuntu0.1) ...
Processing triggers for systemd (245.4-4ubuntu3.15) ...
Processing triggers for man-db (2.9.1-1) ...
Processing triggers for libc-bin (2.31-0ubuntu9.4) ...
從上面的信息中可以找到以下關(guān)鍵信息：
配置文件路徑: /etc/postgresql-common/createcluster.conf
systemd的服務(wù)軟連接: /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service
數(shù)據(jù)目錄: /var/lib/postgresql/12/main
日志文件: /var/log/postgresql/postgresql-12-main.log
特殊的數(shù)據(jù)庫用戶: postgres
1.2. 安裝 postgresql-contrib
什么是 postgresql-contrib?
postgresql-contrib 或者說 contrib 包，包含一些不屬于 PostgreSQL 核心包的實(shí)用工具和功能。在大多數(shù)情況下，最好將 contrib 包與 PostgreSQL 核心一起安裝。
安裝過程：
xd@wxd:~$ sudo apt install postgresql-contrib
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following NEW packages will be installed:
postgresql-contrib
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
Need to get 3,932 B of archives.
After this operation, 67.6 kB of additional disk space will be used.
Get:1 http://mirrors.cloud.aliyuncs.com/ubuntu focal-updates/main amd64 postgresql-contrib all 12+214ubuntu0.1 [3,932 B]
Fetched 3,932 B in 0s (41.7 kB/s)
Selecting previously unselected package postgresql-contrib.
(Reading database ... 150812 files and directories currently installed.)
Preparing to unpack .../postgresql-contrib_12+214ubuntu0.1_all.deb ...
Unpacking postgresql-contrib (12+214ubuntu0.1) ...
Setting up postgresql-contrib (12+214ubuntu0.1) ...
xd@wxd:~$
1.3. 查看數(shù)據(jù)庫服務(wù)是否運(yùn)行
查看當(dāng)前服務(wù)狀態(tài)：
xd@wxd:~$ service postgresql status
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Mon 2022-02-14 18:06:55 CST; 7min ago
Main PID: 9300 (code=exited, status=0/SUCCESS)
Tasks: 0 (limit: 2187)
Memory: 0B
CGroup: /system.slice/postgresql.service
xd@wxd:~$
可以看到服務(wù)處于active狀態(tài)，也就是可以登陸。另外，也可以看到主服務(wù)進(jìn)程的PID。
1.4. 查看數(shù)據(jù)庫版本
默認(rèn)情況下，PostgreSQL 會創(chuàng)建一個擁有所權(quán)限的特殊用戶 postgres, 這個在安裝信息中也可以看到。
使用 psql 工具通過連接 PostgreSQL 數(shù)據(jù)庫并且打印它的版本來驗(yàn)證安裝：
sudo -u postgres psql -c "SELECT version();"
如果輸出了版本信息，那么就說明確實(shí)安裝成功了。
2. 實(shí)際簡單操作數(shù)據(jù)庫
2.1. 登陸與退出數(shù)據(jù)庫
要實(shí)際使用 PostgreSQL，你必須先登錄創(chuàng)建的Linux賬號postgres:
sudo su postgres
登陸后，可以看到當(dāng)前的用戶名(postgres)已經(jīng)發(fā)生變化.
通過默認(rèn)的工具psql登錄數(shù)據(jù)庫，通過\q命令退出數(shù)據(jù)庫。
xd@wxd:~$ sudo su postgres ——————>登陸默認(rèn)的用戶postgres
postgres@wxd:/home/xd$ psql ——————>通過psql工具進(jìn)入數(shù)據(jù)庫
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.
postgres=# \q ——————>退出數(shù)據(jù)庫
postgres@wxd:/home/xd$
2.2. 查看數(shù)據(jù)庫名稱與用戶名稱
查看現(xiàn)有的所有數(shù)據(jù)庫：【字母L的小寫形式,注意這里沒有分號】
postgres-# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
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/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres-#
可以看到有三個默認(rèn)的數(shù)據(jù)庫：postgres, template0, template1 。
查看所有的用戶：【注意這里有分號】
postgres-# \du;
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres-#
可以看到當(dāng)前只有一個默認(rèn)的用戶 postgres .
2.3. 創(chuàng)建用戶并授權(quán)
創(chuàng)建一個用戶：假設(shè)用戶名為【xd】, 密碼【xd_password】,并授權(quán)
postgres=# CREATE USER xd WITH PASSWORD 'xd_password';
CREATE ROLE
postgres=# ALTER USER xd WITH SUPERUSER;
ALTER ROLE
postgres=#
再次查看當(dāng)前數(shù)據(jù)庫系統(tǒng)中的用戶名：
postgres=# \du;
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
xd | Superuser | {}
postgres=#
2.3.1. 嘗試用新用戶登錄
退出數(shù)據(jù)庫系統(tǒng)后嘗試用新的用戶【假設(shè)為xd】登陸數(shù)據(jù)庫：
postgres@wxd:/home/xd$ psql -U xd
psql: error: FATAL: Peer authentication failed for user "xd"
這個問題在StackOverFlow上的回答：https://stackoverflow.com/questions/17443379/psql-fatal-peer-authentication-failed-for-user-dev
原因：安裝完 PostgresQL 后， PostgresQL 連接時的默認(rèn)認(rèn)證方式為 peer。官方的解釋：
The peer authentication method works by obtaining the client’s operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping). This method is only supported on local connections.
簡單翻譯：
Peer認(rèn)證方法的工作原理是：從內(nèi)核中獲取客戶的操作系統(tǒng)用戶名，并將其作為允許的數(shù)據(jù)庫用戶名（可選擇用戶名映射）。這種方法只支持本地連接。
由于我們登錄的Linux系統(tǒng)用戶名（即postgres）并不是等于登錄PG數(shù)據(jù)庫的用戶名（xd）， 因此出現(xiàn)了以上認(rèn)證失敗的信息。
2.3.2. 更改配置文件
上面的問題需要通過更改配置文件來修改認(rèn)證方式，必須要要用超級用戶來修改配置文件:
# 注意：下面命令中的“12”是PostgreSQL主版本號，請與實(shí)際情況保持一致
sudo vim /etc/postgresql/12/main/pg_hba.conf
在這個配置文件中，可以看到下面的配置信息：
# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
對于第一條local配置信息，為了意外操作導(dǎo)致默認(rèn)用戶不能登錄，所以不建議更換。
建議第二個local的認(rèn)證為md5,替換后的內(nèi)容如下：
local all all md5
然后重啟 PostgreSQL：
# 重啟PostgreSQL數(shù)據(jù)庫服務(wù)
sudo service postgresql restart
2.3.3. 重新嘗試登錄
重新登錄：
postgres@wxd:/home/xd$ psql -U xd ---->直接指定用戶名進(jìn)行登錄
Password for user xd:
psql: error: FATAL: database "xd" does not exist
postgres@wxd:/home/xd$ psql -U xd -d postgres ---->加上數(shù)據(jù)庫名登錄
Password for user xd:
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.
postgres=# \q
postgres@wxd:/home/xd$ exit ---->退出當(dāng)前的Linux系統(tǒng)用戶：postgres
xd@wxd:~$ psql -U xd -d postgres ---->在普通的Linux系統(tǒng)用戶下，登錄
Password for user xd:
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.
postgres=#
2.3.4. 嘗試登錄其他數(shù)據(jù)庫
上面看到了可以登錄自動初始化的數(shù)據(jù)庫postgres, 下面嘗試登錄自動初始化的另外兩個數(shù)據(jù)庫。
先嘗試登錄初始化的數(shù)據(jù)庫template1：
xd@wxd:~$ psql -U xd -h 127.0.0.1 -d template1
Password for user xd:
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
template1=# \q
xd@wxd:~$
可以看到數(shù)據(jù)庫template1登錄正常。
但是要注意：初始化的數(shù)據(jù)庫template0是不允許登錄的：
xd@wxd:~$ psql -U xd -h 127.0.0.1 -d template0
Password for user xd:
psql: error: FATAL: database "template0" is not currently accepting connections
xd@wxd:~$
2.4. 刪除用戶
刪除用戶：假設(shè)需要刪除的用戶名為【my_user】
DROP USER my_user;
3. 認(rèn)證方式與遠(yuǎn)程連接
在上面我們遇到了由于權(quán)限的原因，登陸失敗，然后改配置文件來解決，下面我們詳細(xì)介紹
3.1. 查看配置文件路徑
在上面我們介紹了配置文件pg_hba.conf, 這個文件的具體位置，可以在登錄數(shù)據(jù)庫后，通過下面命令來查看：
postgres=# show hba_file;
hba_file
-------------------------------------
/etc/postgresql/12/main/pg_hba.conf
(1 row)
postgres=#
后面還會用到配置文件postgresql.conf, 我們可以通過下面的方式來查看具體路徑：
postgres=# show config_file;
config_file
-----------------------------------------
/etc/postgresql/12/main/postgresql.conf
(1 row)
postgres=#
3.2. 認(rèn)證方式
PostgreSQL 支持多種身份認(rèn)證方式。最常用的方法如下：
Trust: 只要滿足pg_hba.conf定義的條件，一個角色就可以不使用密碼就能連接服務(wù)器
Password: 通過密碼，一個角色可以連接服務(wù)器。密碼可以被存儲為 scram-sha-256, md5, 和 password(明文)。
Ident: 僅僅支持 TCP/IP 連接。它通常通過一個可選的用戶名映射表，獲取客戶端操作系統(tǒng)用戶名。
Peer: 和 Ident 一樣，僅僅支持本地連接。
PostgreSQL 客戶端身份驗(yàn)證通常被定義在pg_hba.conf文件中。默認(rèn)情況下，對于本地連接，PostgreSQL 被設(shè)置成身份認(rèn)證防范 peer。
3.3. 遠(yuǎn)程連接
允許遠(yuǎn)程訪問同樣是需要修改配置文件，注意要用超級用戶。
3.3.1. 修改配置文件pg_hba.conf
配置文件路徑： /etc/postgresql/12/main/pg_hba.conf
注意：這個路徑中的“12”是PostgreSQL主版本號，請與實(shí)際情況保持一致
可以看到默認(rèn)的配置文件中有一條關(guān)于IPv4的規(guī)則
# IPv4 local connections:
host all all 127.0.0.1/32 md5
如果想要允許192.168.1網(wǎng)段的所有主機(jī)通過md5的形式進(jìn)行連接，請?jiān)黾酉旅娴倪@句：
# Allow 192.168.1.XXX hosts to connect via md5
host all all 192.168.1.0/24 md5
如果想要允許所有的主機(jī)通過md5的形式進(jìn)行連接，請?jiān)黾酉旅娴倪@句：
# Allow all hosts to connect via md5
host all all 0.0.0.0/0 md5
3.3.2. 修改配置文件postgresql.conf
配置文件路徑： /etc/postgresql/12/main/postgresql.conf
注意：這個路徑中的“12”是PostgreSQL主版本號，請與實(shí)際情況保持一致
在這個配置文件中，可以看到下面的內(nèi)容：
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
根據(jù)提示信息，我們在#listen_addresses = 'localhost'行后，在port行前增加一行:
listen_addresses = '*'
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
3.3.3. 修改防火墻， 允許端口連接
從上一小節(jié)可以看到訪問數(shù)據(jù)庫的默認(rèn)端口是5432，請修改防火墻允許訪問。
修改完配置文件，修改完防火墻，重啟PostGreSQL服務(wù)后，檢查服務(wù)與端口是否啟動成功：
xd@wxd:~$ service postgresql status
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Tue 2022-02-15 11:30:53 CST; 22min ago
Process: 20288 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 20288 (code=exited, status=0/SUCCESS)
xd@wxd:~$ sudo lsof -i :5432
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
postgres 20270 postgres 3u IPv4 152687 0t0 TCP *:postgresql (LISTEN)
postgres 20270 postgres 4u IPv6 152688 0t0 TCP *:postgresql (LISTEN)
xd@wxd:~$
3.3.4. 嘗試連接
在遠(yuǎn)方電腦（比如我用的是Mac）上下載一個連接數(shù)據(jù)庫的客戶端，目前我用的客戶端是【DBeaver Community】。
之所以用它，是因?yàn)樗赓M(fèi)，還支持多種類型的數(shù)據(jù)庫，包括Mysql。
下載好數(shù)據(jù)庫驅(qū)動， 輸入數(shù)據(jù)庫所在的服務(wù)器IP地址，用戶名，密碼，數(shù)據(jù)庫名稱后，就可以登錄成功。
參考：
本文內(nèi)容根據(jù)網(wǎng)絡(luò)資料整理，出于傳遞更多信息之目的，不代表金鑰匙跨境贊同其觀點(diǎn)和立場。
轉(zhuǎn)載請注明，如有侵權(quán)，聯(lián)系刪除。
Rakuten樂享
