— 场景：创建一个包含多个表空间的生产环境，用于不同类型的数据存储

— 实施步骤：

— 1. 准备存储设备
— 假设我们有以下存储设备：
— – /dev/sdb：SSD，用于存储频繁访问的数据和索引
— – /dev/sdc：HDD，用于存储不频繁访问的数据和归档数据

— 2. 格式化并挂载存储设备
$ fdisk /dev/sdb
$ mkfs.xfs /dev/sdb1
$ fdisk /dev/sdc
$ mkfs.xfs /dev/sdc1

— 添加到/etc/fstab
$ vi /etc/fstab

/dev/sdb1 /postgresql/tablespaces/ssd xfs defaults 0 0
/dev/sdc1 /postgresql/tablespaces/hdd xfs defaults 0 0

— 挂载存储设备
$ mkdir -p /postgresql/tablespaces/ssd /postgresql/tablespaces/hdd
$ mount -a

— 设置权限
$ chown -R postgres:postgres /postgresql/tablespaces/
$ chmod 700 /postgresql/tablespaces/*

— 3. 创建表空间
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb

fgedudb=# CREATE TABLESPACE fgedu_ssd_data OWNER fgedu LOCATION ‘/postgresql/tablespaces/ssd/data’;
fgedudb=# CREATE TABLESPACE fgedu_ssd_index OWNER fgedu LOCATION ‘/postgresql/tablespaces/ssd/index’;
fgedudb=# CREATE TABLESPACE fgedu_hdd_large OWNER fgedu LOCATION ‘/postgresql/tablespaces/hdd/large’;
fgedudb=# CREATE TABLESPACE fgedu_hdd_archive OWNER fgedu LOCATION ‘/postgresql/tablespaces/hdd/archive’;

— 4. 验证表空间创建
fgedudb=# SELECT spcname, spclocation FROM pg_tablespace WHERE spcname LIKE ‘fgedu%’;

— 输出：
— spcname | spclocation
— ——————-+—————————————-
— fgedu_ssd_data | /postgresql/tablespaces/ssd/data
— fgedu_ssd_index | /postgresql/tablespaces/ssd/index
— fgedu_hdd_large | /postgresql/tablespaces/hdd/large
— fgedu_hdd_archive | /postgresql/tablespaces/hdd/archive
— (4 rows)

— 场景：根据数据类型和访问模式，将不同的数据库对象分配到不同的表空间

— 实施步骤：

— 1. 创建用户表，存储在SSD表空间
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb

fgedudb=# CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) TABLESPACE fgedu_ssd_data;

— 2. 创建用户索引，存储在SSD索引表空间
fgedudb=# CREATE UNIQUE INDEX idx_fgedu_users_email ON fgedu_users(email) TABLESPACE fgedu_ssd_index;
fgedudb=# CREATE INDEX idx_fgedu_users_created_at ON fgedu_users(created_at) TABLESPACE fgedu_ssd_index;

— 3. 创建产品表，存储在SSD表空间
fgedudb=# CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
stock INTEGER NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) TABLESPACE fgedu_ssd_data;

— 4. 创建产品索引，存储在SSD索引表空间
fgedu db=# CREATE INDEX idx_fgedu_products_name ON fgedu_products(name) TABLESPACE fgedu_ssd_index;
fgedu db=# CREATE INDEX idx_fgedu_products_price ON fgedu_products(price) TABLESPACE fgedu_ssd_index;

— 5. 创建日志表，存储在HDD表空间
fgedu db=# CREATE TABLE fgedu_audit_logs (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL,
action VARCHAR(100) NOT NULL,
entity_type VARCHAR(50) NOT NULL,
entity_id INTEGER NOT NULL,
details JSONB,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) TABLESPACE fgedu_hdd_large;

— 6. 创建归档表，存储在HDD归档表空间
fgedu db=# CREATE TABLE fgedu_order_archive (
id SERIAL PRIMARY KEY,
order_id VARCHAR(20) UNIQUE NOT NULL,
customer_id INTEGER NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL,
created_at TIMESTAMP NOT NULL,
archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) TABLESPACE fgedu_hdd_archive;

— 7. 查看对象的表空间分配
fgedu db=# SELECT schemaname, tablename, tablespace FROM pg_tables WHERE tablename LIKE ‘fgedu%’;

— 输出：
— schemaname | tablename | tablespace
— ————+——————–+———————
— public | fgedu_users | fgedu_ssd_data
— public | fgedu_products | fgedu_ssd_data
— public | fgedu_audit_logs | fgedu_hdd_large
— public | fgedu_order_archive | fgedu_hdd_archive
— (4 rows)

— 场景：定期监控表空间使用情况，进行必要的维护操作

— 实施步骤：

— 1. 创建表空间监控脚本
$ vi /postgresql/scripts/monitor_tablespaces.sh

#!/bin/bash
# monitor_tablespaces.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

OUTPUT_FILE=”/var/www/html/tablespace_report.html”

psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c ”
COPY (
SELECT
‘

PostgreSQL表空间报告

表空间使用情况

‘
UNION ALL
SELECT
‘

‘
FROM pg_tablespace
WHERE spcname LIKE ‘fgedu%’
UNION ALL
SELECT
‘

表空间对象

‘
UNION ALL
SELECT
‘

‘
FROM pg_tables
WHERE tablespace IS NOT NULL
ORDER BY pg_total_relation_size(quote_ident(schemaname) || ‘.’ || quote_ident(tablename)) DESC
UNION ALL
SELECT ‘

‘
) TO ‘$OUTPUT_FILE’ WITH CSV;

— 2. 设置定时任务
$ crontab -e

0 0 * * * /postgresql/scripts/monitor_tablespaces.sh

— 3. 表空间碎片整理
— 对大表进行VACUUM FULL
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “VACUUM FULL fgedu_audit_logs;”

— 对表进行CLUSTER
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “CLUSTER fgedu_users USING idx_fgedu_users_email;”

— 4. 表空间备份
— 备份SSD表空间
$ pg_basebackup -h 192.168.1.100 -p 5432 -U fgedu -D /backup/tablespaces/ssd -T fgedu_ssd_data=/postgresql/tablespaces/ssd/data -T fgedu_ssd_index=/postgresql/tablespaces/ssd/index

— 备份HDD表空间
$ pg_basebackup -h 192.168.1.100 -p 5432 -U fgedu -D /backup/tablespaces/hdd -T fgedu_hdd_large=/postgresql/tablespaces/hdd/large -T fgedu_hdd_archive=/postgresql/tablespaces/hdd/archive

— 5. 表空间扩展
— 当表空间空间不足时，扩展存储设备
— 例如，扩展/dev/sdb1
$ lvextend -L +100G /dev/sdb1
$ xfs_growfs /postgresql/tablespaces/ssd

— 1. 表空间空间不足
— 症状：
— – 插入数据失败，报错”no space left on device”
— – 表空间使用百分比接近100%

— 排查步骤：
— – 查看表空间大小
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) FROM pg_tablespace;”

— – 查看表空间中最大的对象
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(quote_ident(schemaname) || ‘.’ || quote_ident(tablename))) FROM pg_tables WHERE tablespace = ‘fgedu_data’ ORDER BY pg_total_relation_size(quote_ident(schemaname) || ‘.’ || quote_ident(tablename)) DESC LIMIT 10;”

— – 清理不需要的数据
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “DELETE FROM fgedu_old_data WHERE created_at < '2025-01-01';" $ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c "VACUUM FULL fgedu_old_data;" -- 2. 表空间权限问题 -- 症状： -- - 创建表空间失败，报错"permission denied" -- - 访问表空间中的对象失败 -- 排查步骤： -- - 检查表空间目录权限 $ ls -la /postgresql/tablespaces/ -- - 检查表空间所有者 $ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c "SELECT spcname, spcowner FROM pg_tablespace;" -- - 检查用户权限 $ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c "SELECT * FROM pg_has_role('fgedu', spcowner, 'MEMBER') FROM pg_tablespace WHERE spcname = 'fgedu_data';" -- 3. 表空间I/O性能问题 -- 症状： -- - 查询执行缓慢 -- - I/O等待时间长 -- 排查步骤： -- - 检查存储设备性能 $ iostat -x /dev/sdb 1 -- - 检查表空间中频繁访问的对象 $ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c "SELECT * FROM pg_stat_user_tables WHERE schemaname = 'public' ORDER BY seq_scan DESC LIMIT 10;" -- - 优化存储配置 -- 考虑使用RAID -- 考虑升级到SSD -- 调整表空间分布

— 1. 规划与设计
— – 根据数据类型和访问模式设计表空间
— – 考虑未来数据增长，预留足够空间
— – 选择合适的存储设备

— 2. 创建与配置
— – 使用有意义的表空间名称
— – 设置正确的权限
— – 选择合适的文件系统

— 3. 使用与分配
— – 将频繁访问的数据存储在高速存储上
— – 将大表和归档数据存储在大容量存储上
— – 为索引单独创建表空间

— 4. 监控与维护
— – 定期监控表空间使用情况
— – 及时清理不需要的数据
— – 定期进行VACUUM和CLUSTER操作
— – 实施合理的备份策略

— 5. 性能优化
— – 使用SSD存储频繁访问的表空间
— – 合理分配表空间，提高I/O并行性
— – 考虑使用表空间级别的存储参数

— 6. 安全与可靠性
— – 实施表空间级别的备份策略
— – 定期测试备份恢复
— – 考虑使用RAID保护数据

— 7. 扩展与迁移
— – 提前规划表空间扩展
— – 制定数据迁移策略
— – 测试迁移过程，确保数据安全