PostgreSQL 全方位指南：部署、运维、开发与核心功能#

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

# 2. 添加仓库 (以 PostgreSQL 16 为例，可根据需要修改版本号)
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

# 3. 更新包列表并安装
sudo apt-get update
sudo apt-get -y install postgresql-16 postgresql-contrib-16

# 4. 启动服务并设置开机自启
sudo systemctl start postgresql
sudo systemctl enable postgresql

# 5. 检查状态
sudo systemctl status postgresql

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

# 2. 禁用默认模块 (如果有)
sudo dnf -qy module disable postgresql

# 3. 安装
sudo dnf install -y postgresql16-server postgresql16-contrib

# 4. 初始化数据库 (仅需首次安装时执行)
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

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

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

# 默认连接本地 postgres 用户
psql

# 指定用户、数据库和主机
psql -U myuser -d mydb -h 192.168.1.50 -p 5432

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

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

-- 基本查询
SELECT * FROM users WHERE id = 1;

-- 条件查询与排序
SELECT username, email FROM users 
WHERE created_at > '2023-01-01' 
ORDER BY created_at DESC 
LIMIT 10;

-- 聚合查询
SELECT COUNT(*) as total_users, DATE(created_at) as reg_date 
FROM users 
GROUP BY DATE(created_at);

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

-- 批量更新 (注意 WHERE 条件)
UPDATE users 
SET status = 'active' 
WHERE last_login > NOW() - INTERVAL '30 days';

-- 删除特定记录
DELETE FROM users WHERE id = 5;

-- 清空表 (速度快，不记录单行日志，不可回退)
TRUNCATE TABLE users RESTART IDENTITY;

-- 创建包含 JSONB 的表
CREATE TABLE products (
    id SERIAL,
    name TEXT,
    attributes JSONB
);

-- 创建 GIN 索引以加速 JSON 查询
CREATE INDEX idx_attributes ON products USING GIN (attributes);

-- 查询：查找属性中 color 为 red 的商品
SELECT * FROM products WHERE attributes @> '{"color": "red"}';

-- 简单搜索
SELECT title FROM articles 
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database & performance');

-- 创建 GIN 索引优化搜索
CREATE INDEX idx_fts ON articles USING GIN (to_tsvector('english', content));

-- 计算每个部门内薪资的排名
SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

-- 创建分区表
CREATE TABLE logs (
    log_date DATE NOT NULL,
    message TEXT
) PARTITION BY RANGE (log_date);

-- 创建具体分区
CREATE TABLE logs_2023_q1 PARTITION OF logs
    FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

-- 启用扩展 (需超级用户)
CREATE EXTENSION postgis;

-- 查询距离某点 1000 米内的餐厅
SELECT name FROM restaurants
WHERE ST_DWithin(
    location::geography, 
    ST_MakePoint(116.40, 39.90)::geography, 
    1000
);

-- 确保价格大于 0，且折扣价小于原价
CREATE TABLE products (
    price NUMERIC CHECK (price > 0),
    discount_price NUMERIC,
    CHECK (discount_price IS NULL OR discount_price < price)
);