postgresql二进制大对象清理

PostgreSQL中文社区 2021-02-06

2523

译者简介

晏杰宏，任职于上海新炬网络信息技术股份有限公司，Oracle DBA。负责数据库、中间件、大数据等基础软件建设、优化和业务保障工作。具有10年以上电信与银行企业一线/二线数据库运维管理经验。目前专注研究云计算、开源软件和国产化数据库等领域技术研究。

校对者简介

崔鹏,任职于海能达通信股份有限公司,数据库开发高级工程师,致力于postgresql数据库在专网通信领域、公共安全领域的应用与推广。

PostgreSQL提供了一个很好的BLOB接口，它被广泛使用。但是，最近我们遇到了各种客户面临的问题，有必要进行反思并弄清楚PostgreSQL如何处理二进制大对象，尤其是二进制大对象清理，这是很有意义的。

使用PostgreSQL BLOB接口

在PostgreSQL中，您可以使用各种方法来存储二进制数据。最简单的形式肯定是利用“ bytea”（字节数组）数据类型。在这种情况下，二进制字段基本上被视为行的一部分。

下面是它的工作原因：

test=# CREATE TABLE t_image (id int, name text, image bytea);
CREATE TABLE
test=# \d t_image
Table "public.t_image"
Column | Type    | Collation | Nullable | Default
-------+---------+-----------+----------+---------
id     | integer |           |          |
name   | text    |           |          |
image  | bytea   |           |          |

正如你所看到的，这是一个普通列，可以像普通列一样使用。唯一值得一提的是必须在SQL级别使用的编码。PostgreSQL使用变量来配置此行为：

test=# SHOW bytea_output;
bytea_output
--------------
hex
(1 row)

bytea_output变量接受两个值：“ hex”告诉PostgreSQL以十六进制格式发送数据。“转义”表示必须将数据作为八进制字符串输入。除了每个字段1 GB的最大大小外，这里的应用程序不必担心。但是，PostgreSQL有另一个处理二进制数据的接口：BLOB接口。让我展示一下这个功能强大的工具的示例：

test=# SELECT lo_import('/etc/hosts');
lo_import
-----------
80343
(1 row)

在这种情况下，/etc/hosts的内容已导入到数据库中。请注意，PostgreSQL有数据的副本–它不是文件系统的链接。这里值得注意的是，数据库将返回新条目的OID（对象ID）。为了跟踪这些OID，一些开发人员执行以下操作：

test=# CREATE TABLE t_file (
id int,
name text,
object_id oid
);
CREATE TABLE
test=# INSERT INTO t_file
VALUES (1, 'some_name', lo_import('/etc/hosts'))
RETURNING *;
id  | name          | object_id
----+---------------+-----------
1   | some_name     | 80350
(1 row)
1个  INSERT 0 1

这是绝对可以的，除非您执行以下操作:

test=# DELETE FROM t_file WHERE id = 1;
DELETE 1

问题是对象id被遗忘了。但是，目标仍然存在。pg_largeobject是PostgreSQL中负责存储二进制数据的系统表。所有的lo_functions都将简单地与这个系统表交互，以便处理这些问题：

test=# \x
Expanded display is on.
test=# SELECT * FROM pg_largeobject WHERE loid = 80350;
-[ RECORD 1 ]------------------------------------------
loid   | 80350
pageno | 0
data   | ##\012# Host Database\012#\012# localhost ...

为什么会有问题呢？原因很简单：您的数据库将增长，并且“死对象”的数量将累积。因此，杀死BLOB条目的正确方法如下：

test=# \x
Expanded display is off.
test=#
test=# SELECT lo_unlink(80350);
lo_unlink
-----------
1
(1 row)
 
test=# SELECT * FROM pg_largeobject WHERE loid = 80350;
loid  | pageno | data
------+--------+------
(0 rows)

如果你忘了解除对象的链接，从长远来看，你将遭受损失——我们经常看到这种情况发生。如果使用BLOB接口，这是一个主要问题。

vacuumlo：清理死的大对象

然而，一旦你积累了上千个，甚至百万个死的二进制大对象，你该如何解决这个问题呢?答案是一个名为“vacuumlo”的命令行工具。

让我们首先创建一个死条目

test=# SELECT lo_import('/etc/hosts');
lo_import
-----------
80351
(1 row)

这样我们就可以在任何客户端运行vacuumlo：

iMac:~ hs$ vacuumlo -h localhost -v test
Connected to database "test"
Checking object_id in public.t_file
Successfully removed 2 large objects from database "test".

如你所见，有两个死物被这个工具杀死了。vacuumlo是清除孤立对象的最简单方法。

附加功能

但是，不仅有lo_import和lo_unlink。PostgreSQL提供了多种功能来很好地处理大型对象：

test=# \df lo_*
                                List of functions
   Schema   |   Name        | Result data type | Argument data types       | Type
------------+---------------+------------------+---------------------------+------
 pg_catalog | lo_close      | integer          | integer                   | func
 pg_catalog | lo_creat      | oid              | integer                   | func
 pg_catalog | lo_create     | oid              | oid                       | func
 pg_catalog | lo_export     | integer          | oid, text                 | func
 pg_catalog | lo_from_bytea | oid              | oid, bytea                | func
 pg_catalog | lo_get        | bytea            | oid                       | func
 pg_catalog | lo_get        | bytea            | oid, bigint, integer      | func
 pg_catalog | lo_import     | oid              | text                      | func
 pg_catalog | lo_import     | oid              | text, oid                 | func
 pg_catalog | lo_lseek      | integer          | integer, integer, integer | func
 pg_catalog | lo_lseek64    | bigint           | integer, bigint, integer  | func
 pg_catalog | lo_open       | integer          | oid, integer              | func
 pg_catalog | lo_put        | void             | oid, bigint, bytea        | func
 pg_catalog | lo_tell       | integer          | integer                   | func
 pg_catalog | lo_tell64     | bigint           | integer                   | func
 pg_catalog | lo_truncate   | integer          | integer, integer          | func
 pg_catalog | lo_truncate64 | integer          | integer, bigint           | func
 pg_catalog | lo_unlink     | integer          | oid                       | func
(18 rows)

由于历史原因，还有两个不遵循命名约定的函数：loread和lowrite：

1个
2  pg_catalog | loread  | bytea   | integer, integer | func
pg_catalog | lowrite | integer | integer, bytea   | func

这些函数的名字再也不能轻易改变了。然而，值得注意的是，它们确实存在。

最后

PostgreSQL BLOB接口确实非常有用，可以用于很多事情。它的优点在于它是完全事务性的，因此二进制内容和元数据不能再不同步了。

请点击文章底部“阅读原文”查看原文。

PostgreSQL中文社区欢迎广大技术人员投稿
投稿邮箱：press@postgres.cn

postgresql

文章转载自PostgreSQL中文社区，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

postgresql二进制大对象清理

使用PostgreSQL BLOB接口

vacuumlo：清理死的大对象

附加功能

最后

评论