none
Amazon RDS (PostgreSQL) as source DB RRS feed

  • Question

  • I am trying to connect to a PostgreSQL database that is hosted in AWS and am receiving an error.  I am using the Copy Data Wizard (I have tried v1 and v2) and have tried using source connection type of "PostgreSQL" and Amazon Redshift.  Neither work correctly and give different errors.  

    I believe that PostgreSQL doesn't work because this connection type is for an "On-Prem" instance of PostgreSQL and would require a data gateway installation on the network.  This doesn't apply since the PostgreSQL DB is in AWS.

    When I try Amazon Redshift I get an error that says "could not connect to server: Connection timed out". 

    Is the issue that AWS RDS databases aren't supported? Or am I missing something else?

    Wednesday, January 24, 2018 8:35 PM

All replies

  • Hey Frank,

    Thanks for reporting this. AWS RDS PostgreSQL is actually supported. We need to improve the experience, and add another icon at Copy Wizard v2, to show as "Amazon PostgreSQL". before that, as a temp workaround, you can click "Azure Database for PostgreSQL" to start to config settings, to connect to "Amazon PostgreSQL". That is the way you do no need to have self-hosted IR (aka, Gateway) installed. 

    Feel free to try and share back whether it works.

    thx,

    -Oliver


    Oliver Yao - MSFT

    Thursday, January 25, 2018 1:23 AM
  • Hi Oliver, thanks for the reply.  I switched over to ADF v2 to try and use the "Azure Database for PostgreSQL" like you recommended.  Here's the settings I tried:

    Network Environment: Public Network in Azure Environment

    Server/Database selection method: Enter Manually

    Server Name: <MyAmazonAWSserver.com>

    Database: <MyDB>

    When I try and click Next it gives me a "Connect refused.  Verify Host Name and Port Number." error.  I have verified the Host Name and Port Number with no luck.  

    I've tried creating a pipeline through code and there's no place to even enter a server value for "azure database for PostgresSQL". It asks me for the integration runtime I want to use and that's it.

    I would love a list of steps on how to connect to an AWS PostgreSQL RDS server/db as a source and ADL as a Sink because I'm having no luck.  Thanks again!

    Wednesday, January 31, 2018 4:55 PM
  • I'm starting to believe this connection type isn't supported yet.  So for now I'm testing other work-arounds like spinning up SSIS in v2 which isn't ideal.
    Friday, February 2, 2018 3:00 PM
  • I was finally able to get this to work.  This post helped me get there: https://stackoverflow.com/questions/33249814/npgsql-3-0-3-error-with-power-bi-desktop

    I had to install the npgsql driver on the server that holds my data gateway.  Then I had to import the AWS public certificate.  Then I was able to edit the json and connect like I was hoping to.  Hopefully this helps someone else in the future.

    Tuesday, February 6, 2018 8:32 PM
  • Hey Frank,

    I am able to connect using Azure Database for Postgre however seeing a very strange issue. When i test the connection it got successful and then i saved the same.

    I opened linked service again and testing the connection it is giving error "password authentication failed", when i again paste the password it works fine and when i save and reopen it gives me same error :(

    please advise

    Regards,

    Ashish

    Tuesday, August 13, 2019 2:34 PM
  • Dear All,

    I finally managed to resolve this issue. Azure PostgreSQL connection would not work at all in this case.

    Resolution :

    Please install driver npgsql (latest version) on your machine/VM and then select POSTGRESQL in Linked Service.

    GUI seems not be working in this case so type JSON to make the connection. Refer below

    {
        "name": "<Linked service name>",
        "properties": {
            "type": "PostgreSql",
            "typeProperties": {
                "server": "<server name>",
                "database": "<db name>",
                "username": "<uid>",
                "password": {
                    "type": "SecureString",
                    "value": "<pwd>"
                }
            },
            "connectVia": {
                "referenceName": "<intergrationruntime>",
                "type": "IntegrationRuntimeReference"
            }
        }
    }

    Regards,

    Ashish

    Wednesday, August 14, 2019 11:00 AM
  • Hi Ashish,

    Thanks for sharing your findings. Appreciate for posting the resolution details here. It will be helpful for other members of the community who had similar issue. :)

    Additional info: Just an FYI, a similar issue was discussed in Stackoverflow. https://stackoverflow.com/questions/48586753/azure-data-factory-aws-postgresql-rds-as-source-connection 


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Wednesday, August 14, 2019 1:25 PM
    Moderator