目錄

前言

基本使用

1. 登錄數據庫

2. 數據庫操作

2.1 列出庫

2.2 創建庫

2.3 刪除庫

2.4 切換庫

2.5 查看庫大小

3. 數據表操作

3.1 列出表

3.2 創建表

3.3 復制表

3.4 刪除表

3.5 查看表結構

4. 模式操作命令

4.1 創建模式

4.2 默認模式

4.3 刪除模式

4.4 查看所有模式

4.5 在指定模式中創建表

4.6 切換當前模式

4.7 查看當前所在 schema

4.8 查看搜索路徑（Search Path）

4.9 PostgreSQL 的模式隔離性

5. 數據操作

5.1 添加數據

5.2 查詢數據

5.3 修改數據

5.4 刪除數據

6. 備份與恢復

6.1 SQL 轉儲

6.2 從轉儲中恢復

6.3 使用 pg_dumpall

7. 遠程連接

7.1 修改 PostgreSQL 監聽地址

7.2 配置訪問權限

7.3 重啟服務

7.4 驗證遠程連接

8. 重置密碼

8.1 備份配置文件

8.2 修改配置文件

8.3 重啟服務

8.4 修改密碼

8.5 恢復 pg_hba.conf 配置文件

總結

---

前言

PostgreSQL 作為功能強大的開源關系型數據庫，在企業級開發與數據分析中應用廣泛，本內容圍繞其日常維護展開，涵蓋從基礎操作到遠程連接、備份恢復、密碼重置等關鍵技能，助力使用者系統化掌握維護能力，為高效管理數據庫奠定基礎。

基本使用

1. 登錄數據庫

Pgsql 登錄時，必須使用 postgres 用戶，登錄后的命令提示符為 "postgres=#"，postgres 表示你當前所在的庫。

[postgres@localhost ~]$ psql
psql (15.4)
Type "help" for help.

2. 數據庫操作

2.1 列出庫

常用的三種方法如下：

• 方法一：使用元命令\l

postgres=# \l

在 PostgreSQL 的交互式終端 psq1 中，"" 開頭的命令稱為元命令（類似 MySQL 的 SHOW 語句），用于快速管理數據庫。
常用元命令有：

• \l：列出所有數據庫。

• \c [數據庫名]或\connect [數據庫名]：連接到指定數據庫。

• \dn：列出所有模式（Schema）。

• \db：列出所有表空間。

• \?：顯示 pgsq1 命令的說明（元命令查詢幫助）。

• \q：退出 psql。

• \dt：列出當前數據庫的所有表。

• \d [TABLE]：查看表結構。

• \du：列出所有用戶。

• 方法二：使用元命令\l+

postgres=# \l+

\l+的輸出比\l多了 Size、Tablespace 和 Description 列。+表示擴展輸出，顯示更多字段或詳細信息。

• 方法三：使用 SQL 命令

postgres=# SELECT datname FROM pg_database;

pg_database是系統表，它存儲了 PostgreSQL 實例中所有數據庫的元信息（如數據庫名稱、所有者、編碼等）。屬于系統目錄（System Catalog），類似 MySQL 的 information_schema，但 PostgreSQL 的系統目錄更底層且直接存儲在 pg_catalog 模式中。pg_database 是系統目錄表，所以無論當前連接到哪個數據庫，該表始終可見。系統表默認屬于 pg_catalog 模式，而 pg_catalog 始終位于搜索路徑（search_path）的首位。因此，查詢時無需顯式指定模式（如 pg_catalog.pg_database）。

2.2 創建庫

postgres=# create database mydb;
CREATE DATABASE

2.3 刪除庫

postgres=# drop database mydb;
DROP DATABASE

2.4 切換庫

postgres=# \c mydb
mydb=# You are now connected to database "mydb" as user "postgres".

2.5 查看庫大小

• 函數以字節為單位返回數據庫的大小

postgres=# select pg_database_size('mydb');pg_database_size 
------------------7484207
(1 row)

