database - heroku 上的 pg_largeobject 访问

Question

我正在尝试清理 heroku 上的 postgres 数据库，其中一些大对象已经失控，我想删除不再使用的大对象。

在我的开发机器上，我可以:

select distinct loid from pg_largeobject 
Where loid Not In (select id from table )

然后我跑:

SELECT lo_unlink(loid)

在每个 ID 上。

但是在 heroku 上，我无法在 pg_largeobject 上运行任何选择，甚至

select * from pg_largeobject limit 1;

给我一​​个错误:

ERROR:  permission denied for relation pg_largeobject

关于如何解决这个问题的任何建议，或者为什么我们在 heroku 中没有对 pg_largeobject 的读取权限？

Answer 1

自 PostgreSQL 9.0 起，非 super 用户无法访问 pg_largeobject。这记录在发行说明中:

Add the ability to control large object (BLOB) permissions with GRANT/REVOKE (KaiGai Kohei)

Formerly, any database user could read or modify any large object. Read and write permissions can now be granted and revoked per large object, and the ownership of large objects is tracked.

如果它适用于您的开发实例，那是因为它是 8.4 或更低版本，或者因为您以 super 用户身份登录。

如果你不能在 heroku 上以 super 用户身份登录，我想你可以使用 pg_dump 转储远程数据库，然后在本地重新加载它，以本地 super 用户身份识别泄漏的 OID，将它们放入在带有 lo_unlink 命令的脚本中，最后针对 heroku 实例播放此脚本。

更新:

根据 psql \dl 命令查询数据库的方式，看来 pg_catalog.pg_largeobject_metadata 可用于检索所有大对象的 OID 和所有权，通过这个查询:

SELECT oid as "ID",
  pg_catalog.pg_get_userbyid(lomowner) as "Owner",
  pg_catalog.obj_description(oid, 'pg_largeobject') as "Description"
  FROM pg_catalog.pg_largeobject_metadata   ORDER BY oid

因此对于 9.0+ 的非 super 用户，您发现泄漏大对象的初始查询可以更改为:

select oid from pg_largeobject_metadata
 Where oid Not In (select id from table )

如有必要，可以在 lomowner 上添加条件以过滤特定用户拥有的大型对象。