一、psql 命令行工具

psql 是 PostgreSQL 自带的最强大、最常用的命令行客户端工具，支持交互式操作和脚本执行。

1. 连接数据库

基本连接语法：

# 完整语法
psql -h <主机> -p <端口> -U <用户名> -d <数据库名>

# 常用示例
psql -h localhost -p 5432 -U postgres -d postgres

# 简化形式（使用默认值）
psql -U postgres                    # 默认连接 localhost:5432, 数据库与用户名相同
psql postgres                       # 连接到 postgres 数据库
psql "host=localhost port=5432 dbname=mydb user=myuser"

# 使用连接字符串（URL 格式）
psql postgresql://myuser:mypass@localhost:5432/mydb

连接选项说明：

设置环境变量简化连接：

# Linux/macOS - 添加到 ~/.bashrc 或 ~/.zshrc
export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=postgres

# 设置密码（不推荐，有安全风险）
export PGPASSWORD=mypassword

# 之后只需输入
psql

2. 常用元命令

元命令以反斜杠 \ 开头，用于执行管理操作，不以分号结尾。

数据库信息查看类：

-- 列出所有数据库
\l
\l+                    -- 显示详细信息（包括大小）

-- 列出所有表
\d
\d+                    -- 显示详细信息

-- 查看表结构
\d table_name          -- 显示表的列、类型、修饰符
\d+ table_name         -- 额外显示存储信息

-- 查看索引
\di                    -- 列出所有索引
\di+                   -- 详细信息

-- 查看视图
\dv                    -- 列出所有视图

-- 查看序列
\ds                    -- 列出所有序列

-- 查看函数
\df                    -- 列出所有函数
\df+                   -- 显示函数源码

-- 查看模式
\dn                    -- 列出所有模式
\dn+                   -- 显示所有者

-- 查看表空间
\db                    -- 列出所有表空间
\db+                   -- 显示详细信息

-- 查看用户/角色
\du                    -- 列出所有用户
\du+                   -- 显示详细信息

-- 查看权限
\dp                    -- 查看表的权限
\dp table_name

-- 查看所有对象
\dt *.*                -- 所有模式的表
\dt public.*           -- public 模式的表

操作类命令：

-- 切换数据库
\c database_name
\c database_name username    -- 同时切换用户

-- 切换模式
SET search_path TO schema_name;
\set schema 'schema_name'    -- 设置当前模式

-- 查看当前连接信息
\conninfo

-- 查看查询执行时间
\timing
\timing on              -- 开启计时
\timing off             -- 关闭计时

-- 执行系统命令
\! ls -la               -- 列出当前目录
\! pwd                  -- 显示当前路径
\! clear                -- 清屏

输出格式控制：

-- 对齐/不对齐模式
\a                       -- 切换对齐模式

-- 设置输出格式
\pset format aligned     -- 对齐格式（默认）
\pset format csv         -- CSV 格式
\pset format html        -- HTML 表格
\pset format latex       -- LaTeX 格式
\pset format json        -- JSON 格式

-- 设置每页行数
\pset pager off          -- 禁用分页器
\pset pager on           -- 启用分页器

-- 设置字段分隔符（用于不对齐模式）
\pset fieldsep '|'

-- 简洁输出模式
\t                        -- 只显示元组，不显示列名和行数
\x                        -- 扩展模式（每个字段单独一行）
\x auto                   -- 自动判断宽度

历史命令与脚本：

-- 查看命令历史
\s
\s filename              -- 将历史保存到文件

-- 读取并执行 SQL 文件
\i filename.sql
\ir filename.sql         -- 相对于当前文件位置

-- 写入输出到文件
\o filename.txt
\o                       -- 恢复输出到标准输出

-- 执行外部 SQL 文件
\include filename.sql
\include_relative filename.sql   -- 相对路径

信息查询命令：

-- 查看所有元命令的帮助
\?

-- 查看 SQL 命令帮助
\h
\h SELECT                -- 查看 SELECT 命令帮助
\h CREATE TABLE          -- 查看 CREATE TABLE 帮助

-- 查看 PostgreSQL 版本
\conninfo                -- 连接信息中包含版本
SELECT version();

