IT｜資料庫｜PostgreSQL 安裝於 Mac OS、Ubuntu Linux
PostgreSQL 介紹
PostgreSQL 是以加州大學伯克利分校計算機系開發的 POSTGRES 版本 4.2 為基礎的對象關係型數據庫管理系統（ORDBMS），簡稱 pgsql，是一個自由的對象-關係數據庫服務器，由社區驅動的的對象-關係型 數據庫系統,它支持大部分 SQL 標準並且提供了許多其他現代特性,它在靈活的 BSD-風格許可證下發行.越來越多的人用它來取代mysql 。已成為開發首選的開源關係型數據庫。
安裝 PostgreSQL
[安裝標的：Mac OS X]
在 MAC 環境下,除了可以使用 brew 安裝, 也可以直接從網路下載安裝檔來安裝，本例以安裝檔作為安裝示範。
1.使用 brew 指令安裝
brew install postgresql
2.選擇 Mac OS X 版本下載，直接安裝即可。（本例以此為示範）
PostgreSQL 官網下載地址
系統安裝相關目錄
資料庫系統安裝路徑 /Library/PostgreSQL/10
資料庫安裝路徑 /Library/PostgreSQL/10/data
在安裝的過程中系統會自動為生成一個賬號 PostgreSQL，密碼隨機。
若需要要重設密碼，可透過上面 GUI 介面重設，或透過控制台以指令方式重設
sudo passwd -d postgres
＃指令說明
password -d 为删除指定用户密码
設置新密碼
sudo -u postgres passwd
然後就可以用 postgres 用戶在服務器上通過 pgAdmin 或者 psql 來操作資料庫了。
[執行 SOL Shell]
執行 SQL Shell (psql) 啓動程式
Server [localhost]: //第一步，默認 localhost,遠端資料庫,請輸入遠端服務器 IP
Database [postgres]: //第二步，執行資料庫, postgresql 安裝時已經默認安裝 postgres 數據庫
Port [5432]: //第三步，默認綁定 5432 端口,如果有修改請在這裡指定，否則默認連接此端口(注意防火牆放行端口)
Username [postgres]: //第四步,默認使用 postgres 用戶登錄資料庫，沒有指定用戶的話
psql (10)
Type "help" for help.
postgres=# //看到這一步，已經成功進入 postgres 資料庫.
或者可以用下面一條命令來登錄 PostgreSQL
psql -U postgres -d postgres -h 127.0.0.1 -p 5432
參數說明：
-U 指定用戶 -d 指定資料庫 -h 資料庫服務器 IP -p 端口
如果你的系統登錄帳號和 PostgreSQL 數據庫操作用戶名相同的話,可以省略 -U 參數。
若遇到 Mac 命令控制台不支持 psql 指令 ,則需要手動添加到全局環境變數
$ locate psql | grep /bin
/Library/PostgreSQL/10/bin/psql
編輯 .bash_profile 文件或 .bashrc 文件，添加命令別名
alias psql=/Library/PostgreSQL/10/bin/psql
然後讓 .bash_profile 生效即可
source .bash_profile
移除 PostgreSQL
執行刪除程式
cd /Library/PostgreSQL/10
執行 uninstall-postgresql
刪除系統 postgres 帳號
[安裝標的：Linux]
Create the file /etc/apt/sources.list.d/pgdg.list, and add a line for the repository
$ vim /etc/apt/sources.list.d/pgdg.lis
在檔案內添加這一行
deb http://apt.postgresql.org/pub/repos/apt/ YOUR_UBUNTU_VERSION_HERE-pgdg main
＃YOUR_UBUNTU_VERSION_HERE-pgdg 為 UBUNTU 版本，以我安裝的 Ubuntu 16.04 LTS 為例，可以是如下的指令
deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main
Import the repository signing key, and update the package lists
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
sudo apt-key add -
$ sudo apt-get update
安裝 pg，注意版本號可以自己指定，目前最新穩定版是9.6（2017-12-26 ）
$ apt-get install postgresql-9.6
安裝完之後，Linux 系統中多了一個用戶 postgres。同時 pg 數據庫也會創建一個數據庫用戶，用戶名也叫 postgres。還會創建一個數據庫，資料庫名也是叫 postgres。
我們切換到 postgres 用戶，然後直接連一下數據庫看看：
$ su - postgres
$ psql
系統提示符變成了 postgres=#，表示已經成功登錄到了名為 postgres 的資料庫。
psql 簡單的操作指令
\h select。
\? 查看psql命令列表。
\l 列出所有資料庫。
\c [database_name]：連接其他資料庫。
\d 列出當前資料庫的所有表格。
\d [table_name]：列出某一張表格的結構。
\du 列出所有用戶。
\e 打開文字編輯器。
\conninfo 列出當前資料庫和連接的資訊。
\q 退出
\password 設置命令
# 創建新表
CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
# 插入資料
INSERT INTO user_tbl(name, signup_date) VALUES('張三', '2013-12-22');
# 選擇記錄
SELECT * FROM user_tbl;
# 更新資料
UPDATE user_tbl set name = '李四' WHERE name = '張三';
# 刪除記錄
DELETE FROM user_tbl WHERE name = '李四' ;
# 添加欄位
ALTER TABLE user_tbl ADD email VARCHAR(40);
# 更新結構
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
# 更名欄位
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;
# 刪除欄位
ALTER TABLE user_tbl DROP COLUMN email;
# 表格更名
ALTER TABLE user_tbl RENAME TO backup_tbl;
# 刪除表格
DROP TABLE IF EXISTS backup_tbl;
pg 規則
命令行客戶端 psql 有個規則，就是當登錄命令沒指定數據庫用戶的時候，會以當前系統用戶同名的數據庫用戶來登錄，此時視為已經具備了該數據庫用戶的身份，因此登錄時無需輸入密碼。這叫做 IDENT/PEER authentication。
另外還有個規則，如果登錄時不指定數據庫，會嘗試登錄與用戶名同名的數據庫。
知道這兩條規則，就不難理解為什麼剛剛不指定用戶名密碼和數據庫就成功登上了。
自動創建的數據庫用戶 postgres 是超級用戶，這個用戶權限一旦被攻破後果不堪設想。因此，自動創建的同名系統用戶 postgres 的權限安全也就同樣重要，所以該用戶默認是不能以用戶名密碼登錄操作系統的，此乃故意為之。具體原因這個帖子有解釋。
一般超級用戶 postgres 只用來做管理，其他所有事情最好創建專門的數據庫用戶去做。注意 psql 里執行每條語句都要以分號結束。
創建用戶（注意密碼用單引號括起來）
創建好用戶後，創建一個屬於這個用戶的資料庫：
postgres=# create user <user> with password '<password>';
創建完資料庫用戶，再創建一個同名的系統用戶：
postgres=# create database <database> owner <user>;
切換成這個系統用戶，就可以登錄資料庫
$ adduser <user>
遠程連接配置
$ su - <user>
$ psql -d <database>
允許遠端登入資料庫
默認只允許從本機登錄，如果要遠程連接資料庫，需要改兩個配置文件。
修改配置文件
$ vim /etc/postgresql/9.6/main/postgresql.conf
#把監聽地址由默認的 localhost 改成 *
listen_addresses = '*'
修改配置文件
$ vim /etc/postgresql/9.6/main/pg_hba.conf
#允許某個用戶從任何遠程主機連接某個數據庫：
#host <database> <user> all md5
#允許所有用戶從任何遠程主機連接所有數據庫：
host all all all md5
#在文件中添加一行，把允許哪個用戶訪問哪個數據庫寫進去，如果怕麻煩不想挨個做限制，也可以寫成 all。
#hba，這幾個字母的意思是 host based authorization，就是對主機進行細粒度的訪問控制。
改完這兩個配置文件，需要重啓服務才能生效（注意配置文件中不要有語法錯誤，否則服務不聲不響地起不來，也不報錯。我在設定時犯了點錯，因為在 pg_hba.conf 添加的那行結尾多了個分號，浪費了不少時間。）
$ service postgresql restart
大功告成！
| 這封郵件來自 Evernote。Evernote 是您專屬的工作空間，免費下載 Evernote |
留言
張貼留言