一、什么是PostgreSQL?

PostgreSQL是一個(gè)功能強(qiáng)大的開源對(duì)象關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)(ORDBMS)。 用于安全地存儲(chǔ)數(shù)據(jù); 支持最佳做法们妥，并允許在處理請(qǐng)求時(shí)檢索它們拥刻。
PostgreSQL(也稱為Post-gress-Q-L)由PostgreSQL全球開發(fā)集團(tuán)(全球志愿者團(tuán)隊(duì))開發(fā)潭辈。 它不受任何公司或其他私人實(shí)體控制所袁。 它是開源的盏档，其源代碼是免費(fèi)提供的。
PostgreSQL是跨平臺(tái)的燥爷，可以在許多操作系統(tǒng)上運(yùn)行妆丘，如Linux，F(xiàn)reeBSD局劲，OS X，Solaris和Microsoft Windows等奶赠。

二鱼填、PostgreSQL的特點(diǎn)

• PostgreSQL可在所有主要操作系統(tǒng)(即Linux，UNIX(AIX毅戈，BSD苹丸，HP-UX，SGI IRIX苇经，Mac OS X赘理，Solaris，Tru64)和Windows等)上運(yùn)行扇单。
• PostgreSQL支持文本商模，圖像，聲音和視頻，并包括用于C/C++施流，Java响疚，Perl，Python瞪醋，Ruby忿晕，Tcl和開放數(shù)據(jù)庫(kù)連接(ODBC)的編程接口。
• PostgreSQL支持SQL的許多功能银受，例如復(fù)雜SQL查詢践盼，SQL子選擇，外鍵宾巍，觸發(fā)器咕幻，視圖，事務(wù)蜀漆，多進(jìn)程并發(fā)控制(MVCC)谅河，流式復(fù)制(9.0)，熱備(9.0))确丢。
• 在PostgreSQL中绷耍，表可以設(shè)置為從“父”表繼承其特征∠式模可以安裝多個(gè)擴(kuò)展以向PostgreSQL添加附加功能褂始。

三、安裝并配置描函，并設(shè)置遠(yuǎn)程登陸的用戶名和密碼

1.安裝postgreSQL

sudo apt-get update
主程序：
sudo apt-get install postgresql
數(shù)據(jù)庫(kù)圖形界面：
sudo apt install pgadmin3

• 在Ubuntu下安裝Postgresql后崎苗，會(huì)自動(dòng)注冊(cè)為服務(wù)，并隨操作系統(tǒng)自動(dòng)啟動(dòng)舀寓。
• 在Ubuntu下安裝Postgresql后胆数，會(huì)自動(dòng)添加一個(gè)名為postgres的操作系統(tǒng)用戶，密碼是隨機(jī)的互墓。并且會(huì)自動(dòng)生成一個(gè)名字為postgres的數(shù)據(jù)庫(kù)必尼，用戶名也為postgres，密碼也是隨機(jī)的篡撵。

2.修改postgres數(shù)據(jù)庫(kù)用戶的密碼為123456

打開客戶端工具（psql）
sudo -u postgres psql

• 其中判莉，sudo -u postgres 是使用postgres 用戶登錄的意思
• PostgreSQL數(shù)據(jù)庫(kù)默認(rèn)會(huì)創(chuàng)建一個(gè)postgres的數(shù)據(jù)庫(kù)用戶作為數(shù)據(jù)庫(kù)的管理員，密碼是隨機(jī)的

修改密碼的命令：
postgres=# ALTER USER postgres WITH PASSWORD '123456';

postgres=#為PostgreSQL下的命令提示符育谬，--注意最后的分號(hào)券盅；

3. 退出PostgreSQL psql客戶端

postgres=# \q

4.修改ubuntu操作系統(tǒng)的postgres用戶的密碼（密碼要與數(shù)據(jù)庫(kù)用戶postgres的密碼相同）

切換到root用戶
su root

刪除PostgreSQL用戶密碼
sudo passwd -d postgres

