external_file

通过 PostgreSQL 函数访问服务器端外部文件

概览

扩展包名	版本	分类	许可证	语言
`external_file`	`1.2`	UTIL	PostgreSQL	SQL

ID	扩展名	Bin	Lib	Load	Create	Trust	Reloc	模式
4285	`external_file`	否	否	否	是	否	否	`external_file`

相关扩展	`fio` `floatfile` `file_fdw` `byteamagic` `aws_s3` `envvar` `pg_bulkload` `basic_archive` `basebackup_to_shell` `pg_parquet`

Fixed schema external_file; superuser required.

版本

类型	仓库	版本	PG 大版本	包名	依赖
EXT	PIGSTY	`1.2`	1817161514	`external_file`	-
RPM	PIGSTY	`1.2`	1817161514	`external_file_$v`	-
DEB	PIGSTY	`1.2`	1817161514	`postgresql-$v-external-file`	-

OS / PG	PG18	PG17	PG16	PG15	PG14
el8.x86_64	PIGSTY 1.2 el8.x86_64.pg18 : external_file_18 external_file_18-1.2-1PIGSTY.el8.x86_64.rpm PIGSTY · 1.2 · 12.9KiB	PIGSTY 1.2 el8.x86_64.pg17 : external_file_17 external_file_17-1.2-1PIGSTY.el8.x86_64.rpm PIGSTY · 1.2 · 12.9KiB	PIGSTY 1.2 el8.x86_64.pg16 : external_file_16 external_file_16-1.2-1PIGSTY.el8.x86_64.rpm PIGSTY · 1.2 · 12.9KiB	PIGSTY 1.2 el8.x86_64.pg15 : external_file_15 external_file_15-1.2-1PIGSTY.el8.x86_64.rpm PIGSTY · 1.2 · 12.9KiB	PIGSTY 1.2 el8.x86_64.pg14 : external_file_14 external_file_14-1.2-1PIGSTY.el8.x86_64.rpm PIGSTY · 1.2 · 12.9KiB
el8.aarch64	PIGSTY 1.2 el8.aarch64.pg18 : external_file_18 external_file_18-1.2-1PIGSTY.el8.aarch64.rpm PIGSTY · 1.2 · 12.9KiB	PIGSTY 1.2 el8.aarch64.pg17 : external_file_17 external_file_17-1.2-1PIGSTY.el8.aarch64.rpm PIGSTY · 1.2 · 12.9KiB	PIGSTY 1.2 el8.aarch64.pg16 : external_file_16 external_file_16-1.2-1PIGSTY.el8.aarch64.rpm PIGSTY · 1.2 · 12.9KiB	PIGSTY 1.2 el8.aarch64.pg15 : external_file_15 external_file_15-1.2-1PIGSTY.el8.aarch64.rpm PIGSTY · 1.2 · 12.9KiB	PIGSTY 1.2 el8.aarch64.pg14 : external_file_14 external_file_14-1.2-1PIGSTY.el8.aarch64.rpm PIGSTY · 1.2 · 12.9KiB
el9.x86_64	PIGSTY 1.2 el9.x86_64.pg18 : external_file_18 external_file_18-1.2-1PIGSTY.el9.x86_64.rpm PIGSTY · 1.2 · 12.9KiB	PIGSTY 1.2 el9.x86_64.pg17 : external_file_17 external_file_17-1.2-1PIGSTY.el9.x86_64.rpm PIGSTY · 1.2 · 12.9KiB	PIGSTY 1.2 el9.x86_64.pg16 : external_file_16 external_file_16-1.2-1PIGSTY.el9.x86_64.rpm PIGSTY · 1.2 · 12.9KiB	PIGSTY 1.2 el9.x86_64.pg15 : external_file_15 external_file_15-1.2-1PIGSTY.el9.x86_64.rpm PIGSTY · 1.2 · 12.9KiB	PIGSTY 1.2 el9.x86_64.pg14 : external_file_14 external_file_14-1.2-1PIGSTY.el9.x86_64.rpm PIGSTY · 1.2 · 12.9KiB
el9.aarch64	PIGSTY 1.2 el9.aarch64.pg18 : external_file_18 external_file_18-1.2-1PIGSTY.el9.aarch64.rpm PIGSTY · 1.2 · 12.9KiB	PIGSTY 1.2 el9.aarch64.pg17 : external_file_17 external_file_17-1.2-1PIGSTY.el9.aarch64.rpm PIGSTY · 1.2 · 12.9KiB	PIGSTY 1.2 el9.aarch64.pg16 : external_file_16 external_file_16-1.2-1PIGSTY.el9.aarch64.rpm PIGSTY · 1.2 · 12.9KiB	PIGSTY 1.2 el9.aarch64.pg15 : external_file_15 external_file_15-1.2-1PIGSTY.el9.aarch64.rpm PIGSTY · 1.2 · 12.9KiB	PIGSTY 1.2 el9.aarch64.pg14 : external_file_14 external_file_14-1.2-1PIGSTY.el9.aarch64.rpm PIGSTY · 1.2 · 12.9KiB
el10.x86_64	PIGSTY 1.2 el10.x86_64.pg18 : external_file_18 external_file_18-1.2-1PIGSTY.el10.x86_64.rpm PIGSTY · 1.2 · 13.0KiB	PIGSTY 1.2 el10.x86_64.pg17 : external_file_17 external_file_17-1.2-1PIGSTY.el10.x86_64.rpm PIGSTY · 1.2 · 13.0KiB	PIGSTY 1.2 el10.x86_64.pg16 : external_file_16 external_file_16-1.2-1PIGSTY.el10.x86_64.rpm PIGSTY · 1.2 · 13.0KiB	PIGSTY 1.2 el10.x86_64.pg15 : external_file_15 external_file_15-1.2-1PIGSTY.el10.x86_64.rpm PIGSTY · 1.2 · 13.0KiB	PIGSTY 1.2 el10.x86_64.pg14 : external_file_14 external_file_14-1.2-1PIGSTY.el10.x86_64.rpm PIGSTY · 1.2 · 13.0KiB
el10.aarch64	PIGSTY 1.2 el10.aarch64.pg18 : external_file_18 external_file_18-1.2-1PIGSTY.el10.aarch64.rpm PIGSTY · 1.2 · 13.0KiB	PIGSTY 1.2 el10.aarch64.pg17 : external_file_17 external_file_17-1.2-1PIGSTY.el10.aarch64.rpm PIGSTY · 1.2 · 13.0KiB	PIGSTY 1.2 el10.aarch64.pg16 : external_file_16 external_file_16-1.2-1PIGSTY.el10.aarch64.rpm PIGSTY · 1.2 · 13.0KiB	PIGSTY 1.2 el10.aarch64.pg15 : external_file_15 external_file_15-1.2-1PIGSTY.el10.aarch64.rpm PIGSTY · 1.2 · 13.0KiB	PIGSTY 1.2 el10.aarch64.pg14 : external_file_14 external_file_14-1.2-1PIGSTY.el10.aarch64.rpm PIGSTY · 1.2 · 13.0KiB
d12.x86_64	PIGSTY 1.2 d12.x86_64.pg18 : postgresql-18-external-file postgresql-18-external-file_1.2-1PIGSTY~bookworm_amd64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 d12.x86_64.pg17 : postgresql-17-external-file postgresql-17-external-file_1.2-1PIGSTY~bookworm_amd64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 d12.x86_64.pg16 : postgresql-16-external-file postgresql-16-external-file_1.2-1PIGSTY~bookworm_amd64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 d12.x86_64.pg15 : postgresql-15-external-file postgresql-15-external-file_1.2-1PIGSTY~bookworm_amd64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 d12.x86_64.pg14 : postgresql-14-external-file postgresql-14-external-file_1.2-1PIGSTY~bookworm_amd64.deb PIGSTY · 1.2 · 7.1KiB
d12.aarch64	PIGSTY 1.2 d12.aarch64.pg18 : postgresql-18-external-file postgresql-18-external-file_1.2-1PIGSTY~bookworm_arm64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 d12.aarch64.pg17 : postgresql-17-external-file postgresql-17-external-file_1.2-1PIGSTY~bookworm_arm64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 d12.aarch64.pg16 : postgresql-16-external-file postgresql-16-external-file_1.2-1PIGSTY~bookworm_arm64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 d12.aarch64.pg15 : postgresql-15-external-file postgresql-15-external-file_1.2-1PIGSTY~bookworm_arm64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 d12.aarch64.pg14 : postgresql-14-external-file postgresql-14-external-file_1.2-1PIGSTY~bookworm_arm64.deb PIGSTY · 1.2 · 7.1KiB
d13.x86_64	PIGSTY 1.2 d13.x86_64.pg18 : postgresql-18-external-file postgresql-18-external-file_1.2-1PIGSTY~trixie_amd64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 d13.x86_64.pg17 : postgresql-17-external-file postgresql-17-external-file_1.2-1PIGSTY~trixie_amd64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 d13.x86_64.pg16 : postgresql-16-external-file postgresql-16-external-file_1.2-1PIGSTY~trixie_amd64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 d13.x86_64.pg15 : postgresql-15-external-file postgresql-15-external-file_1.2-1PIGSTY~trixie_amd64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 d13.x86_64.pg14 : postgresql-14-external-file postgresql-14-external-file_1.2-1PIGSTY~trixie_amd64.deb PIGSTY · 1.2 · 7.1KiB
d13.aarch64	PIGSTY 1.2 d13.aarch64.pg18 : postgresql-18-external-file postgresql-18-external-file_1.2-1PIGSTY~trixie_arm64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 d13.aarch64.pg17 : postgresql-17-external-file postgresql-17-external-file_1.2-1PIGSTY~trixie_arm64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 d13.aarch64.pg16 : postgresql-16-external-file postgresql-16-external-file_1.2-1PIGSTY~trixie_arm64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 d13.aarch64.pg15 : postgresql-15-external-file postgresql-15-external-file_1.2-1PIGSTY~trixie_arm64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 d13.aarch64.pg14 : postgresql-14-external-file postgresql-14-external-file_1.2-1PIGSTY~trixie_arm64.deb PIGSTY · 1.2 · 7.1KiB
u22.x86_64	PIGSTY 1.2 u22.x86_64.pg18 : postgresql-18-external-file postgresql-18-external-file_1.2-1PIGSTY~jammy_amd64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 u22.x86_64.pg17 : postgresql-17-external-file postgresql-17-external-file_1.2-1PIGSTY~jammy_amd64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 u22.x86_64.pg16 : postgresql-16-external-file postgresql-16-external-file_1.2-1PIGSTY~jammy_amd64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 u22.x86_64.pg15 : postgresql-15-external-file postgresql-15-external-file_1.2-1PIGSTY~jammy_amd64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 u22.x86_64.pg14 : postgresql-14-external-file postgresql-14-external-file_1.2-1PIGSTY~jammy_amd64.deb PIGSTY · 1.2 · 7.1KiB
u22.aarch64	PIGSTY 1.2 u22.aarch64.pg18 : postgresql-18-external-file postgresql-18-external-file_1.2-1PIGSTY~jammy_arm64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 u22.aarch64.pg17 : postgresql-17-external-file postgresql-17-external-file_1.2-1PIGSTY~jammy_arm64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 u22.aarch64.pg16 : postgresql-16-external-file postgresql-16-external-file_1.2-1PIGSTY~jammy_arm64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 u22.aarch64.pg15 : postgresql-15-external-file postgresql-15-external-file_1.2-1PIGSTY~jammy_arm64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 u22.aarch64.pg14 : postgresql-14-external-file postgresql-14-external-file_1.2-1PIGSTY~jammy_arm64.deb PIGSTY · 1.2 · 7.1KiB
u24.x86_64	PIGSTY 1.2 u24.x86_64.pg18 : postgresql-18-external-file postgresql-18-external-file_1.2-1PIGSTY~noble_amd64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 u24.x86_64.pg17 : postgresql-17-external-file postgresql-17-external-file_1.2-1PIGSTY~noble_amd64.deb PIGSTY · 1.2 · 7.2KiB	PIGSTY 1.2 u24.x86_64.pg16 : postgresql-16-external-file postgresql-16-external-file_1.2-1PIGSTY~noble_amd64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 u24.x86_64.pg15 : postgresql-15-external-file postgresql-15-external-file_1.2-1PIGSTY~noble_amd64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 u24.x86_64.pg14 : postgresql-14-external-file postgresql-14-external-file_1.2-1PIGSTY~noble_amd64.deb PIGSTY · 1.2 · 7.1KiB
u24.aarch64	PIGSTY 1.2 u24.aarch64.pg18 : postgresql-18-external-file postgresql-18-external-file_1.2-1PIGSTY~noble_arm64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 u24.aarch64.pg17 : postgresql-17-external-file postgresql-17-external-file_1.2-1PIGSTY~noble_arm64.deb PIGSTY · 1.2 · 7.2KiB	PIGSTY 1.2 u24.aarch64.pg16 : postgresql-16-external-file postgresql-16-external-file_1.2-1PIGSTY~noble_arm64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 u24.aarch64.pg15 : postgresql-15-external-file postgresql-15-external-file_1.2-1PIGSTY~noble_arm64.deb PIGSTY · 1.2 · 7.1KiB	PIGSTY 1.2 u24.aarch64.pg14 : postgresql-14-external-file postgresql-14-external-file_1.2-1PIGSTY~noble_arm64.deb PIGSTY · 1.2 · 7.1KiB
u26.x86_64	PIGSTY 1.2 u26.x86_64.pg18 : postgresql-18-external-file postgresql-18-external-file_1.2-1PIGSTY~resolute_amd64.deb PIGSTY · 1.2 · 7.2KiB	PIGSTY 1.2 u26.x86_64.pg17 : postgresql-17-external-file postgresql-17-external-file_1.2-1PIGSTY~resolute_amd64.deb PIGSTY · 1.2 · 7.2KiB	PIGSTY 1.2 u26.x86_64.pg16 : postgresql-16-external-file postgresql-16-external-file_1.2-1PIGSTY~resolute_amd64.deb PIGSTY · 1.2 · 7.2KiB	PIGSTY 1.2 u26.x86_64.pg15 : postgresql-15-external-file postgresql-15-external-file_1.2-1PIGSTY~resolute_amd64.deb PIGSTY · 1.2 · 7.2KiB	PIGSTY 1.2 u26.x86_64.pg14 : postgresql-14-external-file postgresql-14-external-file_1.2-1PIGSTY~resolute_amd64.deb PIGSTY · 1.2 · 7.2KiB
u26.aarch64	PIGSTY 1.2 u26.aarch64.pg18 : postgresql-18-external-file postgresql-18-external-file_1.2-1PIGSTY~resolute_arm64.deb PIGSTY · 1.2 · 7.2KiB	PIGSTY 1.2 u26.aarch64.pg17 : postgresql-17-external-file postgresql-17-external-file_1.2-1PIGSTY~resolute_arm64.deb PIGSTY · 1.2 · 7.2KiB	PIGSTY 1.2 u26.aarch64.pg16 : postgresql-16-external-file postgresql-16-external-file_1.2-1PIGSTY~resolute_arm64.deb PIGSTY · 1.2 · 7.2KiB	PIGSTY 1.2 u26.aarch64.pg15 : postgresql-15-external-file postgresql-15-external-file_1.2-1PIGSTY~resolute_arm64.deb PIGSTY · 1.2 · 7.2KiB	PIGSTY 1.2 u26.aarch64.pg14 : postgresql-14-external-file postgresql-14-external-file_1.2-1PIGSTY~resolute_arm64.deb PIGSTY · 1.2 · 7.2KiB

