# 查看PostgreSQL进程
ps aux | grep postgres

# 典型输出示例：
# postgres  1234  0.0  0.1  123456 7890 ?        S    10:00   0:00 /usr/lib/postgresql/14/bin/postgres
# postgres  1235  0.0  0.0  123456 2345 ?        Ss   10:00   0:00 postgres: logger
# postgres  1237  0.0  0.0  123456 3456 ?        Ss   10:00   0:00 postgres: checkpointer
# postgres  1238  0.0  0.0  123456 4567 ?        Ss   10:00   0:00 postgres: writer
# postgres  1239  0.0  0.0  123456 5678 ?        Ss   10:00   0:00 postgres: wal writer
# postgres  1240  0.0  0.0  123456 6789 ?        Ss   10:00   0:00 postgres: autovacuum launcher
# postgres  1241  0.0  0.0  123456 7890 ?        Ss   10:00   0:00 postgres: stats collector
# postgres  1242  0.0  0.0  123456 8901 ?        Ss   10:00   0:00 postgres: logical replication launcher

-- 查看表空间信息
SELECT spcname AS "表空间名称",
       pg_tablespace_location(oid) AS "位置"
FROM pg_tablespace;

-- 查看数据库存储信息
SELECT datname AS "数据库名称",
       pg_database_size(datname) AS "大小"
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- 查看系统列，了解MVCC实现
SELECT xmin, xmax, ctid, * FROM your_table LIMIT 5;

-- xmin: 创建该行版本的事务ID
-- xmax: 删除或更新该行版本的事务ID
-- ctid: 行的物理位置，包含块号和块内偏移量

-- 查看WAL配置参数
SHOW wal_level;
SHOW wal_buffers;
SHOW checkpoint_timeout;
SHOW max_wal_size;

-- 查看WAL文件信息
SELECT pg_walfile_name(pg_current_wal_lsn());

-- 查看查询执行计划
EXPLAIN SELECT * FROM employees WHERE department_id = 10;

-- 查看详细的执行计划和成本估算
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 10;

-- 创建自定义数据类型
CREATE TYPE address AS (
    street VARCHAR(100),
    city VARCHAR(50),
    zip_code VARCHAR(10)
);

-- 创建自定义函数
CREATE OR REPLACE FUNCTION calculate_tax(numeric) RETURNS numeric AS 
      
 LANGUAGE plpgsql;

-- 使用扩展
CREATE EXTENSION postgis;  -- 地理信息系统扩展
CREATE EXTENSION pg_stat_statements;  -- 查询统计扩展

-- JSON类型示例
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSONB
);

INSERT INTO products (name, attributes) VALUES 
('Laptop', '{"color": "silver", "weight": "1.5kg", "price": 999.99}'),
('Phone', '{"color": "black", "weight": "0.2kg", "price": 699.99}');

-- 查询JSON数据
SELECT name, attributes->>'color' AS color, attributes->>'price' AS price
FROM products
WHERE (attributes->>'price')::numeric > 800;

-- 数组类型示例
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100),
    tags TEXT[]
);

INSERT INTO posts (title, tags) VALUES 
('PostgreSQL Guide', ARRAY['database', 'postgres', 'sql']),
('Database Design', ARRAY['database', 'design']);

-- 查询数组数据
SELECT title, tags
FROM posts
WHERE 'database' = ANY(tags);

-- 创建不同类型的索引
-- B-tree索引
CREATE INDEX idx_products_name ON products(name);

-- GIN索引(用于JSONB)
CREATE INDEX idx_products_attributes ON products USING GIN(attributes);

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

-- 表达式索引
CREATE INDEX idx_users_lower_email ON users(lower(email));

-- 复合索引
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- 启用并行查询
SET max_parallel_workers_per_gather = 4;

-- 查看并行查询执行计划
EXPLAIN ANALYZE SELECT * FROM large_table WHERE condition;

-- 创建分区表
CREATE TABLE measurement (
    city_id int,
    log_date date,
    peaktemp int,
    unitsales int
) PARTITION BY RANGE (log_date);

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

CREATE TABLE measurement_y2024 PARTITION OF measurement
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- 配置主库进行流复制
-- 在postgresql.conf中设置：
wal_level = replica
max_wal_senders = 3
max_replication_slots = 3

-- 在pg_hba.conf中添加复制连接：
host replication replicator 192.168.1.0/24 md5

-- 创建复制用户
CREATE USER replicator REPLICATION LOGIN PASSWORD 'password';

-- 在备库上设置恢复配置
standby_mode = on
primary_conninfo = 'host=primary_host port=5432 user=replicator password=password'

-- 安装PostGIS扩展
CREATE EXTENSION postgis;

-- 创建包含地理数据的表
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(Point, 4326)
);

-- 插入地理数据
INSERT INTO locations (name, geom) VALUES 
('北京', ST_GeomFromText('POINT(116.4074 39.9042)', 4326)),
('上海', ST_GeomFromText('POINT(121.4737 31.2304)', 4326));

-- 查询距离北京100公里内的位置
SELECT name, ST_Distance(geom, ST_GeomFromText('POINT(116.4074 39.9042)', 4326)) AS distance
FROM locations
WHERE ST_DWithin(geom, ST_GeomFromText('POINT(116.4074 39.9042)', 4326), 0.9);  -- 0.9度约等于100公里

-- 窗口函数示例
SELECT 
    department_id,
    employee_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary,
    salary - AVG(salary) OVER (PARTITION BY department_id) AS difference_from_avg
FROM employees;

-- 公用表表达式(CTE)示例
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;