簡(jiǎn)介和認(rèn)知

發(fā)音

post-gres-q-l

服務(wù)（server）

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

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

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

模式（schema）

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

表（table）

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

列（column）

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

表空間（tablespace）

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

視圖（view）

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

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

行（row）

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

reindexdb

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

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

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

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

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

安裝

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

psql客戶端簡(jiǎn)單實(shí)用

連接

# $MY_POSTGRES_PATH = D:\PostgreSQL; 這個(gè)環(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" 來(lái)獲取幫助信息. postgres=## 輸入help得到以下提示 postgres=# help 您正在使用psql, 這是一種用于訪問(wèn)PostgreSQL的命令行界面 鍵入： \copyright 顯示發(fā)行條款\h 顯示 SQL 命令的說(shuō)明\? 顯示 pgsql 命令的說(shuō)明\g 或者以分號(hào)(;)結(jié)尾以執(zhí)行查詢\q 退出 postgres=## \l 查看已存在的數(shù)據(jù)庫(kù) postgres-# \l數(shù)據(jù)庫(kù)列表名稱 | 擁有者 | 字元編碼 | 校對(duì)規(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/postgrestemplate1 | 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ù)庫(kù)，template`X`是模板數(shù)據(jù)庫(kù) # template1為可修改模版庫(kù)，template0為不可修改模版庫(kù)

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

postgres=# create database testdb; # CREATE DATABASE# \c = \connect postgres=# \c testdb; # 您現(xiàn)在已經(jīng)連接到數(shù)據(jù)庫(kù) "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 TABLEtestdb=# \d關(guān)聯(lián)列表架構(gòu)模式 | 名稱 | 類型 | 擁有者 ----------+-------+--------+----------public | test1 | 數(shù)據(jù)表 | postgres (1 行記錄) # 架構(gòu)模式(schema)我們后續(xù)會(huì)講，暫時(shí)你可以先理解為一個(gè)數(shù)據(jù)庫(kù)邏輯分類的概念，默認(rèn)創(chuàng)建數(shù)據(jù)庫(kù)都會(huì)有一個(gè)public的schema

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

# 設(shè)置顯示查詢時(shí)間 \timing 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 cLEFT 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 以最冗長(zhǎng)的形式顯示最近的錯(cuò)誤消息\g [文件] or; 執(zhí)行查詢 (并把結(jié)果寫入文件或 |管道)\gexec 執(zhí)行策略，然后執(zhí)行其結(jié)果中的每個(gè)值\gset [PREFIX] 執(zhí)行查詢并把結(jié)果存到psql變量中\(zhòng)gx [FILE] as \g, but forces expanded output mode\q 退出 psql\watch [SEC] 每隔SEC秒執(zhí)行一次查詢幫助\? [commands] 顯示反斜線命令的幫助\? options 顯示 psql 命令行選項(xiàng)的幫助\? variables 顯示特殊變量的幫助\h [名稱] SQL命令語(yǔ)法上的說(shuō)明，用*顯示全部命令的語(yǔ)法說(shuō)明查詢緩存區(qū)\e [FILE] [LINE] 使用外部編輯器編輯查詢緩存區(qū)(或文件)\ef [FUNCNAME [LINE]] 使用外部編輯器編輯函數(shù)定義\ev [VIEWNAME [LINE]] 用外部編輯器編輯視圖定義\p 顯示查詢緩存區(qū)的內(nèi)容\r 重置(清除)查詢緩存區(qū)\w 文件 將查詢緩存區(qū)的內(nèi)容寫入文件輸入/輸出\copy ... 執(zhí)行 SQL COPY，將數(shù)據(jù)流發(fā)送到客戶端主機(jī)\echo [字符串] 將字符串寫到標(biāo)準(zhǔn)輸出\i 文件 從文件中執(zhí)行命令\ir FILE 與 \i類似, 但是相對(duì)于當(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資訊性(選項(xiàng): S = 顯示系統(tǒng)對(duì)象, + = 其余的詳細(xì)信息)\d[S+] 列出表,視圖和序列\(zhòng)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] 顯示沒(méi)有在別處顯示的對(duì)象描述\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 列出大對(duì)象， 功能與\lo_list相同\dL[S+] [PATTERN] 列出所有過(guò)程語(yǔ)言\dm[S+] [PATTERN] 列出所有物化視圖\dn[S+] [PATTERN] 列出所有模式\do[S] [模式] 列出運(yùn)算符\dO[S+] [PATTERN] 列出所有校對(duì)規(guī)則\dp [模式] 列出表，視圖和序列的訪問(wèn)權(quán)限\drds [模式1 [模式2]] 列出每個(gè)數(shù)據(jù)庫(kù)的角色設(shè)置\dRp[+] [PATTERN] list replication publications\dRs[+] [PATTERN] list replication subscriptions\ds[S+] [模式] 列出序列\(zhòng)dt[S+] [模式] 列出表\dT[S+] [模式] 列出數(shù)據(jù)類型\du[S+] [PATTERN] 列出角色\dv[S+] [模式] 列出視圖\dx[+] [PATTERN] 列出擴(kuò)展\dy [PATTERN] 列出所有事件觸發(fā)器\l[+] [PATTERN] 列出所有數(shù)據(jù)庫(kù)\sf[+] FUNCNAME 顯示一個(gè)函數(shù)的定義\sv[+] VIEWNAME 顯示一個(gè)視圖的定義\z [模式] 和\dp的功能相同格式化\a 在非對(duì)齊模式和對(duì)齊模式之間切換\C [字符串] 設(shè)置表的標(biāo)題，或如果沒(méi)有的標(biāo)題就取消\f [字符串] 顯示或設(shè)定非對(duì)齊模式查詢輸出的字段分隔符\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})\t [開|關(guān)] 只顯示記錄 (目前是 關(guān)閉)\T [字符串] 設(shè)置HTML <表格>標(biāo)簽屬性, 或者如果沒(méi)有的話取消設(shè)置\x [on|off|auto] 切換擴(kuò)展輸出模式(目前是 關(guān)閉)連接\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}連接到新數(shù)據(jù)庫(kù)（當(dāng)前是"testdb"）\conninfo 顯示當(dāng)前連接的相關(guān)信息\encoding [編碼名稱] 顯示或設(shè)定客戶端編碼\password [USERNAME] 安全地為用戶更改口令操作系統(tǒng)\cd [目錄](méi) 更改目前的工作目錄\setenv NAME [VALUE] 設(shè)置或清空環(huán)境變量\timing [開|關(guān)] 切換命令計(jì)時(shí)開關(guān) (目前是 開啟)\! [命令] 在 shell中執(zhí)行命令或啟動(dòng)一個(gè)交互式shell變量\prompt [文本] 名稱 提示用戶設(shè)定內(nèi)部變量\set [名稱 [值數(shù)]] 設(shè)定內(nèi)部變量，若無(wú)參數(shù)則列出全部變量\unset 名稱 清空(刪除)內(nèi)部變量大對(duì)象\lo_export LOBOID 文件\lo_import 文件 [注釋]\lo_list\lo_unlink LOBOID 大對(duì)象運(yùn)算 《新程序員》：云原生和全面數(shù)字化實(shí)踐50位技術(shù)專家共同創(chuàng)作，文字、視頻、音頻交互閱讀

總結(jié)

以上是生活随笔為你收集整理的PostgreSQL的实践一：初识的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。