# Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-contrib

# CentOS/RHEL
sudo yum install postgresql-server postgresql-contrib
sudo postgresql-setup --initdb

# macOS (Homebrew)
brew install postgresql
brew services start postgresql

# Windows
下载安装包：https://www.postgresql.org/download/windows/

sudo -u postgres initdb -D /var/lib/postgres/data

sudo systemctl start postgresql
sudo systemctl enable postgresql

# 允许本地所有连接
localallalltrust
# 允许网络连接
hostallall0.0.0.0/0md5

listen_addresses = '*'

-- 连接PostgreSQL
sudo -u postgres psql

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

-- 创建数据库
CREATE DATABASE mydb OWNER myuser;

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

-- 创建表
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
department VARCHAR(50),
salary NUMERIC(10,2),
hire_date DATE DEFAULT CURRENT_DATE,
skills TEXT[]
);

-- 修改表
ALTER TABLE employees ADD COLUMN phone VARCHAR(15);
ALTER TABLE employees ALTER COLUMN department SET NOT NULL;

-- 删除表
DROP TABLE IF EXISTS temp_employees;

-- 插入数据
INSERT INTO employees (name, email, department, salary)
VALUES
('张伟', 'zhang@company.com', '研发部', 15000),
('李娜', 'li@company.com', '市场部', 12000);

-- 查询数据
SELECT * FROM employees WHERE department = '研发部' AND salary > 13000;

-- 更新数据
UPDATE employees SET salary = salary * 1.1 WHERE department = '研发部';

-- 删除数据
DELETE FROM employees WHERE id = 5;

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交事务
COMMIT;

-- 出错时回滚
ROLLBACK;

CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
attributes JSONB
);

INSERT INTO products (name, attributes)
VALUES ('Laptop', '{"color": "silver", "memory": "16GB", "ports": ["USB-C", "HDMI"]}');

-- 查询JSON字段
SELECT name, attributes->>'color' AS color
FROM products
WHERE attributes @> '{"memory": "16GB"}';

SELECT
name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
),
top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region, product, SUM(quantity) AS product_units
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

-- 创建全文搜索索引
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
title TEXT,
body TEXT
);

CREATE INDEX idx_documents_search ON documents USING GIN (to_tsvector('english', body));

-- 执行搜索
SELECT title, ts_headline(body, q) AS highlight
FROM documents, to_tsquery('english', 'database & performance') q
WHERE to_tsvector('english', body) @@ q;

EXPLAIN ANALYZE SELECT * FROM large_table WHERE category_id = 10;

-- 不推荐
SELECT * FROM orders;

-- 推荐
SELECT order_id, customer_id, order_date FROM orders;

-- 使用COPY导入数据
COPY large_table FROM '/path/to/data.csv' DELIMITER ',' CSV HEADER;

-- 创建分区表
CREATE TABLE sales (
id SERIAL,
sale_date DATE,
amount NUMERIC
) PARTITION BY RANGE (sale_date);

-- 创建子分区
CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- 查看当前隔离级别
SHOW default_transaction_isolation;

-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 显式锁定
BEGIN;
LOCK TABLE accounts IN EXCLUSIVE MODE;
-- 执行操作
COMMIT;

-- 行级锁
SELECT * FROM orders WHERE id = 100 FOR UPDATE;