-- 列出扩展
\dx
\dx+                     -- 详细信息

3. psql 实用技巧

变量使用：

-- 设置变量
\set myname 'Alice'
\set myid 100

-- 使用变量（需要加冒号）
SELECT * FROM users WHERE name = :'myname';
SELECT * FROM orders WHERE id = :myid;

-- 设置自动提交模式
\set AUTOCOMMIT on       -- 默认开启
\set AUTOCOMMIT off      -- 关闭后需要手动 COMMIT

-- 设置提示符
\set PROMPT1 '%/%R%# '   -- 显示数据库名
\set PROMPT1 '%M:%> %n@%/%R%# '  -- 显示主机:端口 用户@数据库

-- 查看所有变量
\set

-- 取消变量
\unset myname

多行编辑：

-- 使用 \e 打开外部编辑器编辑当前查询
\e

-- 设置编辑器环境变量
-- Linux/macOS
export EDITOR=vim
-- Windows
set EDITOR=notepad

-- 重新执行上一条查询
\g
\g filename              -- 将结果输出到文件

-- 执行上一条查询并显示执行计划
\gexec

事务控制：

-- 关闭自动提交
\set AUTOCOMMIT off

-- 手动控制事务
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;    -- 或 ROLLBACK;

-- 保存点
BEGIN;
SAVEPOINT sp1;
DELETE FROM users WHERE id = 1;
ROLLBACK TO sp1;
COMMIT;

实用查询脚本：

-- 生成建表语句
\dt public.*
SELECT 'CREATE TABLE ' || tablename || ' AS SELECT * FROM ' || tablename || ';'
FROM pg_tables WHERE schemaname = 'public';

-- 批量删除表
SELECT 'DROP TABLE ' || tablename || ' CASCADE;'
FROM pg_tables WHERE schemaname = 'public' AND tablename LIKE 'temp_%';
\gexec                    -- 执行上一条查询的结果

-- 查看当前活动连接
SELECT pid, usename, application_name, client_addr, state, query
FROM pg_stat_activity
WHERE state = 'active';

-- 终止特定连接
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = 12345;

4. psql 退出

\q                        -- 退出 psql
\quit                     -- 同 \q
exit                      -- 同 \q

-- 快捷键
Ctrl + D                  -- Linux/macOS
Ctrl + Z + Enter          -- Windows

二、pgAdmin 图形化管理工具

pgAdmin 是 PostgreSQL 官方提供的功能最全面的图形化管理工具。

1. 安装与启动

Windows/macOS：

• 随 PostgreSQL 安装包一同安装
• 启动：开始菜单 -> pgAdmin 4

Linux：

# Ubuntu/Debian
sudo apt install pgadmin4

# CentOS/RHEL
sudo yum install pgadmin4

# 启动桌面版
pgadmin4

# 启动 Web 版（服务器模式）
sudo /usr/pgadmin4/bin/setup-web.sh

Docker 方式：

docker run -d \
  --name pgadmin \
  -e PGADMIN_DEFAULT_EMAIL=admin@example.com \
  -e PGADMIN_DEFAULT_PASSWORD=admin \
  -p 5050:80 \
  dpage/pgadmin4

2. 界面功能区域

┌─────────────────────────────────────────────────────────────┐
│  菜单栏 | 工具栏                              [用户] [设置]  │
├──────────────┬──────────────────────────────────────────────┤
│              │                                              │
│   浏览器树    │           主工作区                          │
│              │   - SQL 编辑器                               │
│  ├─ Servers  │   - 结果查看器                               │
│  │   ├─ 本地  │   - 图表/可视化                             │
│  │   └─ 远程  │                                              │
│  ├─ 数据库   │                                              │
│  ├─ 模式     │                                              │
│  ├─ 表       │                                              │
│  └─ 扩展     │                                              │
│              │                                              │
├──────────────┴──────────────────────────────────────────────┤
│  状态栏 | 消息 | 通知                                        │
└─────────────────────────────────────────────────────────────┘

3. 核心功能

服务器管理：

• 注册/连接数据库服务器
• 查看服务器状态和统计信息
• 修改服务器配置参数

对象管理：

