Postgresql查找数据库使用的全部磁盘空间。

I have more than 50 databases hosted in my postgresql server. I need to move some of them on another host, to free-up some disk space,but how can I measure the disk-space used by each database on my volume?

我的postgresql服务器上有超过50个数据库。我需要将它们中的一些移到另一个主机上，以增加磁盘空间，但是如何度量卷上每个数据库使用的磁盘空间呢?

Is there any function exists to get the information that I want?

有什么函数可以得到我想要的信息吗?

3 个解决方案

#1

SELECT pg_database_size('geekdb')

或

SELECT pg_size_pretty(pg_database_size('geekdb'))

http://www.thegeekstuff.com/2009/05/15-advanced-postgresql-commands-with-examples/

#2

You could use postgresql Meta-Commands:

您可以使用postgresql元命令:

\l would list databases
\ l将数据库列表
\l+ extends list with Size, Tablespace, Description.
带大小、表空间、描述扩展列表。

Use \? to get full list of meta-commands. Also see: https://www.postgresql.org/docs/9.5/static/app-psql.html

使用\ ?获取完整的元命令列表。还看到:https://www.postgresql.org/docs/9.5/static/app-psql.html

#3

This is an old question, but I created a way to see the results of linux command df -h (Filesystem, Size, Used, Avail, Use%, Mounted on) via a sql query, thus your free disk space and total available disk space for a given file system. Not exactly what the question is about, but helpful for some of use/me. I wish that answer was here hours ago, so I am putting it here (linux only):

这是一个老问题，但是我创建了一种方法来通过sql查询查看linux命令df -h(文件系统、大小、使用、使用、使用、使用%)的结果，从而查看给定文件系统的空闲磁盘空间和总可用磁盘空间。这不是问题的实质，但对某些人有用。我希望这个答案在几个小时前就已经揭晓了，所以我把它放在这里(只有linux):

create a cron job like this:

创建这样的cron工作:

@hourly df -h | awk '{print $1","$2","$3","$4","$5","$6}' > /pathhere/diskspaceinfo.csv`

create a foreign table to query:

创建一个外表来查询:

create extension file_fdw;

create server logserver FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE diskspaceinfo 
(file_sys text, size text, used text, avail text, used_pct text, mount text) 
SERVER fileserver 
OPTIONS (filename '/pathhere/diskspaceinfo.csv', format 'csv');

Then query your table like this:

然后这样查询您的表:

select * from diskspaceinfo

If you just want something specific, of course just filter the table for what you want. It has limitations, but is very useful for me.

如果你只是想要一些特定的东西，当然只要过滤表格就可以了。它有局限性，但对我很有用。

If you have plperlu, you could use this function: https://wiki.postgresql.org/wiki/Free_disk_space

如果您有plperlu，您可以使用这个函数:https://wiki.postgresql.org/wiki/Free_disk_space

A useful link: https://wiki.postgresql.org/wiki/Disk_Usage

一个有用的链接:https://wiki.postgresql.org/wiki/Disk_Usage

#1

SELECT pg_database_size('geekdb')

或

SELECT pg_size_pretty(pg_database_size('geekdb'))