Cannot run stored procedures for postgreSQL in azure

Harditya Sarvaiya 1 Reputation point
2020-09-01T13:11:15.027+00:00

I have to migrate my PostgreSQL from on-premise to the Azure cloud. I have stored procedures in the on-premise database. But I cannot find a way to run those on Azure Database for Postgresql. There is no option for PostgreSQL as a source for dataflows or stored procedures inside the ADF. Is there any workaround for this?

Azure Database for PostgreSQL

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Navtej Singh Saini 4,221 Reputation points Microsoft Employee
    2020-09-02T18:30:27.773+00:00

    @Harditya Sarvaiya

    ADF does not support a built-in PostgreSQL stored procedure. As a workaround, you can consider using an Azure Function with ADF that runs the logic you want.

    Don't have exact working example for this but will work mostly like:
    module.exports = async function (TableName, LastModifiedDate) {
    var pg = require('pg');
    //const config = "postgres://<username>:<password>@<postgres servername>:5432/<database>"; 

            const config = {  
                            host: 'servername.postgres.database.azure.com',  
                            user: 'user@servername',       
                            password: <password>,  
                            database: '',  
                            port: 5432,  
                            ssl: true  
        };  
          
        var client = new pg.Client(config);  
        const query = ' UPDATE mytable  
      SET [Value] = @LastModifiedtime   
WHERE [TableName] = @TableName';  
        context.log(query);  
        client.connect();  
        client.query(query);  
        await client.end();  
        context.log('insert completed successfully!');        
    };

    There are other methods to migrate you can look into like using DMS or Export/Import

    Hope this helps.

    Thank you
    Navtej S

    0 comments