Spring Boot项目data.sql插入图片报PostgreSQL lo_import文件不存在错误

问题产生原因 #

• 报错信息已经明确提示：could not open server file "Testimage.PNG"，核心问题出在lo_import()函数的运行逻辑上：
  • lo_import()是PostgreSQL的服务端内置函数，所有文件读取操作都在PostgreSQL数据库服务所在的服务器进程上下文中执行，不会访问你开发机、Java应用运行机器上的本地文件，也不会读取项目classpath下的资源
  • 你传入的相对路径Testimage.PNG，会被PostgreSQL解析为自身数据存储根目录下的相对路径，该路径下不存在你放在项目里的测试图片，因此触发文件不存在错误
  • 即使路径写对，普通用户调用lo_import()还需要满足PostgreSQL的超级用户权限要求，否则会被权限校验拦截。

对应解决方案 #

根据你的使用场景选择适配方案：

• 方案1：图片已存放在数据库服务器本地磁盘

  1. 把目标图片放到PostgreSQL服务所在服务器的可访问路径下，比如Windows环境的C:\pg_files\Testimage.PNG、Linux环境的/var/lib/postgresql/files/Testimage.PNG
  2. 确保运行PostgreSQL服务的系统账号对该文件有读权限
  3. 修改insert语句传入文件的绝对路径：

     insert into profile (profile_id,picture, start_in_company,attendance,tel_nr,location,introduction,degree,job_position,hobby,university) 
     values (1234, lo_import('C:/pg_files/Testimage.PNG') ,'2020-02-07', '2020-02-11', '015773845515', 'Stuttgart', 'hello, my name is artur', 'Software Engineering', 'Developer', 'Tennis, Fussball','Uni Ulm');
     

  注意：该方案需要你拥有PostgreSQL的超级用户权限，普通权限账号无法完成调用。

• 方案2：Spring Boot项目初始化时导入classpath下的图片（适配你当前的data.sql场景）
  不要在data.sql中直接调用lo_import()，因为data.sql的执行上下文是数据库服务端，完全无法读取Java项目classpath下的资源：

  1. 新增实现ApplicationRunner或CommandLineRunner接口的初始化类，在应用启动完成后执行自定义的数据写入逻辑
  2. 通过@Value注解读取项目resources路径下的图片二进制流
  3. 借助JDBC/JdbcTemplate接口直接把二进制流写入数据库的字段，完全跳过lo_import的服务端文件读取逻辑
  4. 核心实现示例：

     @Component
     public class ProfileDataInitializer implements ApplicationRunner {
         @Value("classpath:static/Testimage.PNG")
         private Resource testImageResource;
         @Autowired
         private JdbcTemplate jdbcTemplate;
     
         @Override
         public void run(ApplicationArguments args) throws Exception {
             // 读取classpath下的图片二进制内容
             byte[] imageBytes = StreamUtils.copyToByteArray(testImageResource.getInputStream());
             // 直接执行写入，无需依赖数据库服务端的文件路径
             jdbcTemplate.update(
                 "insert into profile (profile_id, picture) values (?, ?)",
                 1234, imageBytes
             );
         }
     }
     

• 方案3：必须保留纯SQL的初始化逻辑
  可以使用PostgreSQL的pg_read_binary_file()函数配合大对象转换逻辑完成读取，但该方式同样要求文件在数据库服务器的可访问路径下、且账号拥有超级用户权限，不适合跨环境部署的项目。

内容的提问来源于stack exchange，提问作者Professor Fritz