postgresql安裝及配置超詳細教程

1. 安裝

根據業務需求選擇版本,官網下載

yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
yum install postgresql96 postgresql96-server
rpm -qa|grep postgre

初始化數據庫

執行完初始化任務之後,postgresql 會自動創建和生成兩個用戶一個數據庫

  • linux 系統用戶 postgres:管理數據庫的系統用戶;
  • 密碼由於是默認生成的,需要在系統中修改一下,$passwd postgres
  • 數據庫用戶 postgres:數據庫超級管理員此
  • 用戶默認數據庫為postgres
/usr/pgsql-9.6/bin/postgresql96-setup initdb

設置成 centos7 開機啟動服務

systemctl enable postgresql-9.6

啟動 postgresql 服務

systemctl start postgresql-9.6
systemctl status postgresql-9.6

2. PostgrepSQL的簡單配置

pgsql9.6配置文件位置默認在:/var/lib/pgsql/9.6/data/postgresql.conf

2.1 修改監聽的ip和端口

監聽IP使用localhost時,隻能通過127.0.0.1訪問數據庫;
如果需要通過其他遠程地址訪問PostgreSQL,可以使用“,”作為分隔符,把IP地址添加到listen_addresses後,或者使用“*”,讓所有IP都可以訪問數據庫。

註意:這裡隻是開啟數據庫的遠程訪問權限,具體是否能夠進行遠程登錄,還需要依據pg_hba.conf的認證配置,詳細內容見下節。

# - 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)

2.2 修改數據庫log相關的參數

日志收集,一般是打開的

# This is used when logging to stderr: 
logging_collector = on # Enable capturing of stderr and csvlog 
     # into log files. Required to be on for 
     # csvlogs. 
     # (change requires restart) 

日志目錄,一般使用默認值

# These are only used if logging_collector is on: 
log_directory = 'pg_log' # directory where log files are written, 
     # can be absolute or relative to PGDATA 

隻保留一天的日志,進行循環覆蓋

log_filename = 'postgresql-%a.log' # log file name pattern, 
     # can include strftime() escapes 
log_truncate_on_rotation = on # If on, an existing log file of the 
     # same name as the new log file will be 
     # truncated rather than appended to. 
     # But such truncation only occurs on 
     # time-driven rotation, not on restarts 
     # or size-driven rotation. Default is 
     # off, meaning append to existing files 
     # in all cases. 
log_rotation_age = 1d # Automatic rotation of logfiles will 
     # happen after that time. 0 disables. 
log_rotation_size = 0 # Automatic rotation of logfiles will 

2.3 內存參數

共享內存的大小,用於共享數據塊。如果你的機器上有足夠的內存,可以把這個參數改的大一些,這樣數據庫就可以緩存更多的數據塊,當讀取數據時,就可以從共享內存中讀,而不需要再從文件上去讀取。

# - Memory - 
shared_buffers = 32MB # min 128kB 
     # (change requires restart) 

單個SQL執行時,排序、hash json所用的內存,SQL運行完後,內存就釋放瞭。

# actively intend to use prepared transactions. 
#work_mem = 1MB # min 64kB 

PostgreSQL安裝完成後,可以主要修改以下兩個主要內存參數:
shared_buffer:共享內存的大小,主要用於共享數據塊,默認是128MB;
如果服務器內存有富餘,可以把這個參數適當改大一些,這樣數據庫就可以緩存更多的數據塊,當讀取數據時,就可以從共享內存中讀取,而不需要去文件讀取。

work_mem:單個SQL執行時,排序、hash join所使用的內存,SQL運行完成後,內存就釋放瞭,默認是4MB;
增加這個參數,可以提高排序操作的速度。

3. 數據庫的基礎操作

3.1 連接數據庫控制臺

如果想連接到數據庫,需要切換到postgres用戶下(默認的認證配置前提下)

在postgres用戶下連接數據庫,是不需要密碼的。

切換 postgres 用戶後,提示符變成 -bash-4.2$
使用psql連接到數據庫控制臺,此時系統提示符變為’postgres=#’

$ su postgres
bash-4.2$ psql
psql (9.6)
Type "help" for help.

postgres=#

3.2 一些常用控制臺命令

命令 作用
\h 查看所有sql命令,\h select 等可以查看具體命令
? 查看所有psql命令
\d 查看當前數據庫所有表
\d [tablename] 查看具體的表結構
\du 查看所有用戶
\l 查看所有數據庫
\e 打開文本編輯器