• pg_size_pretty()函數將字節轉為更易于閱讀的值

postgres=# select pg_database_size('mydb');pg_database_size 
------------------7484207
(1 row)

3. 數據表操作

3.1 列出表

列出表的常用方法：

• mydb=# \dt;：列出表（顯示 search_path 中模式里的表，默認 public）。
• mydb=# \d：列出表、視圖和序列。
• mydb=# \d+：列出詳細信息。
• mydb=# \dt my_schema.*：列出指定模式下的表（例如 my_schema）。
• mydb=# \dt*.*：查看當前數據庫的所有表（包括系統表）。
• mydb=# SELECT * FROM pg_tables WHERE schemaname='public';：使用 SQL 方式列出當前數據庫中 public 模式下的所有表及其詳細信息。

pg_tables是視圖，屬于 pg_catalog 模式，但它是基于 pg_class 和 pg_namespace 的邏輯視圖，并非物理表。無需切換數據庫，直接查詢 pg_catalog.pg_tables 即可獲取當前數據庫的表信息。

3.2 創建表

PostgreSQL 支持標準的 SQL 類型 int、smallint、real、double precision、char (N)、varchar (N)、date、time、timestamp 和 interval，還支持其他的通用功能的類型和豐富的幾何類型。PostgreSQL 中可以定制任意數量的用戶定義數據類型。因而類型名并不是語法關鍵字，除了 SQL 標準要求支持的特例外。

postgres=# create table test (id int, name char(10), age int);
CREATE TABLE

3.3 復制表

要將已有的 table_name 表復制為新表 new_table，包括表結構和數據，請使用以下語句：

CREATE TABLE new_table AS TABLE table_name;

例如：

postgres=# CREATE TABLE test2 AS TABLE test;
postgres=# \dt架構模式 |  名稱  |  類型  | 擁有者
-----------------------+--------+--------+---------hr                    | employees | 數據表 | postgreshr                    | test     | 數據表 | postgreshr                    | test2    | 數據表 | postgres
(3行記錄)

3.4 刪除表

postgres=# drop table test2;

3.5 查看表結構

postgres=# \d test;欄位       |          類型          |  校對規則  | 可空的 | 預設
----------------------------------+------------------------+------------+--------+------id                               | integer                |            |        |name                             | character(10)          |            |        |age                              | integer                |            |        |

4. 模式操作命令

在 PostgreSQL 中，模式（Schema）是一個邏輯容器，用于組織和管理數據庫對象（如表、視圖、函數、索引等）。它類似于文件系統中的文件夾，幫助你在同一個數據庫中分類存儲不同的對象，避免命名沖突，并實現權限隔離。

4.1 創建模式

在當前庫 postgres 中創建名為 hr 的模式：

postgres=# CREATE SCHEMA hr;
CREATE SCHEMA

4.2 默認模式

PostgreSQL 每個數據庫都有一個默認模式 public。如果創建對象（表、視圖等）時不指定模式，默認會放在 public 模式中。通過 search_path 參數可以設置模式的搜索優先級（類似 PATH 環境變量）：

postgres=# SHOW search_path;search_path
-------------"$user",public
(1行記錄)

search_path 用于控制對象解析順序，避免每次查詢都要寫模式名。"$user",public 表示優先查找當前用戶同名模式，再找 public 模式。

4.3 刪除模式

• 刪除空模式：

postgres=# DROP SCHEMA hr;
DROP SCHEMA

• 強制刪除模式及其所有對象：

postgres=# DROP SCHEMA hr CASCADE;
DROP SCHEMA

4.4 查看所有模式

• 元命令列出當前庫中所有模式：

postgres=# \dn名稱   |  架構模式列表  | 擁有者
----------------+----------------+---------public         | pg_database_owner |
(1行記錄)

• SQL 查詢，列出當前庫中所有模式：

postgres=# SELECT schema_name FROM information_schema.schemata;schema_name
---------------information_schemapg_catalogpg_toastpublic
(4行記錄)

4.5 在指定模式中創建表