• 创建/修改/删除数据库、模式、表、索引等
• 可视化的表结构设计器
• 约束、触发器、规则管理

查询工具：

• SQL 编辑器（语法高亮、自动补全）
• 查询执行计划可视化
• 结果导出（CSV、JSON、Excel、XML）

维护功能：

• 备份与恢复向导
• VACUUM/ANALYZE 操作
• 表分区管理

监控功能：

• 活动连接监控
• 锁冲突检测
• 服务器日志查看

三、第三方客户端工具

1. DBeaver（推荐）

通用数据库客户端，支持 PostgreSQL 及 50+ 种数据库。

特点：

• 开源免费（Community Edition）
• 跨平台（Windows、macOS、Linux）
• ER 图生成
• 数据编辑和导入导出
• 支持所有 PostgreSQL 特性

下载与安装：

# 官网下载
https://dbeaver.io/download/

# Windows: 下载 exe 安装
# macOS: 下载 dmg 或 brew install --cask dbeaver-community
# Linux: 下载 deb/rpm 或使用 snap
snap install dbeaver-ce

2. DataGrip（JetBrains）

商业软件，功能强大，适合专业开发团队。

特点：

• 智能 SQL 编辑器（代码补全、错误检测）
• 版本控制集成
• 数据库重构工具
• 查询优化建议

3. Navicat for PostgreSQL

商业软件，界面友好，适合非技术用户。

特点：

• 数据同步和备份
• 数据建模工具
• 报表生成器
• 云数据库支持

4. TablePlus

现代简洁的数据库客户端，支持 macOS/Windows。

特点：

• 原生性能，响应快速
• 多标签页支持
• 内建 SSH 隧道
• 安全加密连接

5. 命令行增强工具

pgcli： 带自动补全和语法高亮的 PostgreSQL 命令行客户端

# 安装
pip install pgcli
# 或
brew install pgcli  # macOS
sudo apt install pgcli  # Ubuntu

# 使用
pgcli -h localhost -U postgres -d mydb

mycli： MySQL 风格，但也可用于 PostgreSQL

四、编程语言连接示例

1. Python（psycopg2）

import psycopg2
from psycopg2 import sql, extras

# 连接配置
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    database="mydb",
    user="myuser",
    password="mypassword"
)

# 创建游标
cur = conn.cursor()

# 执行查询
cur.execute("SELECT * FROM users WHERE id = %s", (1,))
user = cur.fetchone()

# 批量插入
users = [(1, 'Alice'), (2, 'Bob')]
extras.execute_values(
    cur,
    "INSERT INTO users (id, name) VALUES %s",
    users
)

# 使用字典游标
dict_cur = conn.cursor(cursor_factory=extras.RealDictCursor)
dict_cur.execute("SELECT * FROM users")
users = dict_cur.fetchall()  # 返回字典列表

# 提交事务
conn.commit()

# 关闭连接
cur.close()
conn.close()

# 使用上下文管理器
with psycopg2.connect(**db_config) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM users")
        results = cur.fetchall()

2. Node.js（pg 模块）

const { Pool, Client } = require('pg');

// 连接池
const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'mydb',
  user: 'myuser',
  password: 'mypassword',
  max: 20,              // 最大连接数
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// 查询
async function getUsers() {
  try {
    const res = await pool.query('SELECT * FROM users WHERE id = $1', [1]);
    console.log(res.rows);
  } catch (err) {
    console.error(err);
  }
}

// 事务
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE accounts SET balance = balance - 100 WHERE id = $1', [1]);
  await client.query('UPDATE accounts SET balance = balance + 100 WHERE id = $1', [2]);
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

// 关闭连接池
await pool.end();

3. Java（JDBC）

import java.sql.*;
import java.util.Properties;

public class PostgresExample {
    public static void main(String[] args) {
        // 加载驱动
        String url = "jdbc:postgresql://localhost:5432/mydb";
        Properties props = new Properties();
        props.setProperty("user", "myuser");
        props.setProperty("password", "mypassword");
        props.setProperty("ssl", "false");

        // 使用 try-with-resources
        try (Connection conn = DriverManager.getConnection(url, props);
             Statement stmt = conn.createStatement()) {
        
            // 查询
            ResultSet rs = stmt.executeQuery("SELECT * FROM users");
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }
        
            // 预编译语句
            String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setString(1, "Alice");
                pstmt.setString(2, "alice@example.com");
                pstmt.executeUpdate();
            }
        