构建

您可以使用 pig build 命令构建 external_file 扩展的 RPM / DEB 包：

pig build pkg external_file         # 构建 RPM / DEB 包

安装

您可以直接安装 external_file 扩展包的预置二进制包，首先确保 PGDG 和 PIGSTY 仓库已经添加并启用：

pig repo add pgsql -u          # 添加仓库并更新缓存

使用 pig 或者是 apt/yum/dnf 安装扩展：

安装

pig install external_file;          # 当前活跃 PG 版本安装

pig

pig ext install -y external_file -v 18  # PG 18
pig ext install -y external_file -v 17  # PG 17
pig ext install -y external_file -v 16  # PG 16
pig ext install -y external_file -v 15  # PG 15
pig ext install -y external_file -v 14  # PG 14

dnf

dnf install -y external_file_18       # PG 18
dnf install -y external_file_17       # PG 17
dnf install -y external_file_16       # PG 16
dnf install -y external_file_15       # PG 15
dnf install -y external_file_14       # PG 14

apt

apt install -y postgresql-18-external-file   # PG 18
apt install -y postgresql-17-external-file   # PG 17
apt install -y postgresql-16-external-file   # PG 16
apt install -y postgresql-15-external-file   # PG 15
apt install -y postgresql-14-external-file   # PG 14

