117笔记问答优化CentOS上PostgreSQL的磁盘空间是一个重要的任务，以确保数据库的高效运行和数据的可靠性。以下是一些关键的优化策略：
监控磁盘使用情况
- 使用SQL函数监控磁盘空间：
SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'weather';
- 手动检查系统目录：
du -sh /var/lib/pgsql/data/*
清理不必要的数据
- 清理日志文件：
find /var/log/postgresql/ -type f -mtime 30 -name "*.log" -exec rm -f {} \;
- 清理临时文件：
find /tmp/ -type f -mtime 7 -exec rm -f {} \;
- 清理备份文件：
find /var/lib/pgsql/backups/ -type f -mtime -7 -exec rm -f {} \;
扩展磁盘空间
- 在虚拟化平台上扩展虚拟磁盘大小。
- 使用
fdisk重新扫描磁盘分区表。 - 使用
parted或fdisk对磁盘进行分区。 - 使用
resize2fs调整文件系统大小。
调整数据库参数
- 在
postgresql.conf中调整参数，如shared_buffers、work_mem、maintenance_work_mem等。
使用表空间
- 创建新的表空间：
CREATE TABLESPACE my_ts LOCATION '/mnt/psql_ts';
- 在创建表时指定表空间：
CREATE TABLE mytable ( id SERIAL PRIMARY KEY , name VARCHAR ( 100 )) TABLESPACE my_ts;
删除表空间
- 删除不再需要的表空间（确保表空间为空）：
DROP TABLESPACE mytablespace;
查看数据库和表的磁盘空间占用
- 查看数据库总大小：
SELECT pg_size_pretty(pg_database_size('dbname'));
- 查看指定数据库每个表的磁盘空间：
SELECT table_schema, table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' table_schema table_name AS size FROM information_schema.tables ORDER BY pg_total_relation_size('"' table_schema table_name '"') desc;
通过上述方法，可以有效地管理和优化CentOS上PostgreSQL的磁盘空间，确保数据库的高效运行。