postgresql-psql

PostgreSQL psql 技能

PostgreSQL psql（PostgreSQL 交互式终端）是与 PostgreSQL 数据库交互的主要命令行客户端。它既支持交互式查询执行，也提供强大的脚本编写功能，用于数据库管理和维护。

何时使用此技能

以下场景可以使用此技能：

• 从命令行连接 PostgreSQL 数据库
• 以交互方式执行 SQL 查询
• 编写用于自动化的 SQL 脚本
• 创建和管理数据库及架构
• 管理数据库对象（表、视图、索引、函数）
• 备份和恢复数据库
• 配置连接和身份验证
• 格式化和导出查询结果
• 管理事务和权限
• 调试 SQL 查询
• 自动执行数据库管理任务
• 设置复制和高可用性
• 创建存储过程和函数

核心概念

REPL 模型

• psql 以交互式 REPL（读取-求值-输出循环）方式运行
• 接受 SQL 命令和元命令（反斜杠命令）
• 在会话中执行的各条命令之间保持连接状态
• 支持命令历史记录和编辑

命令类型

• SQL 命令：标准 SQL 语句（SELECT、INSERT、UPDATE、DELETE 等）
• 元命令：psql 专用命令，以反斜杠开头（例如 \dt、\d）
• 反斜杠命令：控制查询输出、会话变量和 psql 行为

连接模型

• 每个会话只建立一个数据库连接
• 无需重新连接即可切换数据库
• 连接状态包括当前数据库、用户和搜索路径
• 使用环境变量和 .pgpass 管理凭据

连接选项

基本连接命令

psql [OPTIONS] [DBNAME [USERNAME]]

常见连接选项

# Connect with username and host
psql -U username -h hostname -p 5432 -d database_name

# Connect using connection string
psql postgresql://username:password@hostname:5432/database_name

# Connect with password prompt
psql -U postgres -h localhost -W

# Connect to specific database on local machine
psql -d myapp_development

# Environment variables (alternative)
export PGUSER=postgres
export PGPASSWORD=mypassword
export PGHOST=localhost
export PGPORT=5432
export PGDATABASE=mydb
psql

连接字符串格式

标准 URI 格式：

postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]

示例：

postgresql://app_user:[email protected]:5432/production_db?sslmode=require

身份验证方法

密码文件（.pgpass）：

# ~/.pgpass (chmod 600)
hostname:port:database:username:password
localhost:5432:mydb:postgres:mypassword
*.example.com:5432:*:appuser:apppass

通过 SSH 隧道连接：

ssh -L 5432:localhost:5432 user@remote-host
psql -U postgres -h localhost

SSL/TLS 连接选项

# Require SSL
psql -h hostname -sslmode require -U username database

# Verify certificate
psql -h hostname -sslmode verify-full \
	-sslcert=/path/to/client-cert.crt \
	-sslkey=/path/to/client-key.key \
	-sslrootcert=/path/to/ca-cert.crt database

# SSL modes: disable, allow, prefer (default), require, verify-ca, verify-full

必备元命令

数据库和架构导航

\l or \list                    # List all databases
\l+ or \list+                  # List databases with sizes
\c or \connect DATABASE USER   # Connect to different database
\dn or \dn+                    # List schemas (namespaces)
\dt or \dt+                    # List tables in current schema
\di or \di+                    # List indexes
\dv or \dv+                    # List views
\dm or \dm+                    # List materialized views
\ds or \ds+                    # List sequences
\df or \df+                    # List functions/procedures
\da or \da+                    # List aggregates
\dT or \dT+                    # List data types
\dF or \dF+                    # List text search configurations

对象检查命令

