时间戳

首页 > 解决方案 > How to read a zip entry into a postgres table using the JDBC copymanager

postgresql - How to read a zip entry into a postgres table using the JDBC copymanager

问题描述

I am using the JDBC CopyManager library's copyout method to backup a postgres table to an AWS S3 bucket as a csv file. This works. I am saving this backup as a zip containing the file in order to save space in the AWS bucket.

I am now working on using the CopyManager library's copyin method to restore the backup file to my postgres table and am running into a small issue regarding reading the zipped input stream.

How can I access the zip entry and pass it into the copy manager's copyin method? Ideally, I'd like to not need to save any data to disk as an intermediate step between S3 and postgres.

I think this is a good start, but you ZipEntry is not an input stream, so there must be another line or two of code necessary to pass the zip entry in as an input stream. I am not very experienced working with inputstreams, so I imagine the answer is fairly simple.

public void restoreWithCopyManager( int list_id, String backupFileName) throws IOException, SQLException {
    Connection connection = db.getConnection();
    CopyManager copyManager = new CopyManager(connection.unwrap(BaseConnection.class));
    ImportBucketConfig bucketConf = Config.get().s3ImportBucket;
    String fileFullPath = backupFileName;

    S3InputStream in = new S3InputStream(bucket, fileFullPath, bucketConf.accessKeyId,
            bucketConf.secretAccessKey, S3Util.findRegion(bucketConf.region));

    ZipInputStream zis = new ZipInputStream(in);
    ZipEntry zipEntry = zis.getNextEntry();
    
    long res = copyManager.copyIn("COPY test_copy_table FROM STDIN WITH CSV", zipEntry);
    zis.close();
    connection.close();
}

标签： postgresqlamazon-s3jdbczipinputstream

解决方案

我找到了从 ZipInputStream 复制条目的解决方案

您将 ZipInputStream 传递给复制函数，但必须首先对 zip 输入流执行 getNextEntry() 以设置指向压缩条目的指针。

ZipInputStream zis = new ZipInputStream(in);
zis.getNextEntry();
long res = copyManager.copyIn("COPY test_copy_table FROM STDIN WITH CSV", zis);

推荐阅读