未指定模式時，創建的對象（表，視圖等）會按 search_path 順序創建到第一個可用的模式中。在 postgres 庫中的 hr 模式下創建一個名為 employees 的表：

postgres=# CREATE TABLE hr.employees (id int , name char(10));

4.6 切換當前模式

切換模式也就是調整 search_path 的搜索范圍。

• 切換到單個 schema：

SET search_path TO new_schema;

• 切換到多個 schema（按優先級順序）

SET search_path TO hr, public;

表示優先搜索 hr 模式，其次 public。

4.7 查看當前所在 schema

postgres=# SELECT current_schema();current_schema
----------------hr
(1行記錄)

4.8 查看搜索路徑（Search Path）

postgres=# SHOW search_path;search_path
-------------hr, public
(1行記錄)

4.9 PostgreSQL 的模式隔離性

PostgreSQL 的模式是數據庫內的邏輯分組，不同模式可以存在同名表。這也是和 mysql 的不同之處。跨模式查詢需顯式指定模式名（如 schema1.users），或通過 search_path 設置默認模式。無需切換數據庫連接，所有操作在同一數據庫內完成。

• 步驟 1：創建一個數據庫

postgres=# CREATE DATABASE mydb;
postgres=# \c mydb

• 步驟 2：在數據庫中創建兩個模式

mydb=# CREATE SCHEMA schema1;
mydb=# CREATE SCHEMA schema2;

• 步驟 3：在每個模式中創建同名表，并插入數據

mydb=# CREATE TABLE schema1.users (id int);
mydb=# INSERT INTO schema1.users VALUES (1);
mydb=# CREATE TABLE schema2.users (id int);
mydb=# INSERT INTO schema2.users VALUES(2);

• 步驟 4：跨模式查詢

mydb=# SELECT * FROM schema1.users;
mydb=# SELECT * FROM schema2.users;

設置 search_path 切換默認模式（不需顯式指定模式名）：

mydb=# SET search_path TO schema1;
mydb=# SELECT * FROM users; -- 默認訪問schema1.users
mydb=# SET search_path TO schema2;
mydb=# SELECT * FROM users; -- 默認訪問schema2.users

5. 數據操作

5.1 添加數據

在 postgres 庫，新建表 test：

postgres=# create table test(id int,name char(10),age int);
CREATE TABLE
postgres=# insert into test values(1,'zhangsan', 18);
INSERT 0 1

5.2 查詢數據

postgres=# select * from test;id |  name  | age
----+--------+-----1 | zhangsan | 18
(1行記錄)

5.3 修改數據

postgres=# update test set age=30 where id=1;
UPDATE 1
postgres=# select * from test;id |  name  | age
----+--------+-----1 | zhangsan | 30
(1行記錄)

5.4 刪除數據

postgres=# delete from test where id=1;
DELETE 1
postgres=# select * from test;id | name | age
----+------+-----
(0行記錄)

6. 備份與恢復

PostgreSQL 數據庫應當被定期地備份。雖然過程相當簡單，但清晰地理解其底層技術和假設是非常重要的。有三種不同的基本方法來備份 PostgreSQL 數據：SQL 轉儲、文件系統級備份、連續歸檔。每一種都有其優缺點，我們主要以 SQL 轉儲為主。

6.1 SQL 轉儲

SQL 轉儲方法的思想是創建一個由 SQL 命令組成的文件，當把這個文件回饋給服務器時，服務器將利用其中的 SQL 命令重建與轉儲時狀態一樣的數據庫。PostgreSQL 為此提供了工具pg_dump。這個工具的基本用法是：

pg_dump dbname > dumpfile

正如你所見，pg_dump把結果輸出到標準輸出。pg_dump是一個普通的 PostgreSQL 客戶端應用（盡管是個相當聰明的東西）。這就意味著你可以在任何可以訪問該數據庫的遠端主機上進行備份工作。但是請記住pg_dump不會以任何特殊權限運行。具體說來，就是它必須要有你想備份的表的讀權限，因此為了備份整個數據庫你幾乎總是必須以一個數據庫超級用戶來運行它。