passwd -d 是清空指定用戶密碼的意思
設(shè)置PostgreSQL系統(tǒng)用戶的密碼

sudo -u postgres passwd

按照提示，輸入兩次新密碼

• 輸入新的 UNIX 密碼
• 重新輸入新的 UNIX 密碼
• passwd：已成功更新密碼

5.修改PostgresSQL數(shù)據(jù)庫(kù)配置實(shí)現(xiàn)遠(yuǎn)程訪問(wèn)

編輯配置文件： vi /etc/postgresql/9.4/main/postgresql.conf

添加/修改：在所有IP地址上監(jiān)聽膛檀，從而允許遠(yuǎn)程連接到數(shù)據(jù)庫(kù)服務(wù)器：

listen_addresses = 'localhost' 改為 listen_addresses = '*'

編輯配置文件：vi /etc/postgresql/9.4/main/pg_hba.conf

添加/修改：允許任意用戶從任意機(jī)器上以密碼方式訪問(wèn)數(shù)據(jù)庫(kù)锰镀，把下行添加為第一條規(guī)則：

host all all 0.0.0.0/0 md5

6.重啟服務(wù)

$ sudo systemctl restart postgresql
或者
/etc/init.d/postgresql restart

四娘侍、關(guān)于PostgreSQL的登錄角色，組角色互站，用戶等概念及權(quán)限分配上的區(qū)別

登錄角色就是具有登錄權(quán)限的角色私蕾，是通常意義上的用戶，不具有登錄權(quán)限的角色就是組角色胡桃，是一些登錄角色的集合踩叭。這樣的目的是為了方便批量授權(quán)。在PostgreSQL中登錄角色翠胰、組角色和用戶本質(zhì)上都是角色容贝；看下面的操作就知道了。

如圖锻狗，使用CREATE ROLE創(chuàng)建的角色在組角色下满力，使用CREATE USER創(chuàng)建的角色在登錄角色欄下。

五轻纪、角色的權(quán)限分配

PostgreSQL存在兩種權(quán)限油额，一種是角色權(quán)限即role attribute（例如創(chuàng)建角色，創(chuàng)建數(shù)據(jù)庫(kù)刻帚，修改目錄等）潦嘶，另一種是操作數(shù)據(jù)庫(kù)的權(quán)限即privilege（例如數(shù)據(jù)庫(kù)的連接，數(shù)據(jù)表的各種操作等）崇众。下面分別來(lái)說(shuō)掂僵。

1.角色權(quán)限

2.數(shù)據(jù)庫(kù)權(quán)限、成員角色及權(quán)限的繼承

PostgreSQL中預(yù)定義了許多不同類型的數(shù)據(jù)庫(kù)內(nèi)置權(quán)限眯漩，如：SELECT互妓、INSERT、UPDATE坤塞、DELETE、RULE澈蚌、REFERENCES摹芙、TRIGGER、CREATE宛瞄、TEMPORARY浮禾、EXECUTE和USAGE交胚。

任何角色都可以是成員角色，但是只有組角色能擁有成員角色盈电。成員角色會(huì)自動(dòng)繼承父角色（它所屬的組角色）的數(shù)據(jù)庫(kù)權(quán)限蝴簇。

一個(gè)登錄角色最終的權(quán)限等于其各個(gè)組角色所得權(quán)限的總合。

（準(zhǔn)確說(shuō)超級(jí)用戶的行為是不被檢查的）。那么如果你的登錄角色沒(méi)有組角色替废，那就給他創(chuàng)建一個(gè)組角色箍铭，讓登錄角色成為組角色的成員角色。另外舶担，我強(qiáng)烈建議不要隨便給登錄角色賦予超級(jí)用戶權(quán)限坡疼，因?yàn)檫@是相當(dāng)危險(xiǎn)的。

六衣陶、用戶柄瑰、角色和組的操作命令

