PostgreSQL 基础操作速查

安装与启动

Debian/Ubuntu 安装

# 安装 PostgreSQL
sudo apt update
sudo apt install postgresql postgresql-contrib

# 启动服务
sudo systemctl start postgresql
sudo systemctl enable postgresql

# 检查状态
sudo systemctl status postgresql

CentOS/RHEL 安装

# 安装 PostgreSQL
sudo yum install postgresql-server postgresql-contrib

# 初始化数据库
sudo postgresql-setup initdb

# 启动服务
sudo systemctl start postgresql
sudo systemctl enable postgresql

连接与认证

连接数据库

# 连接默认数据库
sudo -u postgres psql

# 连接指定数据库
psql -U username -d database_name -h host -p port

# 连接字符串格式
psql "postgresql://username:password@host:port/database"

修改密码

-- 修改当前用户密码
ALTER USER postgres PASSWORD 'new_password';

-- 修改其他用户密码
ALTER USER username PASSWORD 'new_password';

数据库操作

创建与删除

-- 创建数据库
CREATE DATABASE database_name;

-- 创建数据库（指定编码）
CREATE DATABASE database_name
  WITH ENCODING='UTF8'
  LC_COLLATE='en_US.UTF-8'
  LC_CTYPE='en_US.UTF-8'
  TEMPLATE=template0;

-- 删除数据库
DROP DATABASE database_name;

-- 查看所有数据库
\l

连接数据库

-- 连接数据库
\c database_name

-- 查看当前数据库
SELECT current_database();

表操作

创建表

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

查看表结构

-- 查看表结构
\d table_name

-- 查看所有表
\dt

-- 查看表详细信息
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'table_name';

修改表

-- 添加列
ALTER TABLE table_name ADD COLUMN column_name data_type;

-- 修改列类型
ALTER TABLE table_name ALTER COLUMN column_name TYPE new_type;

-- 删除列
ALTER TABLE table_name DROP COLUMN column_name;

-- 重命名表
ALTER TABLE old_name RENAME TO new_name;

数据操作

插入数据

-- 插入单行
INSERT INTO users (username, email) VALUES ('john', 'john@example.com');

-- 插入多行
INSERT INTO users (username, email) VALUES
  ('alice', 'alice@example.com'),
  ('bob', 'bob@example.com');

-- 插入查询结果
INSERT INTO users (username, email)
SELECT username, email FROM temp_users;

查询数据

-- 基本查询
SELECT * FROM users;

-- 条件查询
SELECT * FROM users WHERE username = 'john';

-- 排序
SELECT * FROM users ORDER BY created_at DESC;

-- 分页
SELECT * FROM users LIMIT 10 OFFSET 0;

-- 聚合函数
SELECT COUNT(*), AVG(id) FROM users;

-- 分组
SELECT username, COUNT(*) FROM users GROUP BY username;

更新数据

-- 更新单行
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- 更新多行
UPDATE users SET email = 'updated@example.com' WHERE username LIKE 'a%';

删除数据

-- 删除单行
DELETE FROM users WHERE id = 1;

-- 删除所有数据
DELETE FROM users;

-- 清空表（重置序列）
TRUNCATE TABLE users;

索引操作

创建索引

-- 创建B-tree索引
CREATE INDEX idx_users_email ON users(email);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 创建复合索引
CREATE INDEX idx_users_username_email ON users(username, email);

-- 查看索引
\di table_name

删除索引

DROP INDEX idx_users_email;

用户与权限

创建用户

-- 创建用户
CREATE USER username WITH PASSWORD 'password';

-- 创建用户（带数据库权限）
CREATE USER username WITH PASSWORD 'password' CREATEDB;

授权

-- 授予数据库权限
GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

-- 授予表权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;

-- 授予特定表权限
GRANT SELECT, INSERT, UPDATE ON users TO username;

-- 撤销权限
REVOKE ALL PRIVILEGES ON users FROM username;

备份与恢复

备份数据库

# 备份单个数据库
pg_dump -U username database_name > backup.sql

# 备份所有数据库
pg_dumpall -U username > all_databases.sql

# 备份为压缩格式
pg_dump -U username -Fc database_name > backup.dump

恢复数据库

# 恢复SQL格式
psql -U username database_name < backup.sql

# 恢复压缩格式
pg_restore -U username -d database_name backup.dump

性能优化

查看查询计划

EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john';

查看慢查询

-- 启用慢查询日志
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- 超过1秒的查询
SELECT pg_reload_conf();

查看连接数

SELECT count(*) FROM pg_stat_activity;

常用函数

字符串函数

-- 长度
SELECT length('hello');

-- 拼接
SELECT 'hello' || ' ' || 'world';

-- 替换
SELECT replace('hello world', 'world', 'PostgreSQL');

-- 截取
SELECT substring('hello world' from 1 for 5);

日期函数

-- 当前时间
SELECT NOW();
SELECT CURRENT_TIMESTAMP;

-- 日期加减
SELECT NOW() + INTERVAL '1 day';
SELECT NOW() - INTERVAL '1 month';

-- 日期格式化
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');

系统信息

查看版本

SELECT version();

查看数据库大小

SELECT pg_size_pretty(pg_database_size('database_name'));

查看表大小

SELECT pg_size_pretty(pg_total_relation_size('table_name'));

查看活动连接

SELECT * FROM pg_stat_activity WHERE state = 'active';

紧急处理

终止查询

-- 终止指定查询
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = 123;

-- 终止所有长时间运行的查询
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'active' AND query_start < NOW() - INTERVAL '1 hour';

锁定处理

-- 查看锁定
SELECT * FROM pg_locks WHERE NOT granted;

-- 解除锁定
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid IN (SELECT pid FROM pg_locks WHERE NOT granted);