            // 事务
            conn.setAutoCommit(false);
            try {
                stmt.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
                stmt.executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
                conn.commit();
            } catch (SQLException e) {
                conn.rollback();
                throw e;
            }
        
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4. Go（pgx）

package main

import (
    "context"
    "fmt"
    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgxpool"
)

func main() {
    // 连接池
    connString := "postgresql://myuser:mypassword@localhost:5432/mydb"
    config, _ := pgxpool.ParseConfig(connString)
    config.MaxConns = 10
    pool, _ := pgxpool.NewWithConfig(context.Background(), config)
    defer pool.Close()

    // 单行查询
    var name string
    err := pool.QueryRow(context.Background(), 
        "SELECT name FROM users WHERE id = $1", 1).Scan(&name)
    if err != nil {
        fmt.Println(err)
    }

    // 多行查询
    rows, _ := pool.Query(context.Background(), "SELECT id, name FROM users")
    defer rows.Close()
    for rows.Next() {
        var id int
        var name string
        rows.Scan(&id, &name)
        fmt.Printf("%d: %s\n", id, name)
    }

    // 批量插入
    batch := &pgx.Batch{}
    batch.Queue("INSERT INTO users (name) VALUES ($1)", "Alice")
    batch.Queue("INSERT INTO users (name) VALUES ($1)", "Bob")
    br := pool.SendBatch(context.Background(), batch)
    br.Close()

    // 事务
    tx, _ := pool.Begin(context.Background())
    defer tx.Rollback(context.Background())
    tx.Exec(context.Background(), "UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    tx.Exec(context.Background(), "UPDATE accounts SET balance = balance + 100 WHERE id = 2")
    tx.Commit(context.Background())
}

五、日常开发工作流

1. 开发环境初始化

-- 创建开发数据库
CREATE DATABASE myapp_dev;

-- 创建应用用户
CREATE USER app_user WITH PASSWORD 'dev_password';

-- 授予权限
GRANT ALL PRIVILEGES ON DATABASE myapp_dev TO app_user;

-- 连接开发数据库
\c myapp_dev

-- 创建扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";

-- 创建模式
CREATE SCHEMA app;
GRANT ALL ON SCHEMA app TO app_user;

2. 数据库迁移管理

-- 创建迁移记录表
CREATE TABLE schema_migrations (
    version VARCHAR(255) PRIMARY KEY,
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 记录迁移
INSERT INTO schema_migrations (version) VALUES ('20240101000001');

-- 查看迁移状态
SELECT * FROM schema_migrations ORDER BY applied_at;

3. 数据导入导出

# 导出数据库
pg_dump -U postgres -d mydb > mydb_backup.sql

# 导出特定表
pg_dump -U postgres -d mydb -t users > users_backup.sql

# 导出为自定义格式（支持并行恢复）
pg_dump -U postgres -d mydb -Fc -f mydb_backup.dump

# 导入
psql -U postgres -d mydb < mydb_backup.sql

# 使用 pg_restore 恢复自定义格式
pg_restore -U postgres -d mydb mydb_backup.dump

# 导出 CSV
COPY (SELECT * FROM users) TO '/tmp/users.csv' CSV HEADER;

# 导入 CSV
COPY users FROM '/tmp/users.csv' CSV HEADER;

六、常见问题与技巧

问题 1：psql 无法连接

# 检查服务是否运行
sudo systemctl status postgresql  # Linux
pg_ctl status -D /usr/local/var/postgres  # macOS

# 检查端口
netstat -an | grep 5432

# 检查 pg_hba.conf
cat /etc/postgresql/16/main/pg_hba.conf | grep -v "^#"

# 测试连接
psql -h localhost -p 5432 -U postgres -d postgres -W

技巧 2：快速调试

-- 查看当前正在执行的查询
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start;

-- 查看慢查询
SELECT query, calls, total_time, mean_time 
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;

-- 查看锁等待
SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;