PostgreSQL的實踐一：初識

yibinnn 發(fā)布于2019-07-25 12:38 / 3392人閱讀

摘要：每個服務(wù)由多個進(jìn)程組成，為首的進(jìn)程名為。服務(wù)使用字節(jié)長的內(nèi)部事務(wù)標(biāo)識符，即時發(fā)生重疊后仍然繼續(xù)使用，這會導(dǎo)致問題，所以需要定期進(jìn)行操作。操作被認(rèn)為是緊跟操作后的操作。在涉及高比例插入刪除的表中，會造成索引膨脹，這時候可以重建索引。

簡介和認(rèn)知 發(fā)音

post-gres-q-l

服務(wù)（server）

一個操作系統(tǒng)中可以啟動多個postgres服務(wù)。
每個服務(wù)由多個進(jìn)程組成，為首的進(jìn)程名為postmaster。
每個服務(wù)要占用一個端口，多個服務(wù)不能共享端口。
每個服務(wù)都有一個data目錄用于存放數(shù)據(jù)，目錄不允許修改，否則會破壞數(shù)據(jù)庫，并且無法修復(fù)。
服務(wù)使用4字節(jié)長的內(nèi)部事務(wù)標(biāo)識符，即時發(fā)生重疊后仍然繼續(xù)使用，這會導(dǎo)致問題，所以需要定期進(jìn)行VACUUM操作。

數(shù)據(jù)庫（database）

一個服務(wù)中可以擁有多個數(shù)據(jù)庫。
數(shù)據(jù)庫默認(rèn)是任何用戶可連接的，創(chuàng)建好后需要修改相應(yīng)的權(quán)限。
數(shù)據(jù)庫之間的數(shù)據(jù)是隔離的，不能進(jìn)行聯(lián)表。
數(shù)據(jù)庫默認(rèn)的數(shù)據(jù)塊大小為8192。

模式（schema）

一個數(shù)據(jù)庫中可以有多個模式，模式相當(dāng)于表的命名空間，類似于mysql中的database，可以使用帶模式的完整名稱來訪問或者創(chuàng)建對象。
不同模式之間的表是可以聯(lián)表查詢的。
可以通過對用戶設(shè)置search_path參數(shù)來指定默認(rèn)搜索的模式。

表（table）

一個模式中可以有多張表。
表是由多個關(guān)系元素組成的，大字段數(shù)據(jù)放在另一個名為TOAST的表中，每張表都有一個TOAST表和TOAST索引。
用雙引號括起來的表和沒用雙引號括起來的表是不一樣的，即使名字一樣。
雙引號括起來的表區(qū)分大小寫，沒用雙引號括起來的表不區(qū)分大小寫。

列（column）

每張表都由許多列組成，每一列有一個列名、類型、默認(rèn)值等屬性，用來存儲每一條記錄中的各種值。
文本類型統(tǒng)一由一種數(shù)據(jù)類型存儲，支持長度從1B到1G，經(jīng)過優(yōu)化，存儲少的時候很高效，存儲多的時候會自動管理和壓縮。
自增類型serial本質(zhì)上就是整數(shù)，通過創(chuàng)建并關(guān)聯(lián)到一個SEQUENCE類型的對象來記錄自增值。

表空間（tablespace）

默認(rèn)情況下，所有的數(shù)據(jù)都會放在postgres指定的data目錄下，通過定義表空間，可以讓postgres將數(shù)據(jù)存放在不同的設(shè)備上。
表空間是通過軟鏈接來實現(xiàn)的。
建議為每個數(shù)據(jù)庫設(shè)立一個多帶帶的表空間，尤其是不同數(shù)據(jù)庫中有同名的模式或者表的時候。
postgres=# CREATE TABLESPACE tbs LOCATION "/usr/local/tbs";

視圖（view）

視圖本質(zhì)上是預(yù)定義好的一個sql查詢，以一張表的形式給出，在每次調(diào)用時都會執(zhí)行相應(yīng)的sql查詢。
postgres=# CREATE VIEW view AS SELECT * FROM tb;

當(dāng)視圖足夠簡單的時候，postgres是支持視圖更新的，相應(yīng)的更新會傳遞到相應(yīng)的表中。
還可以使用INSTEAD OF觸發(fā)器或者規(guī)則來實現(xiàn)視圖更新，請參考具體的操作手冊。
物化視圖可以預(yù)先將數(shù)據(jù)查詢出來，這樣調(diào)用的時候就不必反復(fù)查詢了，更新需要手動更新。
postgres=# CREATE MATERIALIZED VIEW view AS SELECT * FROM tb;
postgres=# REFRESH MATERIALIZED VIEW view;

