PostgreSQL 9.6只读副本中磁盘与DB文件/记录匹配校验

刚好碰到过类似的场景，给你整理个完全符合需求的实用方案：

先手动获取磁盘上的file_id列表 #

因为你没法在只读副本里创建函数或临时表，且文件存在另一台服务器的/var/www/dbfiles目录下，所以先登录那台服务器，跑下面的命令提取所有纯数字命名的文件名（也就是你的file_id）：

ls /var/www/dbfiles | grep -E '^[0-9]+$' | sort -n

把输出的所有数字整理成PostgreSQL数组格式，比如 array[519288, 519290, 519297, ...]。

执行全外连接查询找不匹配项 #

把上面整理好的数组放到下面的查询里，执行后就能直接找出两边不匹配的内容：

SELECT 
  f.name, 
  dir_listing.file_id AS disk_file_id
FROM 
  files f
FULL OUTER JOIN (
  -- 把这里的数组替换成你手动获取的磁盘file_id列表
  SELECT unnest(array[519288, 519290, 519297, 519298, 519231]) AS file_id
) AS dir_listing ON f.file_id = dir_listing.file_id
-- 只筛选两边不匹配的记录
WHERE 
  f.file_id IS NULL OR dir_listing.file_id IS NULL;

结果说明 #

• 当name有值但disk_file_id为空时：这条数据库记录没有对应的磁盘文件；
• 当disk_file_id有值但name为空时：磁盘上的这个文件没有对应的数据库记录。

这个方案完全用子查询实现全外连接，不用导出CSV做VLOOKUP，完美适配你只读副本的限制~

内容的提问来源于stack exchange，提问作者Nik M