要聲明pg_dump連接哪個數據庫服務器，使用命令行選項-h host和-p port。默認主機是本地主機或你的 PGHOST 環境變量指定的主機。類似地，默認端口是環境變量 PGPORT 或（如果 PGPORT 不存在）內建的默認值。

和任何其他 PostgreSQL 客戶端應用一樣，pg_dump默認使用與當前操作系統用戶名同名的數據庫用戶名進行連接。要使用其他名字，要么聲明-U選項，要么設置環境變量 PGUSER。請注意pg_dump的連接也要通過客戶認證機制。

pg_dump對于其他備份方法的一個重要優勢是，pg_dump的輸出可以很容易地在新版本的 PostgreSQL 中載入，而文件級備份和連續歸檔都是極度的服務器版本限定的。pg_dump也是唯一可以將一個數據庫傳送到一個不同機器架構上的方法，例如從一個 32 位服務器到一個 64 位服務器。

由pg_dump創建的備份在內部是一致的，也就是說，轉儲表現了pg_dump開始運行時刻的數據庫快照，且在pg_dump運行過程中發生的更新將不會被轉儲。pg_dump工作的時候并不阻塞其他的對數據庫的操作。（但是會阻塞那些需要排它鎖的操作，比如大部分形式的 ALTER TABLE）

6.2 從轉儲中恢復

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

不管怎樣，你將只能得到一個部分恢復的數據庫。作為另一種選擇，你可以指定讓整個恢復作為一個單獨的事務運行，這樣恢復要么完全完成要么完全回滾。這種模式可以通過向psql傳遞-1或--single-transaction命令行選項來指定。

pg_dump和psql讀寫管道的能力使得直接從一個服務器轉儲一個數據庫到另一個服務器成為可能，例如：

pg_dump -h host1 dbname | psql -h host2 dbname

注意：pg_dump產生的轉儲是相對于template0。這意味著在template1中加入的任何語言、過程等都會被pg_dump轉儲。結果是，如果在恢復時使用的是一個自定義的template1，你必須從template0創建一個空的數據庫。

一旦完成恢復，在每個數據庫上運行ANALYZE是明智的舉動，這樣優化器就有有用的統計數據了。

6.3 使用 pg_dumpall

pg_dump每次只轉儲一個數據庫，而且它不會轉儲關于角色或表空間（因為它們是集簇范圍的）的信息。為了支持方便地轉儲一個數據庫集簇的全部內容，提供了pg_dumpall程序。pg_dumpall備份一個給定集簇中的每一個數據庫，并且也保留了集簇范圍的數據，如角色和表空間定義。該命令的基本用法是：

pg_dumpall > dumpfile

轉儲的結果可以使用psql恢復：

psql -f dumpfile postgres

（實際上，你可以指定恢復到任何已有數據庫名，但是如果你正在將轉儲載入到一個空集簇中則通常要用 postgres）。在恢復一個pg_dumpall轉儲時常常需要具有數據庫超級用戶訪問權限，因為它需要恢復角色和表空間信息。如果在使用表空間，請確保轉儲中的表空間路徑適合于新的安裝。

7. 遠程連接

7.1 修改 PostgreSQL 監聽地址

默認 PostgreSQL 監聽的地址是 127.0.0.1，別的機器無法遠程連接上，所以需要調整，修改 postgresql.conf 文件。

• 通過 dnf 安裝的 pgsql 配置文件在/var/lib/pgsq1/data/postgresql.conf
• 通過源碼編譯安裝的 pgsql 配置文件在/usr/local/pgsq1/data/postgresql.conf

下面操作以 dnf 安裝為例：
更改第 60 行，取消注釋并把localhost改成 *

[root@bogon ~]# grep 'listen_addresses' /var/lib/pgsq1/data/postgresql.conf
listen_addresses='*' # what IP address(es) to listen on;

重啟服務

