database Postgresql 查找数据库使用的总磁盘空间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14346371/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql find total disk space used by a database
提问by pylover
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?
是否有任何功能可以获取我想要的信息？
回答by Robert Harvey
SELECT pg_database_size('geekdb')
or
或者
SELECT pg_size_pretty(pg_database_size('geekdb'))
http://www.thegeekstuff.com/2009/05/15-advanced-postgresql-commands-with-examples/
http://www.thegeekstuff.com/2009/05/15-advanced-postgresql-commands-with-examples/
回答by sharez
You could use postgresql Meta-Commands:
您可以使用 postgresql 元命令：
\lwould list databases\l+extends list with Size, Tablespace, Description.
\l将列出数据库\l+使用大小、表空间、描述扩展列表。
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
回答by mountainclimber
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):
这是一个老问题，但我创建了一种方法来df -h通过 sql 查询查看 linux 命令（文件系统、大小、已用、可用、使用%、已安装）的结果，从而查看您的可用磁盘空间和总可用磁盘空间给定的文件系统。不完全是问题的内容，但对某些使用/我有帮助。我希望这个答案在几个小时前就在这里，所以我把它放在这里（仅限 Linux）：
create a cron job like this:
创建一个像这样的 cron 作业：
@hourly df -h | awk '{print ","","","","","}' > /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
回答by Steve Lloyd
Love mountainclimber's answer. I tweaked the cron to return bytes and remove the header row as follows.
喜欢登山者的回答。我调整了 cron 以返回字节并删除标题行，如下所示。
* * * * * df -B1 | tail -n+2 | awk '{print ","","","","","}' > /var/www/diskspaceinfo.csv