postgres安裝完成后，會(huì)自動(dòng)在操作系統(tǒng)和postgres數(shù)據(jù)庫(kù)中分別創(chuàng)建一個(gè)名為postgres的用戶以及一個(gè)同樣名為postgres的數(shù)據(jù)庫(kù)剪况。

1.登錄

• 方式1：指定參數(shù)登錄

psql -U username -d database_name -h host -W
參數(shù)含義: -U指定用戶 -d要連接的數(shù)據(jù)庫(kù) -h要連接的主機(jī) -W提示輸入密碼教沾。

• 方式2：切換到postgres同名用戶后登錄

su username
psql
當(dāng)不指定參數(shù)時(shí)psql使用操作系統(tǒng)當(dāng)前用戶的用戶名作為postgres的登錄用戶名和要連接的數(shù)據(jù)庫(kù)名。所以在PostgreSQL安裝完成后可以通過(guò)以上方式登錄译断。

• 方式3：

psql -U jiraadmin -W jira
-U:以哪個(gè)用戶登錄
-W：登錄哪個(gè)數(shù)據(jù)庫(kù)授翻；
psql -U postgres -W transaction
直接回車以postgres身份登錄到transaction數(shù)據(jù)庫(kù)
psql -U postgres
以postgres身份登錄到默認(rèn)數(shù)據(jù)庫(kù)（即postgres數(shù)據(jù)庫(kù)）

2.創(chuàng)建數(shù)據(jù)庫(kù)新用戶，如 dbuser：

postgres=# CREATE USER dbuser WITH PASSWORD '*****';
創(chuàng)建用戶數(shù)據(jù)庫(kù)孙咪，如exampledb：
postgres=# CREATE DATABASE exampledb OWNER dbuser;
將exampledb數(shù)據(jù)庫(kù)的所有權(quán)限都賦予dbuser：
postgres=# GRANT ALL PRIVILEGES ON DATABASE exampledb TO dbuser;
使用命令 \q 退出psql：
postgres=# \q
創(chuàng)建Linux普通用戶堪唐，與剛才新建的數(shù)據(jù)庫(kù)用戶同名，如 dbuser：
sudo adduser dbuser
sudo passwd dbuser
以dbuser的身份連接數(shù)據(jù)庫(kù)exampledb：
su - dbuser

3.創(chuàng)建用戶時(shí)設(shè)定用戶屬性

基本語(yǔ)法：
CREATE ROLE role_name WITH optional_permissions;
示例：在創(chuàng)建用戶時(shí)設(shè)定登錄權(quán)限翎蹈。
CREATE ROLE username WITH LOGIN;

可以通過(guò)\h CREATE ROLE指令查看全部可設(shè)置的管理權(quán)限

4.修改用戶屬性

修改權(quán)限的命令格式
ALTER ROLE username WITH attribute_options;
示例：可通過(guò)以下方式禁止用戶登錄
ALTER ROLE username WITH NOLOGIN;

5.設(shè)置訪問(wèn)權(quán)限

語(yǔ)法格式如下:
GRANT permission_type ON table_name TO role_name;
示例：
GRANT UPDATE ON demo TO demo_role; --賦予demo_role demo表的update權(quán)限
GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC to demo_role; --賦予demo_role所有表的SELECT權(quán)限
特殊符號(hào):ALL代表所訪問(wèn)權(quán)限淮菠，PUBLIC代表所有用戶

GRANT ALL ON demo TO demo_role; --賦給用戶所有權(quán)限
GRANT SELECT ON demo TO PUBLIC; --將SELECT權(quán)限賦給所有用戶
\z或\dp指令顯示用戶訪問(wèn)權(quán)限。
\h GRANT顯示所有可設(shè)置的訪問(wèn)權(quán)限

6.撤銷用戶訪問(wèn)權(quán)限

語(yǔ)法格式如下:
REVOKE permission_type ON table_name FROM user_name;
其中permission_type和table_name含義與GRANT指令中相同荤堪。

7.用戶組

在postgres中用戶實(shí)際上是role合陵，同時(shí)組也是role枢赔。 包含其他role的role就是組。

創(chuàng)建組示例：
CREATE ROLE temporary_users;
GRANT temporary_users TO demo_role;
GRANT temporary_users TO test_user;
切換ROLE
SET ROLE role_name; --切換到role_name用戶
RESET ROLE; --切換回最初的role
INHERIT權(quán)限：該屬性使組成員擁有組的所有權(quán)限
ALTER ROLE test_user INHERIT;

刪除用戶和組
DROP ROLE role_name;
DROP ROLE IF EXISTS role_name;
刪除組role只會(huì)刪除組的role本身拥知，組的成員并不會(huì)被刪除

七踏拜、PostgreSQL常用操作命令

連接數(shù)據(jù)庫(kù), 默認(rèn)的用戶和數(shù)據(jù)庫(kù)是postgres
psql -U user -d dbname
切換數(shù)據(jù)庫(kù),相當(dāng)于mysql的use dbname
\c dbname
列舉數(shù)據(jù)庫(kù)，相當(dāng)于mysql的show databases
\l
列舉表低剔，相當(dāng)于mysql的show tables
\dt
查看表結(jié)構(gòu)速梗，相當(dāng)于desc tblname,show columns from tbname
\d tblname
\di 查看索引

創(chuàng)建數(shù)據(jù)庫(kù)：
create database [數(shù)據(jù)庫(kù)名];
刪除數(shù)據(jù)庫(kù)：
drop database [數(shù)據(jù)庫(kù)名];
重命名一個(gè)表：
alter table [表名A] rename to [表名B];
刪除一個(gè)表：
drop table [表名];
在已有的表里添加字段：
alter table [表名] add column [字段名] [類型];
刪除表中的字段：
alter table [表名] drop column [字段名];
重命名一個(gè)字段：
alter table [表名] rename column [字段名A] to [字段名B];
給一個(gè)字段設(shè)置缺省值：
alter table [表名] alter column [字段名] set default [新的默認(rèn)值];
去除缺省值：
alter table [表名] alter column [字段名] drop default;
在表中插入數(shù)據(jù)：
insert into 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......);
修改表中的某行某列的數(shù)據(jù)：
update [表名] set [目標(biāo)字段名]=[目標(biāo)值] where [該行特征];
刪除表中某行數(shù)據(jù)：
delete from [表名] where [該行特征];
delete from [表名];--刪空整個(gè)表

創(chuàng)建表：
create table ([字段名1] [類型1] ;,[字段名2] [類型2],......<,primary key (字段名m,字段名n,...)>;);
\copyright 顯示 PostgreSQL 的使用和發(fā)行條款
\encoding [字元編碼名稱]
顯示或設(shè)定用戶端字元編碼
\h [名稱] SQL 命令語(yǔ)法上的說(shuō)明，用 * 顯示全部命令
\prompt [文本] 名稱
提示用戶設(shè)定內(nèi)部變數(shù)
\password [USERNAME]
securely change the password for a user
\q 退出 psql

八户侥、啟動(dòng)镀琉，停止和重啟 PostgreSQL 服務(wù)器

命令提示
/etc/init.d/postgresql
啟動(dòng)PostgreSQL
sudo service postgresql start
停止PostgreSQL
sudo service postgresql stop
重新啟動(dòng)PostgreSQL服務(wù)器。
sudo service postgresql restart

九蕊唐、導(dǎo)入和導(dǎo)出postgresql數(shù)據(jù)庫(kù)腳本

• 導(dǎo)出

/usr/bin/pg_dump -U postgres testdb > /home/app/testdbdate +%Y%m%d.sql
cd /home/app
gzip testdbdate +%Y%m%d.sql

• 導(dǎo)入

day=date +%Y%m%d
drop database testdb;//刪除原來(lái)的庫(kù)
CREATE DATABASE testdb;
psql -U postgres -d testdb -f /home/app/testdb${day}.sql