行（row）

行即表中的一條數(shù)據(jù)。
postgres中每個行都有一個行版本，而且還有兩個系統(tǒng)列xmin和xmax，分別標(biāo)示這個行被創(chuàng)建和刪除的事務(wù)。
刪除時，設(shè)置xmax為刪除事務(wù)號，不會實際執(zhí)行刪除。
UPDATE操作被認(rèn)為是緊跟INSERT操作后的DELETE操作。
索引（index）
索引可以用來給表添加約束或者提高查詢速度。
在涉及高比例插入刪除的表中，會造成索引膨脹，這時候可以重建索引。

reindexdb

創(chuàng)建CONCURRENTLY索引時不會持有全表鎖，這條指令分成兩個步驟，第一部分創(chuàng)建索引并標(biāo)記為不可用，這時候INSERT、UPDATE、DELETE操作已經(jīng)開始維護(hù)索引了，但是查詢不能使用索引。建立完畢后才會被標(biāo)記為可用。
postgres=# CREATE CONCURRENTLY INDEX index ON tb(id);

可以手工設(shè)置索引的可用性。

UPDATE pg_index SET indisvalid = false WHERE indexrelid = index::regclass;

pgsql 中表空間／數(shù)據(jù)庫／模式的關(guān)系

表空間是物理結(jié)構(gòu)，同一表空間下可以有多個數(shù)據(jù)庫
數(shù)據(jù)庫是邏輯結(jié)構(gòu)，是表／索引／視圖／存儲過程的集合，一個數(shù)據(jù)庫下可以有多個schema
模式是邏輯結(jié)構(gòu)，是對數(shù)據(jù)庫的邏輯劃分

安裝

# vist https://www.postgresql.org/download/
# Interactive installer by EnterpriseDB -> 選擇10.5版本的Windows x86-64下載
1. 一路的next安裝，當(dāng)然你可以自己選擇安裝的目錄
2. 提示輸入postgres帳號的密碼，你可以根據(jù)自己的喜好，設(shè)置一個，比如這里我設(shè)置了：123456
2. 提示安裝插件擴(kuò)展，取消即可，暫時不需要安裝

psql客戶端簡單實用 連接

# $MY_POSTGRES_PATH = D:PostgreSQL; 這個環(huán)境參數(shù)代表我安裝的Postgresql服務(wù)器所在的目錄
# $MY_POSTGRES_PATH/bin/psql -U postgres
$MY_POSTGRES_PATH/bin/scripts/runpsql
# 依次默認(rèn)回車，如果有需要調(diào)整參數(shù)，你可以自定義
# 輸入123456


#output
Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
用戶 postgres 的口令：
psql (10.5)
輸入 "help" 來獲取幫助信息.
postgres=#

# 輸入help得到以下提示
postgres=# help
您正在使用psql, 這是一種用于訪問PostgreSQL的命令行界面
鍵入： copyright 顯示發(fā)行條款
       h 顯示 SQL 命令的說明
       ? 顯示 pgsql 命令的說明
       g 或者以分號(;)結(jié)尾以執(zhí)行查詢
       q 退出
postgres=#

# l 查看已存在的數(shù)據(jù)庫
postgres-# l
                                                                             數(shù)據(jù)庫列表
   名稱    |  擁有者  | 字元編碼 |                      校對規(guī)則                       |                        Ctype                        |       存取權(quán)限
-----------+----------+----------+-----------------------------------------------------+-----------------------------------------------------+-----------------------
 postgres  | postgres | UTF8     | Chinese (Simplified)_People"s Republic of China.936 | Chinese (Simplified)_People"s Republic of China.936 |
 template0 | postgres | UTF8     | Chinese (Simplified)_People"s Republic of China.936 | Chinese (Simplified)_People"s Republic of China.936 | =c/postgres          +
           |          |          |                                                     |                                                     | postgres=CTc/postgres
 template1 | postgres | UTF8     | Chinese (Simplified)_People"s Republic of China.936 | Chinese (Simplified)_People"s Republic of China.936 | =c/postgres          +
           |          |          |                                                     |                                                     | postgres=CTc/postgres
(3 行記錄)

# 我們可以發(fā)現(xiàn)默認(rèn)存在postgres、template0和template1數(shù)據(jù)庫，template`X`是模板數(shù)據(jù)庫
# template1為可修改模版庫，template0為不可修改模版庫

創(chuàng)建數(shù)據(jù)庫

postgres=# create database testdb;
# CREATE DATABASE

# c = connect
postgres=# c testdb;
# 您現(xiàn)在已經(jīng)連接到數(shù)據(jù)庫 "testdb",用戶 "postgres".