3.3 SQL控制臺操作語句

數據庫創建與修改

# 創建數據庫
create database testdb;
# 刪除數據庫
drop database testdb;
# 重命名數據庫(該數據庫必須沒有活動的連接)
alter database testdb rename to newname;
# 以其他數據庫為模板創建數據庫(表結構、數據都會復制)
create database newdb template testdb;

# 將查詢結果寫入文件
\o /tmp/test.txt
select * from test;
# 列狀顯示
\w
# 再一次\o關閉寫入,否則是連續寫入的
\o
# 退出控制臺
\q

數據庫用戶創建與授權

# 建立新的數據庫用戶
create user zhangsan with password '123456';
# 為新用戶建立數據庫
create database testdb owner zhangsan;
# 把新建的數據庫權限賦予新用戶
grant all privileges on database testdb to zhangsan;

4. 認證登錄

認證權限配置文件: /var/lib/pgsql/9.6/data/pg_hba.conf

命令行的各個參數解釋說明:

  • -U username 用戶名,默認值postgres
  • -d dbname 要連接的數據庫名,默認值postgres。如果單指定-U,沒指定-d參數,則默認訪問與用戶名名稱相同的數據庫。
  • -h hostname 主機名,默認值localhost
  • -p port 端口號,默認值5432

4.1 認證方式

常見的四種身份驗證方式

  • trust:凡是能連接到服務器的,都是可信任的。隻需要提供數據庫用戶名,可以沒有對應的操作系統同名用戶;
  • password 和 md5:對於外部訪問,需要提供 psql 用戶名和密碼。對於本地連接,提供 psql 用戶名密碼之外,還需要有操作系統訪問權(用操作系統同名用戶驗證)。password 和 md5 的區別就是外部訪問時傳輸的密碼是否用 md5 加密;
  • ident:對於外部訪問,從 ident 服務器獲得客戶端操作系統用戶名,然後把操作系統作為數據庫用戶名進行登錄;對於本地連接,實際上使用瞭peer;
  • peer:通過客戶端操作系統內核來獲取當前系統登錄的用戶名,並作為psql用戶名進行登錄。

4.2 遠程登錄

postgresql.conf

listen_addresses = '*' # what IP address(es) to listen on;

pg_hba.conf
所有的用戶通過任意ip都可以通過md5(密碼)的方式登陸PostgreSQL,配置如下:

host all all 0.0.0.0/0 ident

驗證

# server:重啟生效
systemctl restart postgresql-9.6
# client:命令行遠程登錄
psql -U zhangsan -d testdb -h 10.122.45.97 -p 5432

4.3 本地登錄

PostgreSQL登陸默認是peer,不需要驗證用戶密碼即可進入psql相關數據庫,但前提是必須切換用戶登陸。類似於最開始執行的su postgres;psql一樣。

[[email protected] data]# psql -U zhangsan -d testdb -p 5432
psql: FATAL: Peer authentication failed for user "zhangsan"

如果必須按照上述登陸方式登陸的話,有兩種修改方式:

  • 增添map映射
  • 修改認證方式

a. map映射

map映射是用來將系統用戶映射到對應的postgres數據庫用戶,用來限制指定的用戶使用指定的賬號來登陸。

pg_ident.conf
修改pg_ident.conf文件,與pg_hba.conf文件同級目錄。其基本格式如下:

# MAPNAME SYSTEM-USERNAME PG-USERNAME
map_zhangsan root zhangsan
  • MAPNAME指的是映射的名稱,比如map_zhangsan
  • SYSTEM-USERNAME就是系統用戶的名稱,比如root
  • PG-USERNAME就是數據庫裡存在的用戶名稱,比如zhangsan

上面定義的map意思是:定義瞭一個叫做map_zhangsan的映射,當客戶端用戶是root的時候,允許它用zhangsan用戶來登陸PostgreSQL。

修改pg_hba.conf文件
在peer的認證方式後面添加:map=map_tom

重啟PostgreSQL服務,再次嘗試,連接成功。

b. 修改認證方式

需要修改一下pg_hba.cong文件,將local all all peer修改為local all all md5,如下圖所示:

重啟PostgreSQL服務,再次嘗試,連接成功。

到此這篇關於postgresql安裝及配置超詳細教程的文章就介紹到這瞭,更多相關postgresql安裝及配置內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!

推薦閱讀: