PostgreSQL 安装配置与常用命令指南

PostgreSQL 是功能强大的开源关系型数据库，以稳定性和标准兼容性著称。

Windows 安装

下载安装包

从官网下载安装程序：https://www.postgresql.org/download/windows/

推荐使用 EDB 提供的安装包，包含：

PostgreSQL Server
pgAdmin 4（图形管理工具）
Stack Builder（扩展安装工具）
命令行工具

安装步骤

运行安装程序，选择安装目录（默认 C:\Program Files\PostgreSQL\16）
选择组件（建议全选）
设置数据目录（默认 C:\Program Files\PostgreSQL\16\data）
设置 postgres 超级用户密码（务必记住）
设置端口（默认 5432）
选择区域设置（建议选择 Chinese, China 或 Default locale）
完成安装

配置环境变量

# 添加到系统 PATH（以管理员身份运行 PowerShell）
$pgPath = "C:\Program Files\PostgreSQL\16\bin"
[Environment]::SetEnvironmentVariable("Path", $env:Path + ";$pgPath", "Machine")

# 或手动添加
# 系统属性 -> 高级 -> 环境变量 -> Path -> 添加 PostgreSQL bin 目录

验证安装

# 命令提示符
psql --version
psql -U postgres

Windows 服务管理

# 查看服务状态
sc query postgresql-x64-16

# 启动服务
net start postgresql-x64-16

# 停止服务
net stop postgresql-x64-16

# 重启服务
net stop postgresql-x64-16 & net start postgresql-x64-16

# PowerShell 方式
Get-Service -Name "postgresql*"
Start-Service -Name "postgresql-x64-16"
Stop-Service -Name "postgresql-x64-16"
Restart-Service -Name "postgresql-x64-16"

使用 Chocolatey 安装

# 安装 Chocolatey（如未安装）
Set-ExecutionPolicy Bypass -Scope Process -Force
iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))

# 安装 PostgreSQL
choco install postgresql16 -y

# 安装后设置密码
psql -U postgres
\password postgres

使用 Scoop 安装

# 添加 extras bucket
scoop bucket add extras

# 安装 PostgreSQL
scoop install postgresql

# 初始化数据库
initdb -U postgres -A password -E utf8 -W -D $env:USERPROFILE\scoop\apps\postgresql\current\data

# 启动服务
pg_ctl -D $env:USERPROFILE\scoop\apps\postgresql\current\data -l logfile start

Linux 安装

Ubuntu/Debian

sudo apt update
sudo apt install postgresql postgresql-contrib

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

# 查看状态
sudo systemctl status postgresql

使用官方仓库（获取最新版本）

# 添加 PostgreSQL 官方仓库
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# 导入仓库签名密钥
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# 更新并安装
sudo apt update
sudo apt install postgresql-16 postgresql-contrib-16

CentOS/RHEL/Rocky Linux

# 安装仓库
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 禁用内置模块
sudo dnf -qy module disable postgresql

# 安装 PostgreSQL 16
sudo dnf install -y postgresql16-server postgresql16-contrib

# 初始化数据库
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

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

Arch Linux

sudo pacman -S postgresql

# 切换到 postgres 用户初始化
sudo -iu postgres
initdb -D /var/lib/postgres/data

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

Docker

docker run -d \
  --name postgres \
  -p 5432:5432 \
  -e POSTGRES_USER=admin \
  -e POSTGRES_PASSWORD=password \
  -e POSTGRES_DB=mydb \
  -v pgdata:/var/lib/postgresql/data \
  postgres:16

Docker Compose

# docker-compose.yml
version: '3.8'
services:
  postgres:
    image: postgres:16
    container_name: postgres
    restart: unless-stopped
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: password
      POSTGRES_DB: mydb
      TZ: Asia/Shanghai
    volumes:
      - pgdata:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql  # 初始化脚本
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U admin -d mydb"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  pgdata:

初始配置

设置 postgres 用户密码

# Linux - 切换到 postgres 用户
sudo -u postgres psql

# 设置密码
\password postgres
# 或
ALTER USER postgres WITH PASSWORD 'your_secure_password';

创建新用户和数据库

-- 创建用户
CREATE USER myuser WITH PASSWORD 'mypassword';

-- 创建数据库并指定所有者
CREATE DATABASE mydb OWNER myuser;

-- 授予权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

-- 连接到数据库后授予 schema 权限
\c mydb
GRANT ALL ON SCHEMA public TO myuser;

允许远程连接

编辑 postgresql.conf：

# 查找配置文件位置
sudo -u postgres psql -c "SHOW config_file;"

# Linux 通常在
# /etc/postgresql/16/main/postgresql.conf
# /var/lib/pgsql/16/data/postgresql.conf

# Windows 通常在
# C:\Program Files\PostgreSQL\16\data\postgresql.conf

修改监听地址：

# postgresql.conf
listen_addresses = '*'          # 监听所有地址
# listen_addresses = '0.0.0.0'  # 仅 IPv4
port = 5432

编辑 pg_hba.conf 添加访问规则：

# 查找文件位置
sudo -u postgres psql -c "SHOW hba_file;"

# pg_hba.conf
# TYPE  DATABASE  USER      ADDRESS         METHOD
local   all       all                       peer
host    all       all       127.0.0.1/32    md5
host    all       all       ::1/128         md5
host    all       all       0.0.0.0/0       md5      # 允许所有 IPv4
host    all       all       ::/0            md5      # 允许所有 IPv6
host    mydb      myuser    192.168.1.0/24  md5      # 仅允许特定网段

重载配置：

# Linux
sudo systemctl reload postgresql

# 或在 psql 中
SELECT pg_reload_conf();

防火墙设置

# Ubuntu/Debian (ufw)
sudo ufw allow 5432/tcp

# CentOS/RHEL (firewalld)
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload

# Windows 防火墙（管理员 PowerShell）
New-NetFirewallRule -DisplayName "PostgreSQL" -Direction Inbound -Protocol TCP -LocalPort 5432 -Action Allow

连接数据库

# 切换到 postgres 用户
sudo -u postgres psql

# 连接指定数据库
psql -U username -d database -h hostname -p 5432

# 使用连接字符串
psql "postgresql://user:password@localhost:5432/database"

psql 命令

-- 常用元命令
\l              -- 列出数据库
\c dbname       -- 切换数据库
\dt             -- 列出表
\d tablename    -- 查看表结构
\du             -- 列出用户
\df             -- 列出函数
\di             -- 列出索引
\dv             -- 列出视图
\dn             -- 列出 schema
\q              -- 退出
\?              -- 帮助
\h SELECT       -- SQL 帮助

-- 执行文件
\i /path/to/file.sql

-- 输出到文件
\o output.txt
SELECT * FROM users;
\o

-- 显示执行时间
\timing on

用户管理

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

-- 创建超级用户
CREATE USER admin WITH SUPERUSER PASSWORD 'password';

-- 授权
GRANT ALL PRIVILEGES ON DATABASE mydb TO username;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO username;
GRANT USAGE ON SCHEMA public TO username;

-- 修改密码
ALTER USER username WITH PASSWORD 'newpassword';

-- 删除用户
DROP USER username;

-- 查看用户
\du
SELECT * FROM pg_user;

数据库操作

-- 创建数据库
CREATE DATABASE mydb;
CREATE DATABASE mydb OWNER username;
CREATE DATABASE mydb ENCODING 'UTF8';

-- 删除数据库
DROP DATABASE mydb;

-- 重命名
ALTER DATABASE oldname RENAME TO newname;

-- 查看数据库
\l
SELECT datname FROM pg_database;

表操作

创建表

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    age INTEGER CHECK (age >= 0),
    balance DECIMAL(10, 2) DEFAULT 0.00,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 带外键
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

修改表

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 删除列
ALTER TABLE users DROP COLUMN phone;

-- 修改列类型
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(150);

-- 重命名列
ALTER TABLE users RENAME COLUMN email TO user_email;

-- 添加约束
ALTER TABLE users ADD CONSTRAINT email_unique UNIQUE (email);
ALTER TABLE users ADD CHECK (age >= 18);

-- 删除约束
ALTER TABLE users DROP CONSTRAINT email_unique;

-- 重命名表
ALTER TABLE users RENAME TO members;

-- 删除表
DROP TABLE users;
DROP TABLE IF EXISTS users CASCADE;

CRUD 操作

插入

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

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

-- 插入并返回
INSERT INTO users (username, email, password)
VALUES ('jane', 'jane@example.com', 'hash')
RETURNING id, username;

-- 冲突处理
INSERT INTO users (username, email, password)
VALUES ('john', 'john@example.com', 'hash')
ON CONFLICT (username) DO UPDATE SET email = EXCLUDED.email;

INSERT INTO users (username, email, password)
VALUES ('john', 'john@example.com', 'hash')
ON CONFLICT DO NOTHING;

查询

-- 基础查询
SELECT * FROM users;
SELECT id, username FROM users WHERE id = 1;

-- 条件查询
SELECT * FROM users WHERE age > 18 AND is_active = true;
SELECT * FROM users WHERE username LIKE 'j%';
SELECT * FROM users WHERE email ILIKE '%@gmail.com';  -- 不区分大小写
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
SELECT * FROM users WHERE id IN (1, 2, 3);
SELECT * FROM users WHERE phone IS NULL;

-- 排序分页
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY age ASC NULLS LAST;
SELECT * FROM users LIMIT 10 OFFSET 20;

-- 聚合
SELECT COUNT(*) FROM users;
SELECT AVG(age), MAX(age), MIN(age) FROM users;
SELECT status, COUNT(*) FROM orders GROUP BY status;
SELECT status, COUNT(*) FROM orders GROUP BY status HAVING COUNT(*) > 10;

-- 去重
SELECT DISTINCT status FROM orders;

-- 别名
SELECT username AS name, email AS mail FROM users;

更新

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

-- 多字段更新
UPDATE users SET
    email = 'new@example.com',
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

-- 更新并返回
UPDATE users SET age = age + 1 WHERE id = 1 RETURNING *;

删除

-- 删除记录
DELETE FROM users WHERE id = 1;

-- 删除并返回
DELETE FROM users WHERE id = 1 RETURNING *;

-- 清空表
TRUNCATE TABLE users;
TRUNCATE TABLE users RESTART IDENTITY CASCADE;

连接查询

-- INNER JOIN
SELECT u.username, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN
SELECT u.username, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN
SELECT u.username, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL JOIN
SELECT u.username, o.amount
FROM users u
FULL JOIN orders o ON u.id = o.user_id;

-- 多表连接
SELECT u.username, o.amount, p.name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;

索引

-- 创建索引
CREATE INDEX idx_username ON users(username);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE INDEX idx_name_age ON users(username, age);

-- 部分索引
CREATE INDEX idx_active_users ON users(username) WHERE is_active = true;

-- 表达式索引
CREATE INDEX idx_lower_email ON users(LOWER(email));

-- 查看索引
\di
SELECT * FROM pg_indexes WHERE tablename = 'users';

-- 删除索引
DROP INDEX idx_username;

-- 重建索引
REINDEX INDEX idx_username;
REINDEX TABLE users;

事务

-- 开始事务
BEGIN;

-- 执行操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交
COMMIT;

-- 回滚
ROLLBACK;

-- 保存点
BEGIN;
UPDATE users SET age = 30 WHERE id = 1;
SAVEPOINT sp1;
UPDATE users SET age = 40 WHERE id = 2;
ROLLBACK TO sp1;
COMMIT;

视图

-- 创建视图
CREATE VIEW active_users AS
SELECT id, username, email FROM users WHERE is_active = true;

-- 使用视图
SELECT * FROM active_users;

-- 物化视图
CREATE MATERIALIZED VIEW user_stats AS
SELECT status, COUNT(*) as count FROM orders GROUP BY status;

-- 刷新物化视图
REFRESH MATERIALIZED VIEW user_stats;

-- 删除视图
DROP VIEW active_users;

JSON 操作

-- JSON 类型
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB
);

-- 插入 JSON
INSERT INTO events (data) VALUES ('{"name": "click", "page": "/home"}');

-- 查询 JSON
SELECT data->>'name' FROM events;
SELECT data->'page' FROM events;
SELECT * FROM events WHERE data->>'name' = 'click';
SELECT * FROM events WHERE data @> '{"name": "click"}';

-- 更新 JSON
UPDATE events SET data = data || '{"count": 1}' WHERE id = 1;
UPDATE events SET data = jsonb_set(data, '{count}', '2') WHERE id = 1;

备份恢复

# 备份数据库
pg_dump -U postgres mydb > backup.sql
pg_dump -U postgres -Fc mydb > backup.dump  # 自定义格式

# 备份所有数据库
pg_dumpall -U postgres > all_backup.sql

# 恢复
psql -U postgres mydb &#x3C; backup.sql
pg_restore -U postgres -d mydb backup.dump

# 只备份结构
pg_dump -U postgres --schema-only mydb > schema.sql

# 只备份数据
pg_dump -U postgres --data-only mydb > data.sql

配置文件

主要配置文件：

postgresql.conf - 主配置
pg_hba.conf - 访问控制

# 查看配置文件位置
SHOW config_file;
SHOW hba_file;

postgresql.conf

# 监听地址
listen_addresses = '*'

# 端口
port = 5432

# 最大连接数
max_connections = 200

# 内存设置
shared_buffers = 256MB
work_mem = 4MB

# 日志
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'

pg_hba.conf

# TYPE  DATABASE  USER  ADDRESS       METHOD
local   all       all                 peer
host    all       all   127.0.0.1/32  md5
host    all       all   0.0.0.0/0     md5

重载配置：

sudo systemctl reload postgresql
# 或
SELECT pg_reload_conf();

性能优化

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE username = 'john';
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john';
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM users WHERE id = 1;

-- 查看表统计
SELECT * FROM pg_stat_user_tables WHERE relname = 'users';

-- 查看表大小
SELECT pg_size_pretty(pg_total_relation_size('users'));
SELECT pg_size_pretty(pg_database_size('mydb'));

-- 更新统计信息
ANALYZE users;
ANALYZE;  -- 分析所有表

-- 清理死元组
VACUUM users;
VACUUM FULL users;  -- 完全清理，会锁表
VACUUM ANALYZE users;  -- 清理并更新统计

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

-- 终止查询
SELECT pg_cancel_backend(pid);      -- 取消查询
SELECT pg_terminate_backend(pid);   -- 终止连接

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

-- 查看慢查询（需开启日志）
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

常用运维命令

数据库信息查询

-- 查看 PostgreSQL 版本
SELECT version();

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

-- 查看当前用户
SELECT current_user;

-- 查看所有配置
SHOW ALL;
SHOW shared_buffers;
SHOW max_connections;

-- 查看数据库大小
SELECT datname, pg_size_pretty(pg_database_size(datname)) 
FROM pg_database ORDER BY pg_database_size(datname) DESC;

-- 查看表大小排行
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- 查看索引使用情况
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- 查看未使用的索引
SELECT indexrelname FROM pg_stat_user_indexes WHERE idx_scan = 0;

导入导出数据

# 导出为 CSV
psql -U postgres -d mydb -c "\copy users TO 'users.csv' WITH CSV HEADER"

# 导入 CSV
psql -U postgres -d mydb -c "\copy users FROM 'users.csv' WITH CSV HEADER"

# SQL 方式导出
COPY users TO '/tmp/users.csv' WITH CSV HEADER;

# SQL 方式导入
COPY users FROM '/tmp/users.csv' WITH CSV HEADER;

定时备份脚本

Linux：

#!/bin/bash
# backup_postgres.sh

BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="mydb"
DB_USER="postgres"

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
pg_dump -U $DB_USER -Fc $DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.dump

# 删除 7 天前的备份
find $BACKUP_DIR -name "*.dump" -mtime +7 -delete

echo "Backup completed: ${DB_NAME}_${DATE}.dump"

Windows（PowerShell）：

# backup_postgres.ps1

$BackupDir = "C:\Backups\PostgreSQL"
$Date = Get-Date -Format "yyyyMMdd_HHmmss"
$DbName = "mydb"
$PgDump = "C:\Program Files\PostgreSQL\16\bin\pg_dump.exe"

# 创建备份目录
New-Item -ItemType Directory -Force -Path $BackupDir

# 设置密码环境变量
$env:PGPASSWORD = "your_password"

# 执行备份
& $PgDump -U postgres -Fc $DbName -f "$BackupDir\${DbName}_${Date}.dump"

# 删除 7 天前的备份
Get-ChildItem $BackupDir -Filter "*.dump" | Where-Object {
    $_.LastWriteTime -lt (Get-Date).AddDays(-7)
} | Remove-Item

Write-Host "Backup completed: ${DbName}_${Date}.dump"

总结

PostgreSQL 特点：

完整的 SQL 标准支持
强大的 JSON 支持
丰富的数据类型
可靠的事务处理
优秀的扩展性

常用端口：5432

配置文件位置：

Linux: /etc/postgresql/版本/main/ 或 /var/lib/pgsql/版本/data/
Windows: C:\Program Files\PostgreSQL\版本\data\

数据目录：

Linux: /var/lib/postgresql/版本/main/
Windows: C:\Program Files\PostgreSQL\版本\data\

适合需要复杂查询和数据完整性的应用场景。

Windows 安装

下载安装包

安装步骤

配置环境变量

验证安装

Windows 服务管理

使用 Chocolatey 安装

使用 Scoop 安装

Linux 安装

Ubuntu/Debian

使用官方仓库（获取最新版本）

CentOS/RHEL/Rocky Linux

Arch Linux

Docker

Docker Compose

初始配置

设置 postgres 用户密码

创建新用户和数据库

允许远程连接

防火墙设置

连接数据库

psql 命令

用户管理

数据库操作

表操作

创建表

修改表

CRUD 操作

插入

查询

更新

删除

连接查询

索引

事务

视图

JSON 操作

备份恢复

配置文件

postgresql.conf

pg_hba.conf

性能优化

推荐配置参数

常用运维命令

数据库信息查询

导入导出数据

定时备份脚本

总结

评论