\d or \d NAME                  # Describe table, view, index, sequence, or function
\d+ or \d+ NAME                # Extended description with details
\da PATTERN                    # List aggregate functions matching pattern
\db or \db+                    # List tablespaces
\dc or \dc+                    # List character set encodings
\dC or \dC+                    # List type casts
\dd or \dd+                    # List object descriptions/comments
\dD or \dD+                    # List domains
\de or \de+                    # List foreign data wrappers
\dE or \dE+                    # List foreign servers
\dF or \dF+                    # List text search configurations
\dFd or \dFd+                  # List text search dictionaries
\dFp or \dFp+                  # List text search parsers
\dFt or \dFt+                  # List text search templates
\dg or \dg+                    # List database roles/users
\dl or \dl+                    # List large objects (same as \lo_list)
\dL or \dL+                    # List procedural languages
\dO or \dO+                    # List collations
\dp or \dp+                    # List table access privileges
\dRp or \dRp+                  # List replication origins
\dRs or \dRs+                  # List replication subscriptions
\ds or \ds+                    # List sequences
\dt or \dt+                    # List tables
\dU or \dU+                    # List user mapping
\du or \du+                    # List roles
\dv or \dv+                    # List views
\dx or \dx+                    # List extensions
\dX or \dX+                    # List extended statistics

格式和输出命令

\a                             # Toggle between aligned and unaligned output
\C [STRING]                    # Set table title
\f [STRING]                    # Set field separator for unaligned output
\H                             # Toggle HTML output mode
\pset OPTION [VALUE]           # Set output option (detailed below)
\t [on|off]                    # Toggle tuple-only output (no headers/footers)
\T [STRING]                    # Set HTML table tag attributes
\x or \x [on|off|auto]         # Toggle expanded/vertical output
\g or \g [FILENAME|COMMAND]    # Execute query and send output to file/command

\pset 选项

\pset border [0-2]             # Set border display (0=none, 1=ascii, 2=unicode)
\pset columns WIDTH            # Set column width limit
\pset csv                      # Set CSV output format
\pset expanded [on|off|auto]   # Toggle expanded output
\pset fieldsep STRING          # Set field separator
\pset footer [on|off]          # Toggle footer display
\pset format [aligned|unaligned|csv|tsv|html|latex|latex-longtable|troff-ms]
\pset header [on|off]          # Toggle header display
\pset linestyle [ascii|old-ascii|unicode] # Set line drawing style
\pset null STRING              # Set string to represent NULL
\pset numericlocale [on|off]   # Toggle locale-specific number formatting
\pset pager [on|off|always]    # Control pager usage
\pset recordsep STRING         # Set record separator
\pset recordsep0 [on|off]      # Use null terminator between records
\pset tableattr STRING         # Set HTML table attributes
\pset title STRING             # Set query title
\pset tuples_only [on|off]     # Toggle tuple-only mode

文件和历史记录命令

\copy QUERY TO FILENAME [FORMAT]          # Client-side COPY (requires fewer permissions)
\copy QUERY TO STDOUT                     # Copy to standard output
\copy TABLE FROM FILENAME [FORMAT]        # Import data from file
\e or \edit                               # Edit current query buffer in editor
\e FILENAME                               # Edit file in editor
\ef [FUNCNAME]                            # Edit function definition
\ev [VIEWNAME]                            # Edit view definition
\w FILENAME or \write FILENAME            # Write current query buffer to file
\i FILENAME or \include FILENAME          # Execute SQL commands from file
\ir FILENAME or \include_relative FILE    # Execute relative path file
\s [FILENAME]                             # Show command history (or save to file)
\o FILENAME or \out FILENAME              # Send all output to file
\o                                        # Return output to terminal

批处理和脚本命令

\echo TEXT                     # Print text (useful in scripts)
\errverbose                    # Show last error in verbose form
\q or \quit                    # Quit psql
\! COMMAND or \shell COMMAND   # Execute shell command
\cd DIRECTORY                  # Change working directory
\pwd                           # Print current working directory
\set VARIABLE VALUE            # Set psql variable
\unset VARIABLE                # Unset psql variable
\setenv VARNAME VALUE          # Set environment variable
\getenv VARNAME                # Get environment variable value
\prompt [TEXT] VARIABLE        # Prompt user for input and set variable

事务命令

\begin or BEGIN                # Start transaction
\commit or COMMIT              # Commit transaction
\rollback or ROLLBACK          # Rollback transaction
\savepoint NAME                # Create savepoint
\release SAVEPOINT             # Release savepoint
\rollback TO SAVEPOINT         # Rollback to savepoint