创建扩展：

CREATE EXTENSION external_file;

用法

来源：README，Release v1.2

external_file 将文件定位符存储为 (directory, filename) 对，并通过 PostgreSQL lo_* 辅助函数访问服务器端文件，而不是直接读取文件。

基本流程

CREATE EXTENSION external_file;

INSERT INTO directories(directory_name, directory_path)
VALUES ('temporary', '/tmp/');

INSERT INTO directory_roles(directory_name, directory_role, directory_read, directory_write)
VALUES ('temporary', 'app_reader', true, false);

SELECT writeEfile('\x48656c6c6f0a', ('temporary', 'hello.txt'));
SELECT readEfile(efilename('temporary', 'hello.txt'));
SELECT copyEfile(('temporary', 'hello.txt'), ('temporary', 'hello-copy.txt'));

核心对象

directories：将别名映射到服务器上的目录路径。
directory_roles：为角色授予该别名的读写权限。
efilename(directory, filename)：构造一个 efile 定位符。
readEfile(efile)：将目标文件读取为 bytea。
writeEfile(bytea, efile)：将 bytea 写入目标文件。
copyEfile(src, dest)：将一个外部文件复制到另一个外部文件。
getEfilePath(efile, need_read, need_write)：解析完整路径并检查访问权限。

注意事项

创建扩展需要 PostgreSQL superuser。
上游默认会在 external_file schema 中创建全部对象。
PostgreSQL 的 OS user 仍然需要对目标目录具备文件系统读写权限。
文件名不能包含 / 或 \；访问必须通过目录表进行受控中介。

external_file

概览

版本

构建

安装

用法

基本流程

核心对象

注意事项

这个页面对您有帮助吗？