带有Heroku数据库的AWS数据管道

本文介绍了带有Heroku数据库的AWS数据管道的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想知道将AWS Data Pipeline连接到Heroku数据库的可行性. heroku数据库存储在EC2实例(东部区域)上，并且需要SSL.

I'm wondering about the feasibility of connecting an AWS Data Pipeline to a Heroku Database. The heroku databases are stored on EC2 instances (east region), and require SSL.

我尝试使用JdbcDatabase对象打开连接，但是在任何时候都遇到问题.

I've tried to open up a connection using a JdbcDatabase Object, but have run into issues at every turn.

我尝试了以下操作:

{ "id" : "heroku_database", "name" : "heroku_database", "type" : "JdbcDatabase", "jdbcDriverClass" : "org.postgresql.Driver", "connectionString" : "jdbc:postgresql://#{myHerokuDatabaseHost}:#{myHerokuDatabasePort}/#{myHerokuDatabaseName}", "jdbcProperties": "ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory", "username" : "#{myHerokuDatabaseUserName}", "*password" : "#{*myHerokuDatabasePassword}" },

结果为

unable to find valid certification path to requested target ActivityFailed:SunCertPathBuilderException

以及:

{ "id" : "heroku_database", "name" : "heroku_database", "type" : "JdbcDatabase", "jdbcDriverClass" : "org.postgresql.Driver", "connectionString" : "jdbc:postgresql://#{myHerokuDatabaseHost}:#{myHerokuDatabasePort}/#{myHerokuDatabaseName}", "jdbcProperties": "sslmode=require", "username" : "#{myHerokuDatabaseUserName}", "*password" : "#{*myHerokuDatabasePassword}" },

结果为

amazonaws.datapipeline.database.ConnectionFactory: Unable to establish connection to jdbc:postgresql://ec2-54-235-something-somethingpute-1.amazonaws:5442/redacted FATAL: no pg_hba.conf entry for host "52.13.105.196", user "redacted", database "redacted", SSL off

要启动-我还尝试过使用ShellCommandActivity从ec2实例复制postgres表并将其stdout到我的s3存储桶中-但是ec2实例不理解psql命令:

To boot -- I have also tried to use a ShellCommandActivity to copy the postgres table from the ec2 instance and stdout it to my s3 bucket -- however the ec2 instance doesn't understand the psql command:

{ "id": "herokuDatabaseDump", "name": "herokuDatabaseDump", "type": "ShellCommandActivity", "runsOn": { "ref": "Ec2Instance" }, "stage": "true", "stdout": "#{myOutputS3Loc}/#{myOutputFileName}", "command": "PGPASSWORD=#{*myHerokuDatabasePassword} psql -h #{myHerokuDatabaseHost} -U #{myHerokuDatabaseUserName} -d #{myHerokuDatabaseName} -p #{myHerokuDatabasePort} -t -A -F',' -c 'select * #{myHerokuDatabaseTableName}'" },

，而且我也无法提前安装postgres.

and I also cannot yum install postgres beforehand.

同时使用RDS和Heroku作为我们的数据库源真是太糟糕了.关于如何通过数据管道使选择查询针对heroku postgres db运行的任何想法都将有很大的帮助.谢谢.

It sucks to have both RDS and Heroku as our database sources. Any ideas on how to get a select query to run against a heroku postgres db via a data pipeline would be a great help. Thanks.

推荐答案

似乎Heroku需要/想要Postgres 42.2.1驱动程序: devcenter.heroku/articles/heroku-postgresql#connecting-in-java .或者至少如果您正在编译他们告诉您使用的Java应用程序.

It looks like Heroku needs/wants the postgres 42.2.1 driver: devcenter.heroku/articles/heroku-postgresql#connecting-in-java. Or at least if you are compiling a java app that's what they tell you to use.

我无法找出数据管道默认使用的驱动程序，但是它允许您使用jdbcDriverJarUri并指定自定义驱动程序jar: docs.aws.amazon/datapipeline/latest/DeveloperGuide/dp-object-jdbcdatabase.html

I wasn't able to find out which driver Data Pipeline uses by default but it allows you to use the jdbcDriverJarUri and specify custom driver jars: docs.aws.amazon/datapipeline/latest/DeveloperGuide/dp-object-jdbcdatabase.html

这里重要的一点是它需要Java7，因此您将要使用postgres-42.2.1.jre7.jar: jdbc.postgresql/download.html

An important note here is that it requires Java7, so you are going to want to use the postgres-42.2.1.jre7.jar: jdbc.postgresql/download.html

与sslmode=require的jdbcProperties字段结合使用应允许它通过并创建您要查找的转储文件.

That combined with a jdbcProperties field of sslmode=require should allow it to go through and create the dump file you are looking for.