信息命令

\d+ TABLENAME                  # Show table with extended info and storage info
\dt *.*                        # List all tables in all schemas
\dn *                          # List all schemas
\du                            # List all users/roles
\db                            # List tablespaces
\dx                            # List installed extensions
\h or \help                    # List available SQL commands
\h COMMAND or \help COMMAND    # Show help for specific SQL command
\?                             # Show psql help
\copyright                     # Show PostgreSQL copyright/license info
\version or SELECT version()   # Show PostgreSQL version

命令行选项

连接选项

-h, --host=HOSTNAME     # Server host name (default: localhost)
-p, --port=PORT         # Server port (default: 5432)
-U, --username=USERNAME # PostgreSQL user name (default: $USER)
-d, --dbname=DBNAME     # Database name to connect
-w, --no-password       # Never prompt for password
-W, --password          # Force password prompt

输出和格式选项

-A, --no-align              # Unaligned table output mode
-c, --command=COMMAND       # Run single command and exit
-C, --copy-only             # (deprecated, use \copy instead)
-d, --dbname=DBNAME         # Specify database
-E, --echo-hidden           # Display internal queries
-e, --echo-all              # Display each command before sending
-b, --echo-errors           # Display failed commands
-f, --file=FILENAME         # Execute commands from file
-F, --field-separator=CHAR  # Set field separator for unaligned output
-H, --html                  # HTML table output mode
-l, --list                  # List available databases and exit
-L, --log-file=FILENAME     # Log session to file
-n, --no-readline           # Disable readline (line editing)
-o, --output=FILENAME       # Write results to file
-P, --pset=VARIABLE=VALUE   # Set printing option
-q, --quiet                 # Run quietly (no banner, single-line mode)
-R, --record-separator=CHAR # Set record separator for unaligned output
-S, --single-step           # Single-step mode (confirm each command)
-s, --single-transaction    # Execute file in single transaction
-t, --tuples-only           # Print rows only (no headers/footers)
-T, --table-attr=STRING     # Set HTML table tag attributes
-v, --set=VARIABLE=VALUE    # Set psql variable
-V, --version               # Show version and exit
-x, --expanded              # Expanded table output mode
-X, --no-psqlrc             # Do not read ~/.psqlrc startup file
-1, --single-line           # End of line terminates SQL command

其他选项

-a, --all       # (deprecated)
-j, --job=NUM   # (for parallel dumps with pg_dump)
--help          # Show help message
--version       # Show version
--on-error-stop # Stop on first error

变量和配置

内置变量

# Prompt variables
psql -v PROMPT1='%/%R%# ' # Set primary prompt
psql -v PROMPT2='%R%# '   # Set continuation prompt
psql -v PROMPT3='>> '     # Set output mode prompt

# Prompt expansion codes:
# %n = Database user name
# %m = Database server hostname (first part)
# %> = Database server hostname full
# %p = Database server port
# %d = Database name
# %/ = Current schema
# %~ = Like %/ but ~  if schema matches user name
# %# = # if superuser, > otherwise
# %? = Last query result status
# %% = Literal %
# %[..%] = Invisible characters (for terminal control sequences)

配置文件（~/.psqlrc）

# Auto-load on psql startup
# Set default options
\set QUIET ON
\set SQLHISTSIZE 10000

# Configure output
\pset null '[NULL]'
\pset border 2
\pset linestyle unicode
\pset expanded auto
\pset pager always

# Define useful variables
\set conn_user 'SELECT current_user;'
\set dbsize 'SELECT pg_size_pretty(pg_database_size(current_database()));'
\set tables 'SELECT tablename FROM pg_tables WHERE schemaname = ''public'';'
\set functions 'SELECT proname FROM pg_proc;'

# Define shortcuts
\set uptime 'SELECT now() - pg_postmaster_start_time() AS uptime;'
\set locks 'SELECT pid, usename, pg_blocking_pids(pid) as blocked_by, query, state FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;'

# Set timing
\timing ON

# Connect to default database
\c mydb

变量替换

-- Using :variable syntax
\set table_name mytable
SELECT * FROM :table_name;