[postgres@localhost ~]$ pg_ctl -D /usr/local/pgsql/data -l logfile restart
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
[root@localhost ~]# netstat -anpt |grep 5432
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      19958/postgres      
tcp6       0      0 ::1:5432                :::*                    LISTEN      19958/postgres    

7.2 配置訪問權限

默認是只能本地訪問 PostgreSQL 的，我們需要在 pg_hba.conf 里面配置。找到 IPv4 local connections 這一行，在這一行下面添加：

[postgres@localhost ~]$ vi /usr/local/pgsql/data/pg_hba.conf host all all 0.0.0.0/0 trust

• host：這指定了連接類型。host 表示該規則適用于通過 TCP/IP 進行的遠程連接。如果是本地連接，通常會使用 local。
• all：這定義了哪些數據庫可以接受這個規則。all 表示這個規則適用于所有數據庫。也可以指定特定的數據庫名，例如 mydatabase。
• all：這定義了哪些用戶可以接受這個規則。all 表示這個規則適用于所有用戶。也可以指定特定的用戶名，例如 myuser。
• 0.0.0.0/0：這定義了哪些客戶端 IP 地址或 IP 地址范圍可以接受這個規則。0.0.0.0/0 是一個特殊的 CIDR 表示法，它表示任何 IP 地址（即沒有 IP 地址限制）。也可以指定具體的 IP 地址，如 192.168.1.100，或者 IP 地址范圍，如 192.168.1.0/24。
• trust：這定義了認證方法。trust 表示不需要密碼或其他任何形式的認證，客戶端可以直接連接。這通常只在本地或受信任的網絡環境中使用，因為它允許任何人無需認證即可訪問數據庫。在生產環境中，應該使用更安全的認證方法，如 md5 或 password（對于較新版本的 PostgreSQL，建議使用 scram-sha-256）。

如果不是設置的 trust，而是選擇了 md5 或 password 之類的，需要有密碼才行，配置 PostgreSQL 密碼流程如下：

postgres=# ALTER USER postgres WITH PASSWORD '123456';
ALTER ROLE

7.3 重啟服務

[postgres@localhost ~]$ pg_ctl -D /usr/local/pgsql/data -l logfile restart

7.4 驗證遠程連接

• 使用其它主機遠程連接本機數據庫，設置的是 trust，無密碼，可直接登錄：

[postgres@localhost ~]$ psql -h 192.168.10.102
psql (15.4)
Type "help" for help.

• 如果設置的是 md5 或 password 之類的需要有密碼才行：

[postgres@localhost ~]$ psql -h 192.168.10.102
Password for user postgres: 
psql (15.4)
Type "help" for help.

8. 重置密碼

8.1 備份配置文件

對 pg_hba.conf 文件，進行備份：

[root@localhost ~]# cp /var/lib/pgsq1/data/pg_hba.conf /var/lib/pgsq1/data/pg_hba.confbak

8.2 修改配置文件

修改配置文件以信任本地連接不需要密碼。將配置文件中的 scram-sha-256 或者 md5 修改為 trust：

[root@localhost ~]# vim /var/lib/pgsq1/data/pg_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5

8.3 重啟服務

[postgres@localhost ~]$ pg_ctl -D /usr/local/pgsql/data -l logfile restart

8.4 修改密碼

登錄數據庫修改密碼，密碼自定義：

postgres=# ALTER USER postgres WITH PASSWORD '123456789';
ALTER ROLE

8.5 恢復 pg_hba.conf 配置文件

將 pg_hba.confbak 文件的內容覆蓋 pg_hba.conf，重啟 PostgreSQL 數據庫服務器，重新登陸時，如果提示輸入密碼，則輸入剛才修改的密碼即可。

總結

本文圍繞 PostgreSQL 日常維護，系統講解了基本使用（登錄、數據庫及數據表操作、模式管理、數據操作）、備份與恢復、遠程連接配置及密碼重置等內容，助力讀者掌握相關核心技能，為數據庫高效管理和后續高級功能學習奠基。