如何在Postgress中生成结果集的哈希? [英] How to generate a hash of the result set in Postgress?

问题描述

我有两个用于记录内容的数据库，我想检查它们是否同步.

I have two databases for logging stuff, which I want to check for synchronization.

批准的解决方案是定期(分别说每小时)向两者发送选择，生成结果集的哈希值并进行比较.如果它们匹配，那就很好，否则会产生一些警报.

The solution approved is to send periodically (lets say hourly) a select to both, generate a hash of the result set and compare them. If they match then great, otherwise generate some alarms.

当前，我正在通过(bash脚本)执行此操作:

Currently I'm doing it by (bash script):

 log_table="SELECT column1, column2, column3 FROM log_table where to_char(timestamp, '$ts_format') = '$tx_moment'";
PSQL="psql -t -q -h $_gp_host -U $_gp_user -d log_schema -c ";
echo "`${PSQL} $tx_fix${log_table} | sort | cksum`";

我想在postgres端做cksum/hash.因为当前它会下载整个结果集(甚至可能有25MB或更多)，并在服务器端生成校验和.

I would like to do the cksum/hash on the postgres side. Because currently it downloads the whole result set (which can have even 25MB or more) and generates the checksum on the server side.

Google没有帮助.

Google didn't help.

有什么建议吗?

谢谢.

推荐答案

您可以使用md5:

 log_table="
SELECT 
  md5(column1 || column2 || column3) AS hash,
  column1, column2, column3
FROM log_table where to_char(timestamp, '$ts_format') = '$tx_moment'";

这篇关于如何在Postgress中生成结果集的哈希?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！