-- Using :'variable' for literal strings
\set schema_name public
SELECT * FROM :"schema_name".mytable;

-- Using :'variable' syntax in string context
\set username 'postgres'
SELECT * FROM pg_tables WHERE tableowner = :'username';

-- Using :' ' for identifier quoting
\set id_name "customTable"
SELECT * FROM :"id_name";

基本 SQL 操作

执行查询

-- Simple query with immediate execution
SELECT * FROM users;

-- Multi-line query (continues until semicolon)
SELECT id, name, email
FROM users
WHERE active = true;

-- Query with results to file
SELECT * FROM large_table \g output.txt

-- Query with pipe to command
SELECT * FROM users \g | wc -l

-- Execute previous command
\g

-- Execute as only tuples (no headers/footers)
SELECT * FROM users;

创建对象

-- Create database
CREATE DATABASE myapp_db;

-- Create schema
CREATE SCHEMA app_schema;

-- Create table
CREATE TABLE app_schema.users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create index
CREATE INDEX idx_users_email ON app_schema.users(email);

-- Create view
CREATE VIEW app_schema.active_users AS
SELECT id, name, email FROM app_schema.users WHERE active = true;

-- Create function
CREATE OR REPLACE FUNCTION app_schema.get_user_count()
RETURNS INTEGER AS $$
BEGIN
  RETURN (SELECT COUNT(*) FROM app_schema.users);
END;
$$ LANGUAGE plpgsql;

数据操作

-- Insert single row
INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]');

-- Insert multiple rows
INSERT INTO users (name, email) VALUES
  ('Jane Smith', '[email protected]'),
  ('Bob Johnson', '[email protected]');

-- Insert from query
INSERT INTO users_backup SELECT * FROM users;

-- Update data
UPDATE users SET active = false WHERE last_login < now() - interval '30 days';

-- Delete data
DELETE FROM users WHERE id = 999;

-- RETURNING clause (see what was changed)
UPDATE users SET status = 'active'
WHERE id = 1
RETURNING id, name, status;

事务管理

事务控制

-- Begin transaction
BEGIN;
-- or
START TRANSACTION;

-- Commit changes
COMMIT;
-- or
END;

-- Rollback changes
ROLLBACK;

-- Create savepoint
SAVEPOINT sp1;
-- ... execute statements ...
ROLLBACK TO sp1;  -- Rollback to savepoint
RELEASE sp1;      -- Release savepoint

-- Multi-statement transaction
BEGIN;
  INSERT INTO accounts (name, balance) VALUES ('Alice', 1000);
  INSERT INTO accounts (name, balance) VALUES ('Bob', 1000);
  UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
  UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;

事务隔离级别

-- Set transaction isolation level
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- PostgreSQL default
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Show current transaction status
SHOW transaction_isolation;

高级功能

全文搜索

-- Create full-text search vector
ALTER TABLE documents ADD COLUMN search_vector tsvector;

UPDATE documents SET search_vector =
  to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''));

-- Create index for fast search
CREATE INDEX idx_documents_search ON documents USING GIN(search_vector);

-- Search documents
SELECT * FROM documents
WHERE search_vector @@ to_tsquery('english', 'database & tutorial');

-- Ranking results by relevance
SELECT id, title, ts_rank(search_vector, query) AS rank
FROM documents, to_tsquery('english', 'database') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

窗口函数

-- Row number
SELECT id, name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

-- Running sum
SELECT id, amount, date,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

-- Partition results
SELECT id, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- LEAD/LAG (next/previous row)
SELECT id, date, amount,
  LAG(amount) OVER (ORDER BY date) AS prev_amount,
  LEAD(amount) OVER (ORDER BY date) AS next_amount
FROM transactions;

JSON 操作

-- Store JSON
INSERT INTO documents VALUES (1, '{"name": "Alice", "age": 30}');

-- Access JSON fields
SELECT data -> 'name' AS name FROM documents;

-- Access with default
SELECT data ->> 'name' AS name_text FROM documents;  -- Returns text

-- Check if key exists
SELECT * FROM documents WHERE data ? 'name';

-- JSON array operations
SELECT json_array_length(data) FROM documents;

