PostgreSQL connector has failures with Operation on target Extract postgresql data failed: Failure happened on 'Source' side. 'Type=Npgsql.PostgresException,Message=25006: cannot execute UNLISTEN during recovery,Source=Npgsql,'

Question

Hello,

We are trying to switch from the legacy Postgresql connector to the current one in ADF. We are able to connect and pull data however we are intermittently getting the error:

Operation on target Extract postgresql data failed: Failure happened on 'Source' side. 'Type=Npgsql.PostgresException,Message=25006: cannot execute UNLISTEN during recovery,Source=Npgsql,'

We've tried adding various versions of "No Reset On Close" manually to the Linked Service json (there was no option to select that under Additional connection properties in the UI) but we still get the error on various table extractions in the Copy data and Lookup activity.

        "noResetOnClose": "true",

        "NoResetOnClose": "true",

        "No_Reset_On_Close": "true",

        "No Reset On Close": "true"

The source Postgresql servers are replicas reading from Hot Standby (necessary for our company) and are running on 9.3.23 and 9.3.25 which MS ADF documentation says this connector supports anything above 7.4 . I've also tried setting Pooling to 'No' which also did not fix it.

Anyone know how to get this to work consistently?

Our legacy ADF connector pulls data without error but we need to switch over.

Thanks for any help!

    "name": "LS_PostgreSQL_v2",
    "properties": {
        "parameters": {
            "server": {
                "type": "string"
            },
            "database": {
                "type": "string"
            },
            "port": {
                "type": "string"
            },
            "username": {
                "type": "string"
            },
            "password_secret_name": {
                "type": "String"
            }
        },
        "annotations": [],
        "type": "PostgreSqlV2",
        "typeProperties": {
            "server": "@{linkedService().server}",
            "port": "@{linkedService().port}",
            "database": "@{linkedService().database}",
            "username": "@{linkedService().username}",
            "password": {
                "type": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "LS_KeyVault",
                    "type": "LinkedServiceReference"
                },
                "secretName": {
                    "value": "@linkedService().password_secret_name",
                    "type": "Expression"
                }
            },
            "sslMode": 2,
            "authenticationType": "Basic",
            "connectionTimeout": 600,
            "commandTimeout": 0,
            "noResetOnClose": "true",
            "NoResetOnClose": "true",
            "No_Reset_On_Close": "true",
            "No Reset On Close": "true"
        },
        "connectVia": {
            "referenceName": "xxxxx",
            "type": "IntegrationRuntimeReference"
        }
    },
    "type": "Microsoft.DataFactory/factories/linkedservices"
}

Answer 1

Thanks for posting your question in the Microsoft Q&A forum.

You’ve already tried setting "noResetOnClose": "true" in your Linked Service configuration, which is a good step. However, let’s ensure that connection pooling is also disabled. Set "Pooling": "false" in your Linked Service configuration to prevent connection pooling. This can help avoid issues related to connection reuse.

In addition, you can consider using "sslMode": 0 (disable SSL) or "sslMode": 1 (allow SSL but don’t require it) to see if it resolves the issue.

** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful **