centos postgresql磁盘空间优化

优化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的磁盘空间，确保数据库的高效运行。