CREATE OR REPLACE FUNCTION calculate_tax(amount NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
tax_rate NUMERIC := 0.1;
tax_amount NUMERIC;
BEGIN
tax_amount := amount * tax_rate;
RETURN tax_amount;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'Division by zero occurred';
RETURN 0;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT calculate_tax(1000);-- 返回100.00

CREATE OR REPLACE FUNCTION get_employees(dept_name VARCHAR)
RETURNS TABLE (id INT, name VARCHAR, salary NUMERIC) AS $$
BEGIN
RETURN QUERY
SELECT e.id, e.name, e.salary
FROM employees e
WHERE e.department = dept_name;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT * FROM get_employees('研发部');

-- 审计日志表
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
table_name VARCHAR(100),
action VARCHAR(10),
old_data JSONB,
new_data JSONB,
change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 触发器函数
CREATE OR REPLACE FUNCTION log_employee_changes()
RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO audit_log (table_name, action, old_data)
VALUES (TG_TABLE_NAME, 'DELETE', to_jsonb(OLD));
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO audit_log (table_name, action, old_data, new_data)
VALUES (TG_TABLE_NAME, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO audit_log (table_name, action, new_data)
VALUES (TG_TABLE_NAME, 'INSERT', to_jsonb(NEW));
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER employees_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION log_employee_changes();

# 备份单个数据库
pg_dump -U username -d dbname -f backup.sql

# 备份所有数据库
pg_dumpall -U postgres -f alldb.sql

# 压缩备份
pg_dump -U username -d dbname | gzip > backup.gz

# 开启WAL归档
# postgresql.conf中设置：
wal_level = replica
archive_mode = on
archive_command = 'cp %p /path/to/wal_archive/%f'

# 创建基础备份
pg_basebackup -D /path/to/backup -U replicator -P -Fp -Xs -R

# 从逻辑备份恢复
psql -U postgres -d newdb -f backup.sql

# 时间点恢复(PITR)
1. 恢复基础备份
2. 创建recovery.conf文件：
restore_command = 'cp /path/to/wal_archive/%f %p'
recovery_target_time = '2023-05-01 12:00:00'
3. 启动PostgreSQL

# 主库配置 (postgresql.conf)
wal_level = replica
max_wal_senders = 10
hot_standby = on

# 创建复制用户
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'replpass';

# 备库配置
pg_basebackup -h master-host -U replicator -D /var/lib/postgres/data -P -Xs -R

# 备库postgresql.auto.conf中自动生成：
primary_conninfo = 'host=master-host user=replicator password=replpass'

# 安装pgpool-II
sudo apt install pgpool2

# 配置/etc/pgpool2/pgpool.conf
backend_hostname0 = 'master-host'
backend_port0 = 5432
backend_weight0 = 1
backend_hostname1 = 'replica1-host'
backend_port1 = 5432
backend_weight1 = 1

# 启动pgpool
systemctl start pgpool

import psycopg2
from psycopg2 import sql

# 连接数据库
conn = psycopg2.connect(
dbname="mydb",
user="myuser",
password="mypassword",
host="localhost",
port="5432"
)

try:
# 创建游标
with conn.cursor() as cursor:
# 执行查询
cursor.execute("SELECT * FROM employees WHERE department = %s", ('研发部',))
for record in cursor.fetchall():
print(f"ID: {record[0]}, Name: {record[1]}, Salary: {record[4]}")

# 插入数据
insert_query = sql.SQL("""
INSERT INTO employees (name, email, department, salary)
VALUES (%s, %s, %s, %s)
RETURNING id
""")
cursor.execute(insert_query, ('王芳', 'wang@company.com', '市场部', 13000))
new_id = cursor.fetchone()[0]
print(f"New employee ID: {new_id}")

# 提交事务
conn.commit()

except Exception as e:
print(f"Database error: {e}")
conn.rollback()

finally:
# 关闭连接
if conn:
conn.close()

# 连接到本地默认数据库
psql

# 指定用户名和数据库
psql -U username -d dbname

# 指定主机和端口
psql -h localhost -p 5432 -U postgres -d mydb

-- 列出所有数据库
\l

-- 切换数据库
\c dbname

-- 创建新数据库
CREATE DATABASE newdb;

-- 删除数据库
DROP DATABASE olddb;

-- 列出当前数据库所有表
\dt

-- 查看表结构
\d users

-- 显示表详细信息
\d+ orders

-- 显示表、视图和序列
\dv

-- 列出所有用户
\du

-- 创建新用户
CREATE USER dev WITH PASSWORD 'secret123';

-- 修改密码
ALTER USER dev WITH PASSWORD 'newsecret456';

-- 授予权限
GRANT SELECT, INSERT ON customers TO dev;

-- 显示当前连接信息
\conninfo

-- 显示SQL版本
SELECT version();

-- 显示当前时间
SELECT NOW();

-- 显示当前数据库
SELECT current_database();

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

-- 将查询结果输出到文件
\o output.txt
SELECT * FROM products;
\o

-- 显示所有元命令帮助
\?

-- 显示SQL命令帮助
\h

-- 查看特定命令帮助
\h CREATE TABLE

-- 简单查询
SELECT * FROM employees;

-- 条件查询
SELECT name, salary FROM employees WHERE salary > 50000;

-- 排序
SELECT * FROM orders ORDER BY order_date DESC;

-- 限制结果
SELECT * FROM products LIMIT 10;

-- 开启多行模式
\set PROMPT1 '%/%R%# '

-- 输入多行SQL语句
SELECT
first_name,
last_name,
department
FROM employees
WHERE hire_date > '2020-01-01';

-- 结束多行输入（分号后回车）
;

-- 查看历史命令
\s

-- 保存历史到文件
\s history.sql

-- 执行历史命令（!后跟行号）
\! 15

# 导出整个数据库
pg_dump dbname > dbname.sql

# 导出特定表
pg_dump -t tablename dbname > table.sql

# 导出为CSV格式
\copy (SELECT * FROM users) TO '/path/to/users.csv' CSV HEADER

# 导入SQL文件
psql -d dbname -f backup.sql

# 导入CSV文件
\copy users FROM '/path/to/users.csv' CSV HEADER

# 使用COPY命令（需要超级用户权限）
COPY employees FROM '/path/to/employees.csv' DELIMITER ',' CSV HEADER;

-- 设置时间格式
\set HISTFILE ~/.psql_history- :DBNAME
\set HISTSIZE 1000

-- 自定义提示符
\set PROMPT1 '%[%033[1;33m%]%/%R%#%[%033[0m%] '

-- 自动提交
\set AUTOCOMMIT on

-- 常用别名
\set QUIET on
\echo '常用别名:'
\echo 'e: \\dt'
\echo 's: SELECT * FROM'
\set QUIET off

-- 显示查询执行时间
\timing

-- 扩展显示模式
\x auto

-- 设置空值显示
\pset null '[NULL]'

-- 设置边框样式
\pset border 2

-- 开始事务
BEGIN;

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

-- 提交或回滚
COMMIT;
-- ROLLBACK;

-- 查看查询计划
EXPLAIN SELECT * FROM large_table WHERE condition = 'value';

-- 详细分析（实际执行）
EXPLAIN ANALYZE SELECT * FROM large_table;

-- 索引使用情况
SELECT * FROM pg_stat_user_indexes;

-- 重建索引
REINDEX TABLE large_table;

-- 清理表空间
VACUUM FULL ANALYZE;

-- 收集统计信息
ANALYZE VERBOSE;

# 认证失败
psql: FATAL: password authentication failed for user "user"

# 解决方案：
# 1. 检查pg_hba.conf配置
# 2. 重置密码：ALTER USER user WITH PASSWORD 'newpassword';

-- 权限拒绝
ERROR: permission denied for table tablename

# 解决方案：
GRANT SELECT, INSERT ON tablename TO username;

-- 包含引号的字符串
SELECT * FROM comments WHERE text E'contains \'quote\'';

-- 使用$tag$语法
DO $$
BEGIN
INSERT INTO logs (message) VALUES ('This contains ''quotes''');
END $$;

\set id 100
SELECT * FROM users WHERE id = :id;

\! ls -l /backups