創(chuàng)建表

# 查看表
testdb=# d
# Did not find any relations.

# 創(chuàng)建表
testdb=# create table test1(id int primary key, name varchar(50));
# CREATE TABLE

testdb=# d
               關(guān)聯(lián)列表
 架構(gòu)模式 | 名稱  |  類型  |  擁有者
----------+-------+--------+----------
 public   | test1 | 數(shù)據(jù)表 | postgres
(1 行記錄)
# 架構(gòu)模式(schema)我們后續(xù)會講，暫時你可以先理解為一個數(shù)據(jù)庫邏輯分類的概念，默認(rèn)創(chuàng)建數(shù)據(jù)庫都會有一個public的schema

常規(guī)顯示設(shè)置

# 設(shè)置顯示查詢時間
	iming on
# 設(shè)置border的邊框內(nèi)外都有
pset border 2
# 查看編碼
encoding
# 設(shè)置編碼
encoding UTF8
# 開啟擴(kuò)展顯示，縱向打印每列數(shù)據(jù)
x
# 例子：
testdb=# select * from test1;
+-[ RECORD 1 ]-+
| id   | 1   |
| name | qkl |
+------+-----+

# 設(shè)置命令執(zhí)行的真正sql：on打開 off關(guān)閉
set ECHO_HIDDEN on
set ECHO_HIDDEN off
# 案例：
testdb=# set ECHO_HIDDEN on
testdb=# d
********* 查詢 **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN "r" THEN "table" WHEN "v" THEN "view" WHEN "m" THEN "materialized view" WHEN "i" THEN "index" WHEN "S" THEN "sequence" WHEN "s" THEN "special" WHEN "f" T
HEN "foreign table" WHEN "p" THEN "table" END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ("r","p","v","m","S","f","")
      AND n.nspname <> "pg_catalog"
      AND n.nspname <> "information_schema"
      AND n.nspname !~ "^pg_toast"
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

關(guān)聯(lián)列表
+-[ RECORD 1 ]--------+
| 架構(gòu)模式 | public   |
| 名稱     | test1    |
| 類型     | 數(shù)據(jù)表   |
| 擁有者   | postgres |
+----------+----------+

常用命令

testdb-# ?
一般性
  copyright            顯示PostgreSQL的使用和發(fā)行許可條款
  crosstabview [COLUMNS] 執(zhí)行查詢并且以交叉表顯示結(jié)果
  errverbose            以最冗長的形式顯示最近的錯誤消息
  g [文件] or;     執(zhí)行查詢 (并把結(jié)果寫入文件或 |管道)
  gexec                 執(zhí)行策略，然后執(zhí)行其結(jié)果中的每個值
  gset [PREFIX]     執(zhí)行查詢并把結(jié)果存到psql變量中
  gx [FILE]             as g, but forces expanded output mode
  q             退出 psql
  watch [SEC]          每隔SEC秒執(zhí)行一次查詢

幫助
  ? [commands]          顯示反斜線命令的幫助
  ? options             顯示 psql 命令行選項的幫助
  ? variables           顯示特殊變量的幫助
  h [名稱]          SQL命令語法上的說明，用*顯示全部命令的語法說明

查詢緩存區(qū)
  e [FILE] [LINE]        使用外部編輯器編輯查詢緩存區(qū)(或文件)
  ef [FUNCNAME [LINE]]   使用外部編輯器編輯函數(shù)定義
  ev [VIEWNAME [LINE]]  用外部編輯器編輯視圖定義
  p                    顯示查詢緩存區(qū)的內(nèi)容
  
                    重置(清除)查詢緩存區(qū)
  w 文件          將查詢緩存區(qū)的內(nèi)容寫入文件

輸入/輸出
  copy ...             執(zhí)行 SQL COPY，將數(shù)據(jù)流發(fā)送到客戶端主機(jī)
  echo [字符串]       將字符串寫到標(biāo)準(zhǔn)輸出
  i 文件          從文件中執(zhí)行命令
  ir FILE               與 i類似, 但是相對于當(dāng)前腳本的位置
  o [文件]        將全部查詢結(jié)果寫入文件或 |管道
  qecho [字符串]      將字符串寫到查詢輸出串流(參考 o)

Conditional
  if EXPR               begin conditional block
  elif EXPR             alternative within current conditional block
  else                  final alternative within current conditional block
  endif                 end conditional block

