我如何知道 Postgresql 表空间中有什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4970966/
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
How can I tell what is in a Postgresql tablespace?
提问by Andy Lester
I've created a new tablespace called indexes, and I'm trying to remove the old tablespace indexes_old, which used to contain some tables and indexes. When I try to drop the tablespace, I get:
我创建了一个名为 的新表空间indexes，我正在尝试删除旧表空间indexes_old，它曾经包含一些表和索引。当我尝试删除表空间时，我得到：
=> drop tablespace indexes_old;
ERROR: tablespace "indexes_old" is not empty
But when I try to see what's in there, it seems that no tables live in that tablespace:
但是当我尝试查看其中的内容时，似乎该表空间中没有表：
=> select * from pg_tables where tablespace = 'indexes_old';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+-----------+------------+------------+------------+----------+-------------
(0 rows)
=> select * from pg_indexes where tablespace = 'indexes_old';
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-----------+------------+----------
(0 rows)
So what is in that tablespace that is preventing me from dropping it?
那么那个表空间中有什么阻止我删除它呢？
In case it matters, I've just migrated from Pg 8.4 to Pg 9.0 using the pg_upgrade tool.
以防万一，我刚刚使用 pg_upgrade 工具从 Pg 8.4 迁移到 Pg 9.0。
The tablespaces look like this:
表空间如下所示：
Name | Owner | Location | Access privileges | Description
-------------+----------+-----------------+-------------------+-------------
indexes | nobody | /data/pgindex90 | |
indexes_old | nobody | /data/pgindex84 | |
and the contents of /data/pgindex84 include all the old 8.4 indexes, plus this new 9.0 index that pg_upgrade automatically created
/data/pgindex84 的内容包括所有旧的 8.4 索引，以及 pg_upgrade 自动创建的这个新的 9.0 索引
# sudo ls -al /data/pgindex84/PG_9.0_201008051/11874
total 8280
drwx------ 2 postgres postgres 4096 Feb 9 14:58 .
drwx------ 3 postgres postgres 4096 Feb 11 09:28 ..
-rw------- 1 postgres postgres 40960 Feb 9 14:58 10462602
-rw------- 1 postgres postgres 40960 Feb 9 14:58 10462604
-rw------- 1 postgres postgres 4644864 Feb 9 14:58 10462614
-rw------- 1 postgres postgres 3727360 Feb 9 14:58 10462616
采纳答案by Frank Heikens
Check pg_class to see what is located where:
检查 pg_class 以查看位于何处：
SELECT
c.relname,
t.spcname
FROM
pg_class c
JOIN pg_tablespace t ON c.reltablespace = t.oid
WHERE
t.spcname = 'indexes_old';
回答by Mike Sherrill 'Cat Recall'
In PostgreSQL, a tablespace can be used by any PostgreSQL database. (As long as the requesting user has sufficient privileges, that is.) I think this query
在 PostgreSQL 中，任何 PostgreSQL 数据库都可以使用表空间。（只要请求用户有足够的权限，就是这样。）我认为这个查询
SELECT spcname, spclocation FROM pg_tablespace;
will show you the directory that index_old is using in the filesystem in PostgreSQL version through 9.1. Prowl around in there to see if something real is in your way. I'd be really cautious about trying to delete anything in there apart from using PostgreSQL's interface, though.
将向您显示 index_old 在 PostgreSQL 版本到 9.1 的文件系统中使用的目录。在那里徘徊，看看是否有真实的东西挡住了你的路。不过，除了使用 PostgreSQL 的界面之外，我对尝试删除其中的任何内容都非常谨慎。
In 9.2+, try
在 9.2+ 中，尝试
select spcname, pg_tablespace_location(oid) from pg_tablespace;
回答by Shorthand
In PG 10 and possibly a little earlier, this seems to have morphed to:
在 PG 10 和可能更早的版本中，这似乎已经演变为：
SELECT tablename from pg_tables WHERE tablespace = 'foo';
回答by a_horse_with_no_name
Unfortunately there is "global" view across all databases. However this can be done using the dblinkextension together with the following function:
不幸的是，所有数据库都有“全局”视图。但是，这可以使用dblink扩展和以下函数来完成：
create or replace function show_tablespace_objects(p_tablespace text, p_user text, p_password text)
returns table (db_name text, schema_name text, object_name text, object_type text, tablespace_name text)
as
$func$
declare
l_stmt text;
l_con_name text := 'tbs_check_conn';
l_con_string text;
l_rec record;
begin
l_stmt := $query$SELECT current_database(),
n.nspname as schema_name,
c.relname as object_name,
case c.relkind
when 'r' then 'table'
when 'i' then 'index'
when 't' then 'TOAST table'
when 'm' then 'materialized view'
when 'f' then 'foreign table'
when 'p' then 'partitioned table'
else c.relkind::text
end as object_type,
t.spcname as tablespace_name
FROM pg_class c
JOIN pg_namespace n on n.oid = c.relnamespace
JOIN pg_tablespace t ON c.reltablespace = t.oid$query$;
if p_tablespace is not null then
l_stmt := l_stmt || format(' WHERE t.spcname=%L', p_tablespace);
end if;
for l_rec in (select * from pg_database where datallowconn) loop
l_con_string := format('dbname=%L user=%L password=%L',
l_rec.datname, p_user, p_password);
return query
select *
from dblink(l_con_string, l_stmt)
as t(db_name text, schema_name text, object_name text, object_type text, tablespace_name text);
end loop;
end;
$func$
language plpgsql;
The function accepts a tablespace name and a username and password that is valid for all databases in the current server.
该函数接受对当前服务器中的所有数据库有效的表空间名称以及用户名和密码。
If the tablespace name is passed as nullall objects that are not in the default tablespace are listed (that would be pg_globalin a default installation without any additional tablespaces)
如果表空间名称被传递为null所有不在默认表空间中的对象被列出（这将pg_global在没有任何额外表空间的默认安装中）
This can be used like this:
这可以像这样使用：
select *
from show_tablespace_objects('indexes_old', 'postgres', 'verysecretpassword');
回答by Paul Hoffman
A decade later, I had this problem, and one of the small comments above helped me find the solugion. select * from pg_tables where tablespace = 'my_tablespace';only lists tables in the current databasethat use the tablespace. You have to cycle through each database you have trying that command to find one that uses that tablespace.
十年后，我遇到了这个问题，上面的一条小评论帮助我找到了解决方案。select * from pg_tables where tablespace = 'my_tablespace';只列出当前数据库中使用该表空间的表。您必须遍历您尝试该命令的每个数据库，以找到使用该表空间的数据库。
回答by Philluminati
Are we talking about the PgSQL Interface?
我们在谈论 PgSQL 接口吗？
List schemas (tablespaces) like this:
像这样列出模式（表空间）：
\dn
List all the tables inside a schema (tablespace) like this:
像这样列出模式（表空间）中的所有表：
\dn <table_space>.*
Use
用
\?
for more options
更多选择