-- JSON aggregation
SELECT json_agg(name) FROM users;

-- JSONB (binary JSON) is preferred for performance
CREATE TABLE config (id INT, settings JSONB);
INSERT INTO config VALUES (1, '{"theme": "dark", "lang": "en"}');

-- JSONB operators are more efficient
SELECT settings @> '{"theme": "dark"}' FROM config;

公用表表达式（CTE）

-- Simple CTE
WITH active_users AS (
  SELECT id, name, email FROM users WHERE active = true
)
SELECT * FROM active_users WHERE created_at > '2024-01-01';

-- Recursive CTE (tree traversal)
WITH RECURSIVE category_hierarchy AS (
  SELECT id, name, parent_id, 0 AS level
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, h.level + 1
  FROM categories c
  JOIN category_hierarchy h ON c.parent_id = h.id
)
SELECT * FROM category_hierarchy;

-- Multiple CTEs
WITH orders_2024 AS (
  SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2024
),
customer_totals AS (
  SELECT customer_id, SUM(total_amount) AS total
  FROM orders_2024
  GROUP BY customer_id
)
SELECT c.name, ct.total
FROM customers c
JOIN customer_totals ct ON c.id = ct.customer_id
ORDER BY ct.total DESC;

使用 psql 编写脚本

运行 SQL 文件

# Execute file
psql -d mydb -f script.sql

# Execute with output to file
psql -d mydb -f script.sql -o results.txt

# Execute with error stopping
psql -d mydb -f script.sql --on-error-stop

# Execute in single transaction
psql -d mydb -f script.sql -s

# Multiple files (executed in order)
psql -d mydb -f init.sql -f seed.sql -f verify.sql

SQL 脚本最佳实践

-- sample_script.sql

-- Set execution mode
\set ON_ERROR_STOP ON
\set QUIET OFF

-- Drop existing objects if needed
DROP TABLE IF EXISTS temp_table;