資訊性
  (選項: S = 顯示系統(tǒng)對象, + = 其余的詳細(xì)信息)
  d[S+]          列出表,視圖和序列
  d[S+]  名稱      描述表，視圖，序列，或索引
  da[S]  [模式]    列出聚合函數(shù)
  dA[+]  [PATTERN]      list access methods
  db[+]  [模式]     列出表空間
  dc[S+] [PATTERN]      列表轉(zhuǎn)換
  dC[+]  [PATTERN]      列出類型強(qiáng)制轉(zhuǎn)換
  dd[S]  [PATTERN]      顯示沒有在別處顯示的對象描述
  dD[S+] [PATTERN]      列出共同值域
  ddp     [模式]    列出默認(rèn)權(quán)限
  dE[S+] [PATTERN]      列出引用表
  det[+] [PATTERN]      列出引用表
  des[+] [模式]    列出外部服務(wù)器
  deu[+] [模式]     列出用戶映射
 dew[+] [模式]       列出外部數(shù)據(jù)封裝器
   df[antw][S+] [模式]    列出[只包括 聚合/常規(guī)/觸發(fā)器/窗口]函數(shù)
  dF[+]  [模式]   列出文本搜索配置
  dFd[+] [模式]     列出文本搜索字典
 dFp[+] [模式]     列出文本搜索解析器
  dFt[+] [模式]   列出文本搜索模版
  dg[S+] [PATTERN]      列出角色
 di[S+] [模式]  列出索引
  dl                   列出大對象， 功能與lo_list相同
  dL[S+] [PATTERN]      列出所有過程語言
  dm[S+] [PATTERN]      列出所有物化視圖
  dn[S+] [PATTERN]     列出所有模式
  do[S]  [模式]   列出運算符
  dO[S+] [PATTERN]      列出所有校對規(guī)則
  dp     [模式]     列出表，視圖和序列的訪問權(quán)限
  drds [模式1 [模式2]] 列出每個數(shù)據(jù)庫的角色設(shè)置
  dRp[+] [PATTERN]      list replication publications
  dRs[+] [PATTERN]      list replication subscriptions
  ds[S+] [模式]    列出序列
  dt[S+] [模式]     列出表
  dT[S+] [模式]  列出數(shù)據(jù)類型
  du[S+] [PATTERN]      列出角色
  dv[S+] [模式]   列出視圖
  dx[+]  [PATTERN]      列出擴(kuò)展
  dy     [PATTERN]      列出所有事件觸發(fā)器
  l[+]   [PATTERN]      列出所有數(shù)據(jù)庫
  sf[+]  FUNCNAME       顯示一個函數(shù)的定義
  sv[+]  VIEWNAME       顯示一個視圖的定義
  z      [模式]    和dp的功能相同

格式化
  a                  在非對齊模式和對齊模式之間切換
  C [字符串]        設(shè)置表的標(biāo)題，或如果沒有的標(biāo)題就取消
  f [字符串]         顯示或設(shè)定非對齊模式查詢輸出的字段分隔符
  H                    切換HTML輸出模式 (目前是 關(guān)閉)
  pset [NAME [VALUE]]   set table output option
                         (NAME := {border|columns|expanded|fieldsep|fieldsep_zero|
                         footer|format|linestyle|null|numericlocale|pager|
                         pager_min_lines|recordsep|recordsep_zero|tableattr|title|
                         tuples_only|unicode_border_linestyle|
                         unicode_column_linestyle|unicode_header_linestyle})
  	 [開|關(guān)]       只顯示記錄 (目前是 關(guān)閉)
  T [字符串]         設(shè)置HTML <表格>標(biāo)簽屬性, 或者如果沒有的話取消設(shè)置
  x [on|off|auto]       切換擴(kuò)展輸出模式(目前是 關(guān)閉)

連接
  c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
                         連接到新數(shù)據(jù)庫（當(dāng)前是"testdb"）
  conninfo              顯示當(dāng)前連接的相關(guān)信息
  encoding [編碼名稱] 顯示或設(shè)定客戶端編碼
  password [USERNAME]  安全地為用戶更改口令

操作系統(tǒng)
  cd [目錄]     更改目前的工作目錄
  setenv NAME [VALUE]   設(shè)置或清空環(huán)境變量
 	iming [開|關(guān)]       切換命令計時開關(guān) (目前是 開啟)
  ! [命令]      在 shell中執(zhí)行命令或啟動一個交互式shell

變量
  prompt [文本] 名稱 提示用戶設(shè)定內(nèi)部變量
  set [名稱 [值數(shù)]] 設(shè)定內(nèi)部變量，若無參數(shù)則列出全部變量
  unset 名稱    清空(刪除)內(nèi)部變量

大對象
  lo_export LOBOID 文件
  lo_import 文件 [注釋]
  lo_list
  lo_unlink LOBOID   大對象運算