none
Why does my azure managed postgresql server terminates the connection prematurely in ADF Copy activity RRS feed

  • Question

  • Hello

    I have an Azure managed PostgreSQL Server. I am copying data from a table in a database on this server to an Azure managed MySQL Server database. The size if the table can be between 900,000 or 1,200,000 records. I am doing this using the Azure Data Factory Copy activity. The data transfer works seamlessly when I test it with 100 or 9,000 rows. I have been able to copy the whole table from PostgreSQL to MySQL in the past, it completed around 17 hours.

    Now, with the same connections set up, the process will run for up to 9 hours, then the PostgreSQL will close the connection. I have moved the DIU and Parallel copy activities up to 4 in hopes of finishing the copy sooner by giving the copy activity more resources, as the docs recommend. This still results in the process terminating prematurely. 

    Why is the connection being terminated?

    from server logs:

    2019-12-03 18:43:46 UTC-5de6ace1.45f28-LOG:  00000: disconnection: session time: 0:00:01.000 user=azure_superuser database=postgres host=xxx.x.x.x port=xxxxx
    2019-12-03 18:43:46 UTC-5de6ace1.45f28-LOCATION:  log_disconnections, postgres.c:4684
    2019-12-03 18:44:36 UTC-5de6acad.45e54-LOG:  08006: could not send data to client: An existing connection was forcibly closed by the remote host.
    2019-12-03 18:44:36 UTC-5de6acad.45e54-LOCATION:  internal_flush, pqcomm.c:1486
    2019-12-03 18:44:36 UTC-5de6acad.45e54-FATAL:  08006: connection to client lost
    2019-12-03 18:44:36 UTC-5de6acae.45e60-FATAL:  57P01: terminating connection due to administrator command
    2019-12-03 18:44:36 UTC-5de6acae.45e5c-FATAL:  57P01: terminating connection due to administrator command
    2019-12-03 18:44:36 UTC-5de6acae.45e5c-LOCATION:  ProcessInterrupts, postgres.c:3009
    2019-12-03 18:44:36 UTC-5db4fb3a.28-LOG:  00000: worker process: parallel worker for PID 286292 (PID 286300) exited with exit code 1

    It is saying that admin is closing connection, this is not true, no one is terminating this command manually.

    From Azure data factory debug console:

    { "errorCode": "2200", "message": "Failure happened on 'Source' side. 'Type=Npgsql.NpgsqlException,Message=Exception while reading from stream,Source=Npgsql,''Type=System.IO.IOException,Message=Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.,Source=System,''Type=System.Net.Sockets.SocketException,Message=A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond,Source=System,'", "failureType": "UserError", "target": "CopyTable_ToStage", "details": [] }

    Is there settings I can give my PostgreSQL server to let Azure Data Factory to take as long as it needs to finish the copy without cutting connection?

    Can I give my PostgreSQL more memory or CPU to to accommodate the big transfer? It is already at 8 vCores.

    Tuesday, December 3, 2019 7:20 PM

Answers

  • If you are using connection string in Linked service, try adding command timeout = <some higher value> 

    else check if there is any parameter to increase timeout at server level.

    https://docs.microsoft.com/en-us/azure/postgresql/howto-configure-server-parameters-using-portal


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    • Marked as answer by samkg Friday, December 6, 2019 9:05 PM
    Wednesday, December 4, 2019 5:40 AM
  • I have resolved this issue. I was copying from a VIEW on the PostgreSQL database, into a MySQL table. The query is a long query and would time out. It was better to run that long query and save it to a table on the postgreSQL side, and then copy that postgreSQL table to the MySQL table with ADF. 

    Long queries were the problem, this is not a limitation of the tool but bad planning/implementation on my part. Thank you for the answers and help. Much appreciated.


    Friday, December 6, 2019 9:05 PM

All replies

  • If you are using connection string in Linked service, try adding command timeout = <some higher value> 

    else check if there is any parameter to increase timeout at server level.

    https://docs.microsoft.com/en-us/azure/postgresql/howto-configure-server-parameters-using-portal


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    • Marked as answer by samkg Friday, December 6, 2019 9:05 PM
    Wednesday, December 4, 2019 5:40 AM
  • Hi samkg,

    If the above suggestion from Vaibhav did not resolve your issue, please do share your pipeline run ID and activity run ID for further investigation.


    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, December 4, 2019 7:00 PM
    Moderator
  • I have attempted his suggestion, I added parameter "timeout" with value 1024 (MAX it goes up to). This made no difference. I have also adjusted the server parameters to allow for as much time and resources. Still the query does not reach completion. 

    Pipeline run ID: fb9913ad-a8bd-4c36-891b-bfeb6262dbaa

    Wednesday, December 4, 2019 10:08 PM
  • Hi samkg,

    Thanks for your response and details. I will reach out to internal team to get more insights about your issue and will get back to you once I have an update.

    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, December 4, 2019 10:39 PM
    Moderator
  • I have resolved this issue. I was copying from a VIEW on the PostgreSQL database, into a MySQL table. The query is a long query and would time out. It was better to run that long query and save it to a table on the postgreSQL side, and then copy that postgreSQL table to the MySQL table with ADF. 

    Long queries were the problem, this is not a limitation of the tool but bad planning/implementation on my part. Thank you for the answers and help. Much appreciated.


    Friday, December 6, 2019 9:05 PM