目錄
一、PostgreSQL 概述
二、基本使用
（一）登錄數據庫
（二）數據庫操作
1. 列出數據庫
2. 創建數據庫
3. 刪除數據庫
4. 切換數據庫
5. 查看數據庫大小
（三）數據表操作
1. 列出表
2. 創建表
3. 復制表
4. 刪除表
5. 查看表結構
三、模式操作命令
（一）創建模式
（二）在指定模式中創建表
（三）切換當前模式
（四）查看當前所在 schema
（五）查看搜索路徑（Search Path）
（六）PostgreSQL 的模式隔離性
四、數據操作
（一）添加數據
（二）查詢數據
（三）修改數據
五、備份與恢復
（一）備份
（二）從轉儲中恢復
六、遠程連接 PostgreSQL
（一）配置訪問權限
（二）重啟服務
（三）驗證遠程連接
七、重置密碼
（一）備份配置文件
（二）修改配置文件
（三）重啟服務
（四）修改密碼
（五）恢復 pg_hba.conf 配置文件
?
?
一、PostgreSQL 概述
PostgreSQL（簡稱 pgsql）是一款功能強大的開源關系型數據庫，具備穩定性、擴展性以及對 SQL 標準的嚴格遵循等顯著特點。這些優勢使其在企業級開發與數據分析場景中得到廣泛應用。本筆記將圍繞 pgsql 的日常使用展開，涵蓋從基礎登錄到核心操作的完整流程，助力讀者掌握數據庫連接管理、庫表創建與操作、模式（Schema）設計、遠程訪問權限配置以及賬戶安全維護等關鍵技能。
二、基本使用
（一）登錄數據庫
在登錄 PostgreSQL 時，必須使用 postgres 用戶。具體操作步驟如下：
[root@bogon ~]# su -- postgres # 切換到 postgres 用戶
[postgres@bogon ~]$ /usr/local/pgsql/bin/psql # 啟動 psql 客戶端
?
登錄成功后，命令提示符將變為 “postgres=#”，其中 “postgres” 表示當前所在的數據庫。
（二）數據庫操作
1. 列出數據庫
在 PostgreSQL 中，有三種常用的列出數據庫的方法：
?
- 方法一：使用元命令 \l
在 psql 交互式終端中，以 “\” 開頭的命令稱為元命令，類似 MySQL 的 SHOW 語句，可用于快速管理數據庫。輸入 “\l” 即可列出所有數據庫。 - 方法二：使用擴展元命令 \l+
“\l+” 的輸出比 “\l” 多了 Size、Tablespace 和 Description 列，能提供更詳細的數據庫信息。輸入 “\l+” 即可查看擴展后的數據庫列表。 - 方法三：使用 SQL 命令
通過查詢系統表 pg_database 來獲取所有數據庫的信息，SQL 語句如下：postgres=# SELECT datname FROM pg_database;
pg_database 是系統表，存儲了 PostgreSQL 實例中所有數據庫的元信息，如數據庫名稱、所有者、編碼等。它屬于系統目錄（System Catalog），類似 MySQL 的 information_schema，但 PostgreSQL 的系統目錄更底層且直接存儲在 pg_catalog 模式中。由于系統表默認屬于 pg_catalog 模式，且 pg_catalog 始終位于搜索路徑（search_path）的首位，因此查詢時無需顯式指定模式。
2. 創建數據庫
使用 CREATE DATABASE 語句創建新的數據庫，示例如下：
postgres=# create database mydb;
3. 刪除數據庫
若要刪除不再需要的數據庫，可使用 DROP DATABASE 語句，示例如下：
postgres=# drop database mydb;
4. 切換數據庫
在 psql 中，使用 \c 元命令切換當前連接的數據庫，示例如下：
postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=#
5. 查看數據庫大小
- 使用 pg_database_size 函數以字節為單位返回數據庫的大小，示例如下：
postgres=# SELECT pg_database_size('mydb'); - 使用 pg_size_pretty () 函數將字節轉換為更易于閱讀的值，示例如下：
postgres=# SELECT pg_size_pretty(pg_database_size('mydb'));
（三）數據表操作
1. 列出表
在 PostgreSQL 中，有多種列出表的方法：
?
- 使用元命令 \dt：列出當前數據庫中 search_path 中模式里的表（默認 public 模式），示例如下：
mydb=# \dt; - 使用元命令 \d：列出表、視圖和序列，示例如下：
mydb=# \d - 使用元命令 \d+：列出更詳細的表信息，示例如下：
mydb=# \d+ - 使用元命令 \dt my_schema.*：列出指定模式下的表（例如 my_schema），示例如下：
mydb=# \dt my_schema.* - 使用元命令 \dt?.：查看當前數據庫的所有表（包括系統表），示例如下：
mydb=# \dt *.* - 使用 SQL 命令：通過查詢 pg_tables 視圖列出當前數據庫中指定模式下的所有表及其詳細信息，pg_tables 屬于 pg_catalog 模式，是基于 pg_class 和 pg_namespace 的邏輯視圖，并非物理表。無需切換數據庫，直接查詢 pg_catalog.pg_tables 即可獲取當前數據庫的表信息，示例如下：
mydb=# SELECT * FROM pg_tables WHERE schemaname = 'public';
2. 創建表
PostgreSQL 支持標準的 SQL 類型，如 int、smallint、real、double precision、char (N)、varchar (N)、date、time、timestamp 和 interval 等，還支持其他通用功能的類型和豐富的幾何類型，并且可以定制任意數量的用戶定義數據類型。創建表的示例如下：
postgres=# create table test(id int, name char(10), age int);
3. 復制表
要將已有的 table_name 表復制為新表 new_table，包括表結構和數據，可使用以下語句：
CREATE TABLE new_table AS TABLE table_name;
?
例如，將 test 表復制為 test2 表：
postgres=# CREATE TABLE test2 AS TABLE test;
4. 刪除表
使用 DROP TABLE 語句刪除表，示例如下：
postgres=# drop table test2;
5. 查看表結構
使用 \d 元命令查看表結構，示例如下：
postgres=# \d test;
?
輸出結果將顯示表的欄位、類型、校對規則、可空性和預設值等信息。
三、模式操作命令
在 PostgreSQL 中，模式（Schema）是一個邏輯容器，用于組織和管理數據庫對象（如表、視圖、函數、索引等），類似于文件系統中的文件夾。它可以幫助在同一個數據庫中分類存儲不同的對象，避免命名沖突，并實現權限隔離。
（一）創建模式
在當前庫 postgres 中創建名為 hr 的模式，示例如下：
postgres=# CREATE SCHEMA hr;
（二）在指定模式中創建表
未指定模式時，創建的對象（表、視圖等）會按 search_path 順序創建到第一個可用的模式中。在 postgres 庫中的 hr 模式下創建一個名為 employees 的表，示例如下：
postgres=# CREATE TABLE hr.employees (id SERIAL PRIMARY KEY, name TEXT);
（三）切換當前模式
切換模式也就是調整 search_path 的搜索范圍。
?
- 切換到單個 schema，示例如下：
SET search_path TO new_schema; - 切換到多個 schema（按優先級順序），示例如下：
SET search_path TO hr, public;
表示優先搜索 hr 模式，其次是 public 模式。
（四）查看當前所在 schema
使用 current_schema () 函數查看當前所在的 schema，示例如下：
postgres=# SELECT current_schema();
（五）查看搜索路徑（Search Path）
使用 SHOW search_path 命令查看當前的搜索路徑，示例如下：
postgres=# SHOW search_path;
（六）PostgreSQL 的模式隔離性
PostgreSQL 的模式是數據庫內的邏輯分組，不同模式可以存在同名表，這是與 MySQL 的不同之處。跨模式查詢需顯式指定模式名（如 schemal.users），或通過 search_path 設置默認模式，無需切換數據庫連接，所有操作在同一數據庫內完成。以下是一個跨模式查詢的示例：
?
- 創建一個數據庫 mydb，并切換到該數據庫：
postgres=# CREATE DATABASE mydb; postgres=# \c mydb - 在數據庫中創建兩個模式 schemal 和 schema2：
mydb=# CREATE SCHEMA schemal; mydb=# CREATE SCHEMA schema2; - 在每個模式中創建同名表，并插入數據：
mydb=# CREATE TABLE schemal.users (id int); mydb=# INSERT INTO schemal.users VALUES(1); mydb=# CREATE TABLE schema2.users (id int); mydb=# INSERT INTO schema2.users VALUES(2); - 跨模式查詢：
- 顯式指定模式名查詢：
mydb=# SELECT * FROM schemal.users; mydb=# SELECT * FROM schema2.users; - 設置 search_path 切換默認模式查詢：
mydb=# SET search_path TO schemal; mydb=# SELECT * FROM users; -- 默認訪問 schemal.users mydb=# SET search_path TO schema2; mydb=# SELECT * FROM users; -- 默認訪問 schema2.users
- 顯式指定模式名查詢：
四、數據操作
（一）添加數據
在 postgres 庫中新建表 test，并插入數據，示例如下：
postgres=# create table test(id int, name char(10), age int);
postgres=# insert into test values(1,'zhangsan', 18);
（二）查詢數據
使用 SELECT 語句查詢表中的數據，示例如下：
postgres=# select * from test;
（三）修改數據
使用 UPDATE 語句修改表中的數據，示例如下：
postgres=# update test set age=20 where id=1;
五、備份與恢復
（一）備份
pg_dump 是 PostgreSQL 中用于備份數據庫的工具。它可以備份整個數據庫、單個模式或單個表。以下是使用 pg_dump 備份數據庫的基本命令：
pg_dump -U username -h host -p port dbname > dumpfile
?
其中：
?
- -U username：指定連接數據庫的用戶名。
- -h host：指定數據庫服務器的主機名或 IP 地址。
- -p port：指定數據庫服務器的端口號。
- dbname：指定要備份的數據庫名稱。
- dumpfile：指定備份文件的名稱和路徑。
?
如果沒有足夠的特權來備份整個數據庫，仍然可以使用諸如 -n schema 或 -t table 選項來備份該數據庫中能夠訪問的部分。例如，備份指定模式 schema1 中的數據：
pg_dump -U postgres -h localhost -p 5432 mydb -n schema1 > schema1_backup.sql
?
要聲明 pg_dump 連接哪個數據庫服務器，使用命令行選項 -h host 和 -p port。默認主機是本地主機或 PGHOST 環境變量指定的主機，默認端口是環境變量 PGPORT 或（如果 PGPORT 不存在）內建的默認值。pg_dump 默認使用與當前操作系統用戶名同名的數據庫用戶名進行連接，要使用其他名字，要么聲明 -U 選項，要么設置環境變量 PGUSER。請注意，pg_dump 的連接也要通過客戶認證機制。
?
pg_dump 對于其他備份方法的一個重要優勢是，其輸出可以很容易地在新版本的 PostgreSQL 中載入，而文件級備份和連續歸檔都是極度的服務器版本限定的。pg_dump 也是唯一可以將一個數據庫傳送到一個不同機器架構上的方法，例如從一個 32 位服務器到一個 64 位服務器。由 pg_dump 創建的備份在內部是一致的，也就是說，轉儲表現了 pg_dump 開始運行時刻的數據庫快照，且在 pg_dump 運行過程中發生的更新將不會被轉儲。pg_dump 工作的時候并不阻塞其他的對數據庫的操作（但是會阻塞那些需要排它鎖的操作，比如大部分形式的 ALTER TABLE）。
（二）從轉儲中恢復
pg_dump 生成的文本文件可以由 psql 程序讀取。從轉儲中恢復的常用命令是：
psql dbname < dumpfile
?
其中 dumpfile 就是 pg_dump 命令的輸出文件。這條命令不會創建數據庫 dbname，必須在執行 psql 前自己從 template0 創建（例如，用命令 createdb -T template0 dbname）。psql 支持類似 pg_dump 的選項用以指定要連接的數據庫服務器和要使用的用戶名。非文本文件轉儲可以使用 pg_restore 工具來恢復。
?
在開始恢復之前，轉儲庫中對象的擁有者以及在其上被授予了權限的用戶必須已經存在。如果它們不存在，那么恢復過程將無法將對象創建成具有原來的所屬關系以及權限（有時候這就是所需要的，但通常不是）。
?
默認情況下，psql 腳本在遇到一個 SQL 錯誤后會繼續執行。如果希望在遇到一個 SQL 錯誤后讓 psql 退出，那么可以設置 ON_ERROR_STOP 變量來運行 psql，這將使 psql 在遇到 SQL 錯誤后退出并返回狀態 3，示例如下：
psql --set ON_ERROR_STOP=on dbname < infile
六、遠程連接 PostgreSQL
（一）配置訪問權限
默認情況下，PostgreSQL 只能本地訪問，要允許遠程連接，需要在 pg_hba.conf 配置文件中進行配置。找到 IPv4 local connections 這一行，在這一行下面添加以下內容：
host all all 0.0.0.0/0 trust
?
其中：
?
- host：指定連接類型，host 表示該規則適用于通過 TCP/IP 進行的遠程連接，如果是本地連接，通常會使用 local。
- all：定義哪些數據庫可以接受這個規則，all 表示適用于所有數據庫，也可以指定特定的數據庫名。
- all：定義哪些用戶可以接受這個規則，all 表示適用于所有用戶，也可以指定特定的用戶名。
- 0.0.0.0/0：定義哪些客戶端 IP 地址或 IP 地址范圍可以接受這個規則，0.0.0.0/0 表示任何 IP 地址（即沒有 IP 地址限制），也可以指定具體的 IP 地址或 IP 地址范圍。
- trust：定義認證方法，trust 表示不需要密碼或其他任何形式的認證，客戶端可以直接連接。這通常只在本地或受信任的網絡環境中使用，因為它允許任何人無需認證即可訪問數據庫，在生產環境中，應該使用更安全的認證方法，如 md5 或 password（對于較新版本的 PostgreSQL，建議使用 scram-sha-256）。
?
如果選擇使用 md5 或 password 等需要密碼的認證方法，需要先為用戶設置密碼，示例如下：
postgres=# ALTER USER postgres WITH PASSWORD '123456';
（二）重啟服務
配置完 pg_hba.conf 文件后，需要重啟 PostgreSQL 服務，使配置生效，示例如下：
[root@localhost ~]# systemctl start postgresql
（三）驗證遠程連接
使用其它主機遠程連接本機數據庫，根據配置的認證方法不同，操作也有所不同：
?
- 如果設置的是 trust 認證方法，無需密碼可直接登錄，示例如下：
[postgres@localhost ~]$ psql -h 192.168.10.102 - 如果設置的是 md5 或 password 等需要密碼的認證方法，登錄時需要輸入密碼，示例如下：
[postgres@localhost ~]$ psql -h 192.168.10.102 Password for user postgres:
七、重置密碼
如果忘記了 PostgreSQL 的密碼，可以通過以下步驟重置密碼：
（一）備份配置文件
對 pg_hba.conf 文件進行備份，示例如下：
[root@localhost ^]# cp /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.confbak
（二）修改配置文件
修改配置文件以信任本地連接不需要密碼，將配置文件中的認證方法（如 scram-sha-256 或者 md5）修改為 trust，示例如下：
[root@localhost ^]# vim /var/lib/pgsql/data/pg_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 trust
（三）重啟服務
修改完配置文件后，重啟 PostgreSQL 服務，示例如下：
[root@localhost ^]# systemctl restart postgresql
（四）修改密碼
登錄數據庫修改密碼，密碼自定義，示例如下：
postgres=# ALTER USER postgres WITH PASSWORD 'new_password';
（五）恢復 pg_hba.conf 配置文件
將備份的 pg_hba.confbak 文件的內容覆蓋 pg_hba.conf，重啟 PostgreSQL 數據庫服務器，重新登陸時，如果提示輸入密碼，則輸入剛才修改的密碼即可。
?
?
機制)