-- Create table
CREATE TABLE temp_table (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

-- Insert data
INSERT INTO temp_table (name) VALUES
  ('Record 1'),
  ('Record 2'),
  ('Record 3');

-- Verify results
SELECT * FROM temp_table;

-- Cleanup
DROP TABLE temp_table;

-- Report
\echo 'Script completed successfully!'

动态 SQL 脚本

#!/bin/bash

# Bash script with psql variables
DATABASE="myapp_db"
TABLE_NAME="users"
SCHEMA_NAME="public"

# Execute with variable substitution
psql -d $DATABASE -v table_name=$TABLE_NAME \
	-v schema_name=$SCHEMA_NAME -c "
  SELECT COUNT(*) FROM :schema_name.:table_name;
"

# Loop through databases
for db in $(psql -l | awk '{print $1}'); do
	if [[ ! "$db" =~ "template" ]]; then
		echo "Backing up $db..."
		pg_dump $db > /backups/$db.sql
	fi
done

导入和导出

COPY 命令

-- Server-side COPY (requires superuser for file operations)
COPY users (id, name, email)
TO '/tmp/users.csv'
WITH (FORMAT CSV, HEADER TRUE, QUOTE '"', ESCAPE '\\');

-- Import CSV
COPY users (id, name, email)
FROM '/tmp/users.csv'
WITH (FORMAT CSV, HEADER TRUE, QUOTE '"', ESCAPE '\\');

-- Tab-separated values
COPY users TO '/tmp/users.tsv' WITH (FORMAT TEXT, DELIMITER E'\t');

-- With NULL handling
COPY users TO '/tmp/users.csv'
WITH (FORMAT CSV, NULL 'N/A', QUOTE '"');

客户端 COPY（\copy）

# Export to CSV (from psql)
\copy users TO '/home/user/users.csv' WITH (FORMAT CSV, HEADER)

# Export with query results
\copy (SELECT id, name, email FROM users WHERE active = true) \
  TO '/tmp/active_users.csv' WITH (FORMAT CSV, HEADER)

# Import CSV
\copy users (id, name, email) FROM '/tmp/users.csv' WITH (FORMAT CSV, HEADER)

# Export to stdout (pipe to file)
\copy users TO STDOUT WITH (FORMAT CSV, HEADER) > users.csv

# Import from stdin
cat users.csv | \copy users FROM STDIN WITH (FORMAT CSV, HEADER)

使用 pg_dump 和 pg_restore

# Dump entire database
pg_dump -d mydb -U postgres > mydb_backup.sql

# Dump with custom format (compressed)
pg_dump -d mydb -Fc > mydb_backup.dump

# Dump specific table
pg_dump -d mydb -t users > users_backup.sql

# Dump with data only
pg_dump -d mydb -a > mydb_data.sql

# Dump schema only
pg_dump -d mydb -s > mydb_schema.sql

# Restore from SQL file
psql -d mydb_restored -f mydb_backup.sql

# Restore from custom format
pg_restore -d mydb_restored mydb_backup.dump

# List contents of dump
pg_restore -l mydb_backup.dump

性能和调试

查询分析

-- Show query execution plan
EXPLAIN SELECT * FROM users WHERE id = 1;

-- Detailed analysis with actual execution
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

-- Show more details
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
  SELECT * FROM users WHERE active = true;

-- JSON output for programmatic parsing
EXPLAIN (FORMAT JSON, ANALYZE)
  SELECT COUNT(*) FROM users;

查看查询性能

-- Current queries
SELECT pid, usename, state, query FROM pg_stat_activity;

-- Long-running queries
SELECT pid, usename, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Blocking queries
SELECT blocked_pid, blocking_pid, blocked_statement, blocking_statement
FROM pg_stat_statements;

-- Table sizes
SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Database size
SELECT pg_size_pretty(pg_database_size('mydb'));

设置计时

# Enable query timing
\timing ON

# Disable query timing
\timing OFF

# In batch mode
psql -d mydb -c "\timing ON" -f script.sql

查询日志记录

# Log all queries to file
psql -d mydb -L query.log -f script.sql

# Show internal queries (system queries)
psql -d mydb -E

用户和权限管理

创建和管理用户

-- Create user (role)
CREATE USER appuser WITH PASSWORD 'secure_password';

-- Create role without login privilege
CREATE ROLE admin_role;

-- Alter user
ALTER USER appuser WITH PASSWORD 'new_password';

-- Create superuser
CREATE USER superuser_name WITH PASSWORD 'password' SUPERUSER;

-- List users
\du

-- Drop user
DROP USER appuser;

授予权限

-- Grant database usage
GRANT USAGE ON SCHEMA public TO appuser;

-- Grant table permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO appuser;

-- Grant all permissions
GRANT ALL PRIVILEGES ON users TO appuser;

-- Grant sequence permissions (for auto-increment)
GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO appuser;

-- Grant to all tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO appuser;

-- Make privileges default for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO appuser;

-- View permissions
\dp users
\dp+ users

行级安全性（RLS）

-- Enable RLS on table
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Create policy
CREATE POLICY user_policy ON users
  USING (id = current_user_id());  -- This would need to be implemented

-- View policies
\d+ users

psql 高级功能

元命令技巧

# Show last error in detail
\errverbose

# Execution timing
\timing

# Echo all commands sent to server
\set ECHO all

# List all variables
\set

# View specific variable
\echo :DBNAME

# Dynamic query execution
\set query 'SELECT * FROM users WHERE id = ' :user_id
:query

自定义提示符

# Set custom prompts
psql -v PROMPT1='user@db> '
psql -v PROMPT1='%/%R%# ' # database/role#

# In .psqlrc
\set PROMPT1 '%n@%m:%>/%/ %R%# '
\set PROMPT2 '> '
\set PROMPT3 '>> '

函数和过程管理

-- List functions
\df

-- Show function source
\df+ function_name

-- Create function
CREATE OR REPLACE FUNCTION get_user(user_id INT)
RETURNS TABLE(id INT, name TEXT, email TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT u.id, u.name, u.email FROM users u WHERE u.id = user_id;
END;
$$ LANGUAGE plpgsql;

-- Execute function
SELECT * FROM get_user(1);

-- Stored procedure (no return value)
CREATE OR REPLACE PROCEDURE archive_old_records()
AS $$
BEGIN
  INSERT INTO archived_users
  SELECT * FROM users WHERE created_at < now() - interval '1 year';
  DELETE FROM users WHERE created_at < now() - interval '1 year';
  COMMIT;
END;
$$ LANGUAGE plpgsql;

-- Call procedure
CALL archive_old_records();

触发器和事件

-- Create trigger function
CREATE OR REPLACE FUNCTION update_user_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER user_update_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_user_timestamp();

-- View triggers
\d+ users

-- Drop trigger
DROP TRIGGER user_update_timestamp ON users;

备份和恢复

数据库备份策略

# Full database backup (custom format)
pg_dump -d production_db -Fc -j 4 > backup.dump

# Backup with compression
pg_dump -d production_db -Fc -Z 9 > backup.dump

# Parallel backup (faster for large databases)
pg_dump -d production_db -Fd -j 4 -f backup_dir

# Backup specific schemas
pg_dump -d production_db -n public -n app > schemas.sql

# Backup with custom format (allows selective restore)
pg_dump -d production_db -Fc > backup.dump

# View backup contents
pg_restore -l backup.dump | less

# Restore specific table
pg_restore -d restored_db -t users backup.dump

# List available backups
pg_dump -U postgres -l -w postgres

时间点恢复

# Full backup
pg_dump -d mydb > base_backup.sql

# Enable WAL archiving (in postgresql.conf)
wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/%f'

# Restore to point in time
pg_restore -d recovered_db base_backup.sql
# Then apply WAL files up to target time

常见模式和示例

连接池脚本

#!/bin/bash
# Simple connection pool using psql

MAX_CONNECTIONS=10
CONNECTION_POOL=()

for i in {1..$MAX_CONNECTIONS}; do
	(
		while true; do
			psql -d mydb -c "SELECT 1"
			sleep 60
		done
	) &
	CONNECTION_POOL+=($!)
done

# Keep script running
wait

数据库健康检查

-- health_check.sql
SELECT
  'PostgreSQL Version' AS check_type,
  version() AS result
UNION ALL
SELECT
  'Database Size',
  pg_size_pretty(pg_database_size(current_database()))
UNION ALL
SELECT
  'Active Connections',
  count(*)::text
FROM pg_stat_activity
UNION ALL
SELECT
  'Cache Hit Ratio',
  ROUND(sum(heap_blks_hit)::numeric / (sum(heap_blks_hit) + sum(heap_blks_read)), 4)::text
FROM pg_statio_user_tables;

自动化维护

#!/bin/bash
# Weekly maintenance script

DATABASES=$(psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres';")

for db in $DATABASES; do
	echo "Analyzing $db..."
	psql -d "$db" -c "ANALYZE;"

	echo "Vacuuming $db..."
	psql -d "$db" -c "VACUUM;"

	echo "Reindexing $db..."
	psql -d "$db" -c "REINDEX DATABASE \"$db\";"
done

最佳实践

1. 使用连接池 - 适用于应用程序；交互式 psql 无需使用
2. 启用 SSL/TLS - 在生产环境中始终使用加密连接
3. 使用 .pgpass - 避免在脚本中硬编码密码
4. 设置 ON_ERROR_STOP - 在脚本中设置，以避免出错后继续执行
5. 使用事务 - 将相关操作封装在显式事务中
6. 合理创建索引 - 分析查询计划，并在常用的筛选列和联接列上创建索引
7. 监控性能 - 定期检查慢查询和表大小
8. 定期备份 - 使用自定义格式的 pg_dump，以便灵活处理
9. 使用模式 - 以合乎逻辑的方式组织数据库对象
10. 记录权限 - 清晰记录用户角色和权限
11. 测试恢复 - 定期练习从备份恢复
12. 使用参数化查询 - 在应用程序中使用，以防止 SQL 注入
13. 监控锁 - 在 pg_stat_activity 中检查阻塞查询
14. 维护统计信息 - 定期运行 ANALYZE，以便查询优化器使用最新信息

提示与技巧

快速导航

# Connect and execute in one line
psql -d mydb -c "SELECT COUNT(*) FROM users;"

# Execute file and exit
psql -d mydb -f script.sql

# Quiet mode (minimal output)
psql -q -d mydb -c "SELECT * FROM users LIMIT 1;"

# Pipe output to other commands
psql -d mydb -t -c "SELECT name FROM users;" | sort | uniq

# Verify connection without executing commands
psql -d mydb -c ""

实用的 .psqlrc 快捷方式

# Add to ~/.psqlrc for convenient shortcuts
\set dbsize 'SELECT pg_size_pretty(pg_database_size(current_database()))'
\set uptime 'SELECT now() - pg_postmaster_start_time() AS uptime'
\set psql_version 'SELECT version()'
\set table_sizes 'SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'\''.\'\'||tablename)) FROM pg_tables ORDER BY pg_total_relation_size(schemaname||'\''.\'\'||tablename) DESC'

# Usage in psql:
# :dbsize
# :table_sizes

处理大型结果集

# Set pager for large results
\pset pager always

# Use LIMIT for testing
SELECT * FROM huge_table LIMIT 10;

# Use OFFSET for pagination
SELECT * FROM users LIMIT 10 OFFSET 0;
SELECT * FROM users LIMIT 10 OFFSET 10;

# Fetch into file instead of terminal
\copy (SELECT * FROM huge_table) TO huge_export.csv;

故障排除

连接问题

# Verbose connection diagnostics
psql -d mydb -v verbose=on --echo-queries

# Check connection settings
psql --version
psql -d postgres -c "SHOW password_encryption;"

# TCP/IP connectivity test
psql -h hostname -d postgres -U postgres -c "SELECT 1;"

常见错误消息

FATAL: password authentication failed
  → Check password, user exists, .pgpass has correct permissions (600)

FATAL: no pg_hba.conf entry for host
  → Database server's pg_hba.conf needs connection rule

FATAL: database "name" does not exist
  → Create database or check database name spelling

ERROR: permission denied for schema
  → Grant USAGE on schema to user

ERROR: syntax error
  → Check SQL syntax, use \h for help on commands

性能问题

-- Find slow queries
SELECT * FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- Check for missing indexes
SELECT schemaname, tablename, attname
FROM pg_stat_user_tables, pg_attribute
WHERE pg_stat_user_tables.relid = pg_attribute.attrelid
AND seq_scan > 0;

-- Check cache efficiency
SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables;

高级配置

性能调优参数

# In ~/.psqlrc
\set HISTSIZE 10000
\pset pager always
\pset null '[NULL]'
\pset linestyle unicode

# Environment variables for defaults
export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=mydb
export PGPASSFILE=$HOME/.pgpass

输出格式比较

-- Aligned (default)
\pset format aligned

-- CSV
\pset format csv
\copy (SELECT * FROM users) TO STDOUT WITH (FORMAT CSV);

-- HTML
\pset format html
SELECT * FROM users LIMIT 5;

-- LaTeX
\pset format latex
SELECT * FROM users LIMIT 5;

-- Expanded (vertical)
\x
SELECT * FROM users LIMIT 1;

资源与文档

• PostgreSQL 官方文档：https://www.postgresql.org/docs/
• psql 手册：https://www.postgresql.org/docs/current/app-psql.html
• PostgreSQL Wiki：https://wiki.postgresql.org/
• pgAdmin（GUI 工具）：https://www.pgadmin.org/
• DBA 最佳实践：https://www.postgresql.org/docs/current/sql-syntax.html

总结

psql 是一款功能强大、灵活易用的命令行工具，可用于 PostgreSQL 数据库管理和开发。主要优势：

• 交互式 REPL，可即时查看查询结果
• 强大的元命令，可检查和管理对象
• 支持脚本编写，可用于自动化
• 提供丰富的格式选项，满足不同的输出需求
• 内置帮助和文档
• 支持变量替换，可执行动态查询
• 支持连接管理和 SSL/TLS
• 提供性能分析和查询优化工具

掌握 psql，开启高效的 PostgreSQL 工作流，从简单查询到复杂的数据库管理任务都能轻松应对。