将文件表(存储为bytea)复制到磁盘上的单个文件中

Question

使用postgresql 8.4中的copy命令导出类型bytea的数据，这对于单个记录可以很好地工作：

copy (SELECT encode(test_column, 'hex') FROM test_table LIMIT 1) TO '/home/user/file.hex'

之后，我使用xxd命令获得适当的文件类型。抱歉，如果这是一个非常菜鸟的问题，但是是否可以将所有记录导出到file-1，file-2 ... file-n？正确的语法或脚本是什么？我似乎无法在Postgresql副本的人中找到它。
例如，这是一行的bash脚本：

#!/bin/bash
psql   
    -P t   
    -P format=unaligned   
    -X   
    -U myuser   
    -h myhost   
    -c "select my_bytea_col from my_table where id=1"   
    mydb   
| xxd -r -p > dump.txt  

但是，我需要在所有ID上执行它，并在每个ID上保存它们，例如file1.txt，file2.txt，file 3.txt ..也可能非常有趣的是能够使用另一列的数据（例如ID）相应地命名的十六进制文件。

Answer 1

我会使用脚本语言对此进行直接支持。

这可以很好地给定一个名为regress的数据库，该数据库包含一个名为files的表，带有fileid（整数）和filedata（bytea）。它将数据写入outdir变量指定的目录中的文件，以根据{filenameprefix}{fileid}{filenamesuffix}等模式命名的文件。

。

#!/usr/bin/env python3
import os
import sys
import psycopg2
outdir = "files"
filenameprefix = "f"
filenamesuffix = ""
def main():
        os.makedirs(outdir, exist_ok=True)
        conn = psycopg2.connect("dbname=regress")
        curs = conn.cursor();
        curs.execute("SELECT fileid, filedata FROM files;")
        for (fileid, filedata) in curs:
            fn = filenameprefix+str(fileid)+filenamesuffix
            f = open(os.path.join(outdir, fn), "wb")
            f.write(filedata)
            f.close()
        conn.close();
if __name__ == '__main__':
        main()

我使用此代码创建了上述脚本中提到的测试环境：

#!/usr/bin/env python3
import psycopg2
conn = psycopg2.connect("dbname=regress")
curs = conn.cursor()
curs.execute('CREATE TABLE files(fileid serial primary key, filedata bytea);')
for i in range(1,10)
    curs.execute('INSERT INTO  files(filedata) VALUES (%s);', (psycopg2.Binary(open('/dev/urandom','rb').read(1024)),))

编辑：使其与您在问题中使用的命名相同，我刚刚认为这是通用的匿名名称，更改：

• regress->无论您的数据库名称是
什么
• files-> my_table
• fileid-> id
• filedata-> my_bytea_col

等同于此脚本可以在bash中完成，这只是麻烦。您需要使用联合过程以交易安全的方式进行操作。您需要创建一个psql共同处理，并在其中打开SERIALIZABLE事务，然后将SELECT fileid FROM files转到Shell变量中。循环在shell变量上，对于每个ID，将SELECT filedata FROM files循环到xxd管道中，以在ID上使用字符串插值创建的文件名。

imo：不值得麻烦，请使用脚本语言，使您直接访问postgresql访问。

如果您不需要交易安全，则要容易一些；只需在一个psql调用中获取ID，然后在后续文件中获取文件数据即可。它会慢，不会